文档章节

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

o
 osc_bkdv2it5
发布于 2019/08/19 15:55
字数 803
阅读 32
收藏 0

钉钉、微博极速扩容黑科技,点击观看阿里云弹性计算年度发布会!>>>

最近使用数据泵(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,发现还真有一模一样的错误

 

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.

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
Error 942 occured during Initialization of Bufq KUPC$S_1_20181023155636

一台ORACLE实例(Oracle Database 10g Release 10.2.0.5.0)启动时,报“Error 942 occured during Initialization of Bufq KUPC$S120181023155636”错误,具体情况如下所示,内容信息来自告警日...

osc_w55yykyo
2019/08/11
1
0
EXPDP导数报ORA-00942案例

使用数据泵(expdp)导数时遇到了ORA-31626 & ORA-00942 错误,数据库版本为Oracle Database 10g Release 10.2.0.5.0,具体错误如下所示: $ expdp system/xxx tables=xxx.xxx directory=DUMP...

osc_gf11nrvz
2019/07/27
4
0
UDI-31626: operation generated ORACLE error 31626 ORA-31626: job does not exist

数据库重装后,还原备份的文件,报如下错误,有知道的给指导一下吧,感谢了 UDI-31626: operation generated ORACLE error 31626 ORA-31626: job does not exist ORA-39086: cannot retrieve...

pengcheng_1024
2015/04/17
2.6K
0
oracle-常用异常

常用预定义例外 EXCEPTION WHEN CURSORALREADYOPEN THEN -- ORA-06511 SQLCODE = -6511 游标已经打开 ..WHEN DUPVALON_INDEX THEN -- ORA-00001 SQLCODE = -1 向唯一索引中插入重复数据 ..WH......

kaixinguo314
2018/02/08
18
0
一个简单的expdp导出UDE-31623 ORA-31623

问题描述: ORACLE EXADATA 12.2 4节点 一个简单的expdp导出,在之前是正常的,但是隔了一天后出现问题了,具体报错信息如下: expdp '"/as sysdba"' DIRECTORY=dir1 DUMPFILE=vat%U.dmp log......

osc_two9vbkq
2019/05/05
9
0

没有更多内容

加载失败,请刷新页面

加载更多

高职考技能提升教程012期 阶层求和的综合运用

视频讲解 文字讲解: 高职考VB技能提升教程 阶层求和的综合运用 阶层如何理解?看例子 3!=3*2*1 6!=6*5*4*3*2*1 程序实现阶层 举例实现6! 现在看模拟题 模拟题实现 1.设置combobox控件的t...

刘金玉编程
06/05
0
0
22岁的UI设计师,100天成功逆袭UI常用英语,强大到让你别害怕!!

「写在前面」前一段时间有好几个小伙伴留言,告诉我今年一定要多提升自己争取拿一个好的offer。但是不知不觉2020已经过去了三分之一,当初立下突破sketch、学好英语、考驾照的flag依旧没进展...

静电1983
05/08
0
0
docker 容器内服务自启动

centos6/7 有区别先说6 centos6: 方式一:rc.local + 容器内的启动脚本 1.chkconfig docker on  #docker开机自启动 2.[root@master yum.repos.d]# tail -1 /etc/rc.local    #开机自启...

osc_facwbzof
2分钟前
0
0
moco API合集

moco API 解决moco框架API在post请求json参数情况下query失效的问题 解决moco框架API在cycle方法缺失的问题 给moco API添加limit功能 给moco API添加random功能 解决moco框架API在cycle方法缺...

八音弦
07/09
0
0
什么?春节别人抢红包你却在抢锁

今年春节响应国家号召在家宅着抵抗疫情,拜年也改用微信红包,春节发了很多也抢了很多微信红包,也算支持了公司业务,微信支付融入生活,抢红包已经是非常平常的事情,想到WXG的小伙伴丰厚的...

柠檬橙1024
01/30
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部