一次ORA-04030故障处理过程

1.故障背景

客户通过sqlplus执行一个Delete表的SQL脚本,报ORA-04030错误:

SQL> Delete From        XXXXXXXXX A
  2  WHERE A.BILL_MONTH=201301 AND A.I_Type_ID=1 AND A.SBDWBM='A6200';
Delete From        XXXXXXXXX A
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)

Oracle客户端为10.2.0.1,Windows2003 32位。服务器是Oracle Enterprise Edititon 10.2.0.5 RAC AIX 5.3。这个问题不是偶然现象,可以重现。

2.检查系统配置

这套RAC系统,配置不算太高,内存12g,sga配置是4g,pga配置是3g,登录到服务器上看,系统压力不大。

$ lsattr -El mem0
goodsize 11520 Amount of usable physical memory in Mbytes False
size 11520 Total amount of physical memory in Mbytes False

从vmstat来看,还有近5g的空闲内存 1354417*4/1024/1024 ≈ 5g,所以内存是足够的。

$ vmstat 2 10
System configuration: lcpu=6 mem=11520MB

kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
0 0 1514126 1354417 0 0 0 0 0 0 25 2108 2500 1 1 97 1
0 0 1514132 1354411 0 0 0 0 0 0 32 2067 2519 0 1 98 1
0 0 1514129 1354414 0 0 0 0 0 0 22 2710 2584 0 1 98 1
0 0 1514128 1354415 0 0 0 0 0 0 28 2090 2500 0 1 98 1

检查swap也确认,基本没有用到交换区,交换区是很空闲的。

$ lsps -a
Page Space      Physical Volume   Volume Group    Size %Used Active  Auto  Type
hd6             hdisk0            rootvg        8192MB     1   yes   yes    lv

这种情况下发生ORA-04030错误,一般是操作系统用户的ulimit配置有问题。检查oracle用户的ulimit配置如下:

$ ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) unlimited
memory(kbytes) unlimited
coredump(blocks) 2097151
$ ulimit -a -H
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) unlimited
memory(kbytes) unlimited
coredump(blocks) unlimited
nofiles(descriptors) unlimited

这就有点奇怪了,oracle用户ulimit的data、stack等都是unlimited,也就是没有限制。内存足够,但是Oracle进程就是没法用。
当前PGA+SGA内存使用情况,才使用了4g内存左右。

SQL> select round(sum(bytes) / 1024 / 1024, 2) as Mbytes
from (select value as bytes
2 3 from v$sga
union all
select value as bytes
4 5 6 from v$sesstat s, v$statname n
7 where n.STATISTIC# = s.STATISTIC#
8 and n.name = 'session pga memory');

MBYTES
----------
4083.25

3.相关测试

在客户端执行如下SQL失败,但是通过在服务器上使用BEQ协议连接数据库,执行却成功。这个时侯,一般判断是监听和主机unlimited参数问题。但查看Oracle用户的unlimited参数没有问题(如上),监听也重启过,无效果。
由于还不知道问题的根本原因在哪边,我就做了以下测试。
PGA的内存使用与以下两个内存参数有关,但是发现加大以下两个参数均无效。

NAME VALUE ISDEFAULT ISMOD ISADJ
------------------------------ ------------------------- --------
_pga_max_size 209715200 TRUE FALSE FALSE

NAME VALUE ISDEFAULT ISMOD ISADJ
------------------------------ ------------------------- --------
_smm_max_size 102400 TRUE FALSE FALSE

尝试在会话中使用PGA手工管理,但是也无效果,照样也报错。

alter session set workarea_size_policy = manual;
alter session set sort_area_size = 209715200;
alter session set hash_area_size = 419430400;

与客户沟通,可以设置系统级事件来产生ORA-04030错误时的trace文件,于是设置event如下:

SQL> alter system set events '4030 trace name errorstack level 3;name HEAPDUMP level 536870917';

得到报错的trc后,关闭生成trace

SQL> alter system set events '4030 trace name errorstack off';
SQL> alter system set events '4030 trace name HEAPDUMP off';

得到ORA-04030错误的trc文件如下:

*** 2013-02-05 18:30:29.261
*** ACTION NAME:() 2013-02-05 18:30:29.252
*** MODULE NAME:(SQL*Plus) 2013-02-05 18:30:29.252
*** SERVICE NAME:(odsdb) 2013-02-05 18:30:29.252
*** SESSION ID:(840.2853) 2013-02-05 18:30:29.252
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
98% 109 MB, 1858 chunks: "permanent memory "
sort subheap ds=110446e88 dsprt=110118e58
1% 1654 KB, 1767 chunks: "free memory "
top call heap ds=11019e010 dsprt=0
0% 233 KB, 22 chunks: "permanent memory "
pga heap ds=110072600 dsprt=0
0% 67 KB, 9 chunks: "free memory "
session heap ds=11046a818 dsprt=11019e230
0% 52 KB, 7 chunks: "permanent memory "
session heap ds=11046a818 dsprt=11019e230
0% 51 KB, 4 chunks: "free memory "
pga heap ds=110072600 dsprt=0
0% 32 KB, 4 chunks: "permanent memory "
callheap ds=110118e58 dsprt=11019e010
0% 32 KB, 1 chunk : "kgh stack "
pga heap ds=110072600 dsprt=0
0% 24 KB, 6 chunks: "kxsFrame4kPage "
session heap ds=11046a818 dsprt=11019e230
0% 23 KB, 11 chunks: "kzctxhugi2 "
session heap ds=11046a818 dsprt=11019e230

从trc里来看,确实只用了100多M内存,就无法分配了。相关报错堆栈如下:

----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              000000012 ? 104C2955C ?
ksedmp+0290          bl       ksedst               104C1FBD0 ?
ksddoa+0308          bl       _ptrgl
ksdpcg+0104          bl       ksddoa               1104700C0 ? 11046ACB8 ?
ksdpec+00e8          bl       ksdpcg               104C32FD4 ? 104C331A4 ?
                                                   104C331C4 ?
ksfpec+00a4          bl       03F33DB4
kgesev+007c          bl       _ptrgl
kgesec3+0040         bl       kgesev               110444AD4 ? 010072648 ?
                                                   000000000 ? 110444A88 ?
                                                   000000000 ?
kghnospc+0890        bl       kgesec3              11019B718 ? 110450040 ?
                                                   FBE00000FBE ? 000000000 ?
                                                   00000FC20 ? 000000001 ?
                                                   00000000C ? 110444AD4 ?
kghalp+0624          bl       kghnospc             1024D8678 ? FFFFFFFFFFF4880 ?
                                                   110231CD8 ? 11026B880 ?
                                                   110427060 ?
stsAllocFromSubheap  bl       01FC4338
+0248
smbalo+0138          bl       stsAllocFromSubheap  70000001BF33878 ?
                                                   FFFFFFFFFFF4FF0 ?
                                                   FFFFFFFFFFF4F20 ?
                                                   2042434000000014 ?
smbaloIS+00a8        bl       smbalo               70000010A54F138 ? 000000000 ?
                                                   A000A059A0A00 ? 000000001 ?
                                                   FFFFFFFFFFF5260 ? 000000002 ?
smbput+1ce4          bl       smbaloIS             110008B98 ? 000000000 ?
                                                   00147AE14 ?
sorput+0208          bl       smbput               10011700C ? 11019B718 ?
                                                   7000000F6323370 ? 11040B7B8 ?
                                                   70000010A8438D0 ?
kxibPut+03fc         bl       sorput               000008000 ? 104FEC28C ?
                                                   FFFFFFFFFFF53C0 ? 100001000 ?
kaudel+0ee8          bl       kxibPut              110488730 ? 110488720 ?
                                                   000100000 ? 100000000000001 ?
                                                   700000010007FE0 ?
                                                   A00000000000A ? 000000000 ?
                                                   000000000 ?
delrow+1008          bl       kaudel               1104842C0 ? 7000000F290C598 ?
                                                   1104893A8 ? 7000000F3271B58 ?
                                                   4000000000001 ?
                                                   ABD9710231CD8 ?
                                                   FFFFFFFFFFF6C10 ?
qerdlFetch+03d8      bl       delrow               7000000F290CF58 ?
                                                   7FFF00000000 ?
