一個客戶要將現有的Oracle Database 11.1.0.7 for Windows升級到Oracle Database 11.2.0.3.4 for Linux。在測試過程中，使用Post-Upgrade Status Tool驗證升級結果的時候收到如下資訊：

SQL> @utlu112s.sql

.

Oracle Database 11.2 Post-Upgrade Status Tool 12-16-2012 06:52:53

.

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

.

Oracle Server

. ORA-06550: line 2, column 9:

. PLS-00302: component "ORA$_SYS_REP_AUTH" must be declared

. ORA-06550: line 2, column 2:

. PL/SQL: Statement ignored

. ORA-04063: package body "SYS.DBMS_LOGMNR_INTERNAL" has errors

. ORA-06508: PL/SQL: could not find program unit being called:

. "SYS.DBMS_LOGMNR_INTERNAL"

. ORA-06512: at "SYS.DBMS_LOGMNR_D", line 135

. ORA-06512: at line 32

. ORA-06575: Package or function DBMS_DEFER_QUERY_UTL is in an invalid state

. ORA-04063: view "SYS.DEFCALL" has errors

. ORA-04063: package body "SYS.DBMS_AQADM_SYS" has errors

. ORA-06508: PL/SQL: could not find program unit being called:

. "SYS.DBMS_AQADM_SYS"

. ORA-06512: at line 11

. ORA-04063: package body "SYS.DBMS_AQADM_SYS" has errors

. ORA-06508: PL/SQL: could not find program unit being called:

. "SYS.DBMS_AQADM_SYS"

. ORA-06512: at line 10

. ORA-04063: package body "SYS.DBMS_AQADM_SYS" has errors

. ORA-06508: PL/SQL: could not find program unit being called:

. "SYS.DBMS_AQADM_SYS"

. ORA-06512: at "SYS.DBMS_AQADM", line 270

. ORA-06512: at line 2

. VALID 11.2.0.3.0 00:11:28

JServer JAVA Virtual Machine

. VALID 11.2.0.3.0 00:02:04

Oracle Workspace Manager

. VALID 11.2.0.3.0 00:00:28

OLAP Analytic Workspace

. VALID 11.2.0.3.0 00:02:21

OLAP Catalog

. VALID 11.2.0.3.0 00:00:40

Oracle OLAP API

. VALID 11.2.0.3.0 00:00:16

Oracle XDK

. VALID 11.2.0.3.0 00:00:33

Oracle Text

. VALID 11.2.0.3.0 00:00:39

Oracle XML Database

. VALID 11.2.0.3.0 00:02:24

Oracle Database Java Packages

. VALID 11.2.0.3.0 00:00:12

Oracle Multimedia

. VALID 11.2.0.3.0 00:04:25

Spatial

. VALID 11.2.0.3.0 00:04:00

Oracle Expression Filter

. VALID 11.2.0.3.0 00:00:08

Oracle Rules Manager

. VALID 11.2.0.3.0 00:00:06

Gathering Statistics

. 00:01:07

Total Upgrade Time: 00:30:58

PL/SQL procedure successfully completed.

雖然所有的元件顯示都是VALID狀態，但是從上面的報錯不難發現有很多重要的系統包都存在問題，這肯定會影響資料庫的正常使用，包括expdp等都不能正常的工作。

在METALINK上搜尋了一翻，參考瞭如下兩篇文章使得問題得以解決：
CATUPGRD.SQL FOLLOWING MIGRATION LEAVES AQ OBJECTS INVALID [ID 469895.1]
修改時間:2012-3-22

型別:PROBLEM

狀態:PUBLISHED

優先順序:3

In this Document

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3 - Release: 10.2 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 23-Jul-2010***
***Checked for relevance on 21-Mar-2012***

Symptoms

After running catupgrd.sql during an upgrade to 10.2.0.X, many invalid AQ objects remain.

Errors will be seen similar to the following:-

SYS.DBMS_AQADM_SYS and SYSTEM.AQ$_QUEUES were not created .
Hence many related objects are created with warnings:

SQL> show errors
Errors for PACKAGE BODY DBMS_AQADM_SYS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
463/5 PL/SQL: SQL Statement ignored
464/17 PL/SQL: ORA-00942: table or view does not exist
1263/6 PL/SQL: SQL Statement ignored
1264/39 PL/SQL: ORA-00942: table or view does not exist
1336/7 PL/SQL: SQL Statement ignored
1338/42 PL/SQL: ORA-00942: table or view does not exist
1415/20 PL/SQL: Item ignored
1415/20 PLS-00201: identifier 'SYSTEM.AQ$_QUEUES' must be declared
1418/5 PL/SQL: SQL Statement ignored
1418/27 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

1418/33 PL/SQL: ORA-00904: : invalid identifier
1426/5 PL/SQL: SQL Statement ignored
1426/24 PL/SQL: ORA-00942: table or view does not exist
1436/6 PL/SQL: SQL Statement ignored
1438/15 PL/SQL: ORA-00904: "OBJNO": invalid identifier
1438/15 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

.

Changes

SQL>select owner, object_name, object_type from all_objects where object_name = 'SYSTEM';

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
SYSTEM SYNONYM
SYSTEM TABLE

SQL>

If any rows are returned - this is the cause of the AQ objects failing to create/compile.

Cause

A synonym and table called SYSTEM exists within the Database.
System is a reserved word and major problems are caused by having objects called SYSTEM.

Quest/Toad software was found to be in use on three occasions when this issue was identified, however, this is still under investigation and is currently unproven.

Solution

