ORA-07445: exception encountered: core dump [qkaqkn()+5390] [SIGSEGV] [Address not mapped to object]
记一次灵异事件。
开发写的一个sql语句，在原库上能执行，新迁移的库不能执行，报下面这个错
刚开始觉得是开发的语句有问题，后来拿过来认真看了下sql确实没问题。
后台alert日志：
Mon Oct 01 03:27:51 CST 2018
Errors in file /oradata/ztesoft/dump/udump/ztesoft_ora_6912.trc:
ORA-07445: exception encountered: core dump [qkaqkn()+5390] [SIGSEGV] [Address not mapped to object] [0x000000020] [] []
alert日志很简单，里面就dump了一个trace文件，我们在看trace：
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle10/product/102
System name: Linux
Node name: CentOS7.3
Release: 3.10.0-514.el7.x86_64
Version: #1 SMP Tue Nov 22 16:42:41 UTC 2016
Machine: x86_64
Instance name: ztesoft
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 6912, image: [email protected]
*** ACTION NAME:(SQL Window - New) 2018-10-01 03:27:51.386
*** MODULE NAME:(PL/SQL Developer) 2018-10-01 03:27:51.386
*** SERVICE NAME:(ztesoft) 2018-10-01 03:27:51.386
*** SESSION ID:(1628.15) 2018-10-01 03:27:51.386
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x20, PC: [0x18b3944, qkaqkn()+5390]
*** 2018-10-01 03:27:51.390
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [qkaqkn()+5390] [SIGSEGV] [Address not mapped to object] [0x000000020] [] []
Current SQL statement for this session:
select h2.priv_id,h2.priv_code,h2.priv_type,h2.menu_name,h2.menu_url,h2.menu_id,h2.parent_id,h2.isdefalut
from(
select p.priv_id from
daily.v_ci_staff s,daily.ci_staff_role sr,daily.ci_priv p,daily.ci_role_priv rp,daily.ci_roles r
where 1 = 1
and s.staff_id = 12256
and s.staff_id = sr.staff_id
and sr.role_id = r.role_id
and r.role_id = rp.role_id
and rp.priv_id = p.priv_id
and s.state = '00A'
and p.state = '00A'
and r.state = '00A'
and sr.state = '00A'
and rp.state = '00A'
) h1,
(
select p.priv_id,p.priv_code,p.priv_type,m.menu_name,m.menu_url,m.menu_id,m.parent_id,m.state mstate ,pm.state pmstate,m.isdefalut isdefalut from
daily.ci_priv p,daily.ci_menu m,daily.ci_priv_menu pm
where 1 = 1
and p.state = '00A'
and pm.menu_id = m.menu_id(+)
and p.priv_id = pm.priv_id(+)
) h2
where h1.priv_id = h2.priv_id(+)
and (h2.mstate = '00A' or h2.mstate is null)
and (h2.pmstate = '00A' or h2.pmstate is null)
union
select h2.priv_id,h2.priv_code,h2.priv_type,h2.menu_name,h2.menu_url,h2.menu_id,h2.parent_id,h2.isdefalut
from(
select p.priv_id from
daily.v_ci_staff s,daily.ci_priv p,daily.ci_role_priv rp,daily.ci_roles r
where 1 = 1
and s.staff_id = 12256
and s.role_id = r.role_id
and r.role_id = rp.role_id
and rp.priv_id = p.priv_id
and s.state = '00A'
and p.state = '00A'
and r.state = '00A'
and rp.state = '00A'
) h1,
(
select p.priv_id,p.priv_code,p.priv_type,m.menu_name,m.menu_url,m.menu_id,m.parent_id,m.state mstate ,pm.state pmstate,m.isdefalut isdefalut from
daily.ci_priv p,daily.ci_menu m,daily.ci_priv_menu pm
where 1 = 1
and p.state = '00A'
and pm.menu_id = m.menu_id(+)
and p.priv_id = pm.priv_id(+)
) h2
where h1.priv_id = h2.priv_id(+)
and (h2.mstate = '00A' or h2.mstate is null)
and (h2.pmstate = '00A' or h2.pmstate is null)
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+31 call ksedst1() 000000000 ? 000000001 ?
7F9499434BD0 ? 7F9499434C30 ?
7F9499434B70 ? 000000000 ?
ksedmp()+610 call ksedst() 000000000 ? 000000001 ?
7F9499434BD0 ? 7F9499434C30 ?
7F9499434B70 ? 000000000 ?
ssexhd()+629 call ksedmp() 000000003 ? 000000001 ?
7F9499434BD0 ? 7F9499434C30 ?
7F9499434B70 ? 000000000 ?
__restore_rt()+0 call ssexhd() 00000000B ? 7F9499435BF0 ?
7F9499435AC0 ? 7F9499434C30 ?
7F9499434B70 ? 000000000 ?
qkaqkn()+5390 signal __restore_rt() 000000000 ? 7FFDC91264E0 ?
000000000 ? 000000001 ?
000000000 ? 38CB52D60 ?
qkadrv()+854 call qkaqkn() 7F949BCC78D0 ? 000000000 ?
000000000 ? 7FFDC9126660 ?
000000000 ? 38CB52D60 ?
qkadrv()+6216 call qkadrv() 7F949BCC78D0 ? 000000000 ?
000000000 ? 7FFDC9126660 ?
000000000 ? 38CB52D60 ?
qkadrv()+5591 call qkadrv() 7F949BCCB320 ? 000000001 ?
000000000 ? 7FFDC9126660 ?
000000000 ? 38CB52D60 ?
opitca()+1908 call qkadrv() 7F94979BD088 ? 000000001 ?
000000000 ? 7FFDC9126660 ?
000000000 ? 38CB52D60 ?
kksLoadChild()+1005 call opitca() 7F949BC95210 ? 38CB677B0 ?
9 000000000 ? 7FFDC9126660 ?
000000000 ? 38CB52D60 ?
kxsGetRuntimeLock() call kksLoadChild() 006AEB3C0 ? 39E5C78F0 ?
+1454 7FFDC91290F0 ? 000000000 ?
38E8CD658 ? 7F949BC95210 ?
kksfbc()+14902 call kxsGetRuntimeLock() 006AEB3C0 ? 7F949BC95210 ?
7FFDC91290F0 ? 000000000 ?
38E8CD658 ? 7F949BC95210 ?
kkspsc0()+979 call kksfbc() 7F949BC95210 ? 000000003 ?
000000108 ? 7F949BCABA90 ?
0000006D5 ? 000000000 ?
kksParseCursor()+14 call kkspsc0() 7F949BC81760 ? 7F949BCABA90 ?
2 0000006D5 ? 000000003 ?
A400000006 ? 0000000A4 ?
opiosq0()+1641 call kksParseCursor() 7FFDC9129E70 ? 7F949BCABA90 ?
0000006D5 ? 000000003 ?
A400000006 ? 0000000A4 ?
kpooprx()+318 call opiosq0() 000000003 ? 00000000E ?
7FFDC912A058 ? 0000000A4 ?
000000000 ? 6000006D4 ?
kpoal8()+783 call kpooprx() 7FFDC912D23C ? 7F949BCABA90 ?
0000006D4 ? 000000001 ?
000000000 ? 6000006D4 ?
opiodr()+1184 call kpoal8() 00000005E ? 000000017 ?
7FFDC912D238 ? 000000001 ?
000000001 ? 6000006D4 ?
ttcpip()+1226 call opiodr() 00000005E ? 000000017 ?
7FFDC912D238 ? 000000000 ?
005BF06D0 ? 6000006D4 ?
opitsk()+1310 call ttcpip() 006AF3950 ? 0054A9000 ?
7FFDC912D238 ? 000000000 ?
7FFDC912CD38 ? 7FFDC912D3A0 ?
opiino()+1024 call opitsk() 000000003 ? 000000000 ?
7FFDC912D238 ? 000000001 ?
000000000 ? 6AF395000000001 ?
opiodr()+1184 call opiino() 00000003C ? 000000004 ?
7FFDC912E438 ? 000000000 ?
000000000 ? 6AF395000000001 ?
opidrv()+548 call opiodr() 00000003C ? 000000004 ?
7FFDC912E438 ? 000000000 ?
005BF0180 ? 6AF395000000001 ?
sou2o()+114 call opidrv() 00000003C ? 000000004 ?
7FFDC912E438 ? 000000000 ?
005BF0180 ? 6AF395000000001 ?
opimai_real()+163 call sou2o() 7FFDC912E410 ? 00000003C ?
000000004 ? 7FFDC912E438 ?
005BF0180 ? 6AF395000000001 ?
main()+116 call opimai_real() 000000002 ? 7FFDC912E4A0 ?
000000004 ? 7FFDC912E438 ?
005BF0180 ? 6AF395000000001 ?
__libc_start_main() call main() 000000002 ? 7FFDC912E4A0 ?
+245 000000004 ? 7FFDC912E438 ?
005BF0180 ? 6AF395000000001 ?
_start()+41 call __libc_start_main() 00071DFA0 ? 000000002 ?
7FFDC912E5F8 ? 000000000 ?
005BF0180 ? 6AF395000000001 ?
--------------------- Binary Stack Dump ---------------------
========== FRAME [1] (ksedst()+31 -> ksedst1()) ==========
RDI 0000000000000000 RSI 0000000000000001 RDX 00007F9499434BD0
RCX 00007F9499434C30 R8 00007F9499434B70 R9 0000000000000000
RAX 0000000000000000 RBX 0000000000000000 RBP 00007F94994356A0
R10 0000000000000000 R11 0000000000000000 R12 00007F94978EF118
R13 00007F9499435BF0 R14 00007FFDC9126660 R15 00007F9499435AC0
RSP 00007F94994356A0 RIP 000000000075EC7F
Dump of memory from 0x00007F94994305C0 to 0x00007F94994309C0
7F94994305C0 054DAA30 00000000 0000001C 00000000 [0.M.............]
7F94994305D0 054DAA54 00000000 00000000 00000000 [T.M.............]
7F94994305E0 99434BD0 00007F94 99434C30 00007F94 [.KC.....0LC.....]
7F94994305F0 99434B70 00007F94 99435BF0 00007F94 [pKC......[C.....]
7F9499430600 99435AC0 00007F94 C91264E0 00007FFD [.ZC......d......]
7F9499430610 8CB52D60 00000003 9BCC78D0 00007F94 [`-.......x......]
7F9499430620 C9126660 00007FFD 9BCCB320 00007F94 [`f...... .......]
7F9499430630 979BD088 00007F94 9BC95210 00007F94 [.........R......]
7F9499430640 8CB677B0 00000003 06AEB3C0 00000000 [.w..............]
7F9499430650 9E5C78F0 00000003 C91290F0 00007FFD [.x\.............]
7F9499430660 8E8CD658 00000003 9BCABA90 00007F94 [X...............]
7F9499430670 9BC81760 00007F94 C9129E70 00007FFD [`.......p.......]
7F9499430680 C912A058 00007FFD C912D23C 00007FFD [X.......<.......]
7F9499430690 05BF06D0 00000000 06AF3950 00000000 [........P9......]
7F94994306A0 054A9000 00000000 C912CD38 00007FFD [..J.....8.......]
7F94994306B0 C912D3A0 00007FFD C912E438 00007FFD [........8.......]
7F94994306C0 05BF0180 00000000 C912E410 00007FFD [................]
7F94994306D0 C912E4A0 00007FFD 0071DFA0 00000000 [..........q.....]
7F94994306E0 C912E5F8 00007FFD 994356A0 00007F94 [.........VC.....]
7F94994306F0 978EF118 00007F94 0075EC7F 00000000 [..........u.....]
7F9499430700 00000000 00000000 00000000 00000000 [................]
Repeat 43 times
========== FRAME [2] (ksedmp()+610 -> ksedst()) ==========
RDI 0000000000000000 RSI 0000000000000001 RDX 00007F9499434BD0
RCX 00007F9499434C30 R8 00007F9499434B70 R9 0000000000000000
RAX 0000000000000000 RBX 0000000000000000 RBP 00007F94994358C0
R10 0000000000000000 R11 0000000000000000 R12 00007F94978EF118
R13 00007F9499435BF0 R14 00007FFDC9126660 R15 00007F9499435AC0
RSP 00007F94994356B0 RIP 000000000075D49A
Dump of memory from 0x00007F94994356A0 to 0x00007F94994356B0
7F94994356A0 994358C0 00007F94 0075D49A 00000000 [.XC.......u.....]
========== FRAME [3] (ssexhd()+629 -> ksedmp()) ==========
RDI 0000000000000003 RSI 0000000000000001 RDX 00007F9499434BD0
RCX 00007F9499434C30 R8 00007F9499434B70 R9 0000000000000000
RAX 0000000000000000 RBX 000000000000000B RBP 00007F9499435AB0
R10 0000000000000000 R11 0000000000000000 R12 00007F94978EF118
R13 00007F9499435BF0 R14 00007FFDC9126660 R15 00007F9499435AC0
RSP 00007F94994358D0 RIP 0000000001FA2B05
Dump of memory from 0x00007F94994356B0 to 0x00007F94994358D0
7F94994356B0 9BCDA2D0 00007F94 9BE1E4A0 00007F94 [................]
7F94994356C0 9BE1E148 00007F94 9BC06CAF 00007F94 [H........l......]
7F94994356D0 00000000 00000000 9BCDA2D0 00007F94 [................]
7F94994356E0 00000001 00007F94 00000000 00000000 [................]
7F94994356F0 00000001 00007F94 9BE1E148 00007F94 [........H.......]
7F9499435700 994358D0 00007F94 018B3944 00000000 [.XC.....D9......]
7F9499435710 0000000B 00000000 0000000B 00000000 [................]
7F9499435720 00000000 00000000 9BE1E4A0 00007F94 [................]
7F9499435730 99435760 00007F94 99435750 00007F94 [`WC.....PWC.....]
7F9499435740 28745563 00000000 0061D750 00000000 [cUt(....P.a.....]
7F9499435750 0FE8C405 00000000 06AEB550 00000000 [........P.......]
7F9499435760 9943BAB0 00007F94 9BCDB990 00007F94 [..C.............]
7F9499435770 994357B0 00007F94 03D12322 00000000 [.WC....."#......]
7F9499435780 00000000 00000000 00000000 00000000 [................]
7F9499435790 00000004 00000000 994357C0 00007F94 [.........WC.....]
7F94994357A0 99435AC0 00007F94 978EF118 00007F94 [.ZC.............]
7F94994357B0 99435890 00007F94 0075A1FD 00000000 [.XC.......u.....]
7F94994357C0 00000030 00000030 06A655D8 00000000 [0...0....U......]
7F94994357D0 00000000 00000000 99435BF0 00007F94 [.........[C.....]
7F94994357E0 C9126660 00007FFD 99435AC0 00007F94 [`f.......ZC.....]
7F94994357F0 99435AB0 00007F94 00000000 00007F94 [.ZC.............]
7F9499435800 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
7F9499435820 0000000B 00000000 9946D56B 00007F94 [........k.F.....]
7F9499435830 3E7BF217 FFFFFFFE 9BC120C0 00007F94 [..{>..... ......]
7F9499435840 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
7F9499435860 00000000 00000000 00000000 00000001 [................]
7F9499435870 00000001 00000001 00000000 00000001 [................]
7F9499435880 00000001 00000000 00000003 00000000 [................]
7F9499435890 99435AC0 00007F94 C9126660 00007FFD [.ZC.....`f......]
7F94994358A0 99435BF0 00007F94 978EF118 00007F94 [.[C.............]
7F94994358B0 0000000B 00000000 0000000B 00000000 [................]
7F94994358C0 99435AB0 00007F94 01FA2B05 00000000 [.ZC......+......]
========== FRAME [4] (__restore_rt()+0 -> ssexhd()) ==========
RDI 000000000000000B RSI 00007F9499435BF0 RDX 00007F9499435AC0
RCX 00007F9499434C30 R8 00007F9499434B70 R9 0000000000000000
RAX 0000000000000000 RBX 0000000000000000 RBP 00007FFDC91265A0
R10 0000000000000000 R11 0000000000000000 R12 00007F94978EF118
R13 00007F949BCC78D0 R14 00007FFDC9126660 R15 0000000000000000
RSP 00007F9499435AC0 RIP 00007F9499A20370
Dump of memory from 0x00007F94994358D0 to 0x00007F9499435AC0
7F94994358D0 018B2436 00000000 71616B71 29286E6B [6$......qkaqkn()]
7F94994358E0 3933352B 00000030 00000000 00000000 [+5390...........]
7F94994358F0 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
7F9499435920 00000000 00000000 00000001 00000000 [................]
7F9499435930 00000000 00000000 00000000 00000000 [................]
Repeat 11 times
7F94994359F0 00000000 00000000 00000400 00000000 [................]
7F9499435A00 00000000 00000000 00000000 00000000 [................]
Repeat 6 times
7F9499435A70 00000000 00000000 05821E48 00000000 [........H.......]
7F9499435A80 05822008 00000000 00000000 00000000 [. ..............]
7F9499435A90 9BCC78D0 00007F94 00000000 00000000 [.x..............]
7F9499435AA0 00000000 00000000 00000000 00000000 [................]
7F9499435AB0 C91265A0 00007FFD 99A20370 00007F94 [.e......p.......]tracle文件很多，就是把查询出错的语句给打出来了。
后来把内核参数、limit文件限制、包括oracle安装前的依赖包都重新安装走了一遍
又设置了
这些参数重新起库，反正搞了很久后查询还是报错，就去吃了个饭，结果来了查询又好了。简直莫名其妙。
后来在mos上找了以后排查问题可以这么来，写下来以后碰到有个思路。
这个页面，专门对付600,7445这种内部错误
指定error code为7445，第一个参数就是ora-07445后面[]中的内容：
对我这个就是这个地方的内容，然后选择数据库版本，我这边是10.2.0.5.最后点look-up Error后oracle就会给出对应查询文档结果
这个文档匹配到了跟我们一样的错误信息
发现是个bug，点进去
oracle建议设置
_push_join_predicate和/或
_push_join_union_view两个参数值都为false来避免这个错误的场景。
但是很遗憾，本次莫名其妙又没了，无法测试到有效性。
具体到我这个环境的话，怀疑是10g的版本在centos7.3上安装兼容性并不好，导致一些特定的bu*生。