Thursday, February 28, 2008

ORA-4030 on 10gR1 RAC instance at Windows 2003 Server Part1

This error indicates that the oracle server process is unable to allocate more memory from the operating system.This memory consists of the PGA (Program Global Area) and its contents depend upon the server configuration.For dedicated server processes it contains the stack and the UGA (User Global Area) which holds user session data, cursor information and the sort area. In a multithreaded configuration (shared server), the UGA is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.

Diagnostic queries

1) TOTAL ORACLE MEMORY ALLOCATED

select sum(bytes)/1024/1024 Mb from
(select bytes from v$sgastat
union
select value bytes from
v$sesstat s,
v$statname n
where
n.STATISTIC# = s.STATISTIC# and
n.name = 'session pga memory'
);

2) PGA MEMORY
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- -------------
pga_aggregate_target big integer 150M

PGA Memory allocated

select
sum(value)/1024/1024 Mb
from
v$sesstat s, v$statname n
where
n.STATISTIC# = s.STATISTIC# and
name = 'session pga memory';

3) Which process requesting too much memory
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;

If all of your queries are in memory parameters limits then in windows try the following :

1) Find if any job running wants more memory. if this job is necessary to run , move it in an other time or proceed to the next steps.

2) Increase PGA

3) Increase SGA

ATTENTION : SGA + PGA must me less than 2Gb on 32bit Windows System.

If you by pass this system you must set windows to allow 3Gb.

Here is the boot.ini on the system drive

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems] multi(0)disk(0)rdisk(0)partition(2)\WINNT="Windows NT Server Version 4.00" /3GB
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Windows NT Server Version 4.00 [VGA mode]" /basevideo /sos

Note the /3GB setting added to the 4th line above

No comments:

Post a Comment