ORA 600 [qkaQknLTPruneKaf:1] BUG 分析与处理

大家好，这里是 DBA学习之路，专注于提升数据库运维效率。

前言

本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟

今天检查一套 Oracle 12.1 单机数据库发现 alert 日志报错 ORA-600 [qkaQknLTPruneKaf:1]，本文记录一下处理过程。

问题描述

日常检查数据库，adrci 检查发现报错：

Bash

1[oracle@lucifer ~]$ sqlplus -v
2
3SQL*Plus: Release 12.1.0.2.0 Production
4
5adrci> show problem  
6
7ADR Home = /u01/app/oracle/diag/rdbms/lucifer/lucifer:
8*************************************************************************
9PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                             
10-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------     
111                    ORA 600 [qkaQknLTPruneKaf:1]                                113105               2025-03-25 14:02:29.144000 +08:00       
122                    ORA 600 [kewuxs_1]                                          113106               2025-03-25 14:02:42.486000 +08:00

查看 alert 日志：

Bash

1ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], [], [], [], [], [], [], []
2ORA-00600: internal error code, arguments: [kewuxs_1], [600], [ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], [], [], [], [], [], [], []
3ORA-06512: at "SYS.DBMS_SQL", line 1707
4ORA-06512: at "SYS.DBMS_FEATURE_USAGE_INTERNAL", line 312
5ORA-06512: at "SYS.DBMS_FEATURE_USAGE_INTERNAL", line 522
6ORA-06512: at "SYS.DBMS_FEATURE_USAGE_INTERNAL", line 694
7ORA-06512: at "SYS.DBMS_FEATURE_USAGE_INTERNAL", line 791
8ORA-06512: at line 1
9], [], [], [], [], [], [], [], [], []

日志中可以发现数据库每天都在报错：ORA 600 [qkaQknLTPruneKaf:1]，明显是不正常的现象。

问题分析

查看 alert 报错对应的 trc 文件内容：

Bash

1----- Incident Context Dump -----
2Address: 0x7ffc986bb400
3Incident ID: 113625
4Problem Key: ORA 600 [qkaQknLTPruneKaf:1]
5Error: ORA-600 [qkaQknLTPruneKaf:1] [] [] [] [] [] [] [] [] [] [] []
6[00]: dbgexProcessError [diag_dde]
7[01]: dbgeExecuteForError [diag_dde]
8[02]: dbgePostErrorKGE [diag_dde]
9[03]: dbkePostKGE_kgsf [rdbms_dde]
10[04]: kgeadse []
11[05]: kgerinv_internal []
12[06]: kgerinv []
13[07]: kgeasnmierr []
14[08]: qkaQknLTPruneKaf [SQL_Code_Generator]<-- Signaling
15[09]: qkaQknPruneKaf [SQL_Code_Generator]
16[10]: qknProjPushNode_Int [SQL_Code_Generator]
17[11]: qknProjPushNode [SQL_Code_Generator]
18[12]: qkeWalkAllQueryNodes [SQL_Code_Generator]
19[13]: qknProjPushStmt [SQL_Code_Generator]
20[14]: qkeProjPrune [SQL_Code_Generator]
21[15]: qkadrv2 [SQL_Code_Generator]

该问题在 MOS 上搜索后发现是一个 BUG：

ORA-00600:[qkaQknLTPruneKaf:1] WHEN _NLJ_BATCHING_ENABLED=0 ON ORACLE 12C (Doc ID 2067672.1)

其描述与本次内容基本一致：

查看隐含参数 _NLJ_BATCHING_ENABLED 在数据库中的值：

SQL

1set line2222 pages1000
2col name for a40
3col value for a10
4col describ for a60
5select x.ksppinm name, y.ksppstvl value, x.ksppdesc describ
6from sys.x$ksppi x, sys.x$ksppcv y
7where x.inst_id = userenv ('instance')
8and y.inst_id = userenv ('instance')
9and x.indx = y.indx
10and x.ksppinm = '_nlj_batching_enabled';
11
12NAME                                     VALUE      DESCRIB
13---------------------------------------- ---------- ------------------------------------------------------------
14_nlj_batching_enabled                    0          enable batching of the RHS IO in NLJ

确实值被设置为 0，与该 BUG 现象完全一致，根据 MOS 建议有两种解决方案：

这个问题 Oracle 官方提供了两种解决方案：

