ORA-4031导致CJQ进程出现ORA-1003错误

客户数据库出现ORA-4031错误,随后出现了大量的ORA-1003和ORA-604错误。
数据库版本为10.2.0.3 RAC for HP-UX,详细的报错信息为:

Mon Jul 16 15:30:30 2012
Errors IN file /u01/app/oracle/admin/ORCL/udump/orcl2_ora_2389.trc:
ORA-00603: ORACLE server SESSION TERMINATED BY fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 32 bytes OF shared memory ("shared pool","select name,online$,contents...","sql area","tmp")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 32 bytes OF shared memory ("shared pool","select name,online$,contents...","sql area","tmp")
Mon Jul 16 15:30:32 2012
Errors IN file /u01/app/oracle/admin/ORCL/udump/orcl2_ora_2878.trc:
ORA-00603: ORACLE server SESSION TERMINATED BY fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 144 bytes OF shared memory ("shared pool","select name,online$,contents...","Typecheck","coldef: qcopCreateCol")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 144 bytes OF shared memory ("shared pool","select name,online$,contents...","Typecheck","coldef: qcopCreateCol")
Mon Jul 16 15:30:32 2012
Errors IN file /u01/app/oracle/admin/ORCL/udump/orcl2_ora_10030.trc:
ORA-00603: ORACLE server SESSION TERMINATED BY fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 144 bytes OF shared memory ("shared pool","select name,online$,contents...","Typecheck","coldef: qcopCreateCol")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 144 bytes OF shared memory ("shared pool","select name,online$,contents...","Typecheck","coldef: qcopCreateCol")
Mon Jul 16 15:30:39 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable TO allocate 32 bytes OF shared memory ("shared pool","select o.owner#,o.name,o.nam...","sql area","kobjn : kkdcchs")
Mon Jul 16 15:30:40 2012
Trace dumping IS performing id=[cdmp_20120716153040]
Mon Jul 16 15:32:19 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:33:59 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:35:39 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:37:19 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:39:00 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:40:40 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQ
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: err
Mon Jul 16 15:41:59 2012
Thread 2 advanced TO log SEQUENCE 61522
CURRENT log# 7 seq# 61522 mem# 0: +ORCL_CTL/orcl/onlinelog/group_7.263.611598065
Mon Jul 16 15:42:20 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:44:00 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:45:40 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQ
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: err

可以看得,开始还是比较“正常”的ORA-4031错误,但是随着CJQ进程出现了ORA-4031的错误,数据库开始每1分40秒报一次ORA-1003和ORA-604的错误。这里CJQ进程似乎陷入了一个死循环中,持续不停的报错。查询MOS发现,在9.2中Oracle存在类似的BUG:Bug 3289063 ORA-1003 every 5 seconds after CJQ hits ORA-4031。这个bug已经在9.2.0.5和10.1.0.2中被FIXED,但是这个bug的现象和当前的错误完全一致,从这点上看,这个问题显然在10.2.0.3中仍然存在。
Oracle给出的解决方案也很简单,重启CJQ进程既可。除了在操作系统去kill -9杀掉cjq0对应的进程之外,直接通过alter system set job_queue_processes=0,然后在设置回原值,也有可能解决该问题。此外,尽量避免ORA-4031错误的产生是最根本的问题解决之道。

This entry was posted in BUG and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *