合 数据泵导入碰到UDI-31623和ORA-31623 a job is not attached to this session via the specified handle错误
Tags： Oracle故障处理数据泵ORA-31623UDI-31623
UDI-31623和ORA-31623: a job is not attached to this session via the specified handle
现象
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | d:\>impdp system/"lhr"@127.0.0.1/jde DIRECTORY=D1 dumpfile=JDE92.DMP FULL=Y EXCLUDE=STATISTICS table_exists_action=REPLACE Import: Release 19.0.0.0.0 - Production on Wed Jun 15 20:45:56 2022 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production UDI-31623: operation generated ORACLE error 31623 ORA-31623: a job is not attached to this session via the specified handle ORA-06512: at "SYS.DBMS_DATAPUMP", line 4747 ORA-06512: at "SYS.KUPV$FT_INT", line 2144 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.KUPV$FT_INT", line 2081 ORA-06512: at "SYS.DBMS_DATAPUMP", line 2263 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4496 ORA-06512: at "SYS.DBMS_DATAPUMP", line 6127 ORA-06512: at line 1 |
分析
1、查看告警日志是否有啥错误信息输出
2、分析streams_pool_size是否太小，最少配置128M
解决
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | SQL> show parameter streams NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ streams_pool_size big integer 0 SQL> alter system set streams_pool_size=256m ; System altered. SQL> show parameter streams NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ streams_pool_size big integer 256M SQL> show parameter pool NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ buffer_pool_keep string buffer_pool_recycle string java_pool_size big integer 4M large_pool_size big integer 0 memoptimize_pool_size big integer 0 olap_page_pool_size big integer 0 shared_pool_reserved_size big integer 27053260 shared_pool_size big integer 1G streams_pool_size big integer 256M SQL> select * from v$sgainfo; NAME BYTES RES CON_ID -------------------------------- ---------- --- ---------- Fixed SGA Size 9336696 No 0 Redo Buffers 7438336 No 0 Buffer Cache Size 4093640704 Yes 0 In-Memory Area Size 0 No 0 Shared Pool Size 1908408320 Yes 0 Large Pool Size 0 Yes 0 Java Pool Size 4194304 Yes 0 Streams Pool Size 268435456 Yes 0 Shared IO Pool Size 0 Yes 0 Data Transfer Cache Size 0 Yes 0 Granule Size 4194304 No 0 Maximum SGA Size 6291453816 No 0 Startup overhead in Shared Pool 390813544 No 0 Free SGA Memory Available 0 0 14 rows selected. |
另外，shared_pool_size参数也需要配置大一点，否则可能会报如下的错误：
1 2 3 4 | ORA-31693: Table data object "DV"."F9K" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-39776: fatal Direct Path API error loading table "DV"."F9K" ORA-04031: unable to allocate 13224 bytes of shared memory ("shared pool","unknown object","KTSL subheap","ktsl_load_disp-2") |
参考
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)
GOAL
This document explains how to resolve the following errors during an Export DataPump (expdp) or Import DataPump job (impdp).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | $ expdp system/<PASSWORD> DIRECTORY=<directory_name> DUMPFILE=<dmp_name>.dmp LOGFILE=<log_name>.log FULL=y Export: Release 11.2.0.1.0 - Production on Thu Jun 19 13:14:32 2014 Copyright 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options UDE-31623: operation generated ORACLE error 31623 ORA-31623: a job is not attached to this session via the specified handle ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488 ORA-06512: at line 1 -- or: -- UDI-31623: operation generated ORACLE error 31623 ORA-31623: a job is not attached to this session via the specified handle ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4583 ORA-06512: at line 1 -- or: -- UDI-00008: operation generated ORACLE error 31623 ORA-31623: a job is not attached to this session via the specified handle ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4583 ORA-06512: at line 1 ... |
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:
1 2 3 4 5 6 | connect / as sysdba show parameter streams_pool select * from v$sgainfo; ... Streams Pool Size 0 Yes |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | connect / as sysdba show parameter aq col owner for a10 col object_name for a30 analyze table kupc$datapump_quetab validate structure cascade; analyze table kupc$datapump_quetab_1 validate structure cascade; select object_id, owner, object_name, status from dba_objects where object_name like 'KUPC$DATAPUMP_QUETAB%'; set lines 100 col status for a9 col object_type for a20; col owner.object for a50 select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT" from dba_objects where object_name like '%DATAPUMP_QUETAB%' order by 3,4; |
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], [], [], [], [], []
