Oracle数据库出现ORA-00600 [kcblasm_1], [103]报错的解决方法

一套10.2.0.5的RAC，最近经常出现ORA-00600 [kcblasm_1], [103]报错，日志如下：

Thu Jan 09 16:05:09 CST 2014Errors in file /oracle/app/oracle/admin/ora/udump/ora1_ora_27104.trc:ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []Thu Jan 09 16:05:29 CST 2014Trace dumping is performing id=[cdmp_20140109160529]Thu Jan 09 16:05:30 CST 2014Errors in file /oracle/app/oracle/admin/ora/udump/ora1_ora_899.trc:ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []

经检查，这是一个oracle bug，原因是由相关SQL语句的执行导致。
[color=navy]解决方法有几下几种：[color=navy]1）目前trc文件里面的这条SQL，表与表之间的链接相当复杂，如果表与表之间的链接产生的结果集比较大会触发这个BUG，建议修改SQL语句，可以避免该问题。[color=navy]2）安装Oracle PSU补丁集PSU 10.2.0.5.4或更高[color=navy]3）在现有的版本上安装补丁PATCH：7612454[color=navy]4）升级数据库到11.2版本[color=navy]如果目前数据库运行比较稳定，可以先观察，暂时不升级或打补丁，尽量通过优化SQL程序来解决。

另外来自网络上的相关解决方案：

在10.2.0.5版本中，所有平台环境下补丁程序P7612454，该补丁是解决hash join时候，Direct IO最大限制4096，我们从执行计划中可以看出,hash join的build table表的cardinality非常大，这个是造成该问题的罪魁祸首。解决方案如下：1、11.2版本解决了上述问题;
2、升级补丁P7612454，该补丁替换lib中的kcbl.o文件;
3、如果执行计划中是hash join造成的，在会话层中设置"_hash_join_enable"=false，如果执行计划是hash group by 造成的，设置"_gby_hash_aggregation_enable"=false，在 相应的SQL前加execute immediate 'alter session set "_hash_join_enabled" = false'亦可;
4、修改SQL语句，尽量减少build table的cardinality的值，可以避免该问题的生成。
[color=purple]关于这个报错，Oracle Metalink的两篇文章介绍如下：

[color=purple]第一篇文章：　　

[color=purple] Applies to:
　　Oracle Server - Enterprise Edition - Version: 10.2.0.5.0 and later [Release: 10.2 and later ]
　　Information in this document applies to any platform.
[color=purple]
　[color=purple]Symptoms
　　When executing a statement using a SELECT getting:
　　ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []
　　The execution plan from the ORA-600 trace file is showing hash join is used.
　　Call Stack is including:
　　kcblasm1 kcblasm kxhfFndFreeSlot kxhfNewBuffer qerhjGetNewBuffer ksxb1bqb kxhrPack
　　
[color=purple] Cause
　　The issue was investigated in:
　　Bug 9800302 - 10.2.0.5 GETTING ORA-00600 [KCBLASM_1]
　　closed as duplicate of:Bug 7612454 - Abstract: DSS:PERF REGRESSIONS IN SERIAL DIRECT READS fixed in 11.2.
　　As per development team the number of slots available for direct I/Os (limited to 4096) forced the hash-join algorithm to operate on fewer number of slots and resulted in more spills to disk. This caused:
　　 direct path IO to perform worse in 10.2.0.5 than earlier releases with more "direct path read" operations or
　　 ORA-600 [kcblasm_1] errors.
　　
　　Solution
　　
　　1. Upgrade the database to 11.2.
　　OR
　　2. Apply &incFamilyProds=false&flag=search))" target=_blank>Patch 7612454 available on MOS. If a patch is not currently available on top of your database version and/or platform please raise a Service Request to request for it.
　　Please be sure that your database version qualifies for getting a new patch as per Note 209768.1 and Note 742060.1.
　　OR
　　3. Use the workaround of setting:
　　"_hash_join_enabled"= false

第二篇文章：

Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.5.0 to 10.2.0.5.0 [Release 10.2]
Information in this document applies to any platform.
***Checked for relevance on 18-Jul-2013***

Symptoms
Hitting ORA-600 [kcblasm_1] in 10.2.0.5:
In the ALERT:
ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []
The call stack in the generated tracefiles looks similar to:
kcblasm1 kcblasm kxhfFndFreeSlot kxhfNewBuffer qerhjGetNewBuffer ksxb1bqb kxhrPack

Changes
This problem is hit only in 10.2.0.5, up to PSU 10.2.0.5.4 in which problem is fixed.
Problem is affecting 10.2.0.5 to 10.2.0.5.3.

Cause
This is is a known and common problem hit in 10.2.0.5, investigated and corrected in unpublished Bug 7612454.
The problem was introduced in 10.2.0.5, by the fix of Bug:7523755.
This has been corrected in PSU 10.2.0.5.4 and 11.2.

Solution
To quickest way to the solution of this problem is to apply PSU 10.2.0.5.4, Patch 12419392 or later.
All alternative solutions for this problem are listed below:

1、Upgrade the database to 11.2.

OR

2、Apply 10.2.0.5.4 Patch Set Update (Patch 12419392) or later PSUs where bug is fixed.
The available PSUs are mentioned in "10.2.0.5 Patch Set Updates - List of Fixes in each PSU" (Document 1337394.1)

OR

3、Apply interim Patch 7612454 on top of 10.2.0.5 (10.2.0.5.0-10.2.0.5.3):

For UNIX / Linux platforms apply Patch 7612454 available for download on MOS.
For Windows platforms apply Patch 3 or higher.
Please check Document 342443.1 for latest patches available for Windows on top of 10.2.0.5.

Note:
It is possible to hit ORA-600 [kcblasm_1] in 10.2.0.4 or 11.1.0.7 if an early version of the one-off Patch 7523755 was applied.
The problem patch Patch 7523755 was quickly removed and a correct version of the patch is since long available for download at MOS.
For details of the problem of hitting ORA-600 [kcblasm_1] in 10.2.0.4 or 11.1.0.7, please take part of Document 849381.1.