一套Linux x86-64上的11.2.0.1系统&＃xff0c;alert日志中出现ORA-00600: internal error code, arguments: [kdsgrp1]错误&＃xff0c;相关trace的部分内容如下&＃xff1a;
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 &＃61; /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&＃64;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&＃64;x42k604-zone1 (TNS V1-V3)) 2010-07-28 11:08:35.394
*** ACTION NAME:() 2010-07-28 11:08:35.394Dump continued from file: /u01/app/oracle/diag/rdbms/utdw016/utdw016b/trace/utdw016b_ora_5756.trc
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61; Dump for incident 276035 (ORA 600 [kdsgrp1]) &＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;*** 2010-07-28 11:08:35.395
dbkedDefDump(): Starting incident default dumps (flags&＃61;0x2, level&＃61;3, mask&＃61;0x0)
----- Current SQL Statement for this session (sql_id&＃61;9hfdcgzzgmgvp) -----
DELETE FROM EIMABS.FACT_OPEN_SALES_ORDER WHERE DIM_SNAPSHOT_TYPE_ID &＃61; (SELECT DIM_SNAPSHOT_TYPE_ID FROM EIMABS.DIM_SNAPSHOT_TYPE WHERE SNAPSHOT_TYPE_CODE&＃61;&＃39;FUTURE&＃39;)
----- 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()&＃43;36 call kgdsdst() 000000000 ? 000000000 ?
7FFFA85D7418 ? 000000001 ?
7FFFA85DB918 ? 000000000 ?
ksedst1()&＃43;98 call skdstdst() 000000000 ? 000000000 ?
7FFFA85D7418 ? 000000001 ?
000000000 ? 000000000 ?
ksedst()&＃43;34 call ksedst1() 000000000 ? 000000001 ?
7FFFA85D7418 ? 000000001 ?
000000000 ? 000000000 ?
dbkedDefDump()&＃43;2736 call ksedst() 000000000 ? 000000001 ?
7FFFA85D7418 ? 000000001 ?
000000000 ? 000000000 ?
ksedmp()&＃43;36 call dbkedDefDump() 000000003 ? 000000002 ?
7FFFA85D7418 ? 000000001 ?
000000000 ? 000000000 ?
ksfdmp()&＃43;64 call ksedmp() 000000003 ? 000000002 ?
7FFFA85D7418 ? 000000001 ?
000000000 ? 000000000 ?
dbgexPhaseII()&＃43;1764 call ksfdmp() 000000003 ? 000000002 ?
7FFFA85D7418 ? 000000001 ?
000000000 ? 000000000 ?
dbgexProcessError() call dbgexPhaseII() 2B30E1B74718 ? 2B30E1B795F8 ?
&＃43;2279 7FFFA85E3708 ? 000000001 ?
000000000 ? 000000000 ?
dbgeExecuteForError call dbgexProcessError() 2B30E1B74718 ? 2B30E1B795F8 ?
()&＃43;83 000000001 ? 000000000 ?
7FFF00000000 ? 000000000 ?
dbgePostErrorKGE()&＃43; call dbgeExecuteForError 2B30E1B74718 ? 2B30E1B795F8 ?
1615 () 000000001 ? 000000001 ?
000000000 ? 000000000 ?
dbkePostKGE_kgsf()&＃43; call dbgePostErrorKGE() 000000000 ? 2B30E1B87F38 ?
63 000000258 ? 2B30E1B795F8 ?
000000000 ? 000000000 ?
kgeadse()&＃43;383 call dbkePostKGE_kgsf() 00A9B2EE0 ? 2B30E1B87F38 ?
000000258 ? 2B30E1B795F8 ?
000000000 ? 000000000 ?
kgerinv_internal()&＃43; call kgeadse() 00A9B2EE0 ? 2B30E1B87F38 ?
45 000000258 ? 000000000 ?
000000000 ? 000000000 ?
kgerinv()&＃43;33 call kgerinv_internal() 00A9B2EE0 ? 2B30E1B87F38 ?
A85DBCA000000000 ?
000000258 ? 000000000 ?
000000000 ?
kgeasnmierr()&＃43;143 call kgerinv() 00A9B2EE0 ? 2B30E1B87F38 ?
A85DBCA000000000 ?
000000000 ? 000000000 ?
000000000 ?
kdsgrp1_dump()&＃43;832 call kgeasnmierr() 00A9B2EE0 ? 2B30E1B87F38 ?
A85DBCA000000000 ?
000000000 ? 000000003 ?
000000003 ?
kdsgrp1()&＃43;19 call kdsgrp1_dump() 00A9B2EE0 ? 2B30E1B87F38 ?
7FFFA85E4360 ? 000000000 ?
000000003 ? 000000003 ?
kdsgrp()&＃43;6346 call kdsgrp1() 00A9B2EE0 ? 2B30E1B87F38 ?
7FFFA85E4360 ? 000000000 ?
000000003 ? 000000003 ?
qetlbr()&＃43;211 call kdsgrp() 2B30E2018620 ? 000000000 ?
2B30E2018620 ? 000000000 ?
000000003 ? 000000003 ?
qertbFetchByRowID() call qetlbr() 2B30E2018620 ? 00000001A ?
&＃43;850 000000000 ? 0000000C8 ?
00400F521 ? 000000000 ?
qergiFetch()&＃43;276 call qertbFetchByRowID() 000000000 ? 2B30E2018518 ?
000000000 ? 000000000 ?
000000001 ? 000000000 ?
qerdlFetch()&＃43;678 call qergiFetch() 511B78238 ? 2B30E20187F8 ?
000000000 ? 000000000 ?
000000001 ? 000000000 ?
delexe()&＃43;952 call qerdlFetch() 511B78090 ? 2B30E2018900 ?
000000000 ? 000000000 ?
000007FFF ? 000000000 ?
opiexe()&＃43;14449 call delexe() 000000000 ? 000000000 ?
000000000 ? 2B30E27E1E08 ?
2B30E1FCA000 ? 000000000 ?
opipls()&＃43;3098 call opiexe() 000000004 ? 000000005 ?
7FFFA85E6708 ? 2B30E27E1E08 ?
2B30E1FCA000 ? 000000000 ?
opiodr()&＃43;1149 call opipls() 000000066 ? 000000007 ?
7FFFA85E7F90 ? 000000000 ?
2B30E2701C28 ? 300000000 ?
__PGOSF141_rpidrus( call opiodr() 000000066 ? 000000007 ?
)&＃43;206 7FFFA85E7F90 ? 000000003 ?
008C71EF0 ? 300000000 ?
skgmstack()&＃43;148 call __PGOSF141_rpidrus( 7FFFA85E79A0 ? 000000007 ?
) 7FFFA85E7F90 ? 000000003 ?
008C71EF0 ? 300000000 ?
rpiswu2()&＃43;612 call skgmstack() 7FFFA85E7978 ? 00A9B2B20 ?
00000F618 ? 008468D40 ?
7FFFA85E79A0 ? 300000000 ?
rpidrv()&＃43;1342 call rpiswu2() 55CE5B2F8 ? 000000078 ?
55CE5B37C ? 000000009 ?
2B30E1FEEF98 ? 000000078 ?
psddr0()&＃43;459 call rpidrv() 000000003 ? 000000066 ?
7FFFA85E7F90 ? 000000039 ?
2B30E1FEEF98 ? 000000078 ?
psdnal()&＃43;457 call psddr0() 000000003 ? 000000066 ?
7FFFA85E7F90 ? 000000031 ?
2B30E1FEEF98 ? 000000078 ?
pevm_EXECC()&＃43;376 call psdnal() 7FFFA85E9370 ? 7FFFA85E9580 ?
7FFFA85E7F90 ? 2B30E2742370 ?
2B30E1FEEF98 ? 000000078 ?
pfrinstr_EXECC()&＃43;75 call pevm_EXECC() 2B30E273F0A0 ? 7FFFA85E9580 ?
000000020 ? 2B30E2742370 ?
2B30E1FEEF98 ? 000000078 ?
pfrrun_no_tool()&＃43;63 call pfrinstr_EXECC() 2B30E273F0A0 ? 51148B482 ?
2B30E273F110 ? 2B30E2742370 ?
2B30E1FEEF98 ? 2B3000000020 ?
pfrrun()&＃43;1025 call pfrrun_no_tool() 2B30E273F0A0 ? 51148B482 ?
2B30E273F110 ? 2B30E2742370 ?
2B30E1FEEF98 ? 2B3000000020 ?
plsql_run()&＃43;769 call pfrrun() 2B30E273F0A0 ? 000000000 ?
2B30E273F110 ? 7FFFA85E9370 ?
2B30E1FEEF98 ? 503AC23E2 ?
peicnt()&＃43;296 call plsql_run() 2B30E273F0A0 ? 000000001 ?
000000000 ? 7FFFA85E9370 ?
2B30E1FEEF98 ? 000000000 ?
kkxexe()&＃43;520 call peicnt() 7FFFA85E9370 ? 2B30E273F0A0 ?
2B30E2418960 ? 7FFFA85E9370 ?
2B30E2486008 ? 000000000 ?
opiexe()&＃43;14796 call kkxexe() 2B30E2013538 ? 2B30E273F0A0 ?
000000000 ? 7FFFA85E9370 ?
2B30E2486008 ? 000000000 ?
kpoal8()&＃43;2283 call opiexe() 000000049 ? 000000003 ?
7FFFA85EA8F8 ? 7FFFA85E9370 ?
2B30E2486008 ? 000000000 ?
opiodr()&＃43;1149 call kpoal8() 00000005E ? 00000001C ?
7FFFA85ED9D0 ? 7FFFA85E9370 ?
2B30E2486008 ? 5E00000001 ?
ttcpip()&＃43;1251 call opiodr() 00000005E ? 00000001C ?
7FFFA85ED9D0 ? 000000000 ?
008C71DB0 ? 5E00000001 ?
opitsk()&＃43;1628 call ttcpip() 00A9C6450 ? 0086CCB08 ?
7FFFA85ED9D0 ? 000000000 ?
7FFFA85ED430 ? 7FFFA85EDBD4 ?
opiino()&＃43;953 call opitsk() 00A9C6450 ? 000000000 ?
7FFFA85ED9D0 ? 000000000 ?
7FFFA85ED430 ? 7FFFA85EDBD4 ?
opiodr()&＃43;1149 call opiino() 00000003C ? 000000004 ?
7FFFA85EF0C8 ? 000000000 ?
7FFFA85ED430 ? 7FFFA85EDBD4 ?
opidrv()&＃43;565 call opiodr() 00000003C ? 000000004 ?
7FFFA85EF0C8 ? 000000000 ?
008C71860 ? 7FFFA85EDBD4 ?
sou2o()&＃43;98 call opidrv() 00000003C ? 000000004 ?
7FFFA85EF0C8 ? 000000000 ?
008C71860 ? 7FFFA85EDBD4 ?
opimai_real()&＃43;128 call sou2o() 7FFFA85EF0A0 ? 00000003C ?
000000004 ? 7FFFA85EF0C8 ?
008C71860 ? 7FFFA85EDBD4 ?
ssthrdmain()&＃43;209 call opimai_real() 000000002 ? 7FFFA85EF290 ?
000000004 ? 7FFFA85EF0C8 ?
008C71860 ? 7FFFA85EDBD4 ?
main()&＃43;196 call ssthrdmain() 000000002 ? 7FFFA85EF290 ?
000000001 ? 000000000 ?
008C71860 ? 7FFFA85EDBD4 ?
__libc_start_main() call main() 000000002 ? 7FFFA85EF438 ?
&＃43;244 000000001 ? 000000000 ?
008C71860 ? 7FFFA85EDBD4 ?
_start()&＃43;36 call __libc_start_main() 0009D3E88 ? 000000002 ?
7FFFA85EF428 ? 000000000 ?
008C71860 ? 000000002 ?
错误由此删除语句引起:
DELETE FROM EIMABS.FACT_OPEN_SALES_ORDER WHERE
DIM_SNAPSHOT_TYPE_ID &＃61;
(SELECT DIM_SNAPSHOT_TYPE_ID FROM EIMABS.DIM_SNAPSHOT_TYPE WHERE SNAPSHOT_TYPE_CODE&＃61;&＃39;FUTURE&＃39;)
其中EIMABS.DIM_SNAPSHOT_TYPE是一个普通堆表&＃xff0c;而EIMABS.FACT_OPEN_SALES_ORDER是一个分区表&＃xff0c;针对该分区表的各个分区进行了validate structure分析:
SQL> &＃64;?/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分区相关的索引可能出现了讹误&＃xff0c;尝试重建该表上的15个索引&＃xff0c;再次测试相关应用发现问题仍未解决。此次产生的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 &＃61; /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&＃64;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&＃64;x42k604-zone2 (TNS V1-V3)) 2010-07-30 04:10:19.931
*** ACTION NAME:() 2010-07-30 04:10:19.931Dump continued from file: /u01/app/oracle/diag/rdbms/utdw016/utdw016a/trace/utdw016a_ora_26473.trc
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61; Dump for incident 310187 (ORA 600 [kdsgrp1]) &＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;*** 2010-07-30 04:10:19.932
dbkedDefDump(): Starting incident default dumps (flags&＃61;0x2, level&＃61;3, mask&＃61;0x0)
----- Current SQL Statement for this session (sql_id&＃61;9hfdcgzzgmgvp) -----
DELETE FROM EIMABS.FACT_OPEN_SALES_ORDER WHERE DIM_SNAPSHOT_TYPE_ID &＃61; (SELECT DIM_SNAPSHOT_TYPE_ID FROM EIMABS.DIM_SNAPSHOT_TYPE WHERE SNAPSHOT_TYPE_CODE&＃61;&＃39;FUTURE&＃39;)
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----SO: 0x5334781d0, type: 52, owner: 0x55d2646b8, flag: INIT/-/-/0x00 if: 0x3 c: 0x3proc&＃61;0x55caf0778, name&＃61;transaction, file&＃61;ktccts.h LINE:388, pg&＃61;0(trans) flg &＃61; 0x00001e03, flg2 &＃61; 0x000c0000, flg3 &＃61; 0x00000000, prx &＃61; (nil), ros &＃61; 2147483647flg &＃61; 0x00001e03: ALC TRN VUS VID CHG USNflg2 &＃61; 0x000c0000: PGA NIPflg3 &＃61; 0x00000000:bsn &＃61; 0x8419 bndsn &＃61; 0x841b spn &＃61; 0x85c7efd &＃61; 15 DID:file:kta.c lineno:1665parent xid: 0x0000.000.00000000env: (scn: 0x0a19.a0b7c9d4 xid: 0x0021.017.00028415 uba: 0x00e00545.0438.12 statement num&＃61;0 parent xid: xid: 0x0000.000.00000000 scn: 0x0a19.a0b7c9d9 0sch: scn: 0x0000.00000000 mascn: (scn: 0x0000.00000000)cev: (spc &＃61; 11546 arsp &＃61; 0x5335450a8 ubkds (ubk:tsn: 2 rdba: 0x00e00545 flag:0x8 hdl:(nil) addr:0x35c50c014) useg tsn: 2 rdba: 0x00e2c990hwm uba: 0x00e00545.0438.12 col uba: 0x00000000.0000.00num bl: 3 bk list: 0x53db19518)cr opc: 0x0 spc: 11546 uba: 0x00e00545.0438.12ccbstg: 0x00000000(enqueue) TX-00210017-00028415 DID: 0001-003E-00000C70lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x47mode: X, lock_flag: 0x0, lock: 0x533478248, res: 0x5414c2da0own: 0x55d2646b8, sess: 0x8b37718, proc: 0x55caf0778, prv: 0x5414c2db0slk: (nil)xga: (nil), heap: UGATrans IMU st: 0 Pool index 65535, Redo pool 0x533478980, Undo pool 0x533478a68Redo 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: 0x3SO: 0x55d2646b8, type: 4, owner: 0x55caf0778, flag: INIT/-/-/0x00 if: 0x3 c: 0x3proc&＃61;0x55caf0778, name&＃61;session, file&＃61;ksu.h LINE:11467, pg&＃61;0(session) sid: 1993 ser: 31898 trans: 0x5334781d0, creator: 0x55caf0778flags: (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/EIMABSksuxds FALSE at location: 0service name: utdw016-edwclient details:O/S info: user: infom, term: , ospid: 21955machine: x42k604-zone2 program: pmdtm&＃64;x42k604-zone2 (TNS V1-V3)application name: pmdtm&＃64;x42k604-zone2 (TNS V1-V3), hash value&＃61;4200765728Current Wait Stack:Not in wait; last wait ended 1.028093 sec agoWait State:fixed_waits&＃61;0 flags&＃61;0x21 boundary&＃61;(nil)/-1Session Wait History:elapsed time of 1.028173 sec since last wait0: waited for &＃39;Disk file operations I/O&＃39;FileOperation&＃61;0x5, fileno&＃61;0x1, filetype&＃61;0x3wait_id&＃61;4666 seq_num&＃61;4860 snap_id&＃61;2wait times: snap&＃61;0.000039 sec, exc&＃61;0.000181 sec, total&＃61;0.000687 secwait times: max&＃61;infinitewait counts: calls&＃61;0 os&＃61;0occurred after 0.000000 sec of elapsed time1: waited for &＃39;ASM file metadata operation&＃39;msgop&＃61;0x12, locn&＃61;0x0, &＃61;0x0wait_id&＃61;4667 seq_num&＃61;4859 snap_id&＃61;2wait times: snap&＃61;0.000002 sec, exc&＃61;0.000005 sec, total&＃61;0.000506 secwait times: max&＃61;infinitewait counts: calls&＃61;0 os&＃61;0occurred after 0.000000 sec of elapsed time2: waited for &＃39;KSV master wait&＃39;&＃61;0x0, &＃61;0x0, &＃61;0x0wait_id&＃61;4668 seq_num&＃61;4858 snap_id&＃61;1wait times: snap&＃61;0.000501 sec, exc&＃61;0.000501 sec, total&＃61;0.000501 secwait times: max&＃61;infinitewait counts: calls&＃61;1 os&＃61;1occurred after 0.000000 sec of elapsed time3: waited for &＃39;ASM file metadata operation&＃39;msgop&＃61;0x12, locn&＃61;0x0, &＃61;0x0wait_id&＃61;4667 seq_num&＃61;4857 snap_id&＃61;1wait times: snap&＃61;0.000003 sec, exc&＃61;0.000003 sec, total&＃61;0.000003 secwait times: max&＃61;infinitewait counts: calls&＃61;0 os&＃61;0occurred after 0.000000 sec of elapsed time4: waited for &＃39;Disk file operations I/O&＃39;FileOperation&＃61;0x5, fileno&＃61;0x1, filetype&＃61;0x3wait_id&＃61;4666 seq_num&＃61;4856 snap_id&＃61;1wait times: snap&＃61;0.000142 sec, exc&＃61;0.000142 sec, total&＃61;0.000142 secwait times: max&＃61;infinitewait counts: calls&＃61;0 os&＃61;0
......Block header dump: 0x0400f521 DataBlock header dump: 0x2317a579 IndexBlock header dump: 0x2317a577 " "Block header dump: 0x048c8c3c " "Block header dump: 0x2317a1bb " "Block header dump: 0x048c78ad " "Block header dump: 0x0400f51f DataBlock header dump: 0x2317a33b IndexBlock 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]错误的出现&＃xff0c;如下:
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,&＃39;FRI&＃39;) 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 &＃61; B.DIM_BIZ_SECTOR_ID
AND F.DIM_ENT_ITEM_ID &＃61; E.DIM_ENT_ITEM_ID
AND F.DIM_REV_PCH_KEY_ACCT_ID &＃61; PCH.DIM_PCH_KEY_ACCOUNT_ID
AND F.DIM_ULT_DEST_COUNTRY_ID &＃61; CT.DIM_COUNTRY_ID
AND UPPER(B.BIZ_SECTOR_CODE) &＃61; &＃39;MDB&＃39;
AND F.DIM_REVENUE_DATE_ID &＃61; 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 &＃61; TRUNC(SYSDATE) AND A.DIM_CALENDAR_MONTH_ID &＃61; 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 &＃61; TRUNC(SYSDATE) AND A.DIM_CALENDAR_MONTH_ID &＃61; 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 &＃61; /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&＃64;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&＃64;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 at0x0400f521.1a file# 16 block# 62753 slot 26 not found
KDSTABN_GET: 0 ..... ntab: 1
curSlot: 26 ..... nrows: 28
kdsgrp - dump CR block dba&＃61;0x0400f521
Block header dump: 0x0400f521
Object id on Block? Y
seg/obj: 0x11527d csc: 0xa19.61e20b3c itc: 2 flg: E typ: 1 - DATAbrn: 0 bdba: 0x400f500 ver: 0x01 opc: 0inc: 0 exflg: 0Itl 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&＃xff0c;Oracle GCS支持分析日志后认为告警日志中曾经出现kewastUnPackStats记录&＃xff0c;说明可能由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: &＃43;UAT_DATA/utdw016/redo041a.log
Current log# 4 seq# 8652 mem# 1: &＃43;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: &＃43;UAT_DATA/utdw016/redo051a.log
Current log# 5 seq# 8653 mem# 1: &＃43;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 (&＃43; 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&＃39;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/aRELATED BUGS:
-------------
8715387, 8730312REPRODUCIBILITY:
----------------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:
&＃43;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可以解决我们的问题&＃xff0c;但实际apply patch后EIMABS.FACT_OPEN_SALES_ORDER上的DELETE语句可以正常执行了&＃xff0c;而之后发现的查询语句仍会报ORA-00600:[kdsgrp1]错误:
We have applied the patch. And we&＃39;re in process of analyze all the partitions. So far there&＃39;s no error reported. We analyzed all the partitions in table EIMABS.FACT_REVENUE, there&＃39;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,&＃39;FRI&＃39;) 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 &＃61; B.DIM_BIZ_SECTOR_ID
AND F.DIM_ENT_ITEM_ID &＃61; E.DIM_ENT_ITEM_ID
AND F.DIM_REV_PCH_KEY_ACCT_ID &＃61; PCH.DIM_PCH_KEY_ACCOUNT_ID
AND F.DIM_ULT_DEST_COUNTRY_ID &＃61; CT.DIM_COUNTRY_ID
AND UPPER(B.BIZ_SECTOR_CODE) &＃61; &＃39;MDB&＃39;
AND F.DIM_REVENUE_DATE_ID &＃61; 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 &＃61; TRUNC(SYSDATE) AND A.DIM_CALENDAR_MONTH_ID &＃61; 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 &＃61; TRUNC(SYSDATE) AND A.DIM_CALENDAR_MONTH_ID &＃61; 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 &＃61; /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&＃64;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&＃64;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 at0x04608b75.40 file# 17 block# 2132853 slot 64 not found
KDSTABN_GET: 0 ..... ntab: 1
curSlot: 64 ..... nrows: 130
kdsgrp - dump CR block dba&＃61;0x04608b75
Block header dump: 0x04608b75
Object id on Block? Y
seg/obj: 0x1c8071 csc: 0xa19.622a6023 itc: 2 flg: E typ: 1 - DATAbrn: 0 bdba: 0x4608b40 ver: 0x01 opc: 0inc: 0 exflg: 0Itl 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
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
tsiz: 0x3f98
hsiz: 0x116
pbl: 0x47c16c06476543210
flag&＃61;--------
ntab&＃61;1
nrow&＃61;130
frre&＃61;64
..
..
tab 0, row 63, &＃64;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_dumpBlock dump from cache:
Dump of buffer cache at level 4 for tsn&＃61;13, rdba&＃61;73436021
BH (0x47fd84178) file#: 17 rdba: 0x04608b75 (17/2132853) class: 1 ba: 0x47c16c000set: 47 pool 3 bsz: 16384 bsi: 0 sflg: 1 pwc: 0,25dbwrid: 0 obj: 1867889 objn: 1130693 tsn: 13 afn: 17 hint: fhash: [0x54be43860,0x54be43860] lru: [0x49beca1a0,0x3b7e094c0]ckptq: [NULL] fileq: [NULL] objq: [0x52a0e8580,0x52a0e8580]use: [0x54271be40,0x54271be40] wait: [NULL]st: SCURRENT md: SHR tch: 0 le: 0x3effd22c8flags: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&＃61;trans dataBlock header dump: 0x04608b75
Object id on Block? Y
seg/obj: 0x1c8071 csc: 0xa19.622a6023 itc: 2 flg: E typ: 1 - DATAbrn: 0 bdba: 0x4608b40 ver: 0x01 opc: 0inc: 0 exflg: 0Itl 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: 0x04608b75Incident 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&＃61;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,&＃39;FRI&＃39;) AS SNAPSHOT_DATE,F.REVENUE_DT AS FORECAST_DATE,F.REVENUE_QTY AS REVENUE_QTY
FROMEIMABS.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
WHEREF.DIM_BIZ_SECTOR_ID &＃61; B.DIM_BIZ_SECTOR_ID
AND F.DIM_ENT_ITEM_ID &＃61; E.DIM_ENT_ITEM_ID
AND F.DIM_REV_PCH_KEY_ACCT_ID &＃61; PCH.DIM_PCH_KEY_ACCOUNT_ID
AND F.DIM_ULT_DEST_COUNTRY_ID &＃61; CT.DIM_COUNTRY_ID
AND UPPER(B.BIZ_SECTOR_CODE) &＃61; &＃39;MDB&＃39;
AND F.DIM_REVENUE_DATE_ID &＃61; CAL.DIM_CALENDAR_DATE_ID
AND CAL.FISCAL_MONTH_NUM IN
(SELECT FISCAL_MONTH_NUM FROM EIMABS.DIM_FISCAL_CAL_MONTHWHERE DIM_CALENDAR_MONTH_ID IN ((SELECT B.DIM_CALENDAR_MONTH_ID -1 FROM EIMABS.DIM_FISCAL_CAL_DATE A , EIMABS.DIM_FISCAL_CAL_MONTH BWHERE CALENDAR_DT &＃61; TRUNC(SYSDATE) AND A.DIM_CALENDAR_MONTH_ID &＃61; B.DIM_CALENDAR_MONTH_ID),(SELECT B.DIM_CALENDAR_MONTH_ID -2 FROM EIMABS.DIM_FISCAL_CAL_DATE A , EIMABS.DIM_FISCAL_CAL_MONTH BWHERE CALENDAR_DT &＃61; TRUNC(SYSDATE) AND A.DIM_CALENDAR_MONTH_ID &＃61; B.DIM_CALENDAR_MONTH_ID)))----- Call Stack Trace -----kdsgrp1 kdsgrp qetlbr qertbFetchByRowID qerjotRowProc qerbtFetch wProc qergiFetchBH (0x47fd84178) file#: 17 rdba: 0x04608b75 (17/2132853) class: 1 ba: 0x47c16c000set: 47 pool 3 bsz: 16384 bsi: 0 sflg: 1 pwc: 0,25dbwrid: 0 obj: 1867889 objn: 1130693 tsn: 13 afn: 17 hint: faddr: 0x47fd84178 obj: 1867889 cls: DATA bscn: 0xa19.622a621dbuffer tsn: 13 rdba: 0x04608b75 (17/2132853)scn: 0x0a19.622a621d seq: 0x01 flg: 0x06 tail: 0x621d0601frmt: 0x02 chkval: 0xdf67 type: 0x06&＃61;trans dataBlock header dump: 0x04608b75
Object id on Block? Y
seg/obj: 0x1c8071 csc: 0xa19.622a6023 itc: 2 flg: E typ: 1 - DATAbrn: 0 bdba: 0x4608b40 ver: 0x01 opc: 0inc: 0 exflg: 0Itl 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
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
tsiz: 0x3f98
hsiz: 0x116
pbl: 0x47c16c06476543210
flag&＃61;--------
ntab&＃61;1
nrow&＃61;130
frre&＃61;64BH (0x49fe84af8) file#: 29 rdba: 0x076e5058 (29/3035224) class: 1 ba: 0x49ee0c000set: 44 pool 3 bsz: 16384 bsi: 0 sflg: 1 pwc: 0,25dbwrid: 1 obj: 1893758 objn: 1893758 tsn: 17 afn: 29 hint: faddr: 0x49fe84af8 obj: 1893758 cls: DATA bscn: 0xa19.62297970buffer tsn: 17 rdba: 0x076e5058 (29/3035224)scn: 0x0a19.62297970 seq: 0x01 flg: 0x06 tail: 0x79700601frmt: 0x02 chkval: 0xe878 type: 0x06&＃61;trans dataBlock header dump: 0x076e5058
Object id on Block? Y
seg/obj: 0x1ce57e csc: 0xa19.6229787e itc: 2 flg: E typ: 2 - INDEXbrn: 0 bdba: 0x76e5054 ver: 0x01 opc: 0inc: 0 exflg: 0Itl 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又有回复&＃xff0c;认为设置隐式参数_row_cr&＃61;FALSE可以解决问题:
There are few bugs that could raise the ora-600[KDSGRP1] in 11.21) based on the stack --> Bug:8771916 ORA-600 [KDSGRP1] WHEN DOING AN UPDATEAbstract: OERI [kdsgrp1] during CR read
Fixed-Releases: C100
Tags: OERI
Details:ORA-600 [kdsgrp1] can occur intermittently when usinga query access path to a row in a table via an indexdue to a consistent read problem.Workaround:Disabling rowCR (which is an optimization to reduce consistent-readrollbacks during queries) by setting "_row_cr"&＃61;FALSE in theinitialization files in one workaround. However, this could causeperformance degradation of queries - the statistics "RowCR hits" /"RowCR attempts" can help show if this workaround may be detrimentalto performance.2) based on the fact it is hit on partitioned tablesBug 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 onthe same table is run on separate RAC instances, index corruption mighthappen where the index is missing some keys. This is only pertaining tocomposite partitioned indexes.Wrong results or the next errors can be produced by SQL statements:ORA-8102 by a delete/updateORA-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 indexonline 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&＃61;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_64and for Bug 8546356 the one-off patch is done for 11.2.0.1.2There 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 producingORA-600 errors or other external like ORA-8102 by an update/delete,ORA-1499 by "analyze table validate structure cascade".
这个case仍在继续&＃xff0c;to be continued .....
相关文章 | Related posts:
- ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [729], [10992], [SPACE LEAK] Example
- ORA-00600 [3756]内部错误一例
- ORA-600 internal error[kqrfrpo]一例
- ORA-07445: [__lwp_kill()&＃43;8] [SIGIOT]错误一例
- ORA-00600 [4400][48]错误一例
- ora-00600:[17281], [1001]一例
- ORA-00600 [KCBZPB_1], [59033077], [4], [1], [] example
- ORA-00600 [kcbz_check_objd_typ_3]错误一例
京公网安备 11010802041100号
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
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
Code
SubComponent: Performance Tools
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
DETAILED PROBLEM DESCRIPTION
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
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
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
“kewastUnPackStats(): bad magic 1 (0x110a1315b, 0)” still occurs after Patch
8730312 is applied.
WORKAROUND?
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
No
TECHNICAL IMPACT
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
“kewastUnPackStats(): bad magic 1 (0x110a1315b, 0)” written to alert.log
RELATED ISSUES (bugs, forums, RFAs)
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
BUG 8730312
HOW OFTEN DOES THE ISSUE REPRODUCE AT CUSTOMER SITE?
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
Always
DOES THE ISSUE REPRODUCE INTERNALLY?
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
Never
IS A TESTCASE AVAILABLE?
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
No
Link to IPS Package:
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
NA
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:
update /*&＃43; index (a dbarnes.events_tmp_308235_idx1) */ dbarnes.events_tmp_308235 a
set url_perm_id &＃61; (select /*&＃43; INDEX (xt_outbound_pk) */ url_perm_id
from CONFUSEDCOM.t_outbound x
where x.mailgroup_id &＃61; a.mailgroup_id
and x.cid &＃61; a.cid
and x.instance_id &＃61; a.instance_id )
where client_id &＃61; :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
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
Plan Table
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
—————————————————————-&＃43;————-
———————-&＃43;—————&＃43;
| Id | Operation | Name | Rows |
Bytes | Cost | Time | Pstart| Pstop |
—————————————————————-&＃43;————-
———————-&＃43;—————&＃43;
| 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 | | |
—————————————————————-&＃43;————-
———————-&＃43;—————&＃43;
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&＃61;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.
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 &＃61; 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 &＃61; 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&＃61;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 .
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. 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.
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
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.ora add the following event and bounce database:
event&＃61;"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:’,&＃39;’);
SQL> execute dbms_repair.skip_corrupt_blocks(‘
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 block ; /** a trace file will be generated under udump