故障处理 | ​expdp导出遇到ORA-31626/ORA-31638/ORA-39077/ORA-6502

故障处理 | ​expdp导出遇到ORA-31626/ORA-31638/ORA-39077/ORA-6502

问题背景：expdp导数据遇到ORA-31626/ORA-31638/ORA-39077/ORA-6502

报错信息如下：

[oracle@oracle ~]$ expdp username/password directory=mydata dumpfile=full.dmp logfile=full.logExport: Release 11.2.0.4.0 - Production on Sun Mar 07 13:40:59 2020Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORA-31626: job does not existORA-31638: cannot attach to job SYS_EXPORT_SCHEMA_01 for user SYSTEMORA-06512: at "SYS.DBMS_SYS_ERROR", line 95ORA-06512: at "SYS.KUPV$FT_INT", line 428ORA-39077: unable to subscribe agent KUPC$A_1_134100540660000 to queue "KUPC$C_1_20140928134059"ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95ORA-06512: at "SYS.KUPC$QUE_INT", line 250ORA-06502: PL/SQL: numeric or value error: character string buffer too small

数据库版本信息

SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE    11.2.0.4.0      ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - Production

导出工具信息

[oracle@vm010148 ~]$ expdp -helpExport: Release 11.2.0.4.0 - Production on Sun Sep 28 14:21:26 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

错误原因：

当执行导出导入时，其"SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_N"的sequence会增大；最大是999999的六位数，如果超过了，执行导出导入时则会报ORA-39077/ORA-31638错误出来

查看export时的seq名

set linesize 120;col object_name for a60;select OBJECT_NAME,OBJECT_TYPE,STATUSfrom dba_objectswhere owner='SYS'and object_name like '%DATAPUMP%'and object_type='SEQUENCE';SQL> SELECT SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from DUAL; NEXTVAL----------        96SQL> SELECT SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N.CURRVAL val# FROM DUAL; VAL#---------- 96SQL> Alter Sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N Increment By 999991;Sequence altered.SQL> SELECT SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from DUAL; NEXTVAL----------   1000088SQL> SELECT SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N.CURRVAL val# FROM DUAL; VAL#---------- 1000088==>发现当前AQ$_KUPC$DATAPUMP_QUETAB_1_N的sequence号已经大于999999，后面做exp时会出现如下错误

查看有没有打过相关补丁信息

[oracle@vm010148 OPatch]$ ./opatch lsinventoryInvoking OPatch 11.2.0.1.7Oracle 中间补丁程序安装程序版本 11.2.0.1.7版权所有 (c) 2011, Oracle Corporation。保留所有权利。Oracle Home : /u01/app/oracle/product/11.2.0/db_homeCentral Inventory : /u01/app/oracle/oraInventory from : /etc/oraInst.locOPatch version : 11.2.0.1.7OUI version : 11.2.0.3.0Log file location : /u01/app/oracle/product/11.2.0/db_home/cfgtoollogs/opatch/opatch2014-09-28_13-45-51下午.logLsinventory Output file location : /u01/app/oracle/product/11.2.0/db_home/cfgtoollogs/opatch/lsinv/lsinventory2014-09-28_13-45-51下午.txt--------------------------------------------------------------------------------已安装的顶级产品 (1):Oracle Database 11g 11.2.0.3.0此 Oracle 主目录中已安装 1 个产品。此 Oracle 主目录中未安装任何中间补丁程序。--------------------------------------------------------------------------------OPatch succeeded.

查询mos文档1550344.1发现是bug

This issue is identical to the one filled underBug 6471833 - IMPDP FAILED WITH ORA-39002, ORA-31626closed as duplicate ofBug 16473783 - EXPDP ENCOUNTERS ORA-39077 AND ORA-31638

oracle官方文档提出将在12.2上绑定

This issue is fixed in 12.2 (Future Release)

官方解决方法：

1、To address the issue, use any of below alternatives:Apply interim Patch 16928674 for the generic platform if available for your Oracle version.

2、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.sqlPlease refer toNote 16473783.8 - Bug 16473783 - expdp encounters ORA-39077 and ORA-31638 - withdrawnGenerally speaking, we can recreate the datapump objects in 11g by calling;1). Catproc.sql SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql2). To recompile invalid objects, if any SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sqlThis is described inNote 430221.1 - How To Reload Datapump Utility EXPDP/IMPDP

3、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 sysdbaexec 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.

临时解决方法，重建出错sequence

会不会有其他影响，待定，重建其sequence，当达到999999后，再从1开始循环做

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.

如果在导入时发现类似错误，均按照上面的方法进行处理