[原创]ORA-04030错误处理_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
2
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 850 | 回复: 1   主题: [原创]ORA-04030错误处理        下一篇 
xiaogang.xu
注册用户
等级:上士
经验:251
发帖:13
精华:0
注册:1970-1-1
状态:离线
发送短消息息给xiaogang.xu 加好友    发送短消息息给xiaogang.xu 发消息
发表于: IP:您无权察看 2016-8-9 18:27:41 | [全部帖] [楼主帖] 楼主

一、背景

数据库频繁报错ORA-04030错误,导致应用异常,当出现该错误时会得到类似如下信息

04030: out of process memory when trying to allocate 118808 bytes (QERHJ hash-joi,kllcqas:kllsltba)]

 

oracle官方对该错误解释:

ORA-04030 out of process memory when trying to allocate string bytes (string,string)

Cause: Operating system process private memory has been exhausted.

Action: See the database administrator or operating system administrator to increase process memory quota. There may be a bug in the application that causes excessive allocations of process memory space.

 

二、问题分析

这个错误表示操作系统不能分配足够的内存,可能是操作系统上进程内存做了限额,也有可能是BUG导致。

数据库共两个实例,PGA都是采用自动管理的(workarea_size_policy=AUTO).

select inst_id,name,value/1024/1024 MB from gv$pgastat where name in ('maximum PGA allocated','over allocation count');

 

图片1.png


目前PGA设置并没有存在不合理之处,但为什么频繁出现ORA-04030。检查数据库服务器/etc/security/limits,

部分信息如下:

 

*   Attribute        Value

*   ==========    ============

*   fsize_hard    set to fsize

*   cpu_hard      set to cpu

*   core_hard         -1

*   data_hard         -1

*   stack_hard      8388608

*   rss_hard          -1

*   nofiles_hard      -1

*

 

oracle:

data = -1

stack = -1

 

可以看到,虽然oracle用户的stack设置为ulimited,但stack硬限制为8388608KB,导致oracle用户进程的栈的最大值受到限制,但目前stack也有8G,问题关键可能不在这里。

 

检查记录应用执行情况的相关表,里面大量和ORA-04030有关的信息如下:

ORA-04030: out of process memory when trying to allocate 120 bytes

(kxs-heap-w,x$ksmsp)

主要是查询v$ksmsp导致的

试着在服务器(实例2)执行了下面操作:

 

SQL> conn /as sysdba

Connected.

SQL> select * from v$ksmsp where rownum=1;

 

ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM

---------------- ---------- ---------- ---------- ---------- ----------------

KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR

---------------- ---------- -------- ---------- ----------------

0000000118705FF0          0          2          7          4 KGH: NO ACCESS

07000006B7FF7FE0      32800 no acce           0 00

 

 

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options

cwgkp19b1[/home/oracle]sqlplus landingbj/landingbj@landingbj

 

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 9 11:14:54 2012

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

Enter password:

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options

 

SQL> select * from v$ksmsp where rownum=1;

select * from v$ksmsp where rownum=1

*

ERROR at line 1:

ORA-04030: out of process memory when trying to allocate 120 bytes

(kxs-heap-w,x$ksmsp)

 

结果很奇怪,通过listener执行会出现ORA-04030,而不通过listener不会报错。又执行了多次,问题依旧,然后在cwgkvpd1执行,结果一样。

 

觉得好奇,通过下面语句检查select * from v$ksmsp where rownum=1PGA消耗情况:

Select a.NAME,b.VALUE

From v$statname a,v$sesstat b

Where a.STATISTIC#=b.STATISTIC#

And a.Name Like '%pga%'

And b.SID=363

 

NAME

VALUE

session pga memory

659448

session pga memory max

116461560

多次测试观察现象表明当某sessionPGA占用达到100M时就会报ORA-04030

 

通过metalinkORA-04030 When Clients Connect Across Network Using SQLNet [ID 753439.1]找到了答案,里面有这样的描述

 

You may see the one or more of the following scenarios:

- Alert log shows error ORA-04030 when connecting remotely using SQL*Net:

ORA-04030: out of process memory when trying to allocate 258072 bytes (QERGH
hash-agg,kllcqas:kllsltba)

- Local connection works without any errors and user process is using about 142 MB of PGA for the session.

- Increased the parameter pga_aggregate_target to 5G or more but the error still occurs:

ALTER SYSTEM SET pga_aggregate_target='5000M' SCOPE=SPFILE SID='*';

- A heapdump shows a user process is using about 100M of PGA for the session when connecting via SQL*NET which is less than the memory used during local connection.

event= '4030 trace name heapdump level 536870917;name errorstack level 3'

 

Cause

1. The problem is identified as the listener has started up with crs_start or srvctl.

Srvctl or crs_start (which is not supported) uses lsnrctl start command to start the listener.
Srvctl is a wrapper script written to start the listener. Consider it like a shell script. 
From 11.2, if you install Grid for ASM and use srvctl to administer the TNS listener
then you might hit this issue.

Please note that this has nothing to do with RAC or Clusterware.
This is not an Oracle memory resource issue since connecting via SQL*Net is using less PGA for the session than a local connection which is successful.

2. If the listener is being started automatically, then we will inherit the limits of the user who started the process (root). If the root user is given the same ulimits as the oracle user, the problem should go away as when the listener autostarts on boot (root user privs), it will start with the correct ulimits.

在服务器使用本地连接,即使会话占用PGA达到142M也不会报错,但是如果通过listener,即使pga_aggregate_target设为5G或更多,会话的占用PGA较大时仍然会报错。即使在服务器上将/etc/security/limitsoracle涉及指标改为ulimited,问题仍然出现。而导致的原因是用crs_start or srvctl启动的listener,它和直接用lsnrctl start并不一样,做了一次封装。上面描述和我们遇到的情况极其类似,那么是否有该原因引起,metlink给出的解决方案:

 

Solution

As a workaround:

1. Stop the listener and start it with lsnrctl

2. Stop and start the listener as the oracle user so it gives the listener the oracle ulimits.

 

停止监听,然后用lsnrctl start启动监听器,在启动后,listener出现The listener supports no services,这属于正常现象,因为目前采用的是动态注册方式(status READY),需由PMON动态注册或用alter system register;,过一分钟再执行lsnrctl status,出现The command completed successfully

通过listener登录执行select * from v$ksmsp where rownum=1;,此时并没有报错了,应用恢复正常,问题解决。

 

参考文档

1、ORA-04030 When Clients Connect Across Network Using SQL*Net [ID 753439.1]

2、Ora-04030 While Running a query from a remote client [ID 266219.1]





赞(0)    操作        顶端 
联动小白
注册用户
等级:新兵
经验:61
发帖:0
精华:0
注册:2015-5-27
状态:离线
发送短消息息给联动小白 加好友    发送短消息息给联动小白 发消息
发表于: IP:您无权察看 2018-6-5 7:34:11 | [全部帖] [楼主帖] 2  楼

为了方便大家阅读,我对文章中命令来解释一下吧!

-w-->backup:-w

File-->as:File

-w-->more:-W

-w-->as:-W

User-->id:User

希望能对大家有帮助!^_^ By:坚持不懈的小白

-- 来自: 北京联动北方科技有限公司



赞(0)    操作        顶端 
总帖数
2
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论