ORACLE ORA-00600: 內部錯誤代碼, 參數: [kokbcvb1]與merge into

 最近，一地市Oracle數據庫跑一個Job報錯，報錯內容如下：

    操作系統：windows server 2008R2

    數據庫版本：oracle 11.2.0.1

    報錯內容：

Fri Aug 11 11:08:14 2017
Errors in file d:\app\administrator\diag\rdbms\bmi\bmi\trace\bmi_ora_2376.trc  (incident=64225):
ORA-00600: 內部錯誤代碼, 參數: [kokbcvb1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\bmi\bmi\incident\incdir_64225\bmi_ora_2376_i64225.trc

查看告警日志提示的trc文件bmi_ora_2376_i64225.trc，報錯核心內容如下：

Dump file d:\app\administrator\diag\rdbms\bmi\bmi\incident\incdir_64225\bmi_ora_2376_i64225.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1 
CPU                 : 32 - type 8664, 16 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:88095M/130883M, Ph+PgF:218580M/261764M 
Instance name: bmi
Redo thread mounted by this instance: 1
Oracle process number: 78
Windows thread id: 2376, image: ORACLE.EXE (SHAD)
*** 2017-08-11 11:08:14.729
*** SESSION ID:(352.202) 2017-08-11 11:08:14.729
*** CLIENT ID:() 2017-08-11 11:08:14.729
*** SERVICE NAME:(bmi) 2017-08-11 11:08:14.729
*** MODULE NAME:(PL/SQL Developer) 2017-08-11 11:08:14.729
*** ACTION NAME:(Primary Session) 2017-08-11 11:08:14.729
Dump continued from file: d:\app\administrator\diag\rdbms\bmi\bmi\trace\bmi_ora_2376.trc
ORA-00600: 內部錯誤代碼, 參數: [kokbcvb1], [], [], [], [], [], [], [], [], [], [], []
========= Dump for incident 64225 (ORA 600 [kokbcvb1]) ========
*** 2017-08-11 11:08:14.729
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=14qa0m0uufbbv) -----
MERGE INTO DW_BILL T1 USING (SELECT B.HIS_ID, SUM(CASE WHEN B.REJECT_MONEY >= B.CAN_REJECT_MONEY THEN B.CAN_REJECT_MONEY ELSE B.REJECT_MONEY END) REJECT_MONEY, SUM(B.RULE_TYPE) RULE_TYPE, B.TABLE_PAR FROM (SELECT T.HIS_ID, T.DETAIL_ID, T.CAN_REJECT_MONEY, SUM(T.REJECT_MONEY) REJECT_MONEY, SUM(TO_NUMBER(T.RULE_TYPE)) RULE_TYPE, T.TABLE_PAR FROM TMP_DEDUCTIONPLAN_BILLDETAIL T GROUP BY T.HIS_ID, T.DETAIL_ID, T.CAN_REJECT_MONEY, T.TABLE_PAR) B GROUP BY B.HIS_ID, B.TABLE_PAR) T2 ON (T1.HISID = T2.HIS_ID AND T1.TABLE_PAR = T2.TABLE_PAR AND T1.TABLE_PAR >= :B2 AND T1.TABLE_PAR < :B1 ) WHEN MATCHED THEN UPDATE SET T1.BMI_NOPAY = CASE WHEN T1.BMI_NOPAY + T2.REJECT_MONEY >= T1.BMI_CONVERED_AMOUNT THEN T1.BMI_CONVERED_AMOUNT ELSE T1.BMI_NOPAY + T2.REJECT_MONEY END, T1.RULETYPE = CASE WHEN T2.RULE_TYPE > 0 THEN '1' ELSE T1.RULETYPE END, T1.AUDIT_RESULT_F = '3', T1.AUDIT_RESULT_S = '3', T1.ANDIT_MANU_STATUS = '3', T1.ANDIT_MANU_DATE = SYSDATE
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0000000E1719FA18       111  package body BMI.PKG_D_REALMONEY_SUM
0000000E1719FA18        58  package body BMI.PKG_D_REALMONEY_SUM
0000000E1719FA18         8  package body BMI.PKG_D_REALMONEY_SUM
0000000E26CEDB20        15  package body BMI.PKG_DEDUCTION_REALMONEY
0000000E2F232798       156  package body BMI.PKG_DEDUCTION_COMMON
0000000E1E7739A0         1  anonymous block
0000000E1717C1A8       651  package body SYS.DBMS_IJOB
0000000E26CEF490       284  package body SYS.DBMS_JOB
0000000E0F00AEE8         1  anonymous block

根據trc文件提示，ORA-00600 [kokbcvb1] 是如下SQL引起的：

  MERGE INTO DW_BILL T1
  USING (SELECT B.HIS_ID,
                SUM(CASE
                      WHEN B.REJECT_MONEY >= B.CAN_REJECT_MONEY THEN
                       B.CAN_REJECT_MONEY
                      ELSE
                       B.REJECT_MONEY
                    END) REJECT_MONEY,
                SUM(B.RULE_TYPE) RULE_TYPE,
                B.TABLE_PAR
           FROM (SELECT T.HIS_ID,
                        T.DETAIL_ID,
                        T.CAN_REJECT_MONEY,
                        SUM(T.REJECT_MONEY) REJECT_MONEY,
                        SUM(TO_NUMBER(T.RULE_TYPE)) RULE_TYPE,
                        T.TABLE_PAR
                   FROM TMP_DEDUCTIONPLAN_BILLDETAIL T
                  GROUP BY T.HIS_ID,
                           T.DETAIL_ID,
                           T.CAN_REJECT_MONEY,
                           T.TABLE_PAR) B
          GROUP BY B.HIS_ID, B.TABLE_PAR) T2
  ON (T1.HISID = T2.HIS_ID AND T1.TABLE_PAR = T2.TABLE_PAR AND T1.TABLE_PAR >= :B2 AND T1.TABLE_PAR < :B1)
  WHEN MATCHED THEN
    UPDATE
       SET T1.BMI_NOPAY         = CASE
                                    WHEN T1.BMI_NOPAY + T2.REJECT_MONEY >= T1.BMI_CONVERED_AMOUNT THEN
                                     T1.BMI_CONVERED_AMOUNT
                                    ELSE
                                     T1.BMI_NOPAY + T2.REJECT_MONEY
                                  END,
           T1.RULETYPE          = CASE
                                    WHEN T2.RULE_TYPE > 0 THEN
                                     '1'
                                    ELSE
                                     T1.RULETYPE
                                  END,
           T1.AUDIT_RESULT_F    = '3',
           T1.AUDIT_RESULT_S    = '3',
           T1.ANDIT_MANU_STATUS = '3',
           T1.ANDIT_MANU_DATE   = SYSDATE

關于ORA-00600 [kokbcvb1] ，oracle 官方有篇文檔（文檔 ID 1182535.1）提到是Bug 9471103 引起的：

Merge Query Failed with Ora-00600: [Kokbcvb1] (文檔 ID 1182535.1)

APPLIES TO:                                                                               

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.2.0 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 29-Feb-2012*** 

SYMPTOMS                                                                                

A merge query failed with the error below:

ORA-600: internal error code, arguments: [kokbcvb1], [], [], [], [], []

For example :

merge into X USING (select a col1,'B' col2,3 col3,...

Even with Patch 8651671 and Patch 5308497 applied, the problem still reproduces. 

When "_optimizer_push_pred_cost_based"=false; the statement fails with

ORA-2070: database does not support antijoin

With "_subquery_pruning_enabled"=false; statement finishes with

0 rows merged.

CAUSE                                                                                  

This could possibly be due to Bug 9471103 MERGE QUERY FAILED WITH ORA-600 [KOKBCVB1]  which was closed as duplicate of unpublished Bug 9757249 ORA-600 [KOKBCVB1]

SOLUTION

從提取到的報錯信息，與bug 9471103吻合，處理方法是：應用補丁包patch 9757249或者升級數據庫版本到11.2.0.2以上