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！可以导出。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30936525/viewspace-2016696/ ，如需转载，请注明出处，否则将追究法律责任。
