RE: rman failure ORA-04030 indicates memory issue
- From: "David Sharples" <dsharples@xxxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 5 Oct 2004 16:20:42 +0100
Each Oracle process wasn't using 450M, it is a shared memory thing, use
pmap to determine what memory you are actually using
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Paula_Stankus@xxxxxxxxxxxxxxx
Sent: 05 October 2004 16:15
To: Paula_Stankus@xxxxxxxxxxxxxxx; tim@xxxxxxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
Subject: RE: rman failure ORA-04030 indicates memory issue
Well,
We ended up swapping in production although looking at top sessions we
see that each oracle process consumes 450Mb of memory but only 1Gb of
RAM being used and certainly not all swap!!! =3D20
In fact when we were "swapping" we saw 4Gb of RAM still available on the
system. The sysadmin states that processes are swapped when idle!!! =
=3D20
What is the unix tool to see if we are swapping? =3D20
-----Original Message-----
From: Stankus, Paula G=3D20
Sent: Tuesday, October 05, 2004 7:46 AM
To: Stankus, Paula G; 'tim@xxxxxxxxxxxxx'; 'Oracle-L@Freelists. Org
(E-mail)'
Subject: RE: rman failure ORA-04030 indicates memory issue
From documentation on metalink
4- Using automatic PGA memory management will help also reducing the
possibility of getting ora-4030 errors unless we hit a OS limit,
because work area sizes will be controlled and adjusted automatically
based on the PGA_AGGGREGATE_TARGET parameter first and then the
current work load. =3D20
Is this OS limit related to ulimit then??? What is the OS limit????
What I saw was ORA-04030 errors with 4Gb Ram still available. So this
is a target - right - but will go over that target based on current work
load - hmmmmm. I even tried to use alter system and dynamically adjust
the pga aggregate to larger and larger amounts without improvement. =
=3D20
Doesn't PGA_AGGREGATE_TARGET work as described in the documentation -
much like partitioned views once didn't and .............???
-----Original Message-----
From: Stankus, Paula G
Sent: Tuesday, October 05, 2004 7:39 AM
To: Stankus, Paula G; 'tim@xxxxxxxxxxxxx'; 'Oracle-L@Freelists. Org
(E-mail)'
Subject: RE: rman failure ORA-04030 indicates memory issue
Somehow the maximum amount of PGA consumption looking at v$process was
greater than the allocated amount. I found that it basically was a hung
batch process will have to talk to DBA colleague to see how this can be
tuned. =3D20
In any case, I was able to kill the hung job and cycle the database and
my backups worked just fine. There isn't an issue about the backups but
an issue on the batch processes.
Question: with pga_aggregate_target is there anyway to ensure one
process doesn't consume total memory available? What I saw was that the
pga_aggregate_target was entirely used by one batch process causing all
other connections and processes to fail with ORA-04030. =3D20
I went through old fatcity and freelists and saw some reference to
abandoning pga_aggregate_target likely for this very reason. =3D20
-----Original Message-----
From: Stankus, Paula G
Sent: Tuesday, October 05, 2004 5:41 AM
To: Stankus, Paula G; 'tim@xxxxxxxxxxxxx'; 'Oracle-L@Freelists. Org
(E-mail)'
Subject: RE: rman failure ORA-04030 indicates memory issue
Okay,
-one backup doesn't work because it is getting an error indicating a
connection problem but I KNOW IT ISN'T A CONNECTION PROBLEM -one backup
doesn't work because I get the ORA-04030 error -I know that this is
indicating an issue with the user getting enough user process memory -
i.e. pga. However, the aggregate target should be across all instances.
I am playing with pga_aggregate_target using alter system ...but to no
avail.
-I also feel that my ulimits should likely be set to unlimited and I am
wondering if this is playing into this at all.
-Finally, I am wondering if the sga_max_size (unrelated to this
particular error but very scary) and this Sun bug is an issue.
-----Original Message-----
From: Stankus, Paula G
Sent: Tuesday, October 05, 2004 5:31 AM
To: Stankus, Paula G; 'tim@xxxxxxxxxxxxx'; 'Oracle-L@Freelists. Org
(E-mail)'
Subject: RE: rman failure ORA-04030 indicates memory issue
I also ran across some references that indicate there is a problem with
dynamic memory allocation on Solaris 8/9 and a bug patch. I found that
users had disabled sga_max_size (i.e. dynamic sga) until the patch was
applied. =3D20
Has anyone else dealt with this issue? =3D20
-----Original Message-----
From: Stankus, Paula G
Sent: Tuesday, October 05, 2004 5:18 AM
To: Stankus, Paula G; 'tim@xxxxxxxxxxxxx'; 'Oracle-L@Freelists. Org
(E-mail)'
Subject: RE: rman failure ORA-04030 indicates memory issue
Also noticed the following: process stack was set to 8192 and nofiles
to 256. I am wondering if these should be set to unlimited as I
understand they would only be used if needed. Also, if I use a ulimit
to reset the values when do these settings take place? Immediately??
=3D20
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) unlimited
nofiles(descriptors) 256
vmemory(kbytes) unlimited
[oracle@dohsdb10]:/opt/oracle/product/9.2.0/dbs
[leprod:> ulimit -s unlimited
[oracle@dohsdb10]:/opt/oracle/product/9.2.0/dbs
[leprod:> ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) unlimited
coredump(blocks) unlimited
nofiles(descriptors) 256
vmemory(kbytes) unlimited
[oracle@dohsdb10]:/opt/oracle/product/9.2.0/dbs
-----Original Message-----
From: Stankus, Paula G
Sent: Tuesday, October 05, 2004 4:58 AM
To: Stankus, Paula G; 'tim@xxxxxxxxxxxxx'; 'Oracle-L@Freelists. Org
(E-mail)'
Subject: RE: rman failure ORA-04030 indicates memory issue
I am also wondering if the batch processes are somehow holding onto
memory. Some appeared to fail and they should have completed long
before the backup failed.
-----Original Message-----
From: Stankus, Paula G
Sent: Tuesday, October 05, 2004 4:43 AM
To: 'tim@xxxxxxxxxxxxx'; Oracle-L@Freelists. Org (E-mail)
Subject: rman failure ORA-04030 indicates memory issue
Rman fails with:
I notice that
A. A number of batch processes were added to database and likely using
a good deal of "sort" area as large batch processes often do B. Volume
of data added last night.
C. This error occurred along with another error:
ORA-12540: TNS:internal limit restriction exceeded
ORA-04030: out of process memory when trying to allocate 524824 bytes
(pga heap,KSFQ Buffers)
I looked at TOP on Solaris while running the job that failed with
ORA-04030 and out of 8 Gb, 4Gb of RAM were still showing as available.
I decreased the filesperset in rman from 10 to 4 and am concerned with
the degradation of runtime for my backup processes.
The pga_aggregate_target is set at pga_aggregate_target=3D3D1610612736
sga_max_size=3D3D2147483648
processes=3D3D150.
I plan to look at the pga_aggregate_target related views. I also
noticed in the alert.log the following:
ORA-07445: exception encountered: core dump [0000000101D51DF0] [SIGBUS]
[Object specific hardware error] [0xFFFFFFFF7CA9FE90] [] []
Along with a trace file with a specific SQL statement. =3D20
Question: How appropriate/inappropriate are the pga_aggregate_target,
sga_max_size given 8Gb RAM and that this is the only database on the
system?
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
