欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

DataPump遭遇ORA-06512&ORA-39080&ORA-01403错误案例

程序员文章站 2022-06-23 19:13:30
最近使用数据泵(DataPump)比较多,遇到了奇奇怪怪的问题,似乎Apply了补丁PSU 10.2.0.5.180717后,DataPump的问题就格外多。如下所示: expdp system/xxx DIRECTORY=DUMPDIR DUMPFILE=xxxx.dmp TABLES=xxxx.... ......

最近使用数据泵(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.