数据泵导入碰到UDI-31623和ORA-31623 a job is not attached to this session via the specified handle错误

0    584    4

Tags:

👉 本文共约3111个字,系统预计阅读时间或需12分钟。

UDI-31623和ORA-31623: a job is not attached to this session via the specified handle

现象

分析

1、查看告警日志是否有啥错误信息输出

2、分析streams_pool_size是否太小,最少配置128M

解决

另外,shared_pool_size参数也需要配置大一点,否则可能会报如下的错误:

参考

How to resolve the Data Pump error ORA-31623 UDE-31623 (a job is not attached to this session via the specified handle) ? (Doc ID 1907256.1)

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

GOAL

This document explains how to resolve the following errors during an Export DataPump (expdp) or Import DataPump job (impdp).

There are several possible reasons why a Data Pump cannot be started. Each root cause has its own solution.

SOLUTION

Use the steps below one by one to address and fix this issue:

Step 1. First check the value for the STREAMS_POOL_SIZE in the database:

If the STREAMS_POOL_SIZE is too small, then a Data Pump job will fail. This can also happen when using Automatic Shared Memory Management (ASMM), or Automatic Memory Management (AMM) and there is not sufficient memory to increase the STREAMS_POOL_SIZE.
Manual settings for the STREAMS_POOL_SIZE of 64M, 128M or even to 256M have proven to be successful.
Also increase sga_target (for ASMM) or memory_target (for AMM) to have more free memory available during automatic tuning of the SGA components.

To avoid this DataPump error, you will need to configure the database with some Streams Pool.
Manually set the STREAMS_POOL_SIZE (using ALTER SYSTEM or by changing the value in the the PFILE/SPFILE), re-start the database and re-attempt the Data Pump Export.

Step 2. Check for any possible invalid Data Pump queue objects:

If there are any invalid queue objects, then a Data Pump job will fail. This usually also results in the following error in the alert.log file:

ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []

For details and full resolution, see:
Note 754401.1 - Errors ORA-31623 And ORA-600 [kwqbgqc: bad state] During DataPump Export Or Import

Step 3. Check for any invalid registry components (CATALOG, CATPROC and JAVAVM), and invalid sys owned objects:

connect / as sysdba

If the registry components CATALOG, CATPROC and/or JAVAVM, and/or objects like SYS.KUPW$WORKER or SYS.KUPP$PROC are invalid, then a Data Pump job will likely fail.
To resolve this problem, reload Data Pump in the database:

For details and references, see:
Note 430221.1 - How To Reload Datapump Utility EXPDP/IMPDP
Note 863312.1 - Best Practices for running catalog, catproc and utlrp script
Note 308388.1 - Error ORA-31623 When Submitting A DataPump Export Job

In case JAVAVM component is invalid, validate it using the steps from:

Note 1112983.1 - How to Reload the JVM in 11.2.0.x
Note 276554.1 - How to Reload the JVM in 10.1.0.X and 10.2.0.X
Note 1612279.1 - How to Reload the JVM in 12.1.0.x

and/or create a Java SR if more help is needed.

Step 4. Check if parameter _FIX_CONTROL is set for Bug 6167716:

If this hidden parameter is set, then a Data Pump job will fail.

For details and full resolution, see:
Note 1150733.1 - DataPump Export (EXPDP) Fails With Errors ORA-31623 ORA-6512 If Parameter _FIX_CONTROL='6167716:OFF' Has Been Set

Step 5. If the Data Pump job is started through a package, check if the package was created with invoker's right (AUTHID clause):

If the package was created with an invoker's right, then a Data Pump job will fail when started through this package.

For details and full resolution, see:
Note 1579091.1 - DataPump Job Fails With Error ORA-31623 A Job Is Not Attached To This Session Via The Specified Handle

Step 6. If the Data Pump job is started in DBConsole / OEM, and the job is selected to be re-run (or you want to edit the job), then the Data Pump job will fail and following errors will be reported:

-- or --

Edit is not supported for this job type, only general information

For details and full resolution, see:
Note 788301.1 - Error ORA-31623 On DataPump Export Via DBScheduler After First Run Was Successful
Note 461307.1 - How To Export Database Using DBConsole/OEM In 10G

Step 7. If parameter LOGTIME is being used, Data Pump export/import with LOGTIME parameter crashes if the environment variable NLS_DATE_FORMAT is set.

For details and full resolution, see:
Note 1936319.1 - Data Pump Export Or Import Throws ORA-31623 When Using LOGTIME Parameter

Step 8. Running a remote DataPump job against Oracle 12.1.0.2 database, the export can fail with ORA-31623. Database alert.log file reports ORA-0600 [ksfdcmtcre4], [KGNFS SERVER REBOOT] error.

The incident trace file shows the following information:

Dump continued from file: /diag/product_type/product_id/instance_id>/trace/dm00.trc
[TOC00001]
ORA-00600: internal error code, arguments: [ksfdcmtcre4], [KGNFS SERVER REBOOT], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE_INT", line 79
ORA-06512: at "SYS.KUPF$FILE", line 2151
ORA-06512: at "SYS.KUPF$FILE", line 1473
...
Stack Trace: ... kkgereml kuppChkErr kupprdp opirip opidrv sou2o ...

DNFS is enabled by default when installing Oracle. When an alternative storage vendor is used, there is a conflict causing the ORA-0600 [ksfdcmtcre4], [KGNFS SERVER REBOOT] error preventing tasks to be completed successfully. In this case, the task was EXPDP job.

To prevent the error, disable DNFS, since it is not being used.
For details, see Note 954425.1 - Direct NFS: FAQ.

REFERENCES

NOTE:1579091.1 - DataPump Job Fails With Error ORA-31623 A Job Is Not Attached To This Session Via The Specified Handle
NOTE:954425.1 - Direct NFS: FAQ
NOTE:308388.1 - Error ORA-31623 When Submitting A DataPump Job
NOTE:788301.1 - Error ORA-31623 On DataPump Export Via DBScheduler After First Run Was Successful
NOTE:430221.1 - How To Reload Datapump Utility EXPDP/IMPDP
NOTE:1936319.1 - Data Pump Export Or Import Throws ORA-31623 When Using LOGTIME Parameter
NOTE:461307.1 - How To Export Database Using DBConsole/OEM In 10G
NOTE:863312.1 - Best Practices for running catalog, catproc and utlrp script
NOTE:754401.1 - Errors ORA-31623 And ORA-600 [kwqbgqc: bad state] During DataPump Export Or Import
NOTE:1150733.1 - DataPump Export (EXPDP) Fails With Errors ORA-31623 ORA-6512 If Parameter _FIX_CONTROL='6167716:OFF' Has Been Set

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

19 − 18 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部