In one of my production environment I found my EXPDP backup failed with below error. It is not always. Same I re-created in one of my test instance. Here are the reported errors.
Error while running EXPDP backup:
==============================
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
Error in alert log at that time:
========================
Sun Oct 23 00:02:48 2016
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_787060.trc (incident=132844):
ORA-04031: unable to allocate 376 bytes of shared memory ("streams pool","unknown object","streams pool","kwqbsinfy:cco")
Incident details in: /u01/app/oracle/diag/rdbms/prod/PROD/incident/incdir_132844/PROD_ora_787060_i132844.trc
Sun Oct 23 00:02:53 2016
Dumping diagnostic data in directory=[cdmp_20161023000253], requested by (instance=1, osid=787060), summary=[incident=132844].
Solution / Fix :
============
By setting minimum value to streams_pool_size of minimum 200M we can avoid the error.
OR
In Oracle 11g, it is highly recommended to use AMM i.e memory_target where SGA and PGA are automatically managed and set minimum values to SGA_TARGET, PGA and other pools.
This configuration will give better performance and reduces fragmentation and avoids ORA-04031 errors.
Error while running EXPDP backup:
==============================
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
Error in alert log at that time:
========================
Sun Oct 23 00:02:48 2016
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_787060.trc (incident=132844):
ORA-04031: unable to allocate 376 bytes of shared memory ("streams pool","unknown object","streams pool","kwqbsinfy:cco")
Incident details in: /u01/app/oracle/diag/rdbms/prod/PROD/incident/incdir_132844/PROD_ora_787060_i132844.trc
Sun Oct 23 00:02:53 2016
Dumping diagnostic data in directory=[cdmp_20161023000253], requested by (instance=1, osid=787060), summary=[incident=132844].
Solution / Fix :
============
By setting minimum value to streams_pool_size of minimum 200M we can avoid the error.
OR
In Oracle 11g, it is highly recommended to use AMM i.e memory_target where SGA and PGA are automatically managed and set minimum values to SGA_TARGET, PGA and other pools.
This configuration will give better performance and reduces fragmentation and avoids ORA-04031 errors.
AND
set the parameter _shared_pool_reserved_pct instead of the parameter shared_pool_reserved_size.
Set the shared pool reserved percent with:
alter system set "_shared_pool_reserved_pct"=10 scope=spfile;
NOTE: Restart the database and listener once the changes are done.
The above settings will not only fix ORA-04031 but also helps for optimal memory utilization.
Thanks .
No comments:
Post a Comment