Oct 29, 2016

EXPDP backup failed with error ORA-04031

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.


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

Translate >>