expdp遇到UDE-31623ORA-31623ORA-06512
⼀个简单的expdp导出，在之前是正常的，但是隔了⼀天后出现问题了，具体报错信息如下：
UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this ssion via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1
解决过程：
1、刚开始以为是ssion数满了，因为该机器ssion数设置很少，很容易满。经过测试不是该问题，⽽且我后来想了下如果ssion数满了的话，报错信息应该不是这个。
2、删除该⽤户下export表，删除了还是不好使
3、⽹上查资料，说应该授予create any table的权限，授予了还是不好使
4、查看mos，让检查dba_registry和所有⽆效对象308388.1，检查了都是对的
5、查看stream_pool_size⼤⼩，参考⽂章How to resolve the Data Pump error ORA-31623 (a job is not attached to this ssion via the specified handle) ? (⽂档 ID 1907256.1)；其原值是0，也就是如果⾃动调节，这个没有下限；查看v$sgainfo看到这个stream pool 的⼤⼩为32MB，db_cache_size有6G，shared_pool_size有3G，总的sga就9.6G左右，所以这个stream_pool被压制得很厉害，给其设置⼀个下限值，改成128MB时报错，内存没有多的可⽤于调节这个，说明sga空闲内存不多了，改成64MB成功，单后执⾏expdp也成功了。
第⼀次遇到stream_pool_size导致的expdp问题，记录以下备忘。
How to resolve the Data Pump error ORA-31623 (a job is not attached to this ssion via the specified handle) ? (⽂档 ID 1907256.1) 转到底部转到底部
In this Document
Goal
Solution
References
Applies to:
Oracle Databa - Standard Edition - Version 10.1.0.2 to 12.1.0.2 [Relea 10.1 to 12.1]
Enterpri Manager for Oracle Databa - Version 10.1.0.2 to 12.1.0.6.0 [Relea 10.1 to 12.1]
Oracle Databa - Personal Edition - Version 10.1.0.2 to 12.1.0.2 [Relea 10.1 to 12.1]
Oracle Databa - Enterpri Edition - Version 10.1.0.2 to 12.1.0.2 [Relea 10.1 to 12.1]
Information in this document applies to any platform.
Goal
This document explains how to resolve the following errors during an Export DataPump (expdp) or Import DataPump job (impdp):
$ expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_f.dmp LOGFILE=expdp_f.log FULL=y
Export: Relea 11.2.0.1.0 - Production on Thu Jun 19 13:14:32 2014
Copyright 1982, 2009, Oracle and/or its affiliates. All rights rerved.
Connected to: Oracle Databa 11g Enterpri Edition Relea 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 ssion 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 ssion 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 ssion 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 veral possible reasons why a Data Pump cannot be started. Each root cau has its own solution.
Solution
1. First check the value for the STREAMS_POOL_SIZE in the databa:
connect / as sysdba
show parameter streams_pool
lect * from v$sgainfo;
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 increa the STREAMS_POOL_SIZE.
Manual ttings for the STREAMS_POOL_SIZE of 64M, 128M or even to 256M have proven to be successful.
For details and full resolution, e:
Note 1080775.1 - UDE-31623 Error With DataPump Export
2. Check for any possible invalid Data Pump queue objects:
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;
lect object_id, owner, object_name, status from dba_objects
where object_name like 'KUPC$DATAPUMP_QUETAB%';
t lines 100
col status for a9
col object_type for a20;
col owner.object for a50
lect 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], [], [], [], [], []
For details and full resolution, e:
Note 754401.1 - Errors ORA-31623 And ORA-600 [kwqbgqc: bad state] During DataPump Export Or Import
3. Check for any invalid registry components (CATALOG, CATPROC and JAVAVM), and invalid sys owned objects:
connect / as sysdba
t lines 90
col version for a12
col comp_id for a8
col schema like version
col comp_name format a35
col status for a12
lect comp_id,schema,status,version,comp_name from dba_registry order by 1;
t lines 120
col status for a9
col object_type for a20;
col owner.object for a50
lect status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
from dba_objects
where status != 'VALID' and owner='SYS' and object_name not like 'BIN$%'
order by 4,2;
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 databa:
connect / as sysdba
-- Start spooling to file:
spool catproc.out
t lines 120 numwidth 12 pages 10000 long 2000000000
alter ssion t NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
show ur
lect sysdate from dual;
shutdown immediate
-- for 9.2, u: startup migrate
startup migrate
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
spool off
spool registry.out
-- Registry status:
t lines 90
col version for a12
col comp_id for a8
col schema like version
col comp_name format a35
col status for a12
lect comp_id,schema,status,version,comp_name from dba_registry order by 1;
-- Invalid objects:
t lines 120
col status for a9
col object_type for a20;
col owner.object for a50
lect status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
from dba_objects
where status != 'VALID' and owner='SYS' and object_name not like 'BIN$%'
order by 4,2;
shutdown immediate
startup
spool off
For details and references, e:
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 ca 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.
4. Check if parameter _FIX_CONTROL is t for Bug 6167716:
connect / as sysdba
show parameter _fix_control
If this hidden parameter is t, then a Data Pump job will fail.
For details and full resolution, e:
Note 1150733.1 - DataPump Export (EXPDP) Fails With Errors ORA-31623 ORA-6512 If Parameter
_FIX_CONTROL='6167716:OFF' Has Been Set
5. If the Data Pump job is started through a package, check if the package was created with invoker's right (AUTHID clau):
connect / as sysdba
t lines 120 numwidth 12 pages 10000 long 2000000000
col ddl for a100
lect _ddl('PACKAGE','MY_PACKAGE','SCOTT') "DDL" from dual;
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, e:
Note 1579091.1 - DataPump Job Fails With Error ORA-31623 A Job Is Not Attached To This Session Via The Specified Handle
6. If the Data Pump job is started in DBConsole / OEM, and the job is lected to be re-run (or you want to edit the job), then
the Data Pump job will fail and following errors will be reported:
ERROR: No data pump job named "jobname" exists in the databa
ORA-31623: a job is not attached to this ssion via the specified handle
Execute Failed: ORA-31623: a job is not attached to this ssion via the specified handle
ORA-6512: at "SYS.DBMS_DATAPUMP", line 2315
ORA-6512: at "SYS.DBMS_DATAPUMP", line 3157
ORA-6512: at line 27 (DBD ERROR: OCIStmtExecute)
-- or --
Edit is not supported for this job type, only general information
For details and full resolution, e:
Note 788301.1 - Error ORA-31623 On DataPump Export Via DBScheduler After First Run Was Successful
Note 461307.1 - How To Export Databa Using DBConsole/OEM In 10G
7. If parameter LOGTIME is being ud, Data Pump export/import with LOGTIME parameter crashes if the environment
variable NLS_DATE_FORMAT is t.
For details and full resolution, e:
Note 1936319.1 - Data Pump Export Or Import Throws ORA-31623 When Using LOGTIME Parameter
References
NOTE:754401.1 - Errors ORA-31623 And ORA-600 [kwqbgqc: bad state] During DataPump Export Or Import
NOTE:1080775.1 - UDE-31623 Error With DataPump Export
NOTE:308388.1 - Error ORA-31623 When Submitting A DataPump Job
NOTE:1150733.1 - DataPump Export (EXPDP) Fails With Errors ORA-31623 ORA-6512 If Partameter
_FIX_CONTROL='6167716:OFF' Has Been Set
NOTE:1579091.1 - DataPump Job Fails With Error ORA-31623 A Job Is Not Attached To This Sessio
n Via The Specified Handle NOTE:788301.1 - Error ORA-31623 On DataPump Export Via DBScheduler After First Run Was Successful
NOTE:461307.1 - How To Export Databa Using DBConsole/OEM In 10G
NOTE:863312.1 - Best Practices for running catalog, catproc and utlrp script
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
