ORA-7445(evaopn2)错误（二）
在一台Oracle 10.2 for AIX数据库服务上碰到了这个错误。
在alert文件中，错误信息为：
Errors in file /opt/oracle/app/oracle/admin/trade/udump/trade_ora_401772.trc:
ORA-07445: exception encountered: core dump [] [] [] [] [] []
详细的错误信息为：
$ more /opt/oracle/app/oracle/admin/trade/udump/trade_ora_401772.trc
/opt/oracle/app/oracle/admin/trade/udump/trade_ora_401772.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/oracle/app/oracle/product/10.2.0/db_1
System name: AIX
Node name: p570a
Release: 1
Version: 6
Machine: 00CFA7B44C00
Instance name: trade
Redo thread mounted by this instance: 1
Oracle process number: 37
Unix process pid: 401772, image: oracletrade@p570a
*** SERVICE NAME:(trade) 2010-05-04 11:57:48.302
*** SESSION ID:(445.14971) 2010-05-04 11:57:48.302
*** 2010-05-04 11:57:48.302
Exception signal: 11 0x101a8b678 (evaopn2+0038) a01e000a
Registers:
iar: 0000000101a8b678, msr: a00000000000d032
lr: 0000000101fdb294, cr: 0000000084462620
r00: 0000000101fdb294, r01: 0fffffffffff8b80, r02: 0000000110365f60,
r03: 0000000000000060, r04: 00000001105ab1c8, r05: 0000000000000000,
r06: 00000001100e46e0, r07: 0000000000000080, r08: 000000011015b458,
r09: 0000000000000000, r10: 0000000000000000, r11: 0000000000000000,
r12: 00000001038bf4c8, r13: 00000001103a23d8, r14: 0000000000000000,
r15: 0000000110724fe8, r16: 0000000000000000, r17: 0000000104b96bc8,
r18: 0fffffffffff9090, r19: 0fffffffffff90f0, r20: 0000000110724fe8,
r21: 0000000110724fe8, r22: 0000000110000b98, r23: 0000000000000000,
r24: 07000001d75fecd0, r25: 07000001d75fec70, r26: 00000001105ce3c8,
r27: 0000000000000001, r28: 0000000f00000000, r29: 0000000000000000,
r30: 0000000f00000000, r31: 07000001d75fedf8,
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
evaopn2+0054 ? 00000000
rwsstd+004c bl _ptrgl
selexe+0ab0 bl 01FC5374
opiexe+262c bl selexe 11072E398 ? 300000005 ?
102002E7998 ?
kpoal8+0e10 bl opiexe FFFFFFFFFFFC204 ? 1104DD6B8 ?
FFFFFFFFFFFA3E8 ?
opiodr+0adc bl _ptrgl
ttcpip+1004 bl _ptrgl
opitsk+1000 bl ttcpip 11015DE48 ? 000000000 ?
000000001 ? 000000000 ?
001004000 ? 000000030 ?
000000000 ? 000000030 ?
opiino+0990 bl opitsk 000000000 ? 000000000 ?
opiodr+0adc bl _ptrgl
opidrv+0474 bl opiodr 3C103D8710 ? 4103DA770 ?
FFFFFFFFFFFF940 ? 0A00761E0 ?
sou2o+0090 bl opidrv 3C0232293C ? 4A0076254 ?
FFFFFFFFFFFF940 ?
opimai_real+01bc bl 01FC3574
main+0098 bl opimai_real 000000000 ? 000000000 ?
__start+0070 bl main 000000000 ? 000000000 ?
跟踪程序定位到SQL语句，发现SQL语句本身存在不合理的地方：
SQL> select /*+ first_rows */ *
2 from (
3 select row_.* , rownum rownum_
4 from(
5 select nvl(deal.temp_flag,0) as temp_flag, b.url,c.member_flag,b.address,
6 b.plat_id, b.used_name, c.code ,c.name ,b.check_flag, b.factory_flag,
7 b.saler_flag, b.send_flag, c.abbr ,c.id, d.plat_name,d.plat_father
8 from cat_enterprise b,cat_org c, plt_plat d ,CAT_DEALER deal
9 where b.id = c.id
10 and b.plat_id = d.id
11 and (c.name like '%北京%'
12 or c.abbr like '%北京%'
13 or b.used_name like '%北京%'
14 or (exists (
15 select 1
16 from cat_manufacture cm, cat_org c
17 where cm.molecule_orgid = c.id
18 and cm.id=b.id
19 and c.abbr || c.code|| c.name_wb|| c.spell_abbr || c.name like '%北京%' )))
20 and c.ENABLE_FLAG = '1'
21 and deal.id(+)=b.id ) row_
22 where rownum <=10
23 ) where rownum_ >0;
select /*+ first_rows */ *
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
在查询条件中一个EXISTS关联查询被放到OR条件中。而且这个查询还是一个标准分页查询。
测试发现如果去掉OR条件中的EXISTS关联查询，或者去掉外面嵌套的标准分页，都不会导致这个错误的产生。甚至去掉里面外关联的表，试图简化查询的操作都会导致错误不再出现。
在metalink中，记录了大量关于evaopn2的ORA-7445错误，其中大部分都是和函数索引有关，检查了一遍发现和已知的错误都不相同。
以前我也碰到过一个evaopn2的ORA-7445错误，但是和当前的情况也是不一样的，考虑到当前的版本是10.2.0.1，尝试通过升级数据库版本来解决这个问题。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
SQL> select /*+ first_rows */ *
2 from (
3 select row_.* , rownum rownum_
4 from(
5 select nvl(deal.temp_flag,0) as temp_flag, b.url,c.member_flag,b.address,
6 b.plat_id, b.used_name, c.code ,c.name ,b.check_flag, b.factory_flag,
7 b.saler_flag, b.send_flag, c.abbr ,c.id, d.plat_name,d.plat_father
8 from cat_enterprise b,cat_org c, plt_plat d ,CAT_DEALER deal
9 where b.id = c.id
10 and b.plat_id = d.id
11 and (c.name like '%北京%'
12 or c.abbr like '%北京%'
13 or b.used_name like '%北京%'
14 or (exists (
15 select 1
16 from cat_manufacture cm, cat_org c
17 where cm.molecule_orgid = c.id
18 and cm.id=b.id
19 and c.abbr || c.code|| c.name_wb|| c.spell_abbr || c.name like '%北京%' )))
20 and c.ENABLE_FLAG = '1'
21 and deal.id(+)=b.id ) row_
22 where rownum <=10
23 ) where rownum_ >0;
T
-
URL
--------------------------------------------------------------------------------------------
M
-
ADDRESS
--------------------------------------------------------------------------------------------
PLAT_ID
------------------------
USED_NAME
--------------------------------------------------------------------------------------------
CODE
--------------------------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------
C F S S
- - - -
ABBR
--------------------------------------------------------------------------------------------
ID PLAT_NAME PLAT_FATHER ROWNUM_
------------------------ ------------------------------ ------------------------ ----------
0
0
.
.
.
DATA10000000000000906285 中心 000000000000000000000000 10
10 rows selected.
升级后，这个错误不再出现。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-662549/ ，如需转载，请注明出处，否则将追究法律责任。
