ORA-04030
Posted by Arul Ramachandran on November 12, 2007
On seeing the ORA-04030 error reported on a Oracle 9.2.0.6 RAC database running on Solaris 9, VCS SFRAC, being somewhat familiar with this error message I started looking at PGA and ulimit related settings.
Before I delve any further, here is a bit of background:
oerr ora 4030
04030, 00000, “out of process memory when trying to allocate %s bytes (%s,%s)”
// *Cause: Operating system process private memory has been exhausted
// *Action:
Automatic PGA had been set on this database:
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ workarea_size_policy string AUTO pga_aggregate_target big integer 1073741824
1Gb for pga_aggregate_target on a server with 16Gb memory for the nature of the database operations seemed quite reasonable.
ulimit settings were as follows:
ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) unlimited
nofiles(descriptors) 4096
vmemory(kbytes) unlimited
On some further checking, it was found processes and sessions were set as follows
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sessions integer 2500 processes integer 700
These settings were odd, on further checking it was found that another DBA had changed these values to test a change to VCS SFRAC, but the old values were not restored after the test.
On changing these parameters back to old values processes=1000 and sessions=1105, the problem disappeared.
The moral of the story here is, when troubleshooting issues we need to keep an eye on not so straightforward causes as well.
