AIX平臺下報ORA-04030: out of process memory when trying to allocate string bytes

        根據這個錯誤查詢相關的METALINK文章，在這裡貼出自己的處理過程，過程中分別參考的3篇METALINK文章，將以這3篇文章作為主線討論問題處理過程。

文章一：

"ORA-04030: out of process memory (pga heap,redo read buffer) " Errors While Migrating Database To 10.2.0.1 on AIX 5L [ID 423292.1]
		修改時間 23-FEB-2011     型別 PROBLEM     狀態 PUBLISHED

In this Document
  
  
  

---

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
IBM AIX on POWER Systems (64-bit)
IBM AIX Based Systems (64-bit)
AIX5L Based Systems (64-bit)

***Checked for relevance on 23-FEB-2011***

Symptoms

While upgrading Oracle database to 10.2.0.x (10.2.0.1 or any patchset release) on AIX 5L environment, following errors may be seen in the alert.log.

ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY; 
replication_dependency_tracking turned off (no async multimaster replication 
found) 
Completed: ALTER DATABASE OPEN MIGRATE 
.............................. 
.............................. 
Thu Apr 5 13:54:25 2007 
Errors in file /home/oracle/admin/tatm/udump/tatm_ora_3186838.trc: 
ORA-04030: out of process memory when trying to allocate 66184 bytes (pga heap,kco buffer) 
ORA-04030: out of process memory when trying to allocate 2101272 bytes (pga heap,redo read buffer) 
ORA-04030: out of process memory when trying to allocate 2101272 bytes (pga heap,redo read buffer) 
ORA-04030: out of process memory when trying to allocate 16360 bytes (callheap, kcbtmal allocation)

Associated trace file /home/oracle/admin/tatm/udump/tatm_ora_3186838.trc shows

ksedmp: internal or fatal error
ORA-04030: out of process memory when trying to allocate 2101272 bytes (pga heap,redo read buffer)
ORA-04030: out of process memory when trying to allocate 2101272 bytes (pga heap,redo read buffer)
ORA-04030: out of process memory when trying to allocate 16360 bytes (callheap,kcbtmal allocation)

Cause

Database upgrade process will require to run catalog.sql and catproc.sql scripts and the above error can occur when these scripts are run and the shell limits are not sufficient to complete the process.

This issue has also been reported in  RECEIVE ORA-704 WHEN BOUNCING NEW DATABASE AFTER RUNNING CATALOG.SQL

Solution

- Increase the data segment size of shell for the user performing the migration.

- These values can be checked by 'ulimit -a' command which should return output like given below :

ulimit -a
----------
time(seconds) unlimited
file(blocks) unlimited 
data(kbytes) 131072
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000

- Increase the 'data segment' limit value to be unlimited.

Commands to perform. these changes are :

> For Data Segment size : ulimit -d unlimited
> For File size : ulimit -f unlimited

After these changes, 'ulimit -a' command should return these values as follows :

ulimit -a
----------
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
.................

確保在所有節點的ORACLE使用者下執行ulimit -a命令顯示的time,file和data的值都是unlimited。

文章二：

Ora-04030 in RAC environment [ID 760561.1]
		修改時間 18-MAR-2009     型別 PROBLEM     狀態 PUBLISHED

In this Document
  
  
  
  

---

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 11.1.0.7
IBM AIX Based Systems (64-bit)

Symptoms

Get ORA-4030 in RAC environment gathering statistics

Cause

The parameter "mbuf_heap_psize" in Node 1 was 4K while in Node 2 was 64K.  The problem node is using excessive memory for the Cluster System Manager (CSM).  There was not evidence of excessive memory usage in the server where using 64K for the parameter "mbuf_heap_psize". 

Solution

Setting "mbuf_heap_psize" to 64K on both nodes has stopped the CSM from using excessive memory on the problem node and there have been no more reports of ORA-04030. 

"mbuf_heap_psize" is part of the vmo tunable kernel parameters for AIX.   For more on what this parameter is used for and how to adjust it, please review

在RAC所有的AIX節點執行以下的命令，確保所有節點的值是相同的，且值等於65536：
rhel1:/#>vmo -a |grep mbuf_heap_psize
       mbuf_heap_psize = 65536
      
rhel2:/#>vmo -a |grep mbuf_heap_psize

       mbuf_heap_psize = 65536

文章三：

How to Resolve ORA-4030 Errors on UNIX [ID 199746.1]
		修改時間 17-AUG-2011     型別 BULLETIN     狀態 PUBLISHED

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.0 and later   [Release: and later ]
Generic UNIX

Purpose

Checked for relevance on 17-AUG-2011 

Provide assistance in resolving ORA-4030 errors on UNIX systems.

Scope and Application

This document is useful for DBA's and System Administrators tasked with 
resolving an ORA-4030 error.

How to Resolve ORA-4030 Errors on UNIX

 BACKGROUND
==========
The ORA-4030 error is caused when an Oracle process(while doing work on behalf 
of a client program) requests additional memory from the operating system,  but
the operating system cannot accommodate the request.  This can occur because the
operating system does not have enough physical memory or swap available for the
 process, the OS is configured to limit the amount of memory available for UNIX
processes or an actual Oracle BUG has been encountered.


HOW TO RESOLVE THE ORA-4030
===========================
1. Reduce the PGA (Program Global Area) for the client process encountering the
error. This is valid if the database is not configured with MTS (Multi Threaded
Server) or Shared Servers. 