1) Start the database in upgrade mode as sysdba

sqlplus " / as sysdba"
SQL> startup upgrade

2) drop objects with the name of "SYSTEM" i.e. the table and synonym.

SQL> select owner, object_name, object_type from all_objects where object_name = 'SYSTEM';

drop .;

3) Once the object is dropped, proceed to upgrade

SQL> spool catupgrd.log
SQL> set echo on
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off

4) Shutdown and startup normal

5) Check for any invalid objects and Execute utlrp.sql to validate invalid objects if any.

SQL> @?/rdbms/admin/utlrp.sql

References

SR 6172528.994

另一篇文章是：

ORA-00001: UNIQUE CONSTRAINT VIOLATED WHEN RUNNING THE POST UPGRADE STEP TO 11G [ID 1240775.1]

修改時間:2012-7-16

型別:PROBLEM

狀態:PUBLISHED

優先順序:3

In this Document

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Symptoms

When running multiple times catupgrd.sql or the post upgrade script. utlu111s.sql during the upgrade to 11g Release 1, the following errors are reported:

SQL> @utlu111s.sql
.
Oracle Database 11.1 Post-Upgrade Status Tool 10-16-2010 02:47:13
.
Component Status Version HH:MM:SS
.
Oracle Server
. ORA-00001: unique constraint (SYS.I_AUDIT_ACTIONS) violated
. ORA-00001: unique constraint (SYS.I_AUDIT_ACTIONS) violated
. ORA-00001: unique constraint (SYS.I_AUDIT_ACTIONS) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.I_SMB$CONFIG_PKEY) violated
. ORA-00001: unique constraint (SYS.I_SMB$CONFIG_PKEY) violated
. ORA-00001: unique constraint (SYS.I_SMB$CONFIG_PKEY) violated
. ORA-00001: unique constraint (SYS.I_SYSTEM_PRIVILEGE_MAP) violated
. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated
. ORA-00001: unique constraint (SYS.WRM$_BASELINE_PK) violated

The same issue could happen when upgrading to 11.2.0.x. when running utlu112s.sql

Changes

The environment is upgraded to 11g Release 1 or higher.

Cause

This is the expected behavior. as confirmed by development in the unpublished bugs

Bug 5501515 TST&PERF VIOLATED SOME TABLE'S UNIQUE CONSTRAINT WHILE UPGRADING TO 11G
Bug 8834797 APPSST GSI 11G UNIQUE CONSTRAINT VIOLATED WHILE RUNNING CATUPGRD

There are 11g specific configuration rows to be inserted in the dictionary tables during the upgrade. When running multiple times the post upgrade step, the same rows are inserted again, therefore the unique constraint keys are violated.

Solution

Unique constraint errors are expected and acceptable on re-run of catupgrd.sql or utlu111s.sql. These ORA-1 errors can be safely ignored and should not impact the upgrade process in anyway.

References

@ - TST&PERF VIOLATED SOME TABLE'S UNIQUE CONSTRAINT WHILE UPGRADING TO 11G
@ - APPSST GSI 11G: UNIQUE CONSTRAINT VIOLATED WHILE RUNNING CATUPGRD
NOTE:1351112.2 - Information Center: Upgrading and Migration Oracle Database
NOTE:1152016.1 - Master Note For Oracle Database Upgrades and Migrations

透過參考以上兩篇文章重複執行了幾次catupgrd.sql指令碼，終於得以解決，執行utlu112s.sql指令碼得到如下結果：

SQL> @utlu112s.sql

.

Oracle Database 11.2 Post-Upgrade Status Tool 12-16-2012 11:08:53

.

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

.

Oracle Server

. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated

. ORA-00001: unique constraint (SYS.I_SYSTEM_PRIVILEGE_MAP) violated

. ORA-00001: unique constraint (SYS.I_STMT_AUDIT_OPTION_MAP) violated

. VALID 11.2.0.3.0 00:10:14

JServer JAVA Virtual Machine

. VALID 11.2.0.3.0 00:00:00

Oracle Workspace Manager

. VALID 11.2.0.3.0 00:00:00

OLAP Analytic Workspace

. VALID 11.2.0.3.0 00:00:00

OLAP Catalog

. VALID 11.2.0.3.0 00:00:00

Oracle OLAP API

. VALID 11.2.0.3.0 00:00:00

Oracle XDK

. VALID 11.2.0.3.0 00:00:00

Oracle Text

. VALID 11.2.0.3.0 00:00:00

Oracle XML Database

. VALID 11.2.0.3.0 00:00:00

Oracle Database Java Packages

. VALID 11.2.0.3.0 00:00:00

Oracle Multimedia

. VALID 11.2.0.3.0 00:00:00

Spatial

. VALID 11.2.0.3.0 00:00:00

Oracle Expression Filter

. VALID 11.2.0.3.0 00:00:00

Oracle Rules Manager

. VALID 11.2.0.3.0 00:00:00

Gathering Statistics

. 00:00:41

Total Upgrade Time: 00:10:56

PL/SQL procedure successfully completed.

上面的唯一約束問題是由於bug所致，但這並不影響資料庫的使用，可以忽略。
如果將第一篇文章中提到的問題在升級前處理，那麼升級後使用Post-Upgrade Status Tool檢查就不會出現ORA-00001。

--end--

Oracle Database 11.1.0.7 for Windows升級到11.2.0.3.4 for Linux遇到的問題

Applies to:

Symptoms

Changes

Cause

Solution

References

Applies to:

Symptoms

Changes

Cause

Solution

References

相關文章