Ora-4030 HELP!

[Oleg_60]

Hi All!

I have 24Gb RAM on my HP-UX box.

I have many ORA-4030(can not allocate memory) errors in alert.log
At this time i look at glance utility and find out that there is about 2Gb free memory.

Can you say why Oracle can't allocate memory from this 2Gb free piece ???

Thank You!

[Yogeeraj_1]

hi,

can you post more information on the following:

o Oracle Version

o Operating system/platform (hp-ux 11.11 64 bits?)

o Determine which process is requesting too much memory:
SQL> col name format a30
SQL> select
sid,name,value
from
v$statname n,v$sesstat s
where
n.STATISTIC# = s.STATISTIC# and
name like 'session%memory%'
order by 3 asc;

o determine what the process is doing by running the following query:
select sql_text from v$sqlarea a, v$session s where a.address = s.sql_address and s.sid = ;

o what is your sort_area_size?

o If you are running Oracle 9i or later, what are the values for WORKAREA_SIZE_POLICY and PGA_AGGREGATE_TARGET

revert

kind regards
yogeeraj

[Nico Schockaert]

Hello,
A ORA-4030 is generally not an o/s problem, but rather a limit. The Oracle process will attempt to aquire memory up to some limits. If they request memory from the operating system and the operating system does not provide it, an ORA-4030 error is flagged. The two primary reasons for seeing this are the physical memory has been exhausted or
the o/s limit for process memory has been reached.

I suggest the following: Increase the ulimit for the process memory (data).

Hope this helps,

Nico

[Joseph Loo]

hi,

do u also get ORA-06512?

try to increase the following operating system parameters:

maxdsiz
maxtsiz
maxssiz
maxdsiz64
maxtsiz64
maxssiz64

or u may reply with the current parameters.

regards.

[Yogeeraj_1]

hi,
see also metalink note:233869.1
Subject: Diagnosing and Resolving ORA-4030 errors

regards
yogeeraj

[Oleg_60]

We use Oracle 9.2.0.6, HP-UX 11.11(64 bit)

It's very big question for me why Glance shows 2Gb free in OS but Oracle can't take this memory ??

[Oleg_60]

--what is your sort_area_size?

We do not use sort_area_size manual setting

We set WORKAREA_SIZE_POLICY = auto,
PGA_AGGREGATE_TARGET = 2Gb

[Oleg_60]

Some adds from our box ->

$ ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 262144
stack(kbytes) 8192
memory(kbytes) unlimited
coredump(blocks) 4194303
nofiles(descriptors) 2048

Have i to set data(kbytes), stack(kbytes), memory(kbytes) to unlimited ?
Can you explain me what means this three mentioned above parameters ?

[Simon Wickham_6]

Hi

You need to increase process memory quota. Please check for Memory Leak and also check the sort area size.

Regards,
Simon

[Hein van den Heuvel]

>> Some adds from our box ->

That looks fine, but this is 99.9% sure NOT to be a Unix limitation, but a volunteer restriction by Oracle following your, perhaps unintentional, instructions in init.ora.


>> I have many ORA-4030(can not allocate memory) errors in alert.log

What is the EXACT error message?
It should indicate a byte size and the type of memory.

>> Can you say why Oracle can't allocate memory from this 2Gb free piece ???

Because the Oracle init params told it exactly how much it could use and no more than that.

>> We set WORKAREA_SIZE_POLICY = auto,
PGA_AGGREGATE_TARGET = 2Gb

There you go. Oracle was told not to use more than 2gb for PGA even if there was more need and more avaiable.

Similar things happen in the SGA.
Do a SHOW SGA, there you'll see a handfull more limitations that oracle was given: buffers, fixed pool, shared pool.
Now ask the Oracle instance how much is being used.

hth,
Hein.