风哥教程

培训 . 交流 . 分享
Make progress together!

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

[复制链接]
内容发布:风哥| 发布时间:2014-1-20 22:28:25
oracle数据库出现ORA-00600 [kcblasm_1], [103]报错的解决方法

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

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


经检查,这是一个oracle bug,原因是由相关SQL语句的执行导致。

解决方法有几下几种:
1)目前trc文件里面的这条SQL,表与表之间的链接相当复杂,如果表与表之间的链接产生的结果集比较大会触发这个BUG,建议修改SQL语句,可以避免该问题。
2)安装Oracle PSU补丁集PSU 10.2.0.5.4或更高
3)在现有的版本上安装补丁PATCH7612454
4)升级数据库到11.2版本
如果目前数据库运行比较稳定,可以先观察,暂时不升级或打补丁,尽量通过优化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的值,可以避免该问题的生成。

关于这个报错,Oracle Metalink的两篇文章介绍如下:

第一篇文章:  

    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.


 
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

  

   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.




上一篇:Oracle出现ORA-27300,ORA-27301,ORA-27302报错的最终解决办法
下一篇:ORA-19706: Invalid SCN
专业提供Oracle/MySQL/NoSQL/Linux数据库培训与技术支持服务,QQ号:113257174
关注风哥教程微信公众号itpux_com  ,了解本站最新技术资料的分享.

欢迎加QQ群,提供超多高质量Oracle/Unix/Linux技术文档与视频教程的下载。

Oracle/MySQL/Linux群4-5:189070296  150201289  
Oracle/MySQL/Linux群6-8:244609803   522261684   522651731
备注:请勿重复加群,另请注明 from itpux
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

热门文章教程

  • 实战PHP与MySQL权威指南PDF电子书下载
  • 大数据技术与应用入门培训教程(电子版下载
  • Oracle 12cR2 九大新功能全面曝光_详解云数
  • Oracle OCP认证考试IZ0-053题库共712题数据
  • MySQL5权威指南(第3版)PDF电子版下载
  • 风哥Oracle数据库巡检工具V1.0(附2.6网页
快速回复 返回顶部 返回列表