5 Easy Step to Solve ORA-04031 with Oracle Support Provided Tool

Are you still seeing "ORA-04031: unable to allocate 512 bytes of shared memory". Now Remote DBA need not to worry about this error because Oracle has introduced a very good tool to Troubleshoot ORA-04031. You just need to upload alert log files and trace file having error details and an online tool will give a solution immediately.

Before going into detail, Let's first understand what is ORA-04031 ? when It arise ? and what is difference between ORA-04031 and ORA-04030 ?

What is ORA-04031 ? and When it arise ?

ORA-04031 is error message related to lack of available SGA memory component. When a process needs some memory from SGA and memory is not available in this situation ORA-04031 occurs.

What is difference between ORA-04031 and ORA-04030 ?

ORA-04031 is error message related to lack of available SGA memory component While RA-04030 is related to lack of available memory in PGA area.

ORA-4031 error message look like this in Database Alert log files:
Tue Sep 27 22:20:33 2011
Errors in file /u01/app/oracle/diag/rdbms/dbh/Dbh3/trace/Dbh3_j004_16488.trc  (incident=72497):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select t.ts#,t.file#,t.block...","SQLA","tmp")
Incident details in: /u01/app/oracle/diag/rdbms/dbh/Dbh3/incident/incdir_72497/DBH3_j004_16488_i72497.trc
Tue Sep 27 22:20:43 2011
Dumping diagnostic data in directory=[cdmp_20110927222043], requested by (instance=3, osid=16488 (J004)), summary=[incident=72497].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

ORA-4030 error message look like this in Database Alert log files:
Wed Mar 27 13:35:52 2013
Errors in file /u01/app/orappdw1/diag/rdbms/dbh/DBH4/trace/DBH4_pz99_15585_DBMS_SQLDIAG_10053_20130327_132724.trc  (incident=146193):
ORA-04030: out of process memory when trying to allocate 8224 bytes (qesca subheap,qerjo hash table) 
Incident details in: /u01/app/orappdw1/diag/rdbms/dbh/DBH4/incident/incdir_146193/DBH4_pz99_15585_i146193.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Mar 27 13:35:57 2013
Note a big difference, but ORA-04031 is SGA related error and ORA-4030 is PGA related error. Let's understand what does this error statement means.

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select t.ts#,t.file#,t.block...","SQLA","tmp").

The first argument in brackets shows which SGA component failed with lack of memory. Here this is "shared pool" and the amount of memory was needed is given as "allocate 32 bytes". Now it clear to Database Administrator that, shared pool component of SGA is facing scarcity of memory. Same method is applied in case of understanding ORA-04030.

In this post, I will focus on solving ORA-04031: unable to allocate 512 bytes of shared memory kind of issue with an Oracle Provided tool. 

Steps to Resolve ORA-4031 Error:

DBA can easily solve this issue by resizing SGA but the solution would be temporary. To get an accurate solution with reason. He has to use this tool.

1. Access ORA-4031-Troubleshooting Tool:

Login to Oracle Support and search for "Diagnostic Tools Catalog [ID 559339.1]" in knowledge base. This is a page which has all diagnostic tools link at one place for helping DBA's. You can also explore other tools available to solve other issues. Below is the scree shot of "Diagnostic Tools Catalog [ID 559339.1]". Now click on "ORA-4031 Troubleshooting Tool" a new tool page will pop up.


2. Select Troubleshooting Option:

Below is Home page of ORA-4031-Troubleshooting Tool. This has four options

    a. Troubleshoot a new issue
    b. Review a troubleshooting report
    c. Upload new files and re-run a troubleshooting report
    d. Upload new files and re-run a troubleshooting report


Since we are Troubleshoot a new issue so choose option a and click "next" button in right corner of the page.

3. Upload Trace and Alert log file:

Next page will be Upload Files, In this DBA has to upload relevant alert log and trace files for analysis and suggestion.

 
Here also you will find two options

    a. IPS Package: Use this when you have IPS package having error details. This is a default option.
    b. Alertlog: DBA should select this when he has alter log and trace files to upload covering error details.

