Friday, September 27, 2013

ORA-04031 during IMPDP 11g

Scenario:

Received the following error while trying to import data using datapump.

ORA-39097: Data Pump job encountered unexpected error -4031
ORA-39065: unexpected master process exception in MAIN
ORA-04031: unable to allocate 2024 bytes of shared memory ("streams pool","unknown object","streams pool","kodpaih3 image")
Job "SYS"."SYS_IMPORT_FULL_01" stopped due to fatal error at 11:19:39

Version:

Oracle EE 11.2.0.2.0

Reason:

The ORA-4031 error message in the above error stack, indicates a problem with the Streams pool. The database, where the operation failed, was set to follow AMM and individually  STREAMS_POOL_SIZE instance parameter was set to 0. Data Pump operations utilize the Oracle Streams pool and the size of the stream role in ideal case should be increased automatically on demand. In this case that has not happened and resulted in the above error.

Solution:

Set STREAMS_POOL_SIZE>0 to guarantee a minimum size for the streams pool when using DB  that is set to use AMM (or even ASSM).

alter system set streams_pool_size=150m;

Restarted the IMPDP operation. This time the ORA-4031 errors were missing.

 

1 comment: