客户10.2.0.5 RAC数据库出现ORA-600[ktrgcm_3]错误。
错误信息如下：
Sun Jul 07 01:06:00 EAT 2013 Errors IN file /oracle/admin/orcl/udump/orcl1_ora_28947.trc: ORA-00600: internal error code, arguments: [ktrgcm_3], [], [], [], [], [], [], [] |
详细TRACE如下：
*** ACTION NAME:() 2013-07-07 00:49:57.500 *** MODULE NAME:(g@orcl (TNS V1-V3)) 2013-07-07 00:49:57.500 *** SERVICE NAME:(orcl) 2013-07-07 00:49:57.500 *** SESSION ID:(3332.36419) 2013-07-07 00:49:57.500 *** 2013-07-07 00:49:57.500 ksedmp: internal OR fatal error ORA-00600: internal error code, arguments: [ktrgcm_3], [], [], [], [], [], [], [] CURRENT SQL statement FOR this SESSION: INSERT INTO T_B_T_IN (TRADE_ID,SUBSCRIBE_ID,BPM_ID,TRADE_TYPE_CODE,IN_MODE_CODE,PRIORITY,SUBSCRIBE_STATE, NEXT_DEAL_TAG,PRODUCT_ID,BRAND_CODE,USER_ID,CUST_ID,USECUST_ID,ACCT_ID,USER_DIFF_CODE, NET_TYPE_CODE,SERIAL_NUMBER,CUST_NAME,ACCEPT_DATE,ACCEPT_MONTH,TRADE_STAFF_ID, TRADE_DEPART_ID,TRADE_CITY_CODE,TRADE_EPARCHY_CODE,TERM_IP,EPARCHY_CODE,CITY_CODE, OLCOM_TAG,EXEC_TIME,FINISH_DATE,OPER_FEE,FOREGIFT,ADVANCE_PAY,INVOICE_NO,FEE_STATE, FEE_TIME,FEE_STAFF_ID,CANCEL_TAG,CANCEL_DATE,CANCEL_STAFF_ID,CANCEL_DEPART_ID, CANCEL_CITY_CODE,CANCEL_EPARCHY_CODE,CHECK_TYPE_CODE,CHK_TAG,AUDIT_TAG, AUDIT_BATCH_NO,ACTOR_NAME,ACTOR_CERTTYPEID,ACTOR_PHONE,ACTOR_CERTNUM, CONTACT,CONTACT_PHONE,CONTACT_ADDRESS,REMARK,IF_MAINTENANCE, PROVINCE_CODE,TRADE_PROVINCE_CODE,CANCEL_PROVINCE_CODE,RSRV_TAG1) SELECT A.TRADE_ID,SUBSCRIBE_ID,BPM_ID,TRADE_TYPE_CODE,IN_MODE_CODE,PRIORITY,SUBSCRIBE_STATE, NEXT_DEAL_TAG,A.PRODUCT_ID,NVL(D.BRAND_CODE,A.BRAND_CODE), A.USER_ID,A.CUST_ID,A.USECUST_ID,ACCT_ID,A.USER_DIFF_CODE, DECODE(A.PRODUCT_ID,'-1','99',NVL(D.NET_TYPE_CODE,A.NET_TYPE_CODE)),A.SERIAL_NUMBER,CUST_NAME,ACCEPT_DATE,A.ACCEPT_MONTH,TRADE_STAFF_ID, TRADE_DEPART_ID,TRADE_CITY_CODE,TRADE_EPARCHY_CODE,TERM_IP,A.EPARCHY_CODE,A.CITY_CODE, OLCOM_TAG,EXEC_TIME,FINISH_DATE,OPER_FEE,FOREGIFT,ADVANCE_PAY,INVOICE_NO,FEE_STATE, FEE_TIME,FEE_STAFF_ID, CASE WHEN B.TRADE_ID_A IS NOT NULL AND A.CANCEL_DATE IS NULL THEN DECODE(A.CANCEL_TAG,'0','4',A.CANCEL_TAG) ELSE CASE WHEN B.TRADE_ID_A IS NOT NULL AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN DECODE(A.CANCEL_TAG,'3','0',A.CANCEL_TAG) ELSE CASE WHEN TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN DECODE(A.CANCEL_TAG,'3','0',A.CANCEL_TAG) ELSE DECODE(A.CANCEL_TAG,'1','0','4','0',A.CANCEL_TAG) END END END CANCEL_TAG, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_DATE END CANCEL_DATE, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_STAFF_ID END CANCEL_STAFF_ID, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_DEPART_ID END CANCEL_DEPART_ID, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_CITY_CODE END CANCEL_CITY_CODE, CASE WHEN A.CANCEL_TAG IN ('1','4','3') AND TRUNC(A.ACCEPT_DATE,'DD')<TRUNC(A.CANCEL_DATE,'DD') THEN NULL ELSE A.CANCEL_EPARCHY_CODE END CANCEL_EPARCHY_CODE, CHECK_TYPE_CODE,CHK_TAG,AUDIT_TAG, AUDIT_BATCH_NO,ACTOR_NAME,ACTOR_CERTTYPEID,ACTOR_PHONE,ACTOR_CERTNUM, CONTACT,CONTACT_PHONE,CONTACT_ADDRESS,A.REMARK,IF_MAINTENANCE, F_SYS_GETVALUE('get_province_code',NULL,DECODE(LENGTH(A.EPARCHY_CODE),4,A.EPARCHY_CODE,A.TRADE_EPARCHY_CODE),NULL,NULL) PROVINCE_CODE, F_SYS_GETVALUE('get_province_code',NULL,A.TRADE_EPARCHY_CODE,NULL,NULL) TRADE_PROVINCE_CODE, F_SYS_GETVALUE('get_province_code',NULL,A.CANCEL_EPARCHY_CODE,NULL,NULL) CANCEL_PROVINCE_CODE, CASE WHEN C.NEW_STATE_CODE='H' THEN '9130' ELSE CASE WHEN C.OLD_STATE_CODE='H' THEN '9140' END END FROM TF_BH_TRADE A, (SELECT TRADE_ID_A,TRADE_ID_B FROM TF_B_TRADE_REL WHERE RELATION_TYPE_CODE='02' AND STATE IN ('0','A') )B,TF_B_TRADE_STATE_TRANS C,TD_B_PRODUCT D WHERE A.TRADE_ID=B.TRADE_ID_B(+) AND A.TRADE_ID=C.TRADE_ID(+) AND A.PRODUCT_ID = D.PRODUCT_ID(+) AND A.ACCEPT_MONTH = :B3 AND A.ACCEPT_DATE >= :B2 AND A.ACCEPT_DATE < :B1 AND A.TRADE_TYPE_CODE NOT IN ('116','616') AND A.NEXT_DEAL_TAG IN (SELECT VALUEC1 FROM TD_SD_COMMPARA WHERE PARAM_TYPE_CODE ='NEXT_DEAL_TAG') AND EXISTS (SELECT X.TRADE_ID FROM (SELECT B.TRADE_ID TRADE_ID FROM TF_B_TRADE_BATDEAL A,TF_BH_TRADE B WHERE A.TRADE_ID = B.TRADE_ID AND A.CANCEL_TAG = B.CANCEL_TAG AND A.STATE ='2' AND A.B_UPDATE_DATE >= :B2 AND A.B_UPDATE_DATE < :B1 AND A.BATCH_OPER_TYPE IN ('CREATEPREUSER','CREATEUSER','DISCNTCHG','ADDGRPMEMBER','BATREMOVEMEMBER','PRODUCTCHG','STATISTICS','BATCHARGEFEE','SCOREADJUST','CANCELPREUSER') MINUS SELECT TRADE_ID FROM T_B_T_IN ) X WHERE A.TRADE_ID = X.TRADE_ID) ----- PL/SQL Call Stack ----- object line object handle NUMBER name c00000142951a9f8 434 PROCEDURE U_S_1.P_S_D_T_IN c0000013baacb568 1 anonymous block ----- Call Stack Trace ----- calling CALL entry argument VALUES IN hex location TYPE point (? means dubious VALUE) -------------------- -------- -------------------- ---------------------------- ksedst()+64 CALL ksedst1() 000000000 ? 000000001 ? ksedmp()+2176 CALL ksedst() 000000000 ? C000000000000D20 ? 4000000004066EE0 ? 000000000 ? 000000000 ? 000000000 ? ksfdmp()+112 CALL ksedmp() 000000003 ? 9FFFFFFFFFFE8EB0 ? 60000000000BA288 ? 9FFFFFFFFFFE9480 ? C000000000000999 ? 40000000040AEF50 ? kgerinv()+304 CALL ksfdmp() 9FFFFFFFFFFE9A10 ? 000000003 ? 9FFFFFFFFFFE9490 ? 60000000000BA288 ? C000000000000612 ? 4000000009903790 ? kgeasnmierr()+144 CALL kgerinv() 60000000000318D0 ? 4000000001AF87F0 ? 6000000000032988 ? 4000000001AF87F0 ? 9FFFFFFFFFFE9A50 ? $cold_ktrgcm()+1188 CALL kgeasnmierr() 60000000000318D0 ? 8 60000000003139F8 ? 6000000000313A08 ? 6000000000032D00 ? 000000000 ? 000000000 ? C0000014D3B02A54 ? C0000014BC6E8AEC ? ktrget()+1088 CALL $cold_ktrgcm() 9FFFFFFFBF349F60 ? 60000000000BA288 ? C000000000000C1D ? 4000000002FB1560 ? 00003811B ? kdirfrs()+8160 CALL ktrget() 9FFFFFFFBF349F60 ? 000000000 ? C00000089B458014 ? qerixFetchFastFullS CALL kdirfrs() 9FFFFFFFBF349F58 ? can()+7504 60000000000BA288 ? 4000000003057DA0 ? 00003825F ? 9FFFFFFFBF34A070 ? 00003821F ? 000000000 ? C000000000001430 ? qergiFetch()+816 CALL qerixFetchFastFullS C0000013CB1A0CA0 ? can() 4000000001CC4CE0 ? 9FFFFFFFFFFEB750 ? 000007FFF ? 000007FFF ? C0000013CB1A0CA0 ? rwsfcd()+256 CALL qergiFetch() C0000013CB1A08F8 ? 4000000001CC4CE0 ? 9FFFFFFFFFFEB750 ? 000007FFF ? qerhjFetch()+912 CALL rwsfcd() 9FFFFFFFBF34A698 ? 4000000001CC4CE0 ? 9FFFFFFFFFFEB750 ? 000007FFF ? qerflFetch()+336 CALL qerhjFetch() C0000013CB1A0740 ? 4000000001CCEC70 ? 9FFFFFFFFFFEB820 ? 000007FFF ? qersoFetch()+2080 CALL qerflFetch() C0000013CB1A05B0 ? 4000000001CCEED0 ? C000001426EB7FA8 ? 000007FFF ? 60000000000BA288 ? qerseFetch()+400 CALL qersoFetch() C000001426EB7FA8 ? 4000000001CCEBF0 ? 9FFFFFFFFFFEB880 ? 000007FFF ? qervwFetch()+368 CALL qerseFetch() C000001426EB7AE8 ? 9FFFFFFFFFFEB8AC ? 9FFFFFFFFFFEB8AC ? 9FFFFFFFBF3DC5DA ? rwsfcd()+256 CALL qervwFetch() C000001426EB79C8 ? 4000000001CC4CF0 ? 9FFFFFFFFFFEB8D0 ? C000001426EB79C0 ? qerhjFetch()+1152 CALL rwsfcd() 9FFFFFFFBF3DC5F8 ? 4000000001CC4CF0 ? 9FFFFFFFFFFEB8D0 ? 000007FFF ? rwsfcd()+256 CALL qerhjFetch() C000001426EB7710 ? 4000000001CC4D00 ? 9FFFFFFFFFFEB9A0 ? 000007FFF ? qerhjFetch()+1728 CALL rwsfcd() 9FFFFFFFBF3DC800 ? 4000000001CC4D00 ? 9FFFFFFFFFFEB9A0 ? 000007FFF ? 60000000000BA288 ? qerjoFetch()+1168 CALL qerhjFetch() C0000013AB146D68 ? 000000000 ? 000000000 ? 000000001 ? 60000000000BA288 ? C0000000000012AB ? rwsfcd()+256 CALL qerjoFetch() C000001388B4B060 ? 4000000001CCEC20 ? 9FFFFFFFFFFEBA80 ? 000007FFF ? qerhjFetch()+1728 CALL rwsfcd() 9FFFFFFFBF3D7CA8 ? 4000000001CC4D00 ? 9FFFFFFFFFFEBAB0 ? 000007FFF ? 60000000000BA288 ? qerjoFetch()+1168 CALL qerhjFetch() C0000013FF4C97D0 ? 000000000 ? 000000000 ? 000000001 ? 60000000000BA288 ? C0000000000012AB ? qerflFetch()+336 CALL qerjoFetch() C000001380167980 ? 4000000001CCEC70 ? 9FFFFFFFFFFEBBC0 ? 000000001 ? rwsfcd()+256 CALL qerflFetch() C0000013801676A0 ? 000000000 ? 000000000 ? 000000001 ? insfch()+304 CALL rwsfcd() 9FFFFFFFBF3DAEF8 ? 000000000 ? 000000000 ? 000000001 ? insdrv()+912 CALL insfch() 9FFFFFFFB9F46F60 ? C0000000000019BB ? 60000000000BA288 ? 40000000031630F0 ? 00003821D ? 60000000000C6FE0 ? 60000000000C6F18 ? 4000000001C116A0 ? inscovexe()+1408 CALL insdrv() 9FFFFFFFFFFEC390 ? 9FFFFFFFFFFEBBF0 ? 60000000000BA288 ? 40000000031B6160 ? 00003825B ? 9FFFFFFFFFFEBBF4 ? insExecStmtExecIniE CALL inscovexe() C0000013E1A08250 ? ngine()+176 C0000013C3B02F08 ? 9FFFFFFFFFFECB40 ? insexe()+1040 CALL insExecStmtExecIniE C0000013E1A08250 ? ngine() C0000013C3B02F08 ? 9FFFFFFFFFFED3D0 ? 60000000000BA288 ? C000000000000FA5 ? 4000000003166C80 ? opiexe()+7760 CALL insexe() C0000013BE5AB8C0 ? 9FFFFFFFFFFED3D0 ? 9FFFFFFFFFFECB50 ? 60000000000BA288 ? 9FFFFFFFFFFED0D0 ? C0000000000025D3 ? 4000000002F87610 ? 00003821B ? opipls()+3680 CALL opiexe() 9FFFFFFFFFFEED70 ? 4000000003119420 ? 60000000000C6CE0 ? 9FFFFFFFFFFED0F0 ? 60000000000BA288 ? 00002DE7D ? C00000000000224C ? 001121800 ? opiodr()+2144 CALL opipls() 9FFFFFFFFFFEF680 ? 4000000002F58A60 ? 00002E0B7 ? 9FFFFFFFFFFEEDD0 ? 60000000000BA288 ? 000000001 ? rpidrus()+368 CALL opiodr() 000000066 ? 000000006 ? 4000000001C14510 ? 0000046C0 ? 9FFFFFFFFFFEF690 ? 60000000000BA288 ? skgmstack()+288 CALL rpidrus() 9FFFFFFFFFFF1DD0 ? 9FFFFFFFFFFF1810 ? 60000000000BA288 ? 9FFFFFFFFFFF1D90 ? C000000000000716 ? 4000000002F92F80 ? 00002E07F ? 9FFFFFFFFFFF1870 ? rpidru()+240 CALL skgmstack() 9FFFFFFFFFFF1DF0 ? 6000000000030F50 ? 00000F618 ? 4000000001CCBAD0 ? 9FFFFFFFFFFF1DD0 ? rpiswu2()+976 CALL rpidru() 00000057B ? C000000000001026 ? C000000000001026 ? 4000000002F8FD10 ? 9FFFFFFFFFFF1DE8 ? 9FFFFFFFFFFF2C10 ? rpidrv()+2352 CALL rpiswu2() 9FFFFFFFFFFF2B00 ? 4000000002F92360 ? 00002F933 ? 9FFFFFFFFFFF2590 ? 60000000000329A8 ? C000000000001ABD ? 4000000000F32248 ? 60000000000C6CE0 ? psddr0()+464 CALL rpidrv() 000000008 ? 000000066 ? 9FFFFFFFFFFF3710 ? 000000038 ? 9FFFFFFFFFFF2B30 ? 60000000000BA288 ? psdnal()+736 CALL psddr0() 000000008 ? 000000066 ? 9FFFFFFFFFFF3710 ? 000000030 ? pevm_EXECC()+832 CALL psdnal() 9FFFFFFFFFFF54E0 ? C0000014FF9104E0 ? C0000000000011AA ? 9FFFFFFFBF3D3060 ? C00000142951A9F8 ? 4000000003159470 ? 00002C835 ? pfrinstr_EXECC()+16 CALL pevm_EXECC() 9FFFFFFFBF3D9F40 ? 0 9FFFFFFFBF3D30D0 ? 000000020 ? pfrrun_no_tool()+19 CALL pfrinstr_EXECC() 9FFFFFFFBF3D9F40 ? 2 C0000013A9EE034C ? 9FFFFFFFBF3D9FA8 ? pfrrun()+1376 CALL pfrrun_no_tool() 9FFFFFFFBF3D9F40 ? 000002001 ? 9FFFFFFFBF3D9FA8 ? 60000000000BA288 ? C000000000000A1C ? 4000000003180540 ? 9FFFFFFFBF3DA390 ? 9FFFFFFFBF3DA008 ? plsql_run()+1328 CALL pfrrun() 9FFFFFFFFFFF3798 ? 9FFFFFFFFFFF3780 ? 60000000000BA288 ? 9FFFFFFFFFFF4380 ? 9FFFFFFFFFFF4380 ? C000000000000E23 ? 4000000002C52D50 ? |
根据MOS文档，ORA-600[ktrgcm_3]存在三个已知bug，其中和Bug 7225204 – OERI [ktrgcm_3] can occur [ID 7225204.8]的描述非常相似，当前报错的SQL语句就是INSERT SELECT语句，且INSERT对应的表在SELECT中也会出现，这与BUG的描述完全一致。
观察错误语句的执行计划：
============ Plan TABLE ============ ---------------------------------------------------------------------+---------------------- | Id | Operation | Name | ROWS | Bytes | Cost | ---------------------------------------------------------------------+---------------------- | 0 | INSERT STATEMENT | | | | 618K | | 1 | FILTER | | | | | | 2 | NESTED LOOPS SEMI | | 1 | 345 | 618K | | 3 | HASH JOIN OUTER | | 1 | 326 | 618K | | 4 | NESTED LOOPS OUTER | | 1 | 301 | 618K | | 5 | HASH JOIN OUTER | | 1 | 282 | 618K | | 6 | HASH JOIN SEMI | | 1 | 263 | 618K | | 7 | PARTITION RANGE SINGLE | | 16 | 4000 | 4 | | 8 | TABLE ACCESS BY LOCAL INDEX ROWID| TF_BH_TRADE | 16 | 4000 | 4 | | 9 | INDEX RANGE SCAN | IDX_TF_BH_TRADE_ACC| 1 | | 3 | | 10 | VIEW | | 13K | 169K | 618K | | 11 | MINUS | | | | | | 12 | SORT UNIQUE | | 13K | 534K | | | 13 | FILTER | | | | | | 14 | HASH JOIN | | 13K | 534K | 617K | | 15 | TABLE ACCESS FULL | TF_B_TRADE_BATDEAL | 13K | 377K | 441K | | 16 | PARTITION RANGE ALL | | 172M | 2063M | 174K | | 17 | INDEX FAST FULL SCAN | PK_TF_BH_TRADE | 172M | 2063M | 174K | | 18 | SORT UNIQUE | | 260K | 2599K | | | 19 | INDEX FAST FULL SCAN | IDX_T_B_T_ID | 260K | 2599K | 231 | | 20 | TABLE ACCESS FULL | TF_B_T_STATE_TRANS | 1 | 19 | 2 | | 21 | TABLE ACCESS BY INDEX ROWID | TD_B_PRODUCT | 1 | 19 | 2 | | 22 | INDEX UNIQUE SCAN | PK_TD_B_PRODUCT | 1 | | 1 | | 23 | TABLE ACCESS FULL | TF_B_TRADE_REL | 116K | 2888K | 169 | | 24 | TABLE ACCESS BY INDEX ROWID | TD_SD_COMMPARA | 8 | 152 | 2 | | 25 | INDEX RANGE SCAN | PK_TD_SD_COMMPARA | 8 | | 1 | ---------------------------------------------------------------------+---------------------- |
配合报错函数信息，不难确认，问题发生在对T_B_T_IN表的索引快速全扫描上。
但是这个错误影响10.2.0.4和11.1.0.7，Oracle在10.2.0.5中应该已经修正了这个错误。观察文档Bug 6157713 – Wrong result from ORA_ROWSCN [ID 6157713.8]的描述，虽然版本信息符合，但是当前并没有调用ORA_ROWSCN伪列，不过这个BUG的描述中，又指向了上面7225204的bug。
最后检查文档Bug 14076510 – ORA-600 [ktrgcm_3] in 10.2.0.5.3 – 10.2.0.5.7 [ID 14076510.8]，可以看到这个bug描述在10.2.0.5.3之后出现的ktrgcm_3错误的原因，根据三篇文章的描述，以及当前的现象，感觉三篇文档描述的应该是都一个bug，Oracle在处理自关联查询时引发了这个错误。而这个bug并没有在10.2.0.5中被真正修复。
除了升级到10.2.0.5.8以及11.2外，如果可能，修改语句避免INSERT SELECT语句访问插入表。此外Oracle还提供了设置隐含参数”_row_cr”为FALSE的方式，这种方式会带来性能的影响，如果这个错误频繁发生，也可以考虑进行设置。