I am choosing "alert log" option and uploading alert log and trace file is minimum requirement for this option. Select both files one by one . One important point to notice in case of trace file selection make sure you select trace file (/u01/app/oracle/diag/rdbms/dwh/DWH3/trace DWH3_j004_16488.trc) not incident file (/u01/app/orappdw1/diag/rdbms/prdpbdw/PRDPBDW4/incident/incdir_146193/PRDPBDW4_pz99_15585_i146193.trc).

After selecting trace files click "Upload" button at bottom of the page. DBA can see upload status at left bottom of the page (highlighted in above image). As of now this is 16% Once this upload is 100% click "Next" button on top right cornet of the tool.

4. Solution is Ready:

Now, at below Screen Solution is ready to apply. Left hand side shows Issue and on Right hand side is Solution for DBA to implement. You can also send this solution to you email by providing e-mail id. 


Below is the full Report for this issue:

Troubleshooting Report:
Issue Resolution
Primary Issue:

Undersized SGA Causing Memory Shortage in Large Pool

The SGA_TARGET or MEMORY_TARGET is too small and the Large Pool is unable to get memory needed for workload.

The issue could occur if in your Trace file, ORA-4031 comes from Large Pool, and "SGA: allocation forcing component growth" exists in trace file.

The SGA_TARGET or MEMORY_TARGET is too small and the Large Pool is unable to get memory needed for workload, thus leading to ORA-04031.
Evidence Details:
** In your trace file, there is evidence of Automatic Shared Memory Management in use: 'SGA: allocation forcing component growth'

** In your trace file, HEAP DUMP heap name = large pool

ORA-04031: unable to allocate 3932184 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
ORA-04031: unable to allocate 3932184 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
Recommended Solution:

1) Use queries on V$SGA_RESIZE_OPS or V$MEMORY_RESIZE_OPS (see References below) to see if the Large Pool shows continued growth and reporting ERROR or DEFERRED for the status of those attempts to move memory.

2) If so, a 15% increase to the setting for SGA_TARGET/MEMORY_TARGET may be needed to allow MMAN more memory to manage behind the scenes. Note that it will require a restart of instance.

References
NOTE:1088239.1Title: Master Note for Diagnosing ORA-4031
NOTE:835254.1 - Script To Monitor RDBMS Session UGA and PGA Current And Maximum Usage Over Time

Queries on V$SGA_RESIZE_OPS or V$MEMORY_RESIZE_OPS to watch auto-tuning activity.
For 10g:
set pages 999
set lines 130
col component for a25 head "Component"
col status format a10 head "Status"
col initial_size for 999,999,999,999 head "Initial"
col parameter for a25 heading "Parameter"
col final_size for 999,999,999,999 head "Final"
col changed head "Changed At"

select component, parameter, initial_size, final_size, status,
to_char(end_time ,'mm/dd/yyyy hh24:mi:ss') changed
from v$sga_resize_ops
order by 6
/
For 11g:
set pages 999
set lines 130
col component for a25 head "Component"
col status format a10 head "Status"
col initial_size for 999,999,999,999 head "Initial"
col parameter for a25 heading "Parameter"
col final_size for 999,999,999,999 head "Final"
col changed head "Changed At"

select component, parameter, initial_size, final_size, status,
to_char(end_time ,'mm/dd/yyyy hh24:mi:ss') changed
from v$memory_resize_ops
order by 6
/

Go Through this detailed Report, This gives problem definition and solution suggestion. I apply this suggestion and this error was resolved.

5.  Suggested Solution Doesn't Work:

What if suggested solution doesn't work, Database Administrator can directly create an Service Request from this tool itself. On the bottom of this page, you can either give feed back or create direct SR.


Oracle also has another very easy tools to solve ORA-00600 and ORA-7445 Internal errors. Hope you are not seeing ORA-04031 any more !!

2 comments: