导数据遇到ORA-31626/ORA-31638/ORA-39077/ORA-6502 报错信息如下：
[oracle@vm010148 ~]$ expdp system/oracle directory=dir dumpfile=full.dmp logfile=full.log Export: Release 11.2.0.3.0 - Production on Sun Sep 28 13:40:59 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 - 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.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production
导出工具信息
[oracle@vm010148 ~]$ exp -help Export: Release 11.2.0.3.0 - Production on Sun Sep 28 14:21:26 2014 Copyright (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,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. 查询mos文档1550344.1发现是bug 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 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.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. 临时解决方法，重建出错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. 如果在导入时发现类似错误，均按照上面的方法进行处理
更多内容请关注微信公众号：数据与人
