数据库

Oracle告警日志ora-04030

一oracle 11g 出现ORACLE ORA-04030之 out of process memory when trying to allocate报错,查询ORACLE官方MOS确定是:BUG11852492,原因是用户会话单进程占用PGA超过4GB异常终止,根据MOS文章1325100.1的建议:可以根据服务器实?#26159;?#20917;及SQL运行的实?#26159;?#20917;放开PGA单进程使用内存?#21344;?#30340;限?#39057;?6GB。

相关分析处理过程如下:

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内存?#21344;?#30340;限制,查证当前操作系统相关限制参数设置为4GB:

[[email protected] ~]# more /proc/sys/vm/max_map_count
65530
[[email protected] ~]#  more /proc/sys/vm/max_map_count
65530
[[email protected] ~]#

    针对PGA单进程4GB内存?#21344;?#30340;限制,使用如下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';

    如果实?#26159;?#20917;可以,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
?#19968;?#27809;有学会写个人说明!

我是程序?#20445;?#27599;一天都太难了!

上一篇

灵魂三问,帆软主赛道?#31995;?#26497;致追求

下一篇

你也可能喜欢

Oracle告警日志ora-04030

长按储存图像,分享给朋友

ITPUB 每周精要将以邮件的?#38382;?#21457;放至您的邮箱


微信扫一扫

微信扫一扫
大丰收注册