升级可能导致数据库不可用（一）

升级过程也是一个危险的过程，操作不当可能造成数据库无法打开，因此在升级之前应该做好备份。

描述问题产生的原因。

 

 

尝试将一个9.2.0.4的数据库手工升级为11.2.0.1。但是升级过程中犯了一个小错误，在升级之前没有执行11.2的ORACLE_HOME/rdbms/admin/utlu112i.sql。

就是这个小错误，导致数据库升级失败。

在执行catupgrd.sql脚本时，出现了ORA-00942: table or view does not exist错误：

SQL> spo upgrade.sql
SQL> @?/rdbms/admin/catupgrd
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The first time this script. is run, there should be no error messages
DOC>   generated; all normal upgrade error messages are suppressed.
DOC>
DOC>   If this script. is being re-run after correcting some problem, then
DOC>   expect the following error which is not automatically suppressed:
DOC>
DOC>   ORA-00001: unique constraint () violated
DOC>              possibly in conjunction with
DOC>   ORA-06512: at "", line NN
DOC>
DOC>   These errors will automatically be suppressed by the Database Upgrade
DOC>   Assistant (DBUA) when it re-runs an upgrade.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
.
.
.

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause:
DOC>   - "ORA-00942: table or view does not exist" ; or
DOC>   - "ORA-00904: "TZ_VERSION": invalid identifier"" ; or
DOC>   - "ORA-01722: invalid number"
DOC>     if the pre-upgrade utility (utlu112i.sql) has not been run to:
DOC>     a) create and update registry$database table to include the current
DOC>        database timezone file version used in the old release; or
DOC>     b) do inserts into sys.props$.
DOC>
DOC>     o Action:
DOC>       Shutdown ABORT and revert to the original ORACLE_HOME.  Then run
DOC>       utlu112i.sql to populate registry$database with the database timezone
DOC>       file version used by the lower version database and to populate
DOC>       sys.props$ with Day Light Saving Time (DST) properties information.
DOC>
DOC>   OR
DOC>   - An "ORA-01722: invalid number"
DOC>     if the old release uses a timezone file version newer than 8 (shipped with
DOC>     11.2) but the new release has not been patched yet.
DOC>
DOC>     o Action:
DOC>       Shutdown ABORT and patch new ORACLE_HOME to the same timezone file
DOC>       version as used in the old ORACLE_HOME.
DOC>
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
       TO_NUMBER(value$) != (SELECT tz_version from registry$database))
                                                    *
第 6 行出现错误:
ORA-00942: 表或视图不存在

从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

根据Oracle给出的解决方法，需要设置9.2的ORACLE_HOME，启动数据库执行11.2的ORACLE_HOME/rdbms/admin/utlu112i.sql。但是由于已经设置了COMPATIBLE为11.2.0.1.0，并启动过数据库，导致9i环境已经无法打开数据库，启动将报错：

[oracle@bjtest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 4月 30 22:05:47 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown abort
ORACLE 例程已经关闭。
SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
[oracle@bjtest ~]$ export ORACLE_HOME=/opt/oracle/product/9.2
[oracle@bjtest ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@bjtest ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期五 4月 30 22:06:42 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

已连接到空闲例程。

SQL> startup pfile=inittest9.ora
ORA-00401: the value for parameter compatible is not supported by this release

初始化参数的值COMPATIBLE设置为11.2.0.1.0，在9i是无法启动的，但是即使改成9.2.0.1.0，由于已经更改了CONTROL_FILE，因此也是无法启动的。

修改初始化参数后，尝试启动：

SQL> startup pfile=inittest9.ora
ORACLE 例程已经启动。

Total System Global Area  657246184 bytes
Fixed Size                   743400 bytes
Variable Size             385875968 bytes
Database Buffers          268435456 bytes
Redo Buffers                2191360 bytes
ORA-00201: ?????? 9.2.0.0.0 ? ORACLE ?? 9.2.0.0.0 ???
ORA-00202: ????: '/data/oradata/test9/control01.dbf'

而且这时在11g的环境下，利用UPGRADE方式启动，也无法调用utlu112i.sql了，调用会报错：

[oracle@bjtest ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期五 4月 30 22:11:26 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> shutdown abort
ORACLE 例程已经关闭。
SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production中断开
[oracle@bjtest ~]$ export ORACLE_HOME=/data/oracle/product/11.2
[oracle@bjtest ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@bjtest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 4月 30 22:12:00 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

已连接到空闲例程。

将初始化参数改回为11.2.0.1.0，利用UPGRADE启动数据库：

SQL> startup pfile=inittest9.ora upgrade
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORACLE 例程已经启动。

Total System Global Area  618012672 bytes
Fixed Size                  2215784 bytes
Variable Size             343933080 bytes
Database Buffers          268435456 bytes
Redo Buffers                3428352 bytes
数据库装载完毕。
数据库已经打开。
SQL> @?/rdbms/admin/utlu112i.sql
ERROR:
ORA-04023: 无法验证或授权对象 SYS.STANDARD

DECLARE
*
第 1 行出现错误:
ORA-04023: 无法验证或授权对象 SYS.STANDARD

ERROR:
ORA-04023: 无法验证或授权对象 SYS.STANDARD

现在已经陷入了两难的境地，在9i和11g都无法成功的打开数据库。如果在升级操作之前没有备份，就很可能导致数据库的彻底崩溃。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-671429/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-671429/