DataPump遭遇ORA-06512&ORA-39080&ORA-01403錯誤案例

最近使用數據泵（DataPump)比較多，遇到了奇奇怪怪的問題，似乎Apply了補丁PSU 10.2.0.5.180717後，DataPump的問題就格外多。如下所示：

expdp system/xxx DIRECTORY=DUMPDIR DUMPFILE=xxxx.dmp TABLES=xxxx.xxxx  LOGFILE=expdp.log 

Export: Release 10.2.0.5.0 - 64bit Production on Monday, 19 August, 2019 9:52:07

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.5.0 - 64bit Production

ORA-31626: job does not exist

ORA-31637: cannot create job SYS_EXPORT_TABLE_01 for user SYSTEM

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT_INT", line 672

ORA-39080: failed to create queues "" and "" for Data Pump job

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPC$QUE_INT", line 1606

ORA-01403: no data found

遇到這個錯誤，最開始我以為是我之前遇到的是同一個問題“Expdp 導數錯誤 ORA-00832”，檢查發現__streams_pool_size大小不為0

SQL> col name for a36;

SQL> col value for a10;

SQL> col idfefault for a10;

col ismod for a10;

col isadj for a10;

SQL> SELECT X.ksppinm      name       ,

       Y.ksppstvl     value      ,

       Y.ksppstdf     idfefault  ,

       DECODE(bitand(Y.ksppstvf,7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE')  ismod,

       DECODE(bitand(Y.ksppstvf,2), 2, 'TRUE', 'FALSE')  isadj

FROM sys.x$ksppi  X,

     sys.x$ksppcv Y

WHERE X.inst_id = userenv('Instance') AND

      Y.inst_id = userenv('Instance') AND

      X.indx    = Y.indx              AND

      X.ksppinm LIKE '%_streams%'

ORDER BY translate(X.ksppinm, '_', ''); 

NAME                                 VALUE      IDFEFAULT  ISMOD      ISADJ

------------------------------------ ---------- ---------- ---------- ----------

__streams_pool_size                  33554432   FALSE      FALSE      FALSE

_memory_broker_shrink_streams_pool   900        TRUE       FALSE      FALSE

_disable_streams_pool_auto_tuning    FALSE      TRUE       FALSE      FALSE

_streams_pool_max_size               0          TRUE       FALSE      FALSE

 然後開啟跟蹤'1403 trace name errorstack level 3'，執行導出命名後，然後關閉跟蹤'1403 trace name errorstack off'

SQL> alter system set events '1403 trace name errorstack level 3';

System altered.

run the expdp command

SQL> alter system set events '1403 trace name errorstack off';

System altered.

查看trace文件，如下截圖所示，提示“ksedmp:internal or fatal error" ,搜索了一下metalink，發現還真有一模一樣的錯誤

但是這個案例中，在驗證表結構時，發現表不存在，所以必須reload the DataPump utility， reload the DataPump utility候就能正常的導入導出了。

SQL> analyze table kupc$datapump_quetab validate structure;

analyze table kupc$datapump_quetab validate structure

              *

ERROR at line 1:

ORA-00942: table or view does not exist

具體的官方文檔如下所示：

DataPump Import Or Export (IMPDP/EXPDP) Fails With Errors ORA-31626 ORA-31637 (文檔 ID 345198.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

Trying to execute the DataPump export or import ends with errors like:

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_01 for user SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 601
ORA-39080: failed to create queues "" and "" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1550
ORA-01403: no data found

You can also see errors like:

ORA-39006 - internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-01403: no data found
ORA-39097: Data pump job encountered unexpected error 100

CHANGES

CAUSE

Such errors typically happen in instances with incorrectly configured Advanced Queueing (AQ).

SOLUTION

To resolve the issue, follow the steps below:

1. Set the error stack 1403 event to know exactly which SQL is failing:

connect / as sysdba
alter system set events '1403 trace name errorstack level 3';

NOTE:
In databases that are heavy loaded, setting this event will produce many trace files. It is recommended to set this when the database is not intensively used.

Then re-run the DataPump export/import to reproduce the error and then disable the events.

alter system set events '1403 trace name errorstack off';

2. Once the trace file reveals the problematic SQL, check for the existence of table KUPC$DATAPUMP_QUETAB using DBA_OBJECTS.

3. If present, then run:

connect / as sysdba
analyze table kupc$datapump_quetab validate structure;

4. If not present or it errors out in step 3, then reload the DataPump utility as described in Note 430221.1.