expdp匯出、transport_tablespace遇見ORA-04063、ORA-06508: PL/SQL:

2014.11.06
    剛剛在做Oracle 10g 表空間傳輸的時候竟然報錯。因為已經做過多少次transport_tablespace 我自己都不記得。以前做的時候基本上沒遇見過什麼特殊的報錯。

[oracle@zhanglin scripts]$ expdp "'/ as sysdba'" DUMPFILE=oltp_indx.dmp DIRECTORY=dir TRANSPORT_TABLESPACES = oltp,indx

Export: Release 10.2.0.1.0 - Production on Thursday, 06 November, 2014 3:09:33

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_TRANSPORTABLE_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-04063: package body "SYS.KUPC$QUE_INT" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.KUPC$QUE_INT"

雖然俺在一個上市企業，可沒有metalink，悲哀！
從上面的報錯，不難初步確定是SYS.KUPC$QUE_INT 這個包的錯誤引起的。網上一些類似的案例，但是看了很多，報錯的號相同，但是具體的報錯package不同，有相同報錯的package 但是在我的這個情況下不適合。
先按照網上的普遍方式：

1、查出invalid的包
SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where owner='SYS' and status='INVALID';

OBJECT_NAME                    OBJECT_TYPE      STATUS
------------------------------ ---------------- -------
UTL_HTTP                       PACKAGE BODY     INVALID
UTL_I18N                       PACKAGE BODY     INVALID
DBMS_PCLXUTIL                  PACKAGE BODY     INVALID
DBMS_PSP                       PACKAGE BODY     INVALID
DBMS_PRVTAQIM                  PACKAGE BODY     INVALID
AQ$_JMS_MESSAGE                TYPE BODY        INVALID
AQ$_JMS_BYTES_MESSAGE          TYPE BODY        INVALID
AQ$_JMS_STREAM_MESSAGE         TYPE BODY        INVALID
AQ$_JMS_MAP_MESSAGE            TYPE BODY        INVALID
DBMS_AQJMS_INTERNAL            PACKAGE BODY     INVALID
DBMS_AQADM_SYS                 PACKAGE BODY     INVALID

OBJECT_NAME                    OBJECT_TYPE      STATUS
------------------------------ ---------------- -------
DBMS_DEFER_SYS                 PACKAGE BODY     INVALID
KUPC$QUE_INT                   PACKAGE BODY     INVALID
DBMS_SNAPSHOT                  PACKAGE BODY     INVALID
DBMS_ASYNCRPC_PUSH             PACKAGE BODY     INVALID
DBMS_DEFER_QUERY               PACKAGE BODY     INVALID
DBMS_DEFER_SYS_PART1           PACKAGE BODY     INVALID
DBMS_REPCAT_UTL                PACKAGE BODY     INVALID
DBMS_REPCAT_SNA_UTL            PACKAGE BODY     INVALID
DBMS_REPCAT_MAS                PACKAGE BODY     INVALID
DBMS_REPCAT_VALIDATE           PACKAGE BODY     INVALID
DBMS_STREAMS_ADM_UTL           PACKAGE BODY     INVALID

OBJECT_NAME                    OBJECT_TYPE      STATUS
------------------------------ ---------------- -------
DBMS_STREAMS_ADM               PACKAGE BODY     INVALID
DBMS_LOGREP_EXP                PACKAGE BODY     INVALID

24 rows selected.

2、手動修復INVALID包
透過上面的一個select可以找出SYS下無效的包

SQL> alter PACKAGE SYS.KUPC$QUE_INT compile BODY;   ----  修復包 失敗

Warning: Package Body altered with compilation errors.

SQL> alter PACKAGE SYS.KUPV$FT_INT compile BODY;

Package body altered.
-----  在這時候 我又再次執行expdp 但還是原來的錯誤。
------- 檢視更詳細的報錯資訊：

SQL>  show errors;
Errors for PACKAGE BODY SYS.KUPC$QUE_INT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1251/11  PLS-00341: declaration of cursor 'Q_CUR' is incomplete or malformed

