From oracle-l-bounce@freelists.org Tue Oct 5 06:41:22 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i95BfM313997 for ; Tue, 5 Oct 2004 06:41:22 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i95BfMI13992 for ; Tue, 5 Oct 2004 06:41:22 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EFD8E72C211; Tue, 5 Oct 2004 06:47:26 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 17515-15; Tue, 5 Oct 2004 06:47:26 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 54DB172C1BF; Tue, 5 Oct 2004 06:47:26 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Subject: RE: rman failure ORA-04030 indicates memory issue Date: Tue, 5 Oct 2004 07:45:51 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: rman failure ORA-04030 indicates memory issue Thread-Index: AcSqp7nLvBJIQA4QTUyMWwktOh62iwADla7gAADOENAAAGBY0AAAvQDwAABTH4AABBbuMAAARVJA From: To: , , X-OriginalArrivalTime: 05 Oct 2004 11:45:51.0764 (UTC) FILETIME=[DD66D540:01C4AAD0] X-archive-position: 10684 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Paula_Stankus@doh.state.fl.us Precedence: normal Reply-To: Paula_Stankus@doh.state.fl.us X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org >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. =20 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. =20 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=20 Sent: Tuesday, October 05, 2004 7:39 AM To: Stankus, Paula G; 'tim@sagelogix.com'; '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. =20 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. =20 I went through old fatcity and freelists and saw some reference to abandoning pga_aggregate_target likely for this very reason. =20 -----Original Message----- From: Stankus, Paula G=20 Sent: Tuesday, October 05, 2004 5:41 AM To: Stankus, Paula G; 'tim@sagelogix.com'; '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@sagelogix.com'; '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. =20 Has anyone else dealt with this issue? =20 -----Original Message----- From: Stankus, Paula G Sent: Tuesday, October 05, 2004 5:18 AM To: Stankus, Paula G; 'tim@sagelogix.com'; '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?? =20 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@sagelogix.com'; '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@sagelogix.com'; 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=3D1610612736 sga_max_size=3D2147483648 processes=3D150. 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. =20 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