Oracle告警日志ora-04030

jiawang

oracle 11g 出现ORACLE ORA-04030之 out of process memory when trying to allocate报错，查询ORACLE官方MOS确定是：BUG11852492，原因是用户会话单进程占用PGA超过4GB异常终止，根据MOS文章1325100.1的建议：可以根据服务器实际情况及SQL运行的实际情况放开PGA单进程使用内存空间的限制到16GB。

相关分析处理过程如下：

ORA4030告警，告警信息如下：

**************************** 查看数据库告警日志 **************************

ERROR IN ALERT LOG FILE - LAST 3 DAYS

=====================================

15/12/2016 22:44:13 Thu Dec 15 22:44:13 2016

Thu Dec 15 22:44:13 2016

ORA-04030: out of process memory when trying to allocate 64 bytes (kxs-heap-c,allocator state)

ORA-04030: out of process memory when trying to allocate 624 bytes (kxs-heap-c,MSQ2)

ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer)

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_498/orcl_pmon_60993.trc:

See Note 411.1 at My Oracle Support for error and packaging details.

Use ADRCI or Support Workbench to package the incident.

针对PGA单进程4GB内存空间的限制，查证当前操作系统相关限制参数设置为4GB：

[root@host ~]# more /proc/sys/vm/max_map_count

65530

[root@host ~]#  more /proc/sys/vm/max_map_count

65530

[root@host ~]#

    针对PGA单进程4GB内存空间的限制，使用如下sql语句查证oracle数据库相关隐含参数设置为4GB：

_realfree_heap_pagesize_hint=65536

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description

  from x$ksppi a, x$ksppcv b

where a.indx = b.indx and a.ksppinm like '_realfree_heap_pagesize_hint';

    如果实际情况可以，oracle官方允许PGA单进程设置为16GB，相关操作如下：

SOLUTION

Change the upper limit at either the OS or at the database level:

Change the page count at the OS level:

more /proc/sys/vm/max_map_count

sysctl -w vm.max_map_count=262144 (for example)

**Please note the above changes will revert upon host reboot unless the changes are permanently made in the /etc/sysctl.conf file **

Adjust the realfree heap pagesize within the database by setting the following parameters in the init/spfile and restart the database.

For versions 11.2.0.4 and lower:

_use_realfree_heap=TRUE

_realfree_heap_pagesize_hint = 262144

For 12.1 and higher:

_use_realfree_heap=TRUE

_realfree_heap_pagesize = 262144