1252/7   PL/SQL: SQL Statement ignored
1252/23  PL/SQL: ORA-01775: looping chain of synonyms
1257/11  PL/SQL: Item ignored
1262/4   PL/SQL: SQL Statement ignored
1262/22  PLS-00320: the declaration of the type of this expression is incomplete or malformed

1568/7   PL/SQL: SQL Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
1568/24  PL/SQL: ORA-01775: looping chain of synonyms
1582/7   PL/SQL: Statement ignored
1582/19  PLS-00364: loop index variable 'Q_REC' use is invalid
菜鳥只是摳一些關鍵的字看。ERROR裡面說到，不完整的遊標cursor、同義詞synonyms 以及無效的索引迴圈 loop index
大膽猜想一下，應該是資料字典出錯吧！
SQL> alter PACKAGE SYS.KUPC$QUE_INT compile;   ---- 再次修復，還是失敗

Warning: Package altered with compilation errors.

SQL> show errors;
No errors.
3、呼叫資料庫自身的修復包
SQL> @?/rdbms/admin/utlrp.sql;
-----  在這時候 我又再次執行expdp 但還是原來的錯誤。
4、自己的解決方法：
       這麼想吧，既然跟資料字典有關，而且在手動修復失敗和自帶修復包修復失敗的情況下。我們可以選擇重建資料字典。
當然重建資料字典 是存在風險的，不得已的情況下，不建議在生產庫中操作，貌似還有升級可以選擇，對吧。
由於我的是自己的實驗環境，這個例項，是我手動建立的，不是dbca。所以我選擇了重建資料字典。執行手動建立例項時候執行的包
SQL > @?/rdbms/admin/catproc.sql
等執行完了後，再次expdp
[oracle@zhanglin scripts]$ expdp "'/ as sysdba'" DUMPFILE=oltp_indx.dmp DIRECTORY=dir TRANSPORT_TABLESPACES = oltp,indx

Export: Release 10.2.0.1.0 - Production on Thursday, 06 November, 2014 4:13:35

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  '/******** AS SYSDBA' DUMPFILE=oltp_indx.dmp DIRECTORY=dir TRANSPORT_TABLESPACES = oltp,indx
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/scripts/oltp_indx.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 04:14:10

[oracle@orastud1 scripts]$

下面列舉一下，我在解決這個問題時，看到網上的一些常見的案例：
1、streams pool過小引起的
解決辦法：調整stream pool 大小

SQL> show parameter stream

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 52M
SQL>  官方建議大於48m
2、datapump元件中sequence數值大於6位數導致
    ORA-31626: job does not exist
    ORA-31638: cannot attach to job SYS_EXPORT_TABLE_01 for user SYS
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
    ORA-06512: at "SYS.KUPV$FT_INT", line 428
    ORA-39077: unable to subscribe agent KUPC$A_1_191136568928000 to queue "KUPC$C_1_20140807191136"
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
    ORA-06512: at "SYS.KUPC$QUE_INT", line 250
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    此錯誤由oracle Bug 16473783導致，要做升級操作！
參考：http://blog.itpub.net/29324876/viewspace-1248199

 3、

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

參考：http://blog.itpub.net/23135684/viewspace-1098482/


2014.11.12---

[oracle@bidevelop4 scripts]$ ./memdata.sh

Export: Release 11.2.0.4.0 - Production on Tue Nov 11 19:56:21 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31634: job already exists
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 1034
ORA-31637: cannot create job MEMDATA_JOB1 for user SYS
匯出資料庫結構的時候報錯。
用上面的select查出

SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where owner='SYS' and status='INVALID';

OBJECT_NAME                  OBJECT_TYPE         STATUS
---------------------------- ------------------- -------
ALL_TAB_STATISTICS           VIEW                INVALID
USER_TAB_STATISTICS          VIEW                INVALID
ALL_IND_STATISTICS           VIEW                INVALID
USER_IND_STATISTICS          VIEW                INVALID
DBMS_SMB                     PACKAGE BODY        INVALID
DBMS_SQLTCB_INTERNAL         PACKAGE BODY        INVALID
VALIDATE_ORDIM               PROCEDURE           INVALID
DBMS_CUBE_ADVISE             PACKAGE BODY        INVALID
DBMS_CUBE                    PACKAGE BODY        INVALID

9 rows selected.

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

ok！可以匯出。