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以上