经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库/运维 » Oracle » 查看文章
DataPump遭遇ORA-06512&ORA-39080&ORA-01403错误案例
来源:cnblogs  作者:潇湘隐者  时间:2019/8/20 8:49:38  对本文有异议

最近使用数据泵(DataPump)比较多,遇到了奇奇怪怪的问题,似乎Apply了补丁PSU 10.2.0.5.180717后,DataPump的问题就格外多。如下所示:

 

  1. expdp system/xxx DIRECTORY=DUMPDIR DUMPFILE=xxxx.dmp TABLES=xxxx.xxxx  LOGFILE=expdp.log
  1.  
  1. Export: Release 10.2.0.5.0 - 64bit Production on Monday, 19 August, 2019 9:52:07
  1.  
  1. Copyright (c) 2003, 2007, Oracle.  All rights reserved.
  1.  
  1. Connected to: Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
  1. ORA-31626: job does not exist
  1. ORA-31637: cannot create job SYS_EXPORT_TABLE_01 for user SYSTEM
  1. ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
  1. ORA-06512: at "SYS.KUPV$FT_INT", line 672
  1. ORA-39080: failed to create queues "" and "" for Data Pump job
  1. ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
  1. ORA-06512: at "SYS.KUPC$QUE_INT", line 1606
  1. ORA-01403: no data found

 

遇到这个错误,最开始我以为是我之前遇到的是同一个问题“Expdp 导数错误 ORA-00832”,检查发现__streams_pool_size大小不为0

 

  1. SQL> col name for a36;
  1. SQL> col value for a10;
  1. SQL> col idfefault for a10;
  1. col ismod for a10;
  1. col isadj for a10;
  1. SQL> SELECT X.ksppinm      name       ,
  1.        Y.ksppstvl     value      ,
  1.        Y.ksppstdf     idfefault  ,
  1.        DECODE(bitand(Y.ksppstvf,7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE')  ismod,
  1.        DECODE(bitand(Y.ksppstvf,2), 2, 'TRUE', 'FALSE')  isadj
  1. FROM sys.x$ksppi  X,
  1.      sys.x$ksppcv Y
  1. WHERE X.inst_id = userenv('Instance') AND
  1.       Y.inst_id = userenv('Instance') AND
  1.       X.indx    = Y.indx              AND
  1.       X.ksppinm LIKE '%_streams%'
  1. ORDER BY translate(X.ksppinm, '_', '');
  1.  
  1. NAME                                 VALUE      IDFEFAULT  ISMOD      ISADJ
  1. ------------------------------------ ---------- ---------- ---------- ----------
  1. __streams_pool_size                  33554432   FALSE      FALSE      FALSE
  1. _memory_broker_shrink_streams_pool   900        TRUE       FALSE      FALSE
  1. _disable_streams_pool_auto_tuning    FALSE      TRUE       FALSE      FALSE
  1. _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,发现还真有一模一样的错误

 

clip_image001

 

 

但是这个案例中,在验证表结构时,发现表不存在,所以必须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.

原文链接:http://www.cnblogs.com/kerrycode/p/11377646.html

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号