ORACLE11.1升级到11.2报错

钱多多

升级数据库，从11.1升级到11.2.0.3.
装好11.2.0.3后，执行升级脚本catupgrd.sql，结果提示报错。

信息如图：


求大侠指导。。。

Stone

可以参考一下，希望有所帮助 :)

第一个跟你的环境比较相似可以参考下：

http://narenapps.blogspot.com/2012/11/oracle-11107-to-11gr2-upgrade-steps.html

Oracle 11.1.0.7 to 11gR2 Upgrade Steps
1. Download and install the new 11gR2 Oracle in the target machine.

2. After installation copy the ORACLE_HOME/rdbms/admin/utlu112i.sql to some /tmp location as the

script needs to be executed in the 11.1.0.7 box as pre-requisite step.

Pre-requisite Upgrade Steps in the source(11.1.0.7) box

1. Run the utlu112i.sql script which was copied to /tmp location in the earlier steps.

2. If the script was not run before the upgrade then the upgrade will fail while running the catupgrd.sql.

    Common error which we will face when the above script was not run before the upgrade will be

SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
2 FROM registry$database
3 WHERE tz_version != (SELECT version from v$timezone_file);
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
*
ERROR at line 1:
ORA-01722: invalid number

3.Login as / as sysdba and run the below commands

      sql> spool upgrade_pre.log
      sql>@utlu112i,sql
      sql>spool off

4. The above spool file will validate whether the upgrade requirements are met or not like it will display the tablespace info,update parameters,deprecated parameters...etc.

5. Run the /rdbms/admin/utlrp.sql to validate invalidate objects.

6. For 11.1.0.7 no need to apply any DST related patches and we can take after the db upgrade.

7. Run the DBMS_STATS.GATHER_DICTIONARY_STATS.
  
     EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

8. No files should be in backup mode.

    sql>select * from v$recover_file;
    sql>select * from v$backup where status != 'NOT ACTIVE';

9. The users SYS and SYSTEM have 'SYSTEM' as their default tablespace.

10. Shutdown the db and take the cold backup or backup using rman.

11. Currently taken the cold backup like copied all the redo logs,datafiles,temp files,control files...to the target db.

Upgrade Steps in Target

1. Set the ENV in the target box(11gR2).

2. Currently copied the init.ora and modified according to the target db.

3. login as / as sysdba

    1. Startup nomount

    2. Recreate the Control file (If the target box is new)

    3. alter database open resetlogs upgrade;

    4. spool upgrade.info

     5.@/rdbms/admin/catupgrd.sql

     6. spool off

12. Will discuss the DST settings in the next blog.

http://hkaddachi.blogspot.com/2012/08/upgrade-10gr2-database-to-11gr2-database.html

4. Pre-Upgrade Tool
To prepare your 10gr2 database for upgrade, you must first download the scirpt utility utlu112i_sql.  Refer to My Oracle Support Document 884522.1: How to Download and Run Oracle's Database Pre-Upgrade Utility for details on downloading and running the Pre-Upgrade Utility. This SQL script checks the following:
1. Database configuration: Determines if the logfiles and datafiles are sized adequately for the upgrade
2. Initialization parameters: Reports which initialization parameters need changing, replacing or removing before the upgrade
3. Components: Which installed components will be upgraded
4. Miscellaneous Warnings: Any other situations requiring attention before the upgrade
5. Required tablespace: Ensure that the SYSAUX tablespace is created in the current database BEFORE the upgrade is carried out.
6. Timezone file version: Reports which file version is used and when/how to upgrade the timezone version.


Failure to run the pre-upgrade tool (utlu112i.sql) will result in the following error while running the catupgrd.sql script :
SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
2 FROM registry$database
3 WHERE tz_version != (SELECT version from v$timezone_file);
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
*
ERROR at line 1:
ORA-01722: invalid number

http://basklaassen.blogspot.com/2008/04/database-uprade.html

Database upgrade...
Last weekend I started the database upgrade from 10gr2 to 11gr1. As I mentioned before, I decided to manually upgrade the database. Before upgrading I already recieved an error.

[oracle@ebs2 admin]$ sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Apr 10 08:19:14 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1304704 bytes
Variable Size 411043712 bytes
Database Buffers 645922816 bytes
Redo Buffers 13062144 bytes
Database mounted.
Database opened.
SQL> set echo on
SQL> spool upgrade.log
SQL> @catupgrd.sql
Upgrade error :
SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
2 FROM registry$database
3 WHERE tz_version != (SELECT version from v$timezone_file);
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')

*ERROR at line 1:
ORA-01722: invalid number

Have to upgrade the timezone to 4.

Current timezone :
SQL> select * from v$timezone_file;
FILENAME VERSION
------------ ----------
timezlrg.dat 2

To upgrade my timezone to version 4, I applied the following patches :
1. 5601428
2. 5746835
3. 5632264, NEED UPDATED TIMEZONE FILES (VERSION 4) FOR MORE DST RULE CHANGES

After upgrading the timezone I started teh upgrade again, and this time the catupgrd.sql went fine...
Today I will finish the database upgrade following metalink note 429825.1, Complete checklist for Manual Upgrade to 11gR1.

http://docs.oracle.com/cd/B28359_01/server.111/b28300/upgrade.htm#UPGRD12395

TIMESTAMP WITH TIME ZONE Data Type

The time zone files that are supplied with Oracle Database 11g Release 1 (11.1) have been updated from version 2 to version 4 to reflect changes in transition rules for some time zone regions. The changes might affect existing data of TIMESTAMP WITH TIME ZONE data type. If you have not already updated your existing databases to time zone file version 4 (patch 5632264 - TZ V4 file), you must do so prior to upgrading to Oracle Database 11g Release 1 (11.1).

If your existing databases are already using a time zone file version greater than 4, then after installing Oracle Database 11g Release 1 (11.1), you must patch the ORACLE_HOME with the appropriate patch for the time zone file version in use with the databases you will be upgrading.

If the time zone file version used by the Oracle Database 11g Release 1 (11.1) server does not match the time zone file version that was used with the existing database being upgraded, then the upgrade script will terminate without upgrading the database. The TIMESTAMP WITH TIME ZONE data stored in the database can be corrupted during the upgrade if there is a time zone file version mismatch.

See Also:
Oracle Database Globalization Support Guide for a detailed description of time zone upgrade, and the following Metalink notes:
Note 359145.1 "Impact of 2007 USA daylight saving changes on the Oracle database"

Note 414590.1 "Time Zone IDs for 7 Time Zones Changed in Time Zone Files >=V3"