DataPump Export Fails With Errors ORA-39014 ORA-39029 ORA-39079

刘泽宇

现象：
DataPump fails with errorstack similar to the following:

ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-39079: unable to enqueue message DG,KUPC$C_1_20071030223003,KUPC$A_1_20071030223013,MCP,8473,Y
ORA-06512: at "SYS.KUPW$WORKER", line 1342
ORA-06512: at line 2
Job "SYS"."SYS_EXPORT_FULL_01" stopped due to fatal error at 00:44:51
ORA-39014: One or more workers have prematurely exited.

原因：
The important errors here are the ORA-39014 and ORA-39079. The DataPump utility uses Streams in the background and if the Streams memory pool is not large enough, it can result in this error stack.

Use the queries below to investigate memory usage and settings:

col bytes format 999,999,999,999

select * from v$sgastat where pool='streams pool';
select * from v$sgainfo;

If using auto-tuning in the SGA, you may find the memory manager is having difficulty moving memory to satisfy memory needs in the Streams Pool.

set lines 200
col component format a20
col initial_size format 999,999,999,999
col finish_size format 999,999,999,999
col target_size format 999,999,999,999

select to_char(end_time, 'dd-MON-yyyy hh24:mi:ss') end_time,
component, initial_size, target_size,
final_size, status
from v$sga_resize_ops
order by end_time;

Look for STATUS showing up as ERROR or DEFERRED. This can mean that SGA_TARGET is too small to meet all SGA memory needs. This can also indicate that minimum sizes (explicit settings) are not included in the spfile, so under stress the auto-tuner could get too aggressive moving memory inside the SGA.


处理方法：
Increase the memory allocated in the STREAMS_POOL_SIZE to resolve this issue.

If using auto-tuning a higher value for STREAMS_POOL_SIZE will act a higher minimum value and keep the memory tuner from shrinking the Streams Pool too small for workload.