一个客户要将现有的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
  Symptoms
  Changes
  Cause
  Solution
  References

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 <object_type> <owner>.<object_name>;

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

@ 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
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--

ITPub博客

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

最新文章