Oracle資料庫從11.1.0.7升級到11.2.0.3 SYS.DBMS_AQADM_SYS包出現錯誤

尛樣兒發表於2014-03-03
Oracle資料庫

    Oracle資料庫從11.1.0.7升級到11.2.0.3 SYS.DBMS_AQADM_SYS包出現錯誤，最突出的表現是執行expdp備份時出現以下報錯：

[oracle@hisdb ~]$ expdp \'/ as sysdba\' directory=dump_dir2 dumpfile=orcl_full_bak040228.dmp full=y parallel=2

Export: Release 11.2.0.3.0 - Production on Fri Feb 28 23:16:59 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  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
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_02 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 798
ORA-39244: Event to disable dropping null bit image header during relational select
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPC$QUE_INT", line 1825
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"

在metalink找到一篇文章用於解決該問題：
Data Pump Job Fails With ORA-31626 ORA-31637 ORA-39080 ORA-04063 And ORA-06508 (文件 ID 563701.1)
修改時間:2013-3-23型別:PROBLEM

In this Document

Symptoms
Cause
Solution
References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.

SYMPTOMS

An error stack similar to the following occurs when running a Data Pump job:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20080413190701" and "" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1580
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" 

CAUSE

This issue will happen if there is invalid SYS-owned objects, specially on these two scenarios:

i. Presence of user-created objects named the reserved word 'SYSTEM'.  Naming any database object 'SYSTEM' can invalidate advanced queuing objects, which in turn can invalidate some components of the SYS.KUPC$DATAPUMP_QUETAB queue table, which Data Pump uses.

ii. Or if SYS.DBMS_AQADM is invalid.

SOLUTION

To resolve the problem, please apply the steps as described:

1. Remove any objects named the reserved word 'SYSTEM' as per Document 469895.1.

2. Then drop and recreate the SYS.KUPC$DATAPUMP_QUETAB queue table as per Document 361025.1

3. And finally, check and recompile SYS.DBMS_AQADM:

conn / as sysdba
spool support.out
select owner, object_type, status from dba_objects where object_name = 'DBMS_AQADM';
alter package sys.DBMS_AQADM compile;
alter package sys.DBMS_AQADM compile body;

-- If any ALTER fails, then check the errors:
select * from dba_errors where name = 'DBMS_AQADM' and type = 'PACKAGE BODY';

-- If it indicates:
missing : SYS.AQ$_SCHEDULES ;
missing/invalid package specification : DBMS_PRVTAQIP

-- These are both created by catqueue.sql which is run from catproc.sql:
-- Execute catproc.sql:

$ORACLE_HOME/rdbms/admin/catproc.sql
$ORACLE_HOME//rdbms/admin/utlrp.sql

spool off 


Still have questions ?

To discuss this information further with Oracle experts and industry peers, we encourage you to review, join or start a discussion in the My Oracle Support Streams and Distributed Database Community.

The goal of this community is to exchange knowledge and concepts about Oracle Streams Advanced Queuing (AQ) and distributed databases, with special consideration for the components listed below:
    -    Distributed Databases
    -    Streams Replication and Advanced Queuing
    -    Advanced Replication
    -    XA


If you feel that a Service Request is needed, please be sure to provide the information listed below:

1. Instance alert log file.
2. Trace file if generated
3. RDA report. Document 314422.1
4. Upload spooled text file 'support.out' 


To provide feedback on this note, click on the "Rate this document" link above.

REFERENCES

NOTE:361025.1 - Invalid Objects After Installing a 10.2 Patchset
NOTE:469895.1 - CATUPGRD.SQL FOLLOWING MIGRATION LEAVES AQ OBJECTS INVALID

相關文章：
   《Oracle Database 11.1.0.7 for Windows升級到11.2.0.3.4 for Linux遇到的問題》：http://blog.itpub.net/23135684/viewspace-751193/

--end--

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23135684/viewspace-1098482/，如需轉載，請註明出處，否則將追究法律責任。

相關文章