ORA-00600: internal error code, arguments: [kdsgrp1] example

一套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 .....

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Comments

  1. admin says:

    Hdr: 9905071 11.2.0.1 RDBMS 11.2.0.1 SVRMAN AWR PRODID-5 PORTID-212
    Abstract: KEWASTUNPACKSTATS(): BAD MAGIC 1 EVEN AFTER PATCH 8730312

    BUG TYPE CHOSEN
    ===============
    Code

    SubComponent: Performance Tools
    ===============================
    DETAILED PROBLEM DESCRIPTION
    ============================
    Have identified that customer has encountered with BUG 8730312 with errors
    “kewastUnPackStats(): bad magic 1 (0x110a1315b, 0)” written to the
    alert.log. However, after applying Patch 8730312, the message still
    occurred in the alert.log

    DIAGNOSTIC ANALYSIS
    ===================
    “kewastUnPackStats(): bad magic 1 (0x110a1315b, 0)” still occurs after Patch
    8730312 is applied.

    WORKAROUND?
    ===========
    No

    TECHNICAL IMPACT
    ================
    “kewastUnPackStats(): bad magic 1 (0x110a1315b, 0)” written to alert.log

    RELATED ISSUES (bugs, forums, RFAs)
    ===================================
    BUG 8730312

    HOW OFTEN DOES THE ISSUE REPRODUCE AT CUSTOMER SITE?
    ====================================================
    Always

    DOES THE ISSUE REPRODUCE INTERNALLY?
    ====================================
    Never

    IS A TESTCASE AVAILABLE?
    ========================
    No

    Link to IPS Package:
    ====================
    NA

  2. admin says:

    ORA-00600 [KDSGRP1] WHEN DOING AN UPDATE

    Applies to:

    Oracle Server – Enterprise Edition – Version: 11.1.0.7 to 11.2.0.2 – Release: 11.1 to 11.2
    Information in this document applies to any platform.

    Symptoms

    Encountering the following error in the alert.log
    ORA-00600: internal error code, arguments: [kdsgrp1]

    Review of trace file associated with this issue shows the following:

    Current SQL Statement for this session 

    update /*+ index (a dbarnes.events_tmp_308235_idx1) */ dbarnes.events_tmp_308235 a 
    set url_perm_id = (select /*+ INDEX (xt_outbound_pk) */ url_perm_id 
                      
    from  CONFUSEDCOM.t_outbound x  
                      
    where x.mailgroup_id = a.mailgroup_id 
                        
    and  x.cid = a.cid                    
                        
    and x.instance_id = a.instance_id ) 
    where client_id = :a1

    —– PL/SQL Call Stack —–
     
    object      line  object
     
    handle    number  name
    eaf0c8ae0       171  package body DBARNES.REDIRECT_EVENTS
    eaf0c8ae0        25  package body DBARNES.REDIRECT_EVENTS
    ecccb6858         1  anonymous block

    —– Call Stack  Trace —–
    ksedst1 <- ksedst <- dbkedDefDump <- dbgexPhaseII <- dbgexProcessError        
    <- dbgePostErrorKGE <- 1128 <- kgerinv_internal <- kgerinv <- kgeasnmierr    
       
    <- kdsgrp1 <- kdsgrp_cb <- ktrgtc2 <- kdsgrp <-  qetlbrcb <-
    qertbFetchByRowID <- subsr1 <- subsr3 <- evaopn3 <- upderhFastPath          
    <- upduawFastPath <- kdusru <- updrowFastPath <- qerupFetch <- updaul        
      
    <- updThreePhaseExe <- 376 <- updexe <- opiexe  <- opipls <-
    opiodr <- rpidrus <- skgmstack <- rpidru <- rpiswu2 <- rpidrv <-
    psddr0 <- psdnal <- pevm_EXIM <- pfrinstr_EXIM <-
    pfrrun_no_tool <- pfrrun <- plsql_run <- peicnt <- kkxexe <-
    opiexe <- kpoal8 <- opiodr <- ttcpip <- opitsk <- opiino <-  
    opiodr <- opidrv <- sou2o <- main <- start

    ============
    Plan Table
    ============
    —————————————————————-+————-
    ———————-+—————+
    | Id  | Operation                            | Name             | Rows  |
    Bytes | Cost  | Time      |  Pstart| Pstop |
    —————————————————————-+————-
    ———————-+—————+
    | 0   | UPDATE STATEMENT                     |                  |       |    
     
    |   707 |           |       |       |
    | 1   |  UPDATE                              | EVENTS_TMP_308235|       |    
     
    |       |           |       |       |
    | 2   |   TABLE ACCESS FULL                  | EVENTS_TMP_308235|  2104 |  
    43K |   707 |  00:00:10 |       |       |
    | 3   |   TABLE ACCESS BY GLOBAL INDEX ROWID | T_OUTBOUND       |     1 |    
    17 |     3 |  00:00:01 | ROW LOCATION| ROW LOCATION|
    | 4   |    INDEX UNIQUE SCAN                 | T_OUTBOUND_PK    |     1 |    
     
    |     2 |  00:00:01 |       |       |
    —————————————————————-+————-
    ———————-+—————+

    Cause

    Bug 8771916

    REDISCOVERY INFORMATION:
    An ORA-00600 [kdsgrp1] on a query involving a unique key lookup via an index is
    symptomatic of this bug. However, do note that this bug is not the only cause
    of the aforementioned internal error.

    Solution

    Apply patch 8771916

    WORKAROUND:
    Disabling rowCR which is an optimization to reduce consistent-read rollbacks during queries by setting _row_cr=FALSE in the initialization files is one workaround. However, this could have performance degradation of queries. Please check the ratio of the two statistics “RowCR hits”/”RowCR attempts” to determine whether the workaround is to be used. 

     

  3. ORA-00600: [KTRGCM_3]
    Applies to:
    Oracle Server – Enterprise Edition – Version: 9.0.1.4 to 10.2.0.3
    This problem can occur on any platform.
    Symptoms

    The following errors reported in the alert log causing failure of the associated SQL statement
    ORA-00600: internal error code, arguments: [ktrgcm_3]

    Changes

    setting the following parameter 
    _row_cr = TRUE

    Cause

    The problem happens when reclaiming space from a txn free list when migrating blob data from inline to out-of-line.

    This is cause by the setting _row_cr = TRUE

    Solution

    set _row_cr to FALSE or take the parameter out.

    _row_cr hidden parameter used to control CR requests and Buffer waits on remote undo segment headers which is a common problem on RAC instances

    In particular with Oracle Applications, global cache request waits and buffer waits for segment headers of rollback segments owned by remote instances, can become a significant part of the overall wait time. Consult the view V$CR_BLOCK_SERVER (refer to Part II, chapter 3.4 V$CR_BLOCK_SERVER) for confirmation.

    A CR request and buffer wait here can occur in the following situation:

    1. Several instances modify a block.
    2. Instance B queries the block.

    This causes a request to generate a CR version of the current block, which can either be in the local cache or a remote cache, e.g. instance A.
    If remote, the LMS on instance A will try to generate the CR block; when local, the foreground process executing the query on instance B will perform the CR block generation.
    In any case, either instance needs to read the transaction table and undo blocks from the rollback segment that are referenced in the active ITLs of the block.
    This cleanout/rollback process may cause several lookups of local and remote undo headers and undo blocks.
    The remote undo header and undo block lookups will result in a global CR request. Since undo headers are frequently accessed, a buffer wait may also occur.

    The wait event will be gc buffer busy, and the use of Automatic Undo Segments can exacerbate the performance problem because for each transaction we will use a different undo segment.
    The views V$SESSION_WAIT or V$SESSION_WAIT_HISTORY assist in identifying the rollback segment, simply by looking at the columns P1 and P2, which denote the file id and block id.

    RowCR is now enabled and supported with 10.2. RowCR can partially reduce the overhead of the global block cleanout/rollback problem by simply attempting to generate a CR version for the requested row.

    Please note that RowCR (_row_cr=TRUE) is NOT SUPPORTED in Oracle versions prior to 10g Release

    The parameter can be enabled/disabled dynamically.

    -This is a boolean parameter that defaults to TRUE . When set to TRUE it will enable row level consistent read (Row CR) if either of the following are true:

    You are running under a RAC environment

    Event 10313 is enabled .

  4. ORA-600 [kdsgrp1]
    Applies to:
    Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 11.1.0.6
    Information in this document applies to any platform.
    Purpose
    The error is raised when we fail to find a row piece, i. e., the block is fine, but the row does not exist.
    This error is new in 10g, in 9i we didn’t raise this error and the corruption was unnoticed.

    We raise ORA-600[kdsgrp1] assert during table/index full scans if a row piece is not found.
    The error is raised only if Event 10231 or SKIP_CORRUPT_BLOCKS is not set.

    /* Check table number, whether the row slot exists, whether row exists */
    /* if row doesn’t exist, and neither ‘skip corrupt block’ nor
    ** ‘user rowid’ bits are set, then raise error kdsgrp1.

    Note: the block is good and because of that DBV, RMAN, analyze will not fail.
    Last Review Date
    November 28, 2007
    Instructions for the Reader
    A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.
    Troubleshooting Details
    Error may be caused if:
    1. A row referenced in an index does not exist in the table. 
        In this case ‘analyze table <name> validate structure cascade’ will fail with an ora-1499, and trace file  generated will show the index that causes the error. You will have to drop and recreate that index in order to solve the error.

    2. An unexistent rowid pointed by a chained row.
         In this case analyze will not fail.

    In this case error can be eliminated by skipping the "corruption", however
    before doing anything like this you should verify if there is actually any data in the
    referenced blocks that are getting errors and will be skipped.
    Please involve Oracle Support to see if it’s possible to extract the data.

    Trace file generated by ora-600[kdsgrp1] will show at the beginning something similar to:
    *** 2007-04-26 19:53:57.671
    *** SERVICE NAME:(HARTLEY) 2007-04-26 19:53:57.671
    *** SESSION ID:(304.20) 2007-04-26 19:53:57.671

    row 0826817f.ffffffff continuation at

    file# 32 block# 2523519 slot 0 not found
    **************************************************

    Please dump the block in order to retrieve the data:
    SQL> alter system dump datafile 32 block 2523519;
    WORKAROUND:

    In order to skip the error one of these two w/a can be used:

    1) set event 10231 and export the good rows or use ‘create table as select ..’

    Setting the event in a Session

    ~~~~~~~~~~~~~~~~~~~
    SQL> alter session set events ‘10231 trace name context forever, level 10’; 
    SQL> create table salvage_table as select *  from corrupt_table; 

    Setting the event at Instance level 

    ~~~~~~~~~~~~~~~~~~~~~~

    Edit your init <SID>.ora add the following event and bounce database: 

    event="10231 trace name context forever, level 10" 

            export the table or use ‘create table .. as select ..’

    or

    2) run DBMS_REPAIR.SKIP_CORRUPT_BLOCKS() procedure on the table

    Connect as a SYSDBA user and mark the table as needing to skip corrupt blocks thus: 
    SQL> execute dbms_repair.skip_corrupt_blocks(‘<schema>’,'<tablename>’); 

    After this you can export the table or run ‘create table .. as select ..’.

    You can find the lost rows from the corrupted blocks by comparing the corrupted table with the new table.
    Once you have the rowid of the skipped rows you can use DBMS_ROWID package to find the file# and block#, and dump the block to retrieve the data:

    SQL> alter system dump datafile <file#> block <block#>; /** a trace file will be generated under udump
     

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569