一套Linux x86-64上的11.2.0.1系统，alert日志中出现ORA-00600: internal error code, arguments: [kdsgrp1]错误，相关trace的部分内容如下：
Dump file /u01/app/oracle/diag/rdbms/utdw016/utdw016b/incident/incdir_276035/utdw016b_ora_5756_i276035.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/112utdw016 System name: Linux Node name: x42k601 Release: 2.6.18-164.el5 Version: #1 SMP Tue Aug 18 15:51:48 EDT 2009 Machine: x86_64 Instance name: utdw016b Redo thread mounted by this instance: 2 Oracle process number: 52 Unix process pid: 5756, image: oracle@x42k601 *** 2010-07-28 11:08:35.394 *** SESSION ID:(577.11818) 2010-07-28 11:08:35.394 *** CLIENT ID:() 2010-07-28 11:08:35.394 *** SERVICE NAME:(utdw016-edw) 2010-07-28 11:08:35.394 *** MODULE NAME:(pmdtm@x42k604-zone1 (TNS V1-V3)) 2010-07-28 11:08:35.394 *** ACTION NAME:() 2010-07-28 11:08:35.394 Dump continued from file: /u01/app/oracle/diag/rdbms/utdw016/utdw016b/trace/utdw016b_ora_5756.trc ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], [] ========= Dump for incident 276035 (ORA 600 [kdsgrp1]) ======== *** 2010-07-28 11:08:35.395 dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0) ----- Current SQL Statement for this session (sql_id=9hfdcgzzgmgvp) ----- DELETE FROM EIMABS.FACT_OPEN_SALES_ORDER WHERE DIM_SNAPSHOT_TYPE_ID = (SELECT DIM_SNAPSHOT_TYPE_ID FROM EIMABS.DIM_SNAPSHOT_TYPE WHERE SNAPSHOT_TYPE_CODE='FUTURE') ----- PL/SQL Stack ----- ----- PL/SQL Call Stack ----- object line object handle number name 0x5578e0528 6 anonymous block ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- skdstdst()+36 call kgdsdst() 000000000 ? 000000000 ? 7FFFA85D7418 ? 000000001 ? 7FFFA85DB918 ? 000000000 ? ksedst1()+98 call skdstdst() 000000000 ? 000000000 ? 7FFFA85D7418 ? 000000001 ? 000000000 ? 000000000 ? ksedst()+34 call ksedst1() 000000000 ? 000000001 ? 7FFFA85D7418 ? 000000001 ? 000000000 ? 000000000 ? dbkedDefDump()+2736 call ksedst() 000000000 ? 000000001 ? 7FFFA85D7418 ? 000000001 ? 000000000 ? 000000000 ? ksedmp()+36 call dbkedDefDump() 000000003 ? 000000002 ? 7FFFA85D7418 ? 000000001 ? 000000000 ? 000000000 ? ksfdmp()+64 call ksedmp() 000000003 ? 000000002 ? 7FFFA85D7418 ? 000000001 ? 000000000 ? 000000000 ? dbgexPhaseII()+1764 call ksfdmp() 000000003 ? 000000002 ? 7FFFA85D7418 ? 000000001 ? 000000000 ? 000000000 ? dbgexProcessError() call dbgexPhaseII() 2B30E1B74718 ? 2B30E1B795F8 ? +2279 7FFFA85E3708 ? 000000001 ? 000000000 ? 000000000 ? dbgeExecuteForError call dbgexProcessError() 2B30E1B74718 ? 2B30E1B795F8 ? ()+83 000000001 ? 000000000 ? 7FFF00000000 ? 000000000 ? dbgePostErrorKGE()+ call dbgeExecuteForError 2B30E1B74718 ? 2B30E1B795F8 ? 1615 () 000000001 ? 000000001 ? 000000000 ? 000000000 ? dbkePostKGE_kgsf()+ call dbgePostErrorKGE() 000000000 ? 2B30E1B87F38 ? 63 000000258 ? 2B30E1B795F8 ? 000000000 ? 000000000 ? kgeadse()+383 call dbkePostKGE_kgsf() 00A9B2EE0 ? 2B30E1B87F38 ? 000000258 ? 2B30E1B795F8 ? 000000000 ? 000000000 ? kgerinv_internal()+ call kgeadse() 00A9B2EE0 ? 2B30E1B87F38 ? 45 000000258 ? 000000000 ? 000000000 ? 000000000 ? kgerinv()+33 call kgerinv_internal() 00A9B2EE0 ? 2B30E1B87F38 ? A85DBCA000000000 ? 000000258 ? 000000000 ? 000000000 ? kgeasnmierr()+143 call kgerinv() 00A9B2EE0 ? 2B30E1B87F38 ? A85DBCA000000000 ? 000000000 ? 000000000 ? 000000000 ? kdsgrp1_dump()+832 call kgeasnmierr() 00A9B2EE0 ? 2B30E1B87F38 ? A85DBCA000000000 ? 000000000 ? 000000003 ? 000000003 ? kdsgrp1()+19 call kdsgrp1_dump() 00A9B2EE0 ? 2B30E1B87F38 ? 7FFFA85E4360 ? 000000000 ? 000000003 ? 000000003 ? kdsgrp()+6346 call kdsgrp1() 00A9B2EE0 ? 2B30E1B87F38 ? 7FFFA85E4360 ? 000000000 ? 000000003 ? 000000003 ? qetlbr()+211 call kdsgrp() 2B30E2018620 ? 000000000 ? 2B30E2018620 ? 000000000 ? 000000003 ? 000000003 ? qertbFetchByRowID() call qetlbr() 2B30E2018620 ? 00000001A ? +850 000000000 ? 0000000C8 ? 00400F521 ? 000000000 ? qergiFetch()+276 call qertbFetchByRowID() 000000000 ? 2B30E2018518 ? 000000000 ? 000000000 ? 000000001 ? 000000000 ? qerdlFetch()+678 call qergiFetch() 511B78238 ? 2B30E20187F8 ? 000000000 ? 000000000 ? 000000001 ? 000000000 ? delexe()+952 call qerdlFetch() 511B78090 ? 2B30E2018900 ? 000000000 ? 000000000 ? 000007FFF ? 000000000 ? opiexe()+14449 call delexe() 000000000 ? 000000000 ? 000000000 ? 2B30E27E1E08 ? 2B30E1FCA000 ? 000000000 ? opipls()+3098 call opiexe() 000000004 ? 000000005 ? 7FFFA85E6708 ? 2B30E27E1E08 ? 2B30E1FCA000 ? 000000000 ? opiodr()+1149 call opipls() 000000066 ? 000000007 ? 7FFFA85E7F90 ? 000000000 ? 2B30E2701C28 ? 300000000 ? __PGOSF141_rpidrus( call opiodr() 000000066 ? 000000007 ? )+206 7FFFA85E7F90 ? 000000003 ? 008C71EF0 ? 300000000 ? skgmstack()+148 call __PGOSF141_rpidrus( 7FFFA85E79A0 ? 000000007 ? ) 7FFFA85E7F90 ? 000000003 ? 008C71EF0 ? 300000000 ? rpiswu2()+612 call skgmstack() 7FFFA85E7978 ? 00A9B2B20 ? 00000F618 ? 008468D40 ? 7FFFA85E79A0 ? 300000000 ? rpidrv()+1342 call rpiswu2() 55CE5B2F8 ? 000000078 ? 55CE5B37C ? 000000009 ? 2B30E1FEEF98 ? 000000078 ? psddr0()+459 call rpidrv() 000000003 ? 000000066 ? 7FFFA85E7F90 ? 000000039 ? 2B30E1FEEF98 ? 000000078 ? psdnal()+457 call psddr0() 000000003 ? 000000066 ? 7FFFA85E7F90 ? 000000031 ? 2B30E1FEEF98 ? 000000078 ? pevm_EXECC()+376 call psdnal() 7FFFA85E9370 ? 7FFFA85E9580 ? 7FFFA85E7F90 ? 2B30E2742370 ? 2B30E1FEEF98 ? 000000078 ? pfrinstr_EXECC()+75 call pevm_EXECC() 2B30E273F0A0 ? 7FFFA85E9580 ? 000000020 ? 2B30E2742370 ? 2B30E1FEEF98 ? 000000078 ? pfrrun_no_tool()+63 call pfrinstr_EXECC() 2B30E273F0A0 ? 51148B482 ? 2B30E273F110 ? 2B30E2742370 ? 2B30E1FEEF98 ? 2B3000000020 ? pfrrun()+1025 call pfrrun_no_tool() 2B30E273F0A0 ? 51148B482 ? 2B30E273F110 ? 2B30E2742370 ? 2B30E1FEEF98 ? 2B3000000020 ? plsql_run()+769 call pfrrun() 2B30E273F0A0 ? 000000000 ? 2B30E273F110 ? 7FFFA85E9370 ? 2B30E1FEEF98 ? 503AC23E2 ? peicnt()+296 call plsql_run() 2B30E273F0A0 ? 000000001 ? 000000000 ? 7FFFA85E9370 ? 2B30E1FEEF98 ? 000000000 ? kkxexe()+520 call peicnt() 7FFFA85E9370 ? 2B30E273F0A0 ? 2B30E2418960 ? 7FFFA85E9370 ? 2B30E2486008 ? 000000000 ? opiexe()+14796 call kkxexe() 2B30E2013538 ? 2B30E273F0A0 ? 000000000 ? 7FFFA85E9370 ? 2B30E2486008 ? 000000000 ? kpoal8()+2283 call opiexe() 000000049 ? 000000003 ? 7FFFA85EA8F8 ? 7FFFA85E9370 ? 2B30E2486008 ? 000000000 ? opiodr()+1149 call kpoal8() 00000005E ? 00000001C ? 7FFFA85ED9D0 ? 7FFFA85E9370 ? 2B30E2486008 ? 5E00000001 ? ttcpip()+1251 call opiodr() 00000005E ? 00000001C ? 7FFFA85ED9D0 ? 000000000 ? 008C71DB0 ? 5E00000001 ? opitsk()+1628 call ttcpip() 00A9C6450 ? 0086CCB08 ? 7FFFA85ED9D0 ? 000000000 ? 7FFFA85ED430 ? 7FFFA85EDBD4 ? opiino()+953 call opitsk() 00A9C6450 ? 000000000 ? 7FFFA85ED9D0 ? 000000000 ? 7FFFA85ED430 ? 7FFFA85EDBD4 ? opiodr()+1149 call opiino() 00000003C ? 000000004 ? 7FFFA85EF0C8 ? 000000000 ? 7FFFA85ED430 ? 7FFFA85EDBD4 ? opidrv()+565 call opiodr() 00000003C ? 000000004 ? 7FFFA85EF0C8 ? 000000000 ? 008C71860 ? 7FFFA85EDBD4 ? sou2o()+98 call opidrv() 00000003C ? 000000004 ? 7FFFA85EF0C8 ? 000000000 ? 008C71860 ? 7FFFA85EDBD4 ? opimai_real()+128 call sou2o() 7FFFA85EF0A0 ? 00000003C ? 000000004 ? 7FFFA85EF0C8 ? 008C71860 ? 7FFFA85EDBD4 ? ssthrdmain()+209 call opimai_real() 000000002 ? 7FFFA85EF290 ? 000000004 ? 7FFFA85EF0C8 ? 008C71860 ? 7FFFA85EDBD4 ? main()+196 call ssthrdmain() 000000002 ? 7FFFA85EF290 ? 000000001 ? 000000000 ? 008C71860 ? 7FFFA85EDBD4 ? __libc_start_main() call main() 000000002 ? 7FFFA85EF438 ? +244 000000001 ? 000000000 ? 008C71860 ? 7FFFA85EDBD4 ? _start()+36 call __libc_start_main() 0009D3E88 ? 000000002 ? 7FFFA85EF428 ? 000000000 ? 008C71860 ? 000000002 ?
错误由此删除语句引起:
DELETE FROM EIMABS.FACT_OPEN_SALES_ORDER WHERE DIM_SNAPSHOT_TYPE_ID = (SELECT DIM_SNAPSHOT_TYPE_ID FROM EIMABS.DIM_SNAPSHOT_TYPE WHERE SNAPSHOT_TYPE_CODE='FUTURE')
其中EIMABS.DIM_SNAPSHOT_TYPE是一个普通堆表，而EIMABS.FACT_OPEN_SALES_ORDER是一个分区表，针对该分区表的各个分区进行了validate structure分析:
SQL> @?/rdbms/admin/utlvalid Table created. SQL> set time on; 23:32:51 SQL> analyze table EIMABS.DIM_SNAPSHOT_TYPE validate structure cascade; Table analyzed. 00:30:46 SQL> analyze table EIMABS.FACT_OPEN_SALES_ORDER partition (P_2455405) validate structure cascade into invalid_rows; analyze table EIMABS.FACT_OPEN_SALES_ORDER partition (P_2455405) validate structure cascade into invalid_rows * ERROR at line 1: ORA-01499: table/index cross reference failure - see trace file
推测可能是P_2455405分区相关的索引可能出现了讹误，尝试重建该表上的15个索引，再次测试相关应用发现问题仍未解决。此次产生的trace文件:
Dump file /u01/app/oracle/diag/rdbms/utdw016/utdw016a/incident/incdir_310187/utdw016a_ora_26473_i310187.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/112utdw016
System name: Linux
Node name: x42k600
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine: x86_64
Instance name: utdw016a
Redo thread mounted by this instance: 1
Oracle process number: 62
Unix process pid: 26473, image: oracle@x42k600
*** 2010-07-30 04:10:19.931
*** SESSION ID:(1993.31898) 2010-07-30 04:10:19.931
*** CLIENT ID:() 2010-07-30 04:10:19.931
*** SERVICE NAME:(utdw016-edw) 2010-07-30 04:10:19.931
*** MODULE NAME:(pmdtm@x42k604-zone2 (TNS V1-V3)) 2010-07-30 04:10:19.931
*** ACTION NAME:() 2010-07-30 04:10:19.931
Dump continued from file: /u01/app/oracle/diag/rdbms/utdw016/utdw016a/trace/utdw016a_ora_26473.trc
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
========= Dump for incident 310187 (ORA 600 [kdsgrp1]) ========
*** 2010-07-30 04:10:19.932
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=9hfdcgzzgmgvp) -----
DELETE FROM EIMABS.FACT_OPEN_SALES_ORDER WHERE DIM_SNAPSHOT_TYPE_ID = (SELECT DIM_SNAPSHOT_TYPE_ID FROM EIMABS.DIM_SNAPSHOT_TYPE WHERE SNAPSHOT_TYPE_CODE='FUTURE')
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
SO: 0x5334781d0, type: 52, owner: 0x55d2646b8, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x55caf0778, name=transaction, file=ktccts.h LINE:388, pg=0
(trans) flg = 0x00001e03, flg2 = 0x000c0000, flg3 = 0x00000000, prx = (nil), ros = 2147483647
flg = 0x00001e03: ALC TRN VUS VID CHG USN
flg2 = 0x000c0000: PGA NIP
flg3 = 0x00000000:
bsn = 0x8419 bndsn = 0x841b spn = 0x85c7
efd = 15 DID:
file:kta.c lineno:1665
parent xid: 0x0000.000.00000000
env: (scn: 0x0a19.a0b7c9d4 xid: 0x0021.017.00028415 uba: 0x00e00545.0438.12 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0a19.a0b7c9d9 0sch: scn: 0x0000.00000000 mascn: (scn: 0x0000.00000000)
cev: (spc = 11546 arsp = 0x5335450a8 ubkds (ubk:tsn: 2 rdba: 0x00e00545 flag:0x8 hdl:(nil) addr:0x35c50c014) useg tsn: 2 rdba: 0x00e2c990
hwm uba: 0x00e00545.0438.12 col uba: 0x00000000.0000.00
num bl: 3 bk list: 0x53db19518)
cr opc: 0x0 spc: 11546 uba: 0x00e00545.0438.12
ccbstg: 0x00000000
(enqueue) TX-00210017-00028415 DID: 0001-003E-00000C70
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x47
mode: X, lock_flag: 0x0, lock: 0x533478248, res: 0x5414c2da0
own: 0x55d2646b8, sess: 0x8b37718, proc: 0x55caf0778, prv: 0x5414c2db0
slk: (nil)
xga: (nil), heap: UGA
Trans IMU st: 0 Pool index 65535, Redo pool 0x533478980, Undo pool 0x533478a68
Redo pool range [0x2b13aacccb90 0x2b13aacccb90 0x2b13aacd1390]
Undo pool range [0x2b13aacc8390 0x2b13aacc8390 0x2b13aacccb90]
chnf control flags 0x0 CHNF hwm uba uba: 0x00000000.0000.00 ----------------------------------------
SO: 0x53db19518, type: 51, owner: 0x5334781d0, flag: -/-/-/0x00 if: 0x3 c: 0x3
SO: 0x55d2646b8, type: 4, owner: 0x55caf0778, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x55caf0778, name=session, file=ksu.h LINE:11467, pg=0
(session) sid: 1993 ser: 31898 trans: 0x5334781d0, creator: 0x55caf0778
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
DID: , short-term DID:
txn branch: (nil)
oct: 7, prv: 0, sql: 0x5534eae70, psql: 0x55a4ff230, user: 120/EIMABS
ksuxds FALSE at location: 0
service name: utdw016-edw
client details:
O/S info: user: infom, term: , ospid: 21955
machine: x42k604-zone2 program: pmdtm@x42k604-zone2 (TNS V1-V3)
application name: pmdtm@x42k604-zone2 (TNS V1-V3), hash value=4200765728
Current Wait Stack:
Not in wait; last wait ended 1.028093 sec ago
Wait State:
fixed_waits=0 flags=0x21 boundary=(nil)/-1
Session Wait History:
elapsed time of 1.028173 sec since last wait
0: waited for 'Disk file operations I/O'
FileOperation=0x5, fileno=0x1, filetype=0x3
wait_id=4666 seq_num=4860 snap_id=2
wait times: snap=0.000039 sec, exc=0.000181 sec, total=0.000687 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000000 sec of elapsed time
1: waited for 'ASM file metadata operation'
msgop=0x12, locn=0x0, =0x0
wait_id=4667 seq_num=4859 snap_id=2
wait times: snap=0.000002 sec, exc=0.000005 sec, total=0.000506 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000000 sec of elapsed time
2: waited for 'KSV master wait'
=0x0, =0x0, =0x0
wait_id=4668 seq_num=4858 snap_id=1
wait times: snap=0.000501 sec, exc=0.000501 sec, total=0.000501 sec
wait times: max=infinite
wait counts: calls=1 os=1
occurred after 0.000000 sec of elapsed time
3: waited for 'ASM file metadata operation'
msgop=0x12, locn=0x0, =0x0
wait_id=4667 seq_num=4857 snap_id=1
wait times: snap=0.000003 sec, exc=0.000003 sec, total=0.000003 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000000 sec of elapsed time
4: waited for 'Disk file operations I/O'
FileOperation=0x5, fileno=0x1, filetype=0x3
wait_id=4666 seq_num=4856 snap_id=1
wait times: snap=0.000142 sec, exc=0.000142 sec, total=0.000142 sec
wait times: max=infinite
wait counts: calls=0 os=0
......
Block header dump: 0x0400f521 Data
Block header dump: 0x2317a579 Index
Block header dump: 0x2317a577 " "
Block header dump: 0x048c8c3c " "
Block header dump: 0x2317a1bb " "
Block header dump: 0x048c78ad " "
Block header dump: 0x0400f51f Data
Block header dump: 0x2317a33b Index
Block header dump: 0x048c78f3 " "
Block header dump: 0x07541a44 " "
Block header dump: 0x07577b92 " "
Block header dump: 0x078a4eb7 " "
Block header dump: 0x2317a4b7 " "
Block header dump: 0x040fe621 " "
Block header dump: 0x2317a47b " "
同时应用人员发现此系统中另一SQL语句也会导致ORA-600[kdsgrp1]错误的出现，如下:
SELECT
F.DIM_BIZ_SECTOR_ID AS DIM_BIZ_SECTOR_ID,
F.DW_REVENUE_ID ,
F.DIM_ENT_ITEM_ID AS DIM_ENT_ITEM_ID,
F.DIM_REV_PCH_KEY_ACCT_ID AS DIM_PCH_KEY_ACCOUNT_ID,
CT.DIM_PGH_COUNTRY_GROUP_ID AS DIM_PGH_COUNTRY_GROUP_ID,
F.DIM_REVENUE_DATE_ID AS DIM_FORECAST_DATE_ID,
NEXT_DAY(SYSDATE-4.000000000000000,'FRI') AS SNAPSHOT_DATE,
F.REVENUE_DT AS FORECAST_DATE,
F.REVENUE_QTY AS REVENUE_QTY
FROM
EIMABS.FACT_REVENUE F,
EIMABS.DIM_BIZ_SECTOR B,
EIMABS.DIM_ENT_ITEM E,
EIMABS.DIM_PCH_KEY_ACCOUNT PCH,
EIMABS.DIM_COUNTRY CT,
EIMABS.DIM_FISCAL_CAL_DATE CAL
WHERE
F.DIM_BIZ_SECTOR_ID = B.DIM_BIZ_SECTOR_ID
AND F.DIM_ENT_ITEM_ID = E.DIM_ENT_ITEM_ID
AND F.DIM_REV_PCH_KEY_ACCT_ID = PCH.DIM_PCH_KEY_ACCOUNT_ID
AND F.DIM_ULT_DEST_COUNTRY_ID = CT.DIM_COUNTRY_ID
AND UPPER(B.BIZ_SECTOR_CODE) = 'MDB'
AND F.DIM_REVENUE_DATE_ID = CAL.DIM_CALENDAR_DATE_ID
AND CAL.FISCAL_MONTH_NUM IN
(SELECT FISCAL_MONTH_NUM FROM EIMABS.DIM_FISCAL_CAL_MONTH
WHERE DIM_CALENDAR_MONTH_ID IN (
(SELECT B.DIM_CALENDAR_MONTH_ID -1 FROM EIMABS.DIM_FISCAL_CAL_DATE A , EIMABS.DIM_FISCAL_CAL_MONTH B
WHERE CALENDAR_DT = TRUNC(SYSDATE) AND A.DIM_CALENDAR_MONTH_ID = B.DIM_CALENDAR_MONTH_ID),
(SELECT B.DIM_CALENDAR_MONTH_ID -2 FROM EIMABS.DIM_FISCAL_CAL_DATE A , EIMABS.DIM_FISCAL_CAL_MONTH B
WHERE CALENDAR_DT = TRUNC(SYSDATE) AND A.DIM_CALENDAR_MONTH_ID = B.DIM_CALENDAR_MONTH_ID)));
ERROR at line 12:
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [],
[], [], [], [], []
/*相关trace文件如下*/
Trace file /u01/app/oracle/diag/rdbms/utdw016/utdw016a/trace/utdw016a_ora_26473.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/112utdw016
System name: Linux
Node name: x42k600
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine: x86_64
Instance name: utdw016a
Redo thread mounted by this instance: 1
Oracle process number: 62
Unix process pid: 26473, image: oracle@x42k600
*** 2010-07-30 04:10:18.249
*** SESSION ID:(1993.31898) 2010-07-30 04:10:18.249
*** CLIENT ID:() 2010-07-30 04:10:18.249
*** SERVICE NAME:(utdw016-edw) 2010-07-30 04:10:18.249
*** MODULE NAME:(pmdtm@x42k604-zone2 (TNS V1-V3)) 2010-07-30 04:10:18.249
*** ACTION NAME:() 2010-07-30 04:10:18.249
* kdsgrp1-1: *************************************************
row 0x0400f521.1a continuation at
0x0400f521.1a file# 16 block# 62753 slot 26 not found
KDSTABN_GET: 0 ..... ntab: 1
curSlot: 26 ..... nrows: 28
kdsgrp - dump CR block dba=0x0400f521
Block header dump: 0x0400f521
Object id on Block? Y
seg/obj: 0x11527d csc: 0xa19.61e20b3c itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x400f500 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0040.02f.0001bfcd 0x2822b8ea.0224.34 C-U- 0 scn 0x0a19.61e1aa84
0x02 0x003f.01c.00028920 0x2822f9de.01e0.6b --U- 26 fsc 0x0000.61e21527
bdba: 0x0400f521
data_block_dump,data header at 0x2d63ec064
这个case同时提交了SR，Oracle GCS支持分析日志后认为告警日志中曾经出现kewastUnPackStats记录，说明可能由11.2.0.1上的Bug 8967729 引起的:
I researched the issue further based upon the error that followed the log switch command, Tue Aug 03 10:00:04 2010 ALTER SYSTEM ARCHIVE LOG Tue Aug 03 10:00:05 2010 Thread 1 advanced to log sequence 8652 (LGWR switch) Current log# 4 seq# 8652 mem# 0: +UAT_DATA/utdw016/redo041a.log Current log# 4 seq# 8652 mem# 1: +UAT_ARCH/utdw016/redo042a.log Tue Aug 03 10:00:14 2010 Archived Log entry 16346 added for thread 1 sequence 8651 ID 0x8d616825 dest 1: Tue Aug 03 10:00:21 2010 ALTER SYSTEM ARCHIVE LOG Tue Aug 03 10:00:23 2010 Thread 1 advanced to log sequence 8653 (LGWR switch) Current log# 5 seq# 8653 mem# 0: +UAT_DATA/utdw016/redo051a.log Current log# 5 seq# 8653 mem# 1: +UAT_ARCH/utdw016/redo052a.log Tue Aug 03 10:00:23 2010 Archived Log entry 16348 added for thread 1 sequence 8652 ID 0x8d616825 dest 1: Tue Aug 03 10:00:45 2010 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> kewastUnPackStats(): bad magic 1 (0x2b29e289bf13, 0) We need to resolve this issue and then determine whether these indexes corrupt again within the database. I found Bug 8967729 11.2.0.1 Abstract: KEWASTUNPACKSTATS(): BAD MAGIC MESSAGE IN ALERT.LOG. Basically, there is a archivelog switch issue that is causes string corruption. This issue was introduced by the fix of bug #8715387 and has been fully fixed as part of Bug 8730312 11.2 Abstract: FWD MERGE FOR BASE Bug 8715387 FOR 12G. Please download and install patch 8730312 for your Oracle and OS versions. However, if you have installed patches in this 11.2 database, then upload the results of the Opatch inventory. We will check the installed database patches against patch 8730312 for any contradictions. Hdr: 8967729 11.2.0.1 RDBMS 11.2.0.1 SVRMAN AWR PRODID-5 PORTID-226 8730312 Abstract: KEWASTUNPACKSTATS(): BAD MAGIC MESSAGE IN ALERT.LOG PROBLEM: -------- In a two-node RAC cluster (+ Data Guard), the rac instances write many strange messages in alert log, such: kewastUnPackStats(): bad magic 1 (0x2ac893121390, 0) The hex address changes. DIAGNOSTIC ANALYSIS: -------------------- I don't see any error associated with this message. It looks like Bug 8730312 description, but the base bug 8715387 should be already fixed in 11.2.0.1 version. WORKAROUND: ----------- n/a RELATED BUGS: ------------- 8715387, 8730312 REPRODUCIBILITY: ---------------- TEST CASE: ---------- STACK TRACE: ------------ Tue Sep 22 09:15:34 2009 ALTER SYSTEM ARCHIVE LOG Tue Sep 22 09:15:34 2009 LGWR: Standby redo logfile selected to archive thread 1 sequence 1244 LGWR: Standby redo logfile selected for thread 1 sequence 1244 for destination LOG_ARCHIVE_DEST_2 Thread 1 advanced to log sequence 1244 (LGWR switch) Current log# 2 seq# 1244 mem# 0: +DGCTRL/saneons/onlinelog/group_2.264.697133919 Archived Log entry 3903 added for thread 1 sequence 1243 ID 0x5186bbfd dest 1: Tue Sep 22 10:00:02 2009 kewastUnPackStats(): bad magic 1 (0x2b1d760d3038, 0) kewastUnPackStats(): bad magic 1 (0x2b1d760d3038, 0)
MOS认为应用补丁8730312可以解决我们的问题，但实际apply patch后EIMABS.FACT_OPEN_SALES_ORDER上的DELETE语句可以正常执行了，而之后发现的查询语句仍会报ORA-00600:[kdsgrp1]错误:
We have applied the patch. And we're in process of analyze all the partitions. So far there's no error reported. We analyzed all the partitions in table EIMABS.FACT_REVENUE, there's no error either. However, when we run the query, we still get the same error. Please note the other tables in the query are not partitioned.
SELECT
F.DIM_BIZ_SECTOR_ID AS DIM_BIZ_SECTOR_ID,
F.DW_REVENUE_ID ,
F.DIM_ENT_ITEM_ID AS DIM_ENT_ITEM_ID,
F.DIM_REV_PCH_KEY_ACCT_ID AS DIM_PCH_KEY_ACCOUNT_ID,
CT.DIM_PGH_COUNTRY_GROUP_ID AS DIM_PGH_COUNTRY_GROUP_ID,
F.DIM_REVENUE_DATE_ID AS DIM_FORECAST_DATE_ID,
NEXT_DAY(SYSDATE-4.000000000000000,'FRI') AS SNAPSHOT_DATE,
F.REVENUE_DT AS FORECAST_DATE,
F.REVENUE_QTY AS REVENUE_QTY
FROM
EIMABS.FACT_REVENUE F,
EIMABS.DIM_BIZ_SECTOR B,
EIMABS.DIM_ENT_ITEM E,
EIMABS.DIM_PCH_KEY_ACCOUNT PCH,
EIMABS.DIM_COUNTRY CT,
EIMABS.DIM_FISCAL_CAL_DATE CAL
WHERE
F.DIM_BIZ_SECTOR_ID = B.DIM_BIZ_SECTOR_ID
AND F.DIM_ENT_ITEM_ID = E.DIM_ENT_ITEM_ID
AND F.DIM_REV_PCH_KEY_ACCT_ID = PCH.DIM_PCH_KEY_ACCOUNT_ID
AND F.DIM_ULT_DEST_COUNTRY_ID = CT.DIM_COUNTRY_ID
AND UPPER(B.BIZ_SECTOR_CODE) = 'MDB'
AND F.DIM_REVENUE_DATE_ID = CAL.DIM_CALENDAR_DATE_ID
AND CAL.FISCAL_MONTH_NUM IN
(SELECT FISCAL_MONTH_NUM FROM EIMABS.DIM_FISCAL_CAL_MONTH
WHERE DIM_CALENDAR_MONTH_ID IN (
(SELECT B.DIM_CALENDAR_MONTH_ID -1 FROM EIMABS.DIM_FISCAL_CAL_DATE A , EIMABS.DIM_FISCAL_CAL_MONTH B
WHERE CALENDAR_DT = TRUNC(SYSDATE) AND A.DIM_CALENDAR_MONTH_ID = B.DIM_CALENDAR_MONTH_ID),
(SELECT B.DIM_CALENDAR_MONTH_ID -2 FROM EIMABS.DIM_FISCAL_CAL_DATE A , EIMABS.DIM_FISCAL_CAL_MONTH B
WHERE CALENDAR_DT = TRUNC(SYSDATE) AND A.DIM_CALENDAR_MONTH_ID = B.DIM_CALENDAR_MONTH_ID)));
SELECT
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [],
[], [], [], [], []
/*以下为trace文件*/
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/112utdw016
System name: Linux
Node name: x42k600
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine: x86_64
Instance name: utdw016a
Redo thread mounted by this instance: 1
Oracle process number: 64
Unix process pid: 7170, image: oracle@x42k600 (TNS V1-V3)
*** 2010-08-05 19:10:19.776
*** SESSION ID:(10.4634) 2010-08-05 19:10:19.776
*** CLIENT ID:() 2010-08-05 19:10:19.776
*** SERVICE NAME:(SYS$USERS) 2010-08-05 19:10:19.776
*** MODULE NAME:(sqlplus@x42k600 (TNS V1-V3)) 2010-08-05 19:10:19.776
*** ACTION NAME:() 2010-08-05 19:10:19.776
* kdsgrp1-1:
*************************************************
row 0x04608b75.40 continuation at
0x04608b75.40 file# 17 block# 2132853 slot 64 not found
KDSTABN_GET: 0 ..... ntab: 1
curSlot: 64 ..... nrows: 130
kdsgrp - dump CR block dba=0x04608b75
Block header dump: 0x04608b75
Object id on Block? Y
seg/obj: 0x1c8071 csc: 0xa19.622a6023 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x4608b40 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000f.040.0001267d 0x01067067.022e.1e --U- 64 fsc 0x0000.622a621d
0x02 0x0029.026.0001be36 0x282115c8.0223.0a C--- 0 scn 0x0a19.62297970 <---------- this TX shows in the index too
bdba: 0x04608b75
data_block_dump,data header at 0x47c16c064
===============
tsiz: 0x3f98
hsiz: 0x116
pbl: 0x47c16c064
76543210
flag=--------
ntab=1
nrow=130
frre=64
..
..
tab 0, row 63, @0x3a65
tl: 110 fb: --H-FL-- lb: 0x1 cc: 22
col 0: [ 5] c4 0e 31 32 04
col 1: [ 2] c1 02
col 2: [ 5] c4 02 44 33 44
col 3: [ 5] c4 03 14 10 23
col 4: [ 5] c4 09 5f 35 38
col 5: [ 5] c4 05 32 42 0b
col 6: [ 2] c2 03
col 7: [ 5] c4 03 2e 36 49
col 8: [ 1] 80
col 9: [ 1] 80
col 10: [ 7] 32 30 34 31 39 32 35
col 11: [ 1] 4e
col 12: [ 7] 78 6e 06 18 01 01 01
col 13: [ 2] c1 06
col 14: [ 3] c2 4d 33
col 15: [ 7] 78 6e 07 1b 0a 0d 04
col 16: [ 4] c3 05 2f 25
col 17: [ 2] c1 09
col 18: [ 3] c2 02 50
col 19: [ 3] c2 02 50
col 20: [ 2] c1 0e
col 21: [ 8] 38 30 30 31 31 38 35 38
end_of_block_dump
Block dump from cache:
Dump of buffer cache at level 4 for tsn=13, rdba=73436021
BH (0x47fd84178) file#: 17 rdba: 0x04608b75 (17/2132853) class: 1 ba: 0x47c16c000
set: 47 pool 3 bsz: 16384 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 0 obj: 1867889 objn: 1130693 tsn: 13 afn: 17 hint: f
hash: [0x54be43860,0x54be43860] lru: [0x49beca1a0,0x3b7e094c0]
ckptq: [NULL] fileq: [NULL] objq: [0x52a0e8580,0x52a0e8580]
use: [0x54271be40,0x54271be40] wait: [NULL]
st: SCURRENT md: SHR tch: 0 le: 0x3effd22c8
flags:
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 13 rdba: 0x04608b75 (17/2132853)
scn: 0x0a19.622a621d seq: 0x01 flg: 0x06 tail: 0x621d0601
frmt: 0x02 chkval: 0xdf67 type: 0x06=trans data
Block header dump: 0x04608b75
Object id on Block? Y
seg/obj: 0x1c8071 csc: 0xa19.622a6023 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x4608b40 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000f.040.0001267d 0x01067067.022e.1e --U- 64 fsc 0x0000.622a621d
0x02 0x0029.026.0001be36 0x282115c8.0223.0a C--- 0 scn 0x0a19.62297970
bdba: 0x04608b75
Incident 322216 created, dump file: /u01/app/oracle/diag/rdbms/utdw016/utdw016a/incident/incdir_322216/utdw016a_ora_7170_i322216.trc
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
utdw016a_ora_7170_i322218.trc
-------------------------------
----- Current SQL Statement for this session (sql_id=88kca6rwpm9pc) -----
SELECT
F.DIM_BIZ_SECTOR_ID AS DIM_BIZ_SECTOR_ID,
F.DW_REVENUE_ID ,
F.DIM_ENT_ITEM_ID AS DIM_ENT_ITEM_ID,
F.DIM_REV_PCH_KEY_ACCT_ID AS DIM_PCH_KEY_ACCOUNT_ID,
CT.DIM_PGH_COUNTRY_GROUP_ID AS DIM_PGH_COUNTRY_GROUP_ID,
F.DIM_REVENUE_DATE_ID AS DIM_FORECAST_DATE_ID,
NEXT_DAY(SYSDATE-4.000000000000000,'FRI') AS SNAPSHOT_DATE,
F.REVENUE_DT AS FORECAST_DATE,
F.REVENUE_QTY AS REVENUE_QTY
FROM
EIMABS.FACT_REVENUE F,
EIMABS.DIM_BIZ_SECTOR B,
EIMABS.DIM_ENT_ITEM E,
EIMABS.DIM_PCH_KEY_ACCOUNT PCH,
EIMABS.DIM_COUNTRY CT,
EIMABS.DIM_FISCAL_CAL_DATE CAL
WHERE
F.DIM_BIZ_SECTOR_ID = B.DIM_BIZ_SECTOR_ID
AND F.DIM_ENT_ITEM_ID = E.DIM_ENT_ITEM_ID
AND F.DIM_REV_PCH_KEY_ACCT_ID = PCH.DIM_PCH_KEY_ACCOUNT_ID
AND F.DIM_ULT_DEST_COUNTRY_ID = CT.DIM_COUNTRY_ID
AND UPPER(B.BIZ_SECTOR_CODE) = 'MDB'
AND F.DIM_REVENUE_DATE_ID = CAL.DIM_CALENDAR_DATE_ID
AND CAL.FISCAL_MONTH_NUM IN
(SELECT FISCAL_MONTH_NUM FROM EIMABS.DIM_FISCAL_CAL_MONTH
WHERE DIM_CALENDAR_MONTH_ID IN (
(SELECT B.DIM_CALENDAR_MONTH_ID -1 FROM EIMABS.DIM_FISCAL_CAL_DATE A , EIMABS.DIM_FISCAL_CAL_MONTH B
WHERE CALENDAR_DT = TRUNC(SYSDATE) AND A.DIM_CALENDAR_MONTH_ID = B.DIM_CALENDAR_MONTH_ID),
(SELECT B.DIM_CALENDAR_MONTH_ID -2 FROM EIMABS.DIM_FISCAL_CAL_DATE A , EIMABS.DIM_FISCAL_CAL_MONTH B
WHERE CALENDAR_DT = TRUNC(SYSDATE) AND A.DIM_CALENDAR_MONTH_ID = B.DIM_CALENDAR_MONTH_ID)))
----- Call Stack Trace -----
kdsgrp1 kdsgrp qetlbr qertbFetchByRowID qerjotRowProc qerbtFetch wProc qergiFetch
BH (0x47fd84178) file#: 17 rdba: 0x04608b75 (17/2132853) class: 1 ba: 0x47c16c000
set: 47 pool 3 bsz: 16384 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 0 obj: 1867889 objn: 1130693 tsn: 13 afn: 17 hint: f
addr: 0x47fd84178 obj: 1867889 cls: DATA bscn: 0xa19.622a621d
buffer tsn: 13 rdba: 0x04608b75 (17/2132853)
scn: 0x0a19.622a621d seq: 0x01 flg: 0x06 tail: 0x621d0601
frmt: 0x02 chkval: 0xdf67 type: 0x06=trans data
Block header dump: 0x04608b75
Object id on Block? Y
seg/obj: 0x1c8071 csc: 0xa19.622a6023 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x4608b40 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000f.040.0001267d 0x01067067.022e.1e --U- 64 fsc 0x0000.622a621d
0x02 0x0029.026.0001be36 0x282115c8.0223.0a C--- 0 scn 0x0a19.62297970
bdba: 0x04608b75
data_block_dump,data header at 0x47c16c064
===============
tsiz: 0x3f98
hsiz: 0x116
pbl: 0x47c16c064
76543210
flag=--------
ntab=1
nrow=130
frre=64
BH (0x49fe84af8) file#: 29 rdba: 0x076e5058 (29/3035224) class: 1 ba: 0x49ee0c000
set: 44 pool 3 bsz: 16384 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 1 obj: 1893758 objn: 1893758 tsn: 17 afn: 29 hint: f
addr: 0x49fe84af8 obj: 1893758 cls: DATA bscn: 0xa19.62297970
buffer tsn: 17 rdba: 0x076e5058 (29/3035224)
scn: 0x0a19.62297970 seq: 0x01 flg: 0x06 tail: 0x79700601
frmt: 0x02 chkval: 0xe878 type: 0x06=trans data
Block header dump: 0x076e5058
Object id on Block? Y
seg/obj: 0x1ce57e csc: 0xa19.6229787e itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x76e5054 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0029.026.0001be36 0x282115de.0223.2a --U- 14 fsc 0x0000.62297970 <--------------- same TX as in the table
之后MOS又有回复，认为设置隐式参数_row_cr=FALSE可以解决问题:
There are few bugs that could raise the ora-600[KDSGRP1] in 11.2
1) based on the stack --> Bug:8771916 ORA-600 [KDSGRP1] WHEN DOING AN UPDATE
Abstract: OERI [kdsgrp1] during CR read
Fixed-Releases: C100
Tags: OERI
Details:
ORA-600 [kdsgrp1] can occur intermittently when using
a query access path to a row in a table via an index
due to a consistent read problem.
Workaround:
Disabling rowCR (which is an optimization to reduce consistent-read
rollbacks during queries) by setting "_row_cr"=FALSE in the
initialization files in one workaround. However, this could cause
performance degradation of queries - the statistics "RowCR hits" /
"RowCR attempts" can help show if this workaround may be detrimental
to performance.
2) based on the fact it is hit on partitioned tables
Bug 8546356 ORA-8102/ORA-1499/OERI[kdsgrp1] Composite Partitioned Index corruption after rebuild ONLINE
Details:
When a COMPOSITE PARTITIONED index is rebuild ONLINE and concurrent DML on
the same table is run on separate RAC instances, index corruption might
happen where the index is missing some keys. This is only pertaining to
composite partitioned indexes.
Wrong results or the next errors can be produced by SQL statements:
ORA-8102 by a delete/update
ORA-1499 by "analyze table validate structure cascade"
ORA-600 [kdsgrp1]
ORA-600 [qertbFetchByRowID]
Workaround:
Do not run concurrent DML from any instance other than the one where index
online rebuild is happening or rebuild the index without the ONLINE clause.
To fix it: Rebuild the index.
Please set in init.ora this parameter _row_cr=FALSE and bounce database,
see after that if OERI[kdsgrp1] still reproduces.
There is one-off patch available for Bug 8771916 for 11.2.0.1 on LInux x86_64
and for Bug 8546356 the one-off patch is done for 11.2.0.1.2
There is also Bug:8951812 hit a lot in 11.2.0.1, one-off patch available
An index can become corrupt during index rebuild online producing
ORA-600 errors or other external like ORA-8102 by an update/delete,
ORA-1499 by "analyze table validate structure cascade".
这个case仍在继续，to be continued .....