For Oracle 8i and below the major portion of ORA-4030 instances can be solved by
reducing the SORT_AREA_SIZE for the sessions. This will lessen the demand that
the process places on physical memory. SORT_AREA_SIZE can be modified by the
'alter session' command but persistent changes must be recorded in the 
init.ora. See the Oracle Reference guide available on your Online Generic 
Documentation CD-ROM for more information on the 'sort_area_size' parameter.

For Oracle 9i and up, the PGA can be sized using the PGA_AGGREGATE_TARGET 
parameter set in the init.ora or spfile.ora (See  and ).   NOTE:   PGA_AGGREGATE_TARGET influences the workarea sizes
internally for sorts, hash joins, bitmap index operations, etc.   This is a 
"target".   PGA memory will grow as it needs to on the database and will not
be restricted by the PGA_AGGREGATE_TARGET settings.

At 11g, the PGA can be auto-tuned along with SGA memory to meet demand for spikes
in user PGA memory when the OS is already pressured for memory. @Internal only @Now with 10g Release 2 and up, the PGA memory information can be investigated @more closely using the new view v$process_memory_detail. @ @You can get a detailed view of the sizes in a PGA heap by populating @the view as follows: @ @SQL> alter session set events @'immediate trace name PGA_DETAIL_GET level '; @ @where  is the Oracle process ID of the process whose pga heaps you @need to scan or use 1 for all processes.   You can call PGA_DETAIL_GET @multiple times for multiple  numbers without having to gather @ @The V$PROCESS_MEMORY_DETAIL view is persistent and can only be updated @for a process with another call to PGA_DETAIL_GET. @ @Name                       Null?     Type @--------------------------- --------- ------------ @PID                                   NUMBER @SERIAL#                               NUMBER @CATEGORY                              VARCHAR2(15) @NAME                                  VARCHAR2(26) @HEAP_NAME                             VARCHAR2(16) @BYTES                                 NUMBER @ALLOCATION_COUNT                      NUMBER @HEAP_DESCRIPTOR                       RAW(4) @PARENT_HEAP_DESCRIPTOR                RAW(4) @ @Each row of the V$PROCESS_MEMORY_DETAIL view is an allocation count @and byte total for all allocations with the same process ID, allocation @comment, heap name, and heap category. @ @This data is also available in a new trace option to generate a file @instead of using the V$PROCESS_MEMORY_DETAIL view. @ @SQL> alter session set events @'immediate trace name PGA_DETAIL_DUMP level ; @ @NOTE:  The trace file is generated for the process issuing the @'ALTER SESSION' command, even if all processes or another process is @specified.  If 4030 errors occur continuously, the trace will dump a new @heapdump trace every 10 seconds. @ @To force a PGA memory summary dump in an idle process, you first find @the process ID with the V$PROCESS view and use @ @SQL>  oradebug setorapid @SQL>  oradebug dump pga_detail_dump level @ @The output will be in the target process's trace file. @ @To "turn off" the event setting, you can issue @SQL> alter session set events @'immediate trace name@PGA_DETAIL_CANCEL level '; @ @Again using pid=1 will disable this for all processes. @ @You can validate that V$PROCESS_MEMORY_DETAIL information is complete using @the companion view called V$PROCESS_MEMORY_DETAIL_PROG.   This view has one @row for every process being scanned. @ @This view has the columns @PID @SERIAL# @STATUS  (ENABLED--scanning turned on; SCANNING; COMPLETE--data is stored) @ At 11g, the view V$MEMORY_RESIZE_OPS will show memory tuning going on within
the database between the SGA components and PGA.

2. Increase the amount of memory a UNIX process can request and use from the
operating system. This usually refers to stack and/or data size UNIX process 
resource limits.  This process varies slightly depending on the UNIX platform. and the type of
UNIX shell you are using.  Generally speaking either the 'limit' or 'ulimit'
command will allow your System Administrator to increase memory and data size
limits. Oracle Support cannot recommend a specific value for these limits.
However, doubling existing values or setting them to 'unlimited' is usually
sufficient.

For more information please refer to:  How to Display and Change UNIX Process Resource Limits

3. Increase the amount of swap available on your system.  You should have 2-3 
times the amount of physical memory available as swap space.  How to Display the Amount of Physical Memory and Swap Space on UNIX Systems

4. Finally, if you still experience the problem after addressing the above
issues, it's recommended that you move to the latest patchset release to
eliminate any possible Oracle product defects:  Understanding and Obtaining Oracle RDBMS Patchsets

REFERENCES
==========  OERR:  ORA 4030  "out of process memory when  ORA-04030 -07324: AFTER INCREASING THE SGA OR EXECUTING A 
                 LARGE QUERY  SOLARIS: SGA size, sgabeg attach address and Sun architectures  SOLARIS Determing Oracle Memory Usage on Solaris  AIX Determining Oracle memory usage on AIX  Determining the Size of an Oracle Process @Note 399497.1 FAQ: ORA-4030 @see Note 399497.1 for how to gather heapdumps @on 4030 error or manually

請注意以上背景加紅，字型加粗的內容。
根據以上3篇文章的內容，做出如下的配置，在RAC的所有AIX節點修改/etc/security/limits檔案，加入如下有關oracle使用者的配置：
oracle:
       fsize = -1
       data = -1
       stack = -1
       core = -1
       fsize_hard = -1
       cpu_hard = -1
       data_hard = -1
       stack_hard = -1
       core_hard = -1
       rss = -1
       nofiles = 4000
 
儲存退出之後即可生效，在RAC的所有AIX節點伺服器上，用oracle使用者執行ulimit -a命令，返回的結果除了nofiles不等於unlimited外，其餘的配置都等於unlimited。
透過觀察，在所有節點的配置修改生效後，沒有再出現ORA-04030的錯誤。

--end--