今天在做一个测试库的升级，本以为和由10.2.0.1升级至10.2.0.4那样，于是直接就做了，没有去做由10g升级至11g之前的准备工作。
于是直接就使用11g的软件把10g的数据库启动到upgrade状态，然后开始执行升级脚本：
@catupgrd.sql
结果这个脚本一执行就报错了，检查发现时在执行下面的select语句时报错的：
SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
FROM sys.props$
WHERE
(
(
(0 = (select count(*) from registry$database))
OR
((SELECT tz_version from registry$database) is null)
)
AND
(
((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
'9.2.') OR
((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
'10.1') OR
((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
'10.2') OR
((SELECT substr(version,1,4) FROM registry$ where cid = 'CATPROC') =
'11.1')
)
);错误内容为：
SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
*
ERROR at line 1:
ORA-01722: invalid number
其实这条SQL的目的就是为了检查是否执行过pre-upgrate tool脚本【utlu112i.sql】，该脚本会往表【registry$database】里面添加一条和TimeZone有关的数据。
未执行预升级工具前这个表的结构和数据如下：
[racdb1@oracle]$ sqlplus / as sysdba而如果执行了预升级工具脚本后表【registry$database】里面会多了一个叫【TZ_VERSION】的列，同时：
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 28 12:48:52 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
12:47:21 its1@SYS> desc registry$database
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
EDITION VARCHAR2(30)
12:48:52 its1@SYS> select * from sys.registry$database;
PLATFORM_ID
-----------
PLATFORM_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
EDITION
------------------------------------------------------------
6
AIX-Based Systems (64-bit)
[cs3@oracle]$ sql即执行该工具后会修改数据库的TimeZone版本：
SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 28 12:51:55 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc registry$database
Name Null? Type
----------------------------------------- -------- ----------------------------
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
EDITION VARCHAR2(30)
TZ_VERSION NUMBER
SQL> select * from sys.registry$database;
PLATFORM_ID
-----------
PLATFORM_NAME
--------------------------------------------------------------------------------
EDITION TZ_VERSION
------------------------------ ----------
6
AIX-Based Systems (64-bit)
14
以下是10g的TimeZone版本信息：
12:50:39 its1@SYS> select * from v$timezone_file;以下是11g的TimeZone版本信息：
FILENAME VERSION
------------------------ ----------
timezlrg.dat 4
SQL> select * from v$timezone_file;【附注】
FILENAME VERSION
-------------------- ----------
timezlrg_14.dat 14
根据当前timezone的版本，又分三种情况：
1）等于14：这已经是11g需要的版本了，所以升级前后都不需要做任何事，这种情况很罕见。
2）高于14：升级前，必须得给11g软件打上该timezone版本的DST补丁，这种情况也很罕见。
3）低于14：大多数都是这种情况，在升级前不需要在11g软件层面打补丁，在升级后需要再数据库层面将Timezone升级至14，具体看后面的步骤
综上，由于我没有安装正常的升级流程进行升级工作，所以我执行升级脚本时就报错了，解决方法就是重建表【registry$database】，并手工往里面插入一条记录：
SQL> CREATE TABLE registry$database(注意：虽然表之前已经存在，但是可以不先drop，可以直接create，不过如果是直接create的话，表中的【数据不会被清除】，而只是【新增列】，因此还需要手工插入一条数据。
2 platform_id NUMBER,
3 platform_name VARCHAR2(101),
4 edition VARCHAR2(30),
5 tz_version NUMBER
6 );
另：为了避免数据重复，可先做一次truncate或者delete操作，清空数据后再插入。
SQL> truncate table registry$database;
SQL> INSERT into registry$database
2 (platform_id, platform_name, edition, tz_version)
3 VALUES
4 ((select platform_id from v$database),
5 (select platform_name from v$database),
6 NULL,
7 (select version from v$timezone_file));
题外话：当我执行升级脚本失败后尝试使用10g的软件启动数据库去执行预升级工具脚本时，我又遇到了另一个问题ORA-00201：
10:10:25 srcbfat@SYS> startup网上都是需要把数据库的参数文件里面的【compatible】参数修改为错误信息里面提示的就可以了，具体我没有试过但是我觉得应该是不行的。
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2084400 bytes
Variable Size 1006633424 bytes
Database Buffers 587202560 bytes
Redo Buffers 14692352 bytes
ORA-00201: control file version 11.2.0.0.0 incompatible with ORACLE version 10.2.0.3.0
ORA-00202: control file: '/soft/oracle/oradata/srcbfat/control01.ctl'
因为，数据库软件的版本只有10g，不可能把compatible参数设置为11g的，这不科学。
所以，我只能有返回去使用11g启动到upgrade然后再重建表。
总结，以后升级还是需要一步一步来，一步一个脚印，不能跳，也不能省，这会出事儿。
最后附上Oracle官文：
catupgrd.sql fails With ORA-01722 Invalid Number after running the Pre-Upgrade Script [ID 1466464.1] |
Modified:19-Sep-2012Type:PROBLEMStatus:PUBLISHEDPriority:3 |
|
In this Document
|
|
|
Applies to:
Oracle Server - Enterprise Edition - Version 10.2.0.3 and later
Information in this document applies to any platform.
While upgrading database version homes, the upgradeprocess fails with the below error:
SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
*
ERROR at line 1:
ORA-01722: invalid number
This Error is reported even after running the Pre-Upgrade Script &when the latest/acceptable DST Patch is applied on both the Source & TargetOracle Homes.
1: If you didnot run the pre-upgrad script, this action described in this note can causecorruption of the database.
2: The TZ valueyou insert into that table MUST be the proper value of the pre-upgraded DB. Anyother value can cause corruption of the database.
The Pre-Upgrade Script is not creating the registry$database table& inserting the Platform DST Patch Information.
The error may also appear if the the registry$database tableexists, but does not contain the required information.
1) If the registry$database table does not get created by thePre-Upgrade Script,then it may be created using the below SQL statement:
SQL> 'CREATE TABLE registry$database(
platform_id NUMBER,
platform_name VARCHAR2(101),
edition VARCHAR2(30),
tz_version NUMBER
)';
2) Then manually insert the Platform DST Patchinformation using the below SQL statement:
SQL> 'INSERT intoregistry$database
(platform_id, platform_name, edition, tz_version)
VALUES ((selectplatform_id from v$database),
(select platform_name from v$database),
NULL,
(select version from v$timezone_file))';
3) Query the newly created Table for the accurate results.
SQL> select * from sys.registry$database;
The output should look similar to the one shown below.
PLATFORM_ID PLATFORM_NAME EDITION TZ_VERSION
--------------------------------------------------------------------------------
6 AIX-Based Systems(64-bit) 17
If you are able to get the required output, then you may switch tothe Target Home & start the Manual Upgrade from there.
If the registry$database table exists but does not contain the requiredinformation then the pertinent DST information can be added using the Commandprovided in Step 2.
BUG:14174083 - PRE-UPGRADE FAILS TO CREATE REGISTRY$DATABASE& POPULATE THE TZ VALUE
NOTE:1351112.1 - Information Center: Upgradingand Migration Oracle Database
NOTE:1152016.1 - Master Note For Oracle DatabaseUpgrades and Migrations
Troubleshooting ORA-201 ORA-202 [ID 948710.1] |
|
修改时间: 2012-1-11类型:TROUBLESHOOTING状态:PUBLISHED优先级:3 |
|
In this Document
Purpose
LastReview Date
Instructionsfor the Reader
TroubleshootingDetails
References
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.3- Release: 9.2 to 11.2
Information in this document applies to any platform.
***Checked for relevance on 11-Jan-2012***
Purpose
This document aims to explain the circumstances when you mayreceive the below errors and what to consider in such cases:
- ORA-201: control file version <num> incompatible with Oracle version<num>
- ORA-202: control file: <name>
LastReview Date
October 6, 2009
Instructionsfor the Reader
A TroubleshootingGuide is provided to assist in debugging a specific issue. When possible,diagnostic tools are included in the document to assist in troubleshooting.
TroubleshootingDetails
GettingORA-201 and ORA-202 is directly connected to the COMPATIBLE initializationparameter:
- either you are trying to set COMPATIBLE to a wrong value
- or an incompatible operation is performed with regards to the current setting of COMPATIBLE
Significanceand use of COMPATIBLE parameter:
- COMPATIBLE allows you to use a new release of Oracle, while at the same time guaranteeing backward compatibility with an earlier release. This is helpful if it becomes necessary to revert to the earlier release.
- It specifies the release with which Oracle needs to keep compatibility, at the same time permitting you to use some of the advantages of a newer release. Trying to use the new specific features which require compatibility set to the higher release will return errors.
Somerequirements and restrictions that will help you to avoid ORA-201/ORA-202:
1.At upgrade/downgrade:
- For upgrade from 9.2.0.x to 11g it is mandatory to set the COMPATIBLE parameter to at least 10.1.0. After the upgrade has been completed successfully, it can be further increased accordingly if necessary.
- For upgrade from 10.1.0.x or 10.2.0.x to 11g you can leave the COMPATIBLE parameter set to its current value until the upgrade has been completed successfully.
- Downgrades from 11g to 9iR2 are not supported. This is because in the upgrade process the compatible parameter is set to a minimum 10.1.0. This prevents downgrades. For additional information, see
Note 388604.1 - ORA-00201 while downgrading from 10gR2 to 10gR1 or 9iR2
2.At database creation:
- For 11.2, the default value of the COMPATIBLE parameter is 11.2.0. The minimum value is 10.0.0.
- For 11.1, the default value of the COMPATIBLE parameter is 11.1.0. The minimum value is 10.0.0.
- For 10.2, the default value of the COMPATIBLE parameter is 10.2.0. The minimum value is 9.2.0.
- For 10.1, the default value of the COMPATIBLE parameter is 10.0.0. The minimum value is 9.2.0.
- For 9.2, the default value of the COMPATIBLE parameter is 8.1.0. The range of values is between 8.1.0 to the current release.
NOTE:
For 10.1 - 11.2, if you create an Oracle Database using the default value, youcan immediately use all the new features in this release, and you can neverdowngrade the database.
Note that a new database can in fact never be downgraded because you can onlydowngrade to the last version you actually upgraded from.
3.When lowering the value of COMPATIBLE:
- You cannot start the database with lower compatibility, unless restoring from a backup. This is because datafiles contain COMPATIBLE information in their headers. If the parameter is increased and the database is then restarted, the datafile headers will be updated to the higher release. If then you decide to lower the parameter, this will fail with ORA-201/ORA-202.
- A default value is considered lower than a specific patchset value for the setting of the COMPATIBLE parameter. E.g. 10.2.0 is considered lower than 10.2.0.1, 10.2.0.2 etc.
What to do if you getORA-201/ORA-202
NOTE:
As with all destructive actions, depending on the solution you will considerfrom below, Oracle Corporation strongly advises taking a FULL COLD BACKUP ofthe database before these actions are performed.
- Set the COMPATIBLE initialization parameter to an appropriate value.
- The impossibility to lower COMPATIBLE in some cases should not be confused with the ability to downgrade a database. It is possible to 'downgrade' a database but only as far as the compatible value setting. That is, if the db has been operating with compatible=9.2.0, you can downgrade from 10.x to 9.2.0.1.0. If the db was operating with compatible=10.1.0, you can downgrade to 10.1.0.2.0 (initial 10g Release). Outside of these restrictions you must use export/import to downgrade.
- Another option, if you need the COMPATIBLE setting lowered, you can do a point-in-time recovery to a time before the compatibility was advanced.
- Recreate the control files to make them compatible with the needed release. There is a problem at controlfile level when the error message does not contain version numbers: "control file version incompatible with Oracle version" . This may indicate that the controlfile is corrupt. This may also help when no backup is available
Other observations
- to avoid the errors in RAC the instances must have the COMPATIBLE parameter set to the same value
- to avoid the errors in standby database, this parameter must have the same value on both the primary and standby databases
References
NOTE:30910.1 - Recreating Database Objects
NOTE:429825.1 - Complete Checklist for Manual Upgradesto 11gR1
NOTE:883335.1 - How To Downgrade From Database 11.2To Previous Release (includes 11.2.0.3-11.2.0.1)