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

[oracle@oracle ~]$ expdp username/password directory=mydata dumpfile=full.dmp logfile=full.log
Export: Release 11.2.0.4.0 - Production on Sun Mar 07 13:40:59 2020
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-31638: cannot attach to 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 428
ORA-39077: unable to subscribe agent KUPC$A_1_134100540660000 to queue "KUPC$C_1_20140928134059"
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

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production


NLSRTL Version 11.2.0.4.0 - Production

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

set linesize 120;
col object_name for a60;
select OBJECT_NAME,OBJECT_TYPE,STATUS
from dba_objects
where owner='SYS'
and object_name like '%DATAPUMP%'
and object_type='SEQUENCE';


SQL> SELECT SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from DUAL;
   NEXTVAL
----------
        96


SQL> SELECT SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N.CURRVAL val# FROM DUAL;
      VAL#
----------
        96


SQL> 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
----------
   1000088


SQL> 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 lsinventory
Invoking OPatch 11.2.0.1.7
Oracle 中间补丁程序安装程序版本 11.2.0.1.7
版权所有 (c) 2011, Oracle Corporation。保留所有权利。
Oracle Home       : /u01/app/oracle/product/11.2.0/db_home
Central Inventory : /u01/app/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/db_home/cfgtoollogs/opatch/opatch2014-09-28_13-45-51下午.log
Lsinventory 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.

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

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.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

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 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.

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.