Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

unable to allocate shared memory

SOLVED
Go to solution
uspfoms
Regular Advisor

unable to allocate shared memory

First off I'm not the DBA.
My Oracle database keeps getting shared memory errors.

Ora-00604: error occurred at recursive SQL level 2.

Ora-04031: unable to allocate 4048 bytes of shared mem ("sharedpool") , "TRIGGERS$", "sga heap", "state objects")

Is this a Kernel Parameter thats needs tweaking or oracle
setting causing this ? Thanks
7 REPLIES
John Palmer
Honored Contributor
Solution

Re: unable to allocate shared memory

This is an Oracle issue, nothing to do with HP-UX (unless you can't increase the size of the SGA that is).

oerr ORA 4031 gives the following:-
// *Cause: More shared memory is needed than was allocated in the shared
// pool.
// *Action: If the shared pool is out of memory, either use the
// dbms_shared_pool package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// INIT.ORA parameters "shared_pool_reserved_size" and
// "shared_pool_size".
// If the large pool is out of memory, increase the INIT.ORA
// parameter "large_pool_size".

As a first suggestion I'd increase shared_pool_reserved_size and shared_pool_size.

Regards,
John
A. Clay Stephenson
Acclaimed Contributor

Re: unable to allocate shared memory

I could actually be both or either. You might need to increase shmmax or you might need to change some init.ora parameters.

Rather than list the steps, your best bet (and specific to your platform and Oracle versions) is to run oerr.

oerr ora 00604

oerr ora 04031

Even if you are not a dba, this is a good command to know.
If it ain't broke, I can fix that.
T G Manikandan
Honored Contributor

Re: unable to allocate shared memory

check the attachment

you need to increase the

shared_pool_size in the init.ora file
restart the database to take effect
Ian Lochray
Respected Contributor

Re: unable to allocate shared memory

What version of Oracle are you using? At 8.1.7.00 we encountered this problem due to Oracle bug 1397603. This bug means that SGA space is not freed up and so, over time, you run out of shared memory. If you increase the size of the SGA the problem will take longer to occur but the correct way to avoid the bug is to upgrade to a later Oracle patch. You can avoid the bug by reularly restarting Oracle but this may be tricky in a 24x7 environment.
uspfoms
Regular Advisor

Re: unable to allocate shared memory

THANKS ALL, I'll give these suggestions a try.
Rory R Hammond
Trusted Contributor

Re: unable to allocate shared memory

Per Ian note.

We are running on oracle patched to 8.1.7.4. HPUX 11i. and all is now stable.
Make sure you have the latest patches and then consider changing your Shared_pool_size settings. Remember to not increase oracle memory pools so large that they cause paging. If you can help it you need to buy memory.

I don't recommend regularly shutting down oracle to fix this problem. Becuase you affect production uptime. It also takes awhile for oracle to build cache, which will help performance.

There are a 100 ways to do things and 97 of them are right
Bill Hassell
Honored Contributor

Re: unable to allocate shared memory

If the size of SGA is in the 400-900 meg region anhd Oracle is running in 32bit mode, you may be hitting shared memory area fragmentation. There is only one small area for 32bit apps to use and it is a shared area with memory mapped files, shared libraries and other shared memory objects from other programs.

Set shmmax to 1000 megs (1750 megs if you have Oracle executables linked to handle a large shared area). If you still have problems, you can implement memory windows (read the docs carefully) or switch to 64bit Oracle and all your shared memory limits disappear.


Bill Hassell, sysadmin