delexe+0820          bl       01FC4204
opiexe+28c4          bl       delexe               110499928 ?
kpoal8+0ef0          bl       opiexe               FFFFFFFFFFFB354 ?
                                                   FFFFFFFFFFFB050 ?
                                                   FFFFFFFFFFF94F0 ?
opiodr+0b2c          bl       _ptrgl
ttcpip+1020          bl       _ptrgl
opitsk+117c          bl       01FC6988
opiino+09d0          bl       opitsk               0FFFFD320 ? 000000000 ?
opiodr+0b2c          bl       _ptrgl
opidrv+04a4          bl       opiodr               3C1028DD18 ? 404C72BF0 ?
                                                   FFFFFFFFFFFF2E0 ? 01028DD10 ?
sou2o+0090           bl       opidrv               3C02A289BC ? 400000010 ?
                                                   FFFFFFFFFFFF2E0 ?
opimai_real+01bc     bl       01FC31F4
main+0098            bl       opimai_real          000000000 ? 000000000 ?
__start+0098         bl       main                 000000000 ? 000000000 ?

stsAllocFromSubheap 表明确实是在分配堆内存,但为什么无法分配就不知道了。
客户端是10.2.0.1,服务器是10.2.0.5,怀疑是不是客户端版本太低,存在什么Bug,于是把客户端升级到11.2.0.1,但是照样还是报错。
这时侯感觉能够使用的手段基本已经用尽,在MOS上查到以下BUG
Bug 6526552 : ORA-4030 AFTER SETTING PGA_AGGREGATE_TARGET TO 3 GB ON (SORT SUBHEAP,SORT KEY)
调整PGA为2G,无效果,还是报错
Bug 9876605 : ORA-04030 OUT OF PROCESS MEMORY WHEN TRYING TO ALLOCATE 64544 BYTES (SORT SUBHE
该Bug提到sort_area_size和hash_area_size太大,客户PGA是自动管理的,所以这两个参数应该是不起作用了。

4.峰回路转

这时侯感觉能检查的都已经检查了,能测试的也基本测试了,但问题还是没有得到解决,有点找不到北了。但是后面一个偶然的操作,给问题的解决带来了成功的希望。
前面客户端连接,一直是连接节点1进行测试,后来灵光一现还是其它原因就不知道了,连接节点2进行测试时,发现节点2上可以进行delete,不报错。这时侯我就知道,两个主机肯定有什么配置是不一样的。
我又逐步检查数据库参数、监听配置是一致的,sqlnet.ora两个节点也是一样,甚至是tnslsnr文件的权限,也是一致。
最后我检查root用户的ulimit配置,终于发现了不一致的情况。
节点1:

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

节点2:

# ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 4194304
memory(kbytes) unlimited
coredump(blocks) 2097151
nofiles(descriptors) unlimited

然后我找到了SOME Client Connection FAIL WITH ORA-4030 ERROR when connecting using RAC [ID 758131.1]这篇文档,里面明确提到root用户的ulimit是需要设置为unlimited的,但是这一条,基本没有官方安装文档上提到。
最后调整root用户的ulimited参数与2号节点一致,并重启主机,故障就没再出现了。

5.总结

这个故障的解决,是有运气的成份,但是运气也是在做了大量的测试和检查后才来的,因为故障不会总是无缘无故。如果基础知识深厚,对一些不知所措的故障,在经过大量的检查和测试后,总是能找到一些蛛丝蚂迹。
在有些故障的处理过程中,很多客户为了保证系统安全,是不允许做这些测试,这无形中会给故障解决带来难度,因为有些故障确实很隐蔽,系统和数据库都不是自已装的,里面有一些什么隐蔽设置,完全就不清楚。所以这时侯就需要和客户保持良好的沟通,让客户对自已保持充分的信任,对解决问题会有很大的帮助,这一块只能自行体会了。

本篇文章已于2017年1月16日发布于OCM之家:一次ORA-04030故障处理过程
欢迎关注:ocm_home

关于紫砂壶

感悟技术人生
此条目发表在故障处理分类目录,贴了标签。将固定链接加入收藏夹。

3 则回应给 一次ORA-04030故障处理过程

  1. 匿名说:

    学习了!!!

  2. 匿名说:

    评论怎么看不到!!

  3. 紫砂壶说:

    控制反动言论,评论需要批准,省得半夜被查水表

评论已关闭。