Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: rman failure ORA-04030 indicates memory issue

RE: rman failure ORA-04030 indicates memory issue

From: David Sharples <dsharples_at_cerebrussolutions.com>
Date: Tue, 5 Oct 2004 16:20:42 +0100
Message-ID: <EA29A3FCC723674293FD6286D3F0513E983DAF@louis.cerebrus.com>


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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Paula_Stankus_at_doh.state.fl.us
Sent: 05 October 2004 16:15
To: Paula_Stankus_at_doh.state.fl.us; tim_at_sagelogix.com; oracle-l_at_freelists.org
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_at_sagelogix.com'; 'Oracle-L_at_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_at_sagelogix.com'; 'Oracle-L_at_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_at_sagelogix.com'; 'Oracle-L_at_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_at_sagelogix.com'; 'Oracle-L_at_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_at_sagelogix.com'; 'Oracle-L_at_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_at_dohsdb10]:/opt/oracle/product/9.2.0/dbs
[leprod:> ulimit -s unlimited

[oracle_at_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_at_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_at_sagelogix.com'; 'Oracle-L_at_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_at_sagelogix.com'; Oracle-L_at_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?

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Tue Oct 05 2004 - 10:15:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US