导数据遇到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
数据库版本信息
|
1
2
3
4
5
6
7
8
9
|
SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production |
导出工具信息
1 [oracle@vm010148 ~]$ exp -help 2 Export: Release 11.2.0.3.0 - Production on Sun Sep 28 14:21:26 2014 3 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.
如果在导入时发现类似错误，均按照上面的方法进行处理