ORA-7445 [qkaqkn]

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

发表评论

电子邮件地址不会被公开。

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>