oracle报错（ORA-00600）问题处理

数据库  /  管理员 发布于 6年前   203

告警日志里这两天一直显示这个错误：

ORA-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[]TueAug1209:20:17CST2014Errorsinfile/u01/app/oracle/admin/orcl/udump/orcl_ora_29974.trc:ORA-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[]TueAug1209:30:17CST2014Errorsinfile/u01/app/oracle/admin/orcl/udump/orcl_ora_30084.trc:ORA-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[]TueAug1209:40:17CST2014Errorsinfile/u01/app/oracle/admin/orcl/udump/orcl_ora_29919.trc:ORA-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[]

网上查的解决办法：

1:临时的解决方法
如果执行计划中是hashjoin造成的，在会话层中设置"_hash_join_enable"=false，如：altersessionset"_hash_join_enabled"=false亦可;

如果执行计划是hashgroupby造成的，设置"_gby_hash_aggregation_enabled"=false
2:根本的解决方法
2.1.优化sql语句，避免遇到bug；
2.2.升级
(1)将数据库升级psu到10.2.0.5.4和11.2可以修正该问题
(2)对于10.2.0.5.0到10.2.0.5.3的版本，打PATCH7612454来避免改错误(该补丁替换lib中的kcbl.o文件)。

通过临时解决办法解决问题示例：

追踪报警日志里提示的trace文件，找到导致出现此错误的sql语句

ORA-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[]CurrentSQLstatementforthissession:

格式化后的sql语句如下：

SELECTINDENTDATE,INDENTGROUP,TRANSDATE,TRANSBY,TRANSGROUP,FEEDBACKBY,FEEDBACKGROUP,FINANCEDATE,FINANCEBY,FINANCEGROUP,TOTALCOST,A.TOTALPAY,PAY_CASH,PAY_POINTS,PAY_ADVANCE1,PAY_ADVANCE2,PAY_TYPE,TRANS_PAY,DISCOUNT_STAFF,DISCOUNT_SPECIAL,GAIN_CASH,GAIN_POINTS,GAIN_ADVANCE1,GAIN_ADVANCE2,TRANS_CUSTNAME,TRANS_TEL,TRANS_PROVINCE,TRANS_CITY,TRANS_ADDRESS,TRANS_ZIPCODE,TRANS_WEIGHT,TRANS_COMMENTS,INDENT_COMMENTS,INDENT_ID,A.PARTNER_GUID,A.PROXY_GUID,TRANS_TEL2,CUST_MEDIA_ID,CUST_PARTNER_GUID,CUST_PROXY_GUID,PARTNER_VALUE,PROXY_VALUE,CUST_PARTNER_VALUE,CUST_PROXY_VALUE,DEALBY,A.FAILREASON,ISFOOT,S_REASONID,DEALFAILREASON,A.PRE_FUND,MEDIA_CALLTYPE,PRE_ADVANCE,WEB_FLAG,NEED_INVOICE,INVOICE_TITLE,TRANS_AREA,ORDERTYPE,PAY_POINTSPRICE,A.MEDIA,USERDEFINEDSTATUS,CUSTOMERNAME,CUSTOMERIDFROMELITE.TABCINDENTALEFTJOINELITE.OBJECTIVEBONA.RELATION_ID=B.OBJECTIVE_GUIDLEFTJOINELITE.CUSTOMERCONA.CUSTOMER_GUID=C.CUSTOMER_GUIDWHERE(INDENTDATEBETWEEN:1AND:2ORB.MODIFIEDDATEBETWEEN:3AND:4);

将变量:1,:2,:3,:4替换成具体的值执行：

SELECTINDENTDATE,INDENTGROUP,TRANSDATE,TRANSBY,TRANSGROUP,FEEDBACKBY,FEEDBACKGROUP,FINANCEDATE,FINANCEBY,FINANCEGROUP,TOTALCOST,A.TOTALPAY,PAY_CASH,PAY_POINTS,PAY_ADVANCE1,PAY_ADVANCE2,PAY_TYPE,TRANS_PAY,DISCOUNT_STAFF,DISCOUNT_SPECIAL,GAIN_CASH,GAIN_POINTS,GAIN_ADVANCE1,GAIN_ADVANCE2,TRANS_CUSTNAME,TRANS_TEL,TRANS_PROVINCE,TRANS_CITY,TRANS_ADDRESS,TRANS_ZIPCODE,TRANS_WEIGHT,TRANS_COMMENTS,INDENT_COMMENTS,INDENT_ID,A.PARTNER_GUID,A.PROXY_GUID,TRANS_TEL2,CUST_MEDIA_ID,CUST_PARTNER_GUID,CUST_PROXY_GUID,PARTNER_VALUE,PROXY_VALUE,CUST_PARTNER_VALUE,CUST_PROXY_VALUE,DEALBY,A.FAILREASON,ISFOOT,S_REASONID,DEALFAILREASON,A.PRE_FUND,MEDIA_CALLTYPE,PRE_ADVANCE,WEB_FLAG,NEED_INVOICE,INVOICE_TITLE,TRANS_AREA,ORDERTYPE,PAY_POINTSPRICE,A.MEDIA,USERDEFINEDSTATUS,CUSTOMERNAME,CUSTOMERIDFROMELITE.TABCINDENTALEFTJOINELITE.OBJECTIVEBONA.RELATION_ID=B.OBJECTIVE_GUIDLEFTJOINELITE.CUSTOMERCONA.CUSTOMER_GUID=C.CUSTOMER_GUIDWHERE(INDENTDATEBETWEEN'2012-06-19'AND'2012-08-19'ORB.MODIFIEDDATEBETWEEN'2012-06-19'AND'2012-08-1');

执行报错：

解决办法：

altersessionset"_hash_join_enabled"=false;

altersessionset"_gby_hash_aggregation_enabled"=false

--先尝试一种，如果一种解决了，就没必要设置另外一种了。

然后再次执行上面的查询语句，不报错啦，嘎嘎

成功啦，(*^__^*)嘻嘻……

让开发人员在程序里加上这条命令即可。