ORA-04030及解决方法

根据这个错误查找相关的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

Symptoms

Cause

Solution

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 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 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 Bug 5045771 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 note:153367.1 and note:146577.1). 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: note:188149.1 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. note.1016233.6 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: note:169547.1 Understanding and Obtaining Oracle RDBMS Patchsets

REFERENCES

========== note:19836.1 OERR: ORA 4030 "out of process memory when note:1023744.6 ORA-04030 -07324: AFTER INCREASING THE SGA OR EXECUTING A

LARGE QUERY note:61896.1 SOLARIS: SGA size, sgabeg attach address and Sun architectures note:153655.1 SOLARIS Determing Oracle Memory Usage on Solaris note.123754.1 AIX Determining Oracle memory usage on AIX note:174555.1 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