客户的一套10.2.0.5 RAC在日常健康检查时发现告警日志中有如下报错：
Errors in file /home/db/oracle/admin/k1odsbptdb/udump/k1odsbpt1_ora_11457.trc:
ORA-07445: exception encountered: core dump [qkaqkn()+5216] [SIGSEGV] [unknown code] [0x000000020] [] []
后查看对应的trace日志发现如下相关信息：
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [qkaqkn()+5216] [SIGSEGV] [unknown code] [0x000000020] [] []
Current SQL statement for this session:
insert into B_F_OSCK_HF_TRUST_LN_SL
select to_char(sysdate, 'YYYY-MM-DD') RPT_DT,
'FXC201407100001' PRO_ID,
'20141001-20141127-650670000-0001' BAT_NO,
rownum DATA_NO,
'1' ECP_TYP, --异常类型
am.SA_TX_DT,
am.SA_TX_TM,
am.Flag,
decode(am.Flag, 'ACA', '内部帐流水', 'SAE', '企业活期流水') Flag_desc,
am.TRAN_CD,
am.OPEN_INST_NO, --开户机构
oi.INST_NAME_L4 OPEN_INST_NM, --开户机构名称
am.OPEN_TLR, --开户柜员
ot.TELLER_NAME, --开户柜员名称
am.bus_inst_no,
bi.INST_NAME_L4 bus_inst_nm, --交易机构名称
am.BUS_TLR,
bt.TELLER_NAME, --交易柜员名称
am.TX_LOG_NO,
am.cust_acct_no,
am.cust_name, --账户名称
am.CR_TX_AMT,
am.ACCT_BAL,
am.DR_CR_COD,
am.DOC_TYP,
am.BINO,
am.DSCRP_COD,
am.SA_RMRK_DESC,
am.SA_RMRK,
am.CM_OPP_ACCT_NO,
am.SA_OP_CUST_NAME,
am.SA_OP_BANK_NO,
am.SA_OP_TX_NO,
am.SA_OP_TX_NO_TYP,
am.SA_TX_CRD_NO,
am.TX_TYPE,
am.CUST_NO,
am.ACCT_NO,
to_char(sysdate, 'YYYY-MM-DD hh24:mi:ss'), --数据加载时间
'', --备用1
'', --备用2
'', --备用3
'', --备用4
'' --备用5
from (select a.SA_TX_DT,
to_char(to_timestamp(a.SA_TX_TM, 'hh24missff3'),
'hh24:mi:ss') SA_TX_TM,
'SAE' Flag, --记录类型,ACA内部帐流水，SAE企业活期流水
a.TRAN_CD,
sae.INST_NO OPEN_INST_NO, --经营管理机构
sae.TLR_NO OPEN_TLR, --开户柜员号
a.BUS_INST_NO,
a.XT_OP_TRL BUS_TLR,
TX_LOG_NO,
a.CUST_ACCT_NO,
sae.CUST_NAME,
a.CR_TX_AMT,
a.ACCT_BAL,
a.DR_CR_COD,
a.DOC_TYP,
a.BINO,
a.DSCRP_COD,
a.SA_RMRK_DESC,
a.SA_RMRK,
a.CM_OPP_ACCT_NO,
a.SA_OP_CUST_NAME,
a.SA_OP_BANK_NO,
a.SA_OP_TX_NO,
a.SA_OP_TX_NO_TYP,
a.SA_TX_CRD_NO,
a.TX_TYPE,
a.CUST_NO,
a.ACCT_NO
FROM BF_EVT_DEP_SAE a
left join odsbdata.BF_AGT_DEP_ACCT_SAE sae
on sae.ACCT_NO = a.ACCT_NO
inner join (select sae.sa_tx_dt, sae.acct_no
from tmp_osck_sae_bal sae,
tmp_osck_aca_sae_inst_false fal
where sae.SA_TX_DT = fal.SA_TX_DT
and sae.inst_no = fal.inst_no) b
on a.SA_TX_DT = b.SA_TX_DT
and a.acct_no = b.acct_no
union all
select BK_DATE,
to_char(to_timestamp(CR_TX_TM, 'hh24missff3'), 'hh24:mi:ss') CR_TX_TM,
'ACA' Flag, --记录类型,ACA内部帐流水，SAE企业活期流水
'' TRAN_CD,
aca.OPR_UNIT_CD open_inst_no,
'' open_TLR, --没有开户柜员
nvl(trim(a2.TRAD_BRAN), substr(LN_TX_OPR_NO, 1, 9)) bus_inst_no, --交易机构
LN_TX_OPR_NO bus_tlr,
TX_LOG_NO,
GL_ACCT_NO,
GL_ACCT_NAME,
CR_AMT,
BAL,
DR_CR_COD,
DOC_TYP,
BINO,
DSCRP_COD,
SUMMARY,
'' SA_RMRK,
'' CM_OPP_ACCT_NO,
'' SA_OP_CUST_NAME,
'' SA_OP_BANK_NO,
'' SA_OP_TX_NO,
'' SA_OP_TX_NO_TYP,
'' SA_TX_CRD_NO,
'' TX_TYPE,
'' CUST_NO,
cust_acct_no ACCT_NO
FROM BF_EVT_GL_INN_CCBS_TXN a2
left join BF_AGT_INN_CCBS_ACA aca
on aca.CCBS_ACCT_NO = a2.GL_ACCT_NO
inner join (select aca.sa_tx_dt, aca.acct_no
from tmp_osck_aca_bal aca,
tmp_osck_aca_sae_inst_false fal
where aca.SA_TX_DT = fal.SA_TX_DT
and aca.inst_no = fal.inst_no) b2
on a2.bk_date = b2.SA_TX_DT
and a2.GL_ACCT_NO = b2.acct_no) am
left join B_S_DIM_INST_CV bi
on bi.INST_COD_L4 = am.bus_INST_NO
left join B_F_CM_IEMP_TLR_V bt
on bt.TELLER_ID = am.bus_tlr
left join B_S_DIM_INST_CV oi
on oi.INST_COD_L4 = am.open_INST_NO
left join B_F_CM_IEMP_TLR_V ot
on ot.TELLER_ID = am.OPEN_TLR
where am.BUS_INST_NO in
(select ORG_ID
from B_F_DIM_MAP_INST_V
start with ORG_ID = '650670000'
connect by prior ORG_ID = UP_ORG_ID)
对应trace文件中的stack包含如下信息：
qkaqkn <- qkadrv <- qkadrv <- opitca <- kksFullTypeCheck <- rpiswu2 <- kksLoadChild <- kxsGetRuntimeLock <- kksfbc <- kkspsc0 <= kksParseCusor <- opiosq0 <- kpooprx <- kpoal8 <- opiodr
将该SQL语句中的select部分单独在SQL*Plus中执行，前台报错如下：
ORA-03113: end-of-file on communication channel.
后台告警日志中出现上述ORA-7445错误，在MOS上查到与此相关的三篇文章：
Query Via DBLINK Fails With Ora-07445: Exception Encountered: Core Dump [Qkaqkn()+4744] [Sigsegv] (Doc ID 1130973.1)
ORA-7445 [qkaqkn] (Doc ID 1288518.1)
Bug 9597175 : ORA-7445 [QKAQKN] ON QUERY OVER DBLINK TO 10.2.0.4
这三篇文章提到Bug 9061785和9597175，原因描述：
The problem seems to be related to unpublished Bug 9061785 where there is a crash in qka for a query block containing either union all or outer joined views, and some references to remote tables. The bug incorrectly pushes join predicates into a view resulting in ORA-7445.
处理方法：
Apply 11.2.0.2 or above where the problem is fixed
or:
Check Patch 9061785 for the availability of one-off fixes on your version and platform
or:
Workaround the problem by disabling Join Predicate Push Down (JPPD) by setting “_push_join_predicate” = false and/or “_push_join_union_view” = false.
This will stop the optimizer from attempting to push join predicates into a view.