打补丁 26153372（需要停机）
修改隐含参数 _nlj_batching_enabled=1（在线修改）

如果停机时间充足的情况下，还是比较建议打补丁。

问题解决

打补丁修复，首先检查 OPatch 是否符合要求：

Bash

1[oracle@lucifer ~]$ cd $ORACLE_HOME
2[oracle@lucifer dbhome_1]$ cd OPatch/
3[oracle@lucifer OPatch]$ ./opatch version
4OPatch Version: 12.1.0.1.3
5
6OPatch succeeded.

补丁 README 需要 opatch 版本为：12.1.0.1.4 or the latest version，这里 12.1.0.2 版本下载 12CR2 版本的 OPatch 即可：

OPatch - 可以在什么位置找到最新版本的 OPatch(6880880)？[视频] (Doc ID 1525335.1)

更新 OPatch 版本：

Bash

1[root@lucifer u01]# chown oracle:oinstall /home/oracle/p6880880_122010_Linux-x86-64.zip 
2[oracle@lucifer ~]$ unzip -qo p6880880_122010_Linux-x86-64.zip -d $ORACLE_HOME
3[oracle@lucifer ~]$ cd $ORACLE_HOME/OPatch
4[oracle@lucifer OPatch]$ ./opatch version
5OPatch Version: 12.2.0.1.45
6
7OPatch succeeded.

解压补丁：

Bash

1[root@lucifer ~]# chown oracle:oinstall /home/oracle/p26153372_121020_Linux-x86-64.zip
2[oracle@lucifer ~]$ unzip -q p26153372_121020_Linux-x86-64.zip

执行安装前检查：

Bash

1[oracle@lucifer ~]$ cd 26153372/
2[oracle@lucifer 26153372]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
3Oracle Interim Patch Installer version 12.2.0.1.45
4Copyright (c) 2025, Oracle Corporation.  All rights reserved.
5
6PREREQ session
7
8Oracle Home       : /u01/app/oracle/product/12.1.0.2/dbhome_1
9Central Inventory : /u01/app/oraInventory
10   from           : /u01/app/oracle/product/12.1.0.2/dbhome_1/oraInst.loc
11OPatch version    : 12.2.0.1.45
12OUI version       : 12.1.0.2.0
13Log file location : /u01/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/opatch/opatch2025-03-26_15-12-18PM_1.log
14
15Invoking prereq "checkconflictagainstohwithdetail"
16
17Prereq "checkConflictAgainstOHWithDetail" passed.
18
19OPatch succeeded.

关闭 oracle 相关资源：

Bash

1## 关闭监听
2[oracle@lucifer ~]$ lsnrctl stop
3
4## 关闭数据库
5SQL> shu immediate

正式安装补丁：

Bash

1[oracle@lucifer 26153372]$ $ORACLE_HOME/OPatch/opatch apply

安装完成后检查补丁：

Bash

1$ORACLE_HOME/OPatch/opatch lspatches

关闭 oracle 相关资源：

Bash

1## 关闭监听
2[oracle@lucifer ~]$ lsnrctl start
3
4## 打开数据库
5SQL> startup

后续检查是否还会报错 ORA-600 即可。

写在最后

既然 BUG 难以避免，做好备份尤为重要。

Bash

1[oracle@lucifer ~]$ sqlplus -v
2
3SQL*Plus: Release 12.1.0.2.0 Production
4
5adrci> show problem  
6
7ADR Home = /u01/app/oracle/diag/rdbms/lucifer/lucifer:
8*************************************************************************
9PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                             
10-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------     
111                    ORA 600 [qkaQknLTPruneKaf:1]                                113105               2025-03-25 14:02:29.144000 +08:00       
122                    ORA 600 [kewuxs_1]                                          113106               2025-03-25 14:02:42.486000 +08:00

Bash

1ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], [], [], [], [], [], [], []
2ORA-00600: internal error code, arguments: [kewuxs_1], [600], [ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], [], [], [], [], [], [], []
3ORA-06512: at "SYS.DBMS_SQL", line 1707
4ORA-06512: at "SYS.DBMS_FEATURE_USAGE_INTERNAL", line 312
5ORA-06512: at "SYS.DBMS_FEATURE_USAGE_INTERNAL", line 522
6ORA-06512: at "SYS.DBMS_FEATURE_USAGE_INTERNAL", line 694
7ORA-06512: at "SYS.DBMS_FEATURE_USAGE_INTERNAL", line 791
8ORA-06512: at line 1
9], [], [], [], [], [], [], [], [], []

