一套Linux x86-64上的11.2.0.2数据库在使用data pump数据泵expdp工具导出时遭遇了ORA-31623错误，详细日志如下:
[oracle@rh2 dbs]$ expdp system/password dumpfile=s01:sh.dmp schemas=sh
Export: Release 11.2.0.2.0 - Production on Mon May 2 22:46:57 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, 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 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1
/* 伴随以上ORA-31623错误在告警日志中出现了以下记录 */
2011-05-02 22:47:05.792000 +08:00
Errors in file /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ora_22060.trc (incident=54525):
ORA-04031: unable to allocate 56 bytes of shared memory
("streams pool","unknown object","streams pool","fixed allocation callback")
Incident details in: /s01/orabase/diag/rdbms/prod/PROD1/incident/incdir_54525/PROD1_ora_22060_i54525.trc
2011-05-02 22:47:08.189000 +08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Dumping diagnostic data in directory=[cdmp_20110502224708], requested by (instance=1, osid=22060), summary=[incident=54525].
2011-05-02 22:47:10.502000 +08:00
Sweep [inc][54525]: completed
Sweep [inc2][54525]: completed
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 912M
memory_target big integer 912M
shared_memory_address integer 0
从以上日志可以看到导出进程在为”fixed allocation callback”从streams pool流池中分配内存时遇到了ORA-04031错误，进而导致了UDE-31623错误。为什么expdp要从streams pool中分配内存呢？data pump数据泵在11g中开始Advanced Queue高级队列来控制其job作业的启动、停止和重启了。如果streams pool的当前size为0，那么显然无法分配到任何内存；而当前系统中就只设置了AMM特性的memory_target内存参数，并没有显式地设置streams_pool_size参数，这就导致了问题的发生。
我们可以通过上症状在MOS上搜索可以找到Note”UDE-31623 Error with Data Pump Export”:
Applies to: Oracle Server - Enterprise Edition - Version: 11.2.0.0. and later [Release: 11.2 and later ] Information in this document applies to any platform. Goal Data Pump Export does not start: $ expdp system/ directory=test dumpfile=system.dmp logfile=abcd.log full=y Export: Release 11.2.0.1.0 - Production on Thu Mar 25 12:11:31 2010 Copyright (c) 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 Solution For this Data Pump error, you will need to configure the database with some Streams Pool. Perform the following query to find out how much STREAMS_POOL_SIZE has been allocated select * from v$sgainfo; ... Streams Pool Size 0 Yes Data Pump now uses Advanced Queuing (AQ) internally, so it can handle job stops/starts/re-starts. However, AQ using the Streams Pool, where the database currently has the STREAMS_POOL_SIZE set to ZERO. Manually set the STREAMS_POOL_SIZE, re-start the database and re-attempt the Data Pump Export. Alternately, turn on Automatic Shared Memory management (ASMM) by setting SGA_TARGET or MEMORY_TARGET.
显然上述Note在有一点上说错了，那就是设置memory_target参数无益于解决该UDE-31623问题；最好的做法还是显式地去设置streams_pool_size参数，但遗憾的是streams_pool_size虽然是一个可以动态修改的参数，但实际上是无法动态修改的:
SQL> col name for a20
SQL>
SQL> select name,isinstance_modifiable from v$system_parameter where name='streams_pool_size';
NAME ISINS
-------------------- -----
streams_pool_size TRUE
SQL> alter system set streams_pool_size=60M sid='PROD1';
alter system set streams_pool_size=60M sid='PROD1'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
SQL> alter system set streams_pool_size=60M sid='PROD1' scope=spfile;
System altered.
Restart Instance .................
[oracle@rh2 dbs]$ expdp system/password dumpfile=s01:sh1.dmp schemas=sh
Export: Release 11.2.0.2.0 - Production on Mon May 2 23:28:33 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=s01:sh1.dmp schemas=sh
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 273.8 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/DIMENSION
. . exported "SH"."CUSTOMERS" 9.853 MB 55500 rows
. . exported "SH"."COSTS":"COSTS_Q1_1998" 139.5 KB 4411 rows
. . exported "SH"."COSTS":"COSTS_Q1_1999" 183.5 KB 5884 rows
. . exported "SH"."COSTS":"COSTS_Q1_2000" 120.6 KB 3772 rows
. . exported "SH"."COSTS":"COSTS_Q1_2001" 227.8 KB 7328 rows
. . exported "SH"."COSTS":"COSTS_Q2_1998" 79.52 KB 2397 rows
. . exported "SH"."COSTS":"COSTS_Q2_1999" 132.5 KB 4179 rows
. . exported "SH"."COSTS":"COSTS_Q2_2000" 119.0 KB 3715 rows
. . exported "SH"."COSTS":"COSTS_Q2_2001" 184.5 KB 5882 rows
. . exported "SH"."COSTS":"COSTS_Q3_1998" 131.1 KB 4129 rows
. . exported "SH"."COSTS":"COSTS_Q3_1999" 137.3 KB 4336 rows
. . exported "SH"."COSTS":"COSTS_Q3_2000" 151.4 KB 4798 rows
. . exported "SH"."COSTS":"COSTS_Q3_2001" 234.4 KB 7545 rows
. . exported "SH"."COSTS":"COSTS_Q4_1998" 144.7 KB 4577 rows
. . exported "SH"."COSTS":"COSTS_Q4_1999" 159.0 KB 5060 rows
. . exported "SH"."COSTS":"COSTS_Q4_2000" 160.2 KB 5088 rows
. . exported "SH"."COSTS":"COSTS_Q4_2001" 278.4 KB 9011 rows
. . exported "SH"."SALES":"SALES_Q1_1998" 1.412 MB 43687 rows
. . exported "SH"."SALES":"SALES_Q1_1999" 2.071 MB 64186 rows
. . exported "SH"."SALES":"SALES_Q1_2000" 2.012 MB 62197 rows
. . exported "SH"."SALES":"SALES_Q1_2001" 1.965 MB 60608 rows
. . exported "SH"."SALES":"SALES_Q2_1998" 1.160 MB 35758 rows
. . exported "SH"."SALES":"SALES_Q2_1999" 1.754 MB 54233 rows
. . exported "SH"."SALES":"SALES_Q2_2000" 1.802 MB 55515 rows
. . exported "SH"."SALES":"SALES_Q2_2001" 2.051 MB 63292 rows
. . exported "SH"."SALES":"SALES_Q3_1998" 1.633 MB 50515 rows
. . exported "SH"."SALES":"SALES_Q3_1999" 2.166 MB 67138 rows
. . exported "SH"."SALES":"SALES_Q3_2000" 1.909 MB 58950 rows
. . exported "SH"."SALES":"SALES_Q3_2001" 2.130 MB 65769 rows
. . exported "SH"."SALES":"SALES_Q4_1998" 1.581 MB 48874 rows
. . exported "SH"."SALES":"SALES_Q4_1999" 2.014 MB 62388 rows
. . exported "SH"."SALES":"SALES_Q4_2000" 1.814 MB 55984 rows
. . exported "SH"."SALES":"SALES_Q4_2001" 2.257 MB 69749 rows
. . exported "SH"."SUPPLEMENTARY_DEMOGRAPHICS" 697.3 KB 4500 rows
. . exported "SH"."FWEEK_PSCAT_SALES_MV" 419.8 KB 11266 rows
. . exported "SH"."PROMOTIONS" 58.89 KB 503 rows
. . exported "SH"."TIMES" 380.8 KB 1826 rows
. . exported "SH"."CAL_MONTH_SALES_MV" 6.312 KB 48 rows
. . exported "SH"."CHANNELS" 7.25 KB 5 rows
. . exported "SH"."COUNTRIES" 10.20 KB 23 rows
. . exported "SH"."PRODUCTS" 26.17 KB 72 rows
. . exported "SH"."COSTS":"COSTS_1995" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_1996" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_H1_1997" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_H2_1997" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2003" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2003" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2003" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2002" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2003" 0 KB 0 rows
. . exported "SH"."DIMENSION_EXCEPTIONS" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_1995" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_1996" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_H1_1997" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_H2_1997" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q1_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q1_2003" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q2_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q2_2003" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q3_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q3_2003" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q4_2002" 0 KB 0 rows
. . exported "SH"."SALES":"SALES_Q4_2003" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/s01/sh1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 23:30:20
/* 11g中加强了data pump在告警日志中的记录信息，可以看到这里成功启动了DM进程*/
DM00 started with pid=55, OS id=25347, job SYSTEM.SYS_EXPORT_SCHEMA_01
2011-05-02 23:28:35.814000 +08:00
Starting background process SMCO
SMCO started with pid=56, OS id=25349
DW00 started with pid=57, OS id=25351, wid=1, job SYSTEM.SYS_EXPORT_SCHEMA_01
2011-05-02 23:28:54.029000 +08:00
XDB installed.
XDB initialized.
Leave a Reply