一套oracle 11.2.0.2 数据仓库系统使用expdp导出数据时 进程hang 死 使用kill -9命令杀掉之后 database一直没有释放latch 通过下面的日志可以发现 pmon已经发现 ospid=15038的process dead 但是无法clean 进程在为 “fixed allocation callback” 分配内存时 遭遇了ORA-4031,在这套库中stream_pool_size 并没有显式指定,而从11g开始oracle data pump开始使用Advanced Queue高级队列来控制其job作业,这也就是这个case的root cause。

我们可以通过显式指定stream_pool_size解决这个问题,注意指定stream_pool_size之后必须restart instance.

我们可以通过3个note完整的理解这个问题

DataPump Export (EXPDP) Fails With Errors UDE-31623 ORA-4031 [ID 1318528.1]
UDE-31623 Error With DataPump Export [ID 1080775.1]
Bug 9896536: MEMORY LEAKE OCCURS IN STREAM SPOOL WHEN EXPDP IS EXECUTED.

*** 2012-07-16 13:34:14.231
found process 0x1bd12952b0 pid=403 serial=16 ospid = 15038 dead

*** 2012-07-16 13:34:19.445
kssxdl: error deleting SO: 0x1bd98ba410 = transaction (55)
  ----------------------------------------
  SO: 0x1bd98ba410, type: 55, owner: 0x1b015a1100, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x1bd12952b0, name=transaction, file=ktccts.h LINE:407, pg=0
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")

*** 2012-07-16 13:34:29.456
found process 0x1bd12952b0 pid=403 serial=16 ospid = 15038 dead

*** 2012-07-16 13:34:34.713
kssxdl: error deleting SO: 0x1bd98ba410 = transaction (55)
  ----------------------------------------
  SO: 0x1bd98ba410, type: 55, owner: 0x1b015a1100, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x1bd12952b0, name=transaction, file=ktccts.h LINE:407, pg=0
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")

*** 2012-07-16 13:34:44.732
found process 0x1bd12952b0 pid=403 serial=16 ospid = 15038 dead
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Mon Jul 16 13:49:03 2012
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /data/oracle/diag/rdbms/std/edw1/trace/edw1_pmon_14903.trc:
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Mon Jul 16 13:49:18 2012
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /data/oracle/diag/rdbms/std/edw1/trace/edw1_pmon_14903.trc:
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Mon Jul 16 13:49:34 2012
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /data/oracle/diag/rdbms/std/edw1/trace/edw1_pmon_14903.trc:
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")