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
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.
Very nice post, thanks for sharing.
ReplyDelete