客户的一套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.