ORA-04030错误再现

一、4月5日更换了风扇后，应用程序又出现ORA-04030的错误：

错误号:-4030  错误信息:ORA-04030: out of process memory when trying to allocate 16776728 bytes (QERHJ hash-joi,QERHJ Hash Table Entries)
select * from etl.t_sys_total_exec_msg  where to_char(begintime,'yyyymmdd')>=to_char(sysdate ,'yyyymmdd')  

 二、根据历史SR回复，处理建议如下：

=== ODM Action Plan ===
Dear customer,

1. Detect whether 3.5GB memory can be allocated by oracle account

1)Save the program as mem.c

+++++++++++++
/* mem.c */
#include <stdio.h>
#include <errno.h>

main()
{
long int i;
i = 1;
for(;;)
{
if (!malloc(1048576))
{
printf("malloc failed with error: %u at iteration %u\n", errno,i);
exit(-1);
}
i++;
printf("allocated %u M of memory\n", i);
if ((i*1048576) >= 3758096384) {
printf ("Leaving...\n");
exit(0);
}
}
}

+++++++++++++

2) compile it with this command

gcc -maix64 -o ./mem ./mem.c

3)run this program:
chmod 777 ./mem
./mem

2. AIX
There should be some limit on running memory of process besides limits.
There is a tool can detect resource limit of a running process on AIX, but can’t obtain it unless contact IBM support.
Please try contacting IBM support if test on mem.c failed with memory allocating.

 

执行后主机连续分配内存正常，修改1机的应用用户limits资源，根据oracle建议需要重启监听和实例。

--停机前做检查点
alter system checkpoint
alter system checkpoint
alter system switch logfile

--清理进程
ps -ef|grep LOCAL=NO|grep -v grep|wc -l
ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $2}'|xargs -i kill -9 {}

--shutdown
srvctl stop listener -n p5701
srvctl stop instance -d bi -i bi1
--start
srvctl start instance -d bi -i bi1

 数据库和监听重启后，应用没有再报此错误。