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.