合 数据泵导出导入ORA-06502、ORA-39077 错误分析与解决方案
Tags： Oracle
expdp/impdp操作报错信息如下：
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
根据报错信息初步分析，可能是datapump组件中sequence数值大于6位数或sequence失效导致。参考mos 文档1550344.1分析，此错误由oracle Bug 16473783 导致,在Oracle 12.2版本中被修复，如果执行脚本重新安装datapump组件可能会引起其它问题。
错误模拟
1.测试导出操作sequence值是否会增加
1.查看datapump组件包含的sequence
SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from all_objects where owner='SYS' and object_name like '%DATAPUMP%' and object_type='SEQUENCE';
OBJECT_NAME OBJECT_TYPE STATUS
AQ$_KUPC$DATAPUMP_QUETAB_N SEQUENCE VALID
AQ$_KUPC$DATAPUMP_QUETAB_1_N SEQUENCE VALID
2.查看两个sequence的值
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_N.nextval from dual;
NEXTVAL
----------
1
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;
NEXTVAL
----------
361
3.执行导出
[oracle@ENMOEDU ~]$ expdp \'/ as sysdba\' directory=MY_DIR dumpfile=test.dmp tables=test.T_TASKDONE
4.查看sequence值
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_N.nextval from dual;
NEXTVAL
----------
2
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;
NEXTVAL
----------
368
说明：导出操作datapump组件中的sequence值会增加
2.测试sequence值超过6位数是否出现此错误
1.通过Increment By来实现修改初始值。序列名称是AQ$_KUPC$DATAPUMP_QUETAB_1_N，初始值是368，而现在要设置初始值为999999，Increment By值为:999619(999999-380)
SQL> Alter Sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N Increment By 999619;
Sequence altered.
SQL> Select AQ$_KUPC$DATAPUMP_QUETAB_1_N.NextVal From Dual;
NEXTVAL
----------
999987
SQL> Alter Sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N Increment By 7;
Sequence altered.
SQL> Select AQ$_KUPC$DATAPUMP_QUETAB_1_N.NextVal From Dual;
NEXTVAL
----------
999994
2.执行expdp导出操作查看是否报错
[oracle@ENMOEDU ~]$ expdp \'/ as sysdba\' directory=MY_DIR dumpfile=test.dmp logfile=test.log tables=test.T_TASKDONE
Export: Release 11.2.0.3.0 - Production on Thu Aug 7 19:11:36 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
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
说明：成功模拟错误。
手动修复
1.重建sequence
1.重建sequence使用cycle参数限制最大值不超过6位数
SQL> drop sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N ;
Sequence dropped.
SQL> create sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N minvalue 1 maxvalue 999999 start with 1 increment by 1 cache 20 cycle;
Sequence created.
2.验证sequence
1.验证sequence值超过6位时是否报错
SQL> drop sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N;
Sequence dropped.
SQL> create sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N minvalue 1 maxvalue 999999 start with 999997 increment by 1 cache 20 cycle;
Sequence created.
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;
NEXTVAL
----------
999998
2.执行expdp导入操作
[oracle@ENMOEDU admin]$ expdp \'/ as sysdba\' directory=my_dir logfile=test01.log dumpfile=test02.dmp tables=test.T_BASEITEM
Export: Release 11.2.0.3.0 - Production on Fri Aug 8 00:56:10 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/**** AS SYSDBA" directory=my_dir logfile=test01.log dumpfile=test02.dmp tables=test.T_BASEITEM
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."T_BASEITEM" 4.470 MB 35322 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/dmp/test02.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 00:56:21
[oracle@ENMOEDU admin]$ expdp \'/ as sysdba\' directory=my_dir logfile=test01.log dumpfile=test03.dmp tables=test.T_BASEITEM
Export: Release 11.2.0.3.0 - Production on Fri Aug 8 00:56:30 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/**** AS SYSDBA" directory=my_dir logfile=test01.log dumpfile=test03.dmp tables=test.T_BASEITEM
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."T_BASEITEM" 4.470 MB 35322 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/dmp/test03.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 00:56:36
[oracle@ENMOEDU admin]$ expdp \'/ as sysdba\' directory=my_dir logfile=test01.log dumpfile=test04.dmp tables=test.T_BASEITEM
Export: Release 11.2.0.3.0 - Production on Fri Aug 8 00:56:44 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/**** AS SYSDBA" directory=my_dir logfile=test01.log dumpfile=test04.dmp tables=test.T_BASEITEM
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."T_BASEITEM" 4.470 MB 35322 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/dmp/test04.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 00:56:50
说明：三次expdp导出操作都成功
4.查询sequence已经cycle到40
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;
NEXTVAL
----------
40
说明：问题可以解决。需要在生产库执行两条sql:
SQL> drop sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N ;
**SQL> create sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N minvalue 1 maxvalue 999999 start with 1 increment by 1 cache 20 cycle;
**
MOS解决方案分析
MOS**文档1550344.1提供的解决方案**
SOLUTION
To address the issue, use any of below alternatives:
o Apply interim Patch 16928674 for the generic platform if available for your Oracle version.
- OR -
o As a workround, execute next scripts to recreate the datapump objects:
@$ORACLE_HOME/rdbms/admin/catdph.sql
@$ORACLE_HOME/rdbms/admin/prvtdtde.plb
@$ORACLE_HOME/rdbms/admin/catdpb.sql
@$ORACLE_HOME/rdbms/admin/dbmspump.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql
Please refer to
Note 16473783.8 - Bug 16473783 - expdp encounters ORA-39077 and ORA-31638 - withdrawn
Generally speaking, we can recreate the datapump objects in 11g by calling;
\1. Catproc.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
\2. To recompile invalid objects, if any
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
This is described in
Note 430221.1 - How To Reload Datapump Utility EXPDP/IMPDP
- OR -
o As an alternative to a re-installation of datapump, which would need an instance shutdown/restart, you could recreate the queue table, e.g:
connect / as sysdba
exec dbms_aqadm.drop_queue_table(queue_table => 'SYS.KUPC$DATAPUMP_QUETAB', force => TRUE);
dbms_aqadm.create_queue_table(
queue_table => 'SYS.KUPC$DATAPUMP_QUETAB',
multiple_consumers => TRUE,
queue_payload_type => 'SYS.KUPC$_MESSAGE',
comment => 'DataPump Queue Table',
compatible => '8.1.3',
storage_clause=>'TABLESPACE SYSAUX');
Note that this will cause any running Data Pump jobs on the instance to fail with queue errors. However, they should be restartable.
分析：
\1. 打Patch 16928674可以修复此bug，可以回退，不用停机风险小。
\2. 执行如下脚本，但此方法会引起其它问题，被官方撤回。
@$ORACLE_HOME/rdbms/admin/catdph.sql
@$ORACLE_HOME/rdbms/admin/prvtdtde.plb
@$ORACLE_HOME/rdbms/admin/catdpb.sql
@$ORACLE_HOME/rdbms/admin/dbmspump.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql
或者
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql
\3. 执行存储过程重新安装datapump组件，需要重启实例。
connect / as sysdba
exec dbms_aqadm.drop_queue_table(queue_table => 'SYS.KUPC$DATAPUMP_QUETAB', force => TRUE);
dbms_aqadm.create_queue_table(
queue_table => 'SYS.KUPC$DATAPUMP_QUETAB',
multiple_consumers => TRUE,
queue_payload_type => 'SYS.KUPC$_MESSAGE',
comment => 'DataPump Queue Table',
compatible => '8.1.3',
storage_clause=>'TABLESPACE SYSAUX');
验证打path 16928674修复bug
1.错误再现
1.将sequence值设置为999987
SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;