Bash

1----- Incident Context Dump -----
2Address: 0x7ffc986bb400
3Incident ID: 113625
4Problem Key: ORA 600 [qkaQknLTPruneKaf:1]
5Error: ORA-600 [qkaQknLTPruneKaf:1] [] [] [] [] [] [] [] [] [] [] []
6[00]: dbgexProcessError [diag_dde]
7[01]: dbgeExecuteForError [diag_dde]
8[02]: dbgePostErrorKGE [diag_dde]
9[03]: dbkePostKGE_kgsf [rdbms_dde]
10[04]: kgeadse []
11[05]: kgerinv_internal []
12[06]: kgerinv []
13[07]: kgeasnmierr []
14[08]: qkaQknLTPruneKaf [SQL_Code_Generator]<-- Signaling
15[09]: qkaQknPruneKaf [SQL_Code_Generator]
16[10]: qknProjPushNode_Int [SQL_Code_Generator]
17[11]: qknProjPushNode [SQL_Code_Generator]
18[12]: qkeWalkAllQueryNodes [SQL_Code_Generator]
19[13]: qknProjPushStmt [SQL_Code_Generator]
20[14]: qkeProjPrune [SQL_Code_Generator]
21[15]: qkadrv2 [SQL_Code_Generator]

SQL

1set line2222 pages1000
2col name for a40
3col value for a10
4col describ for a60
5select x.ksppinm name, y.ksppstvl value, x.ksppdesc describ
6from sys.x$ksppi x, sys.x$ksppcv y
7where x.inst_id = userenv ('instance')
8and y.inst_id = userenv ('instance')
9and x.indx = y.indx
10and x.ksppinm = '_nlj_batching_enabled';
11
12NAME                                     VALUE      DESCRIB
13---------------------------------------- ---------- ------------------------------------------------------------
14_nlj_batching_enabled                    0          enable batching of the RHS IO in NLJ

Bash

1[oracle@lucifer ~]$ cd $ORACLE_HOME
2[oracle@lucifer dbhome_1]$ cd OPatch/
3[oracle@lucifer OPatch]$ ./opatch version
4OPatch Version: 12.1.0.1.3
5
6OPatch succeeded.

Bash

1[root@lucifer u01]# chown oracle:oinstall /home/oracle/p6880880_122010_Linux-x86-64.zip 
2[oracle@lucifer ~]$ unzip -qo p6880880_122010_Linux-x86-64.zip -d $ORACLE_HOME
3[oracle@lucifer ~]$ cd $ORACLE_HOME/OPatch
4[oracle@lucifer OPatch]$ ./opatch version
5OPatch Version: 12.2.0.1.45
6
7OPatch succeeded.

Bash

1[root@lucifer ~]# chown oracle:oinstall /home/oracle/p26153372_121020_Linux-x86-64.zip
2[oracle@lucifer ~]$ unzip -q p26153372_121020_Linux-x86-64.zip

Bash

1[oracle@lucifer ~]$ cd 26153372/
2[oracle@lucifer 26153372]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
3Oracle Interim Patch Installer version 12.2.0.1.45
4Copyright (c) 2025, Oracle Corporation.  All rights reserved.
5
6PREREQ session
7
8Oracle Home       : /u01/app/oracle/product/12.1.0.2/dbhome_1
9Central Inventory : /u01/app/oraInventory
10   from           : /u01/app/oracle/product/12.1.0.2/dbhome_1/oraInst.loc
11OPatch version    : 12.2.0.1.45
12OUI version       : 12.1.0.2.0
13Log file location : /u01/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/opatch/opatch2025-03-26_15-12-18PM_1.log
14
15Invoking prereq "checkconflictagainstohwithdetail"
16
17Prereq "checkConflictAgainstOHWithDetail" passed.
18
19OPatch succeeded.

Bash

1## 关闭监听
2[oracle@lucifer ~]$ lsnrctl stop
3
4## 关闭数据库
5SQL> shu immediate

Bash

1[oracle@lucifer 26153372]$ $ORACLE_HOME/OPatch/opatch apply

Bash

1$ORACLE_HOME/OPatch/opatch lspatches

Bash

1## 关闭监听
2[oracle@lucifer ~]$ lsnrctl start
3
4## 打开数据库
5SQL> startup