ORA-08102原因分析及处理
执行truncate partition时报错：
SQL> alter table EC_IOM_OCMDRDER_TBL
truncate partition E_I_O_T_2009_APR update global indexes
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 6958, dba 113254602 (2)
看样子是索引出问题了，根据报错的obj# 6958，找到索引的信息：
SQL> select object_id,data_object_id,object_name,object_type,status
2 from dba_objects where object_id=6958;
OBJECT_ID DATA_OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- -------------- --------------------------- -------------- -------
6958 7769 EC_IOM_OCMDRDER_TYPE_IX INDEX VALID
根据dba找到叶子节点对应的文件号、块号：
SQL> select
2 dbms_utility.data_block_address_file(113254602) file_id#,
3 dbms_utility.data_block_address_block(113254602) block_id#
4 from dual;
FILE_ID# BLOCK_ID#
---------- ----------
27 8394
Dump这个叶子节点（27,8394）：
Start dump data blocks tsn: 4 file#: 27 minblk 8394 maxblk 8394
buffer tsn: 4 rdba: 0x06c020ca (27/8394)
scn: 0x09c8.4060b320 seq: 0x01 flg: 0x06 tail: 0xb3200601
frmt: 0x02 chkval: 0xcc67 type: 0x06=trans data
Block header dump: 0x06c020ca
Object id on Block? Y
seg/obj: 0x1e56 csc: 0x9c8.caab481 itc: 20 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x6c0208a ver: 0x01
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.029.000943d5 0x08c02650.1a3b.01 -BU- 1 fsc 0x0000.1efc6530
0x02 0x0001.025.000be8f9 0x00c01240.2db0.1e --U- 0 fsc 0x0000.1efba692
0x03 0x0001.024.000be950 0x09000366.2db1.1c --U- 1 fsc 0x0000.1efbc628
0x04 0x0005.01e.00098c39 0x00c01b7f.2755.1e --U- 0 fsc 0x0000.1efbf63a
0x05 0x0001.012.000be982 0x00800da5.2dbb.1e --U- 1 fsc 0x0000.1efc6524
0x06 0x0002.02d.000944cd 0x08c0264e.1a3b.20 --U- 1 fsc 0x0000.1efc6533
0x07 0x0006.00d.0009bff7 0x00801023.2e4e.19 --U- 1 fsc 0x0000.1efcf6bb
0x08 0x0004.020.000962bc 0x00c00ce7.2618.14 --U- 1 fsc 0x0000.4060b320
0x09 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0a 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0b 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0c 0x0002.005.00094363 0x08c02654.1a0d.21 --U- 1 fsc 0x0000.0caab484
0x0d 0x0001.001.000be80e 0x0900036a.2d78.1b --U- 0 fsc 0x0000.0cabab6b
0x0e 0x0001.00d.000be7f0 0x00807b10.2d6c.1d --U- 1 fsc 0x0000.0caad2b5
0x0f 0x0008.013.000a1094 0x08c07864.34ed.14 --U- 1 fsc 0x0000.0cabe6a3
0x10 0x0008.01d.000a1018 0x09000ee9.34ee.18 --U- 1 fsc 0x0000.0cabed97
0x11 0x0009.013.000a4aa6 0x08c0018b.38fb.18 --U- 1 fsc 0x0000.0cac0b0b
0x12 0x0006.02f.0009bf04 0x09003e20.2e41.20 --U- 1 fsc 0x0000.1efa79a8
0x13 0x0007.001.0009d7a6 0x08c01e59.3451.18 --U- 0 fsc 0x0000.1efa9a33
0x14 0x0002.01a.0009443e 0x00800d25.1a28.14 --U- 0 fsc 0x0000.1efaa065
Leaf block dump
===============
header address 9223372041151224340=0x8000000100169a14
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 177
kdxcofbo 390=0x186
kdxcofeo 4044=0xfcc
kdxcoavs 3654
kdxlespl 0
kdxlende 0
kdxlenxt 117440849=0x7000151
kdxleprv 113254605=0x6c020cd
kdxledsz 0
kdxlebksz 7584
row#0[4084] flag: ----S, lock: 15
col 0; len 6; (6): c5 02 09 24 19 02
col 1; len 10; (10): 00 00 1c 7a 07 80 29 cb 00 03
row#1[4104] flag: -----, lock: 0
col 0; len 6; (6): c5 02 09 24 19 08
col 1; len 10; (10): 00 00 1c 7a 07 40 21 01 00 8c
row#2[4124] flag: -----, lock: 0
col 0; len 6; (6): c5 02 09 24 19 0b
col 1; len 10; (10): 00 00 1c 7a 07 40 20 d3 00 13
................................
................................
省略部份内容
................................
................................
这个叶子节点的前后叶子节点为：
kdxlenxt 117440849=0x7000151
kdxleprv 113254605=0x6c020cd
再来看看索引的treedump：
----- begin tree dump
branch: 0x3881c95 59251861 (0: nrow: 4, level: 2)
branch: 0x7402077 121643127 (-1: nrow: 349, level: 1)
leaf: 0x3881c96 59251862 (-1: nrow: 320 rrow: 320)
leaf: 0x3881c97 59251863 (0: nrow: 320 rrow: 320)
leaf: 0x3881c98 59251864 (1: nrow: 320 rrow: 320)
leaf: 0x3c01a79 62921337 (2: nrow: 320 rrow: 320)
leaf: 0x3c01a7a 62921338 (3: nrow: 320 rrow: 320)
leaf: 0x3c01a7b 62921339 (4: nrow: 320 rrow: 320)
leaf: 0x3c01a7c 62921340 (5: nrow: 320 rrow: 320)
leaf: 0x3c01a7d 62921341 (6: nrow: 320 rrow: 320)
leaf: 0x3c01a7e 62921342 (7: nrow: 320 rrow: 320)
leaf: 0x3c01a7f 62921343 (8: nrow: 320 rrow: 320)
leaf: 0x3c01a80 62921344 (9: nrow: 320 rrow: 320)
leaf: 0x4085d92 67657106 (10: nrow: 320 rrow: 320)
leaf: 0x4085d93 67657107 (11: nrow: 320 rrow: 320)
leaf: 0x4085d94 67657108 (12: nrow: 320 rrow: 320)
leaf: 0x4085d95 67657109 (13: nrow: 320 rrow: 320)
leaf: 0x4085d96 67657110 (14: nrow: 320 rrow: 320)
leaf: 0x4085d97 67657111 (15: nrow: 320 rrow: 320)
leaf: 0x4085d98 67657112 (16: nrow: 320 rrow: 320)
leaf: 0x501c481 84001921 (17: nrow: 320 rrow: 320)
leaf: 0x501c482 84001922 (18: nrow: 320 rrow: 320)
leaf: 0x501c483 84001923 (19: nrow: 320 rrow: 320)
leaf: 0x501c484 84001924 (20: nrow: 320 rrow: 320)
leaf: 0x501c485 84001925 (21: nrow: 320 rrow: 320)
leaf: 0x501c486 84001926 (22: nrow: 320 rrow: 320)
leaf: 0x501c487 84001927 (23: nrow: 320 rrow: 320)
leaf: 0x501c488 84001928 (24: nrow: 320 rrow: 320)
leaf: 0x6c020ca 113254602 (25: nrow: 177 rrow: 177)
leaf: 0x6c020cb 113254603 (26: nrow: 191 rrow: 191)
leaf: 0x6c020cc 113254604 (27: nrow: 277 rrow: 277)
leaf: 0x6c020cd 113254605 (28: nrow: 283 rrow: 283)
leaf: 0x6c020ce 113254606 (29: nrow: 332 rrow: 332)
................................
................................
省略部份内容
................................
................................
在TREEDUMP中可以找到113254602、113254605这两个叶子节点，而找不到117440849这个叶子节点：
$ cat tadba_ora_3477.trc | grep 113254602
leaf: 0x6c020ca 113254602 (25: nrow: 177 rrow: 177)
$ cat tadba_ora_3477.trc | grep 117440849
$ cat tadba_ora_3477.trc | grep 113254605
leaf: 0x6c020cd 113254605 (28: nrow: 283 rrow: 283)
117440849是113254602的下一个叶子节点，所以报错很可能就是找不到117440849这个叶子节点的原因了。
再来dump下117440849这个leaf block（截取部份内容）：
SQL> select
2 dbms_utility.data_block_address_file(117440849) file_id#,
3 dbms_utility.data_block_address_block(117440849) block_id#
4 from dual;
FILE_ID# BLOCK_ID#
---------- ----------
28 337
Start dump data blocks tsn: 4 file#: 28 minblk 337 maxblk 337
buffer tsn: 4 rdba: 0x07000151 (28/337)
scn: 0x09c8.eb152c39 seq: 0x01 flg: 0x06 tail: 0x2c390601
frmt: 0x02 chkval: 0x0641 type: 0x06=trans data
Block header dump: 0x07000151
Object id on Block? Y
seg/obj: 0x1e56 csc: 0x9c8.eb152c36 itc: 20 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x700010a ver: 0x01
inc: 0 exflg: 0
Leaf block dump
===============
header address 9223372041151224340=0x8000000100169a14
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 170
kdxcofbo 376=0x178
kdxcofeo 4185=0x1059
kdxcoavs 3809
kdxlespl 0
kdxlende 0
kdxlenxt 113254652=0x6c020fc
kdxleprv 113254602=0x6c020ca
kdxledsz 0
kdxlebksz 7584
row#0[4205] flag: -----, lock: 0
col 0; len 6; (6): c5 02 09 24 3c 0d
col 1; len 10; (10): 00 00 1c 7a 07 40 20 ca 00 3a
从DUMP中我们可以看出，这个叶子节点对应的索引的确是（seg/obj: 0×1e56 －> data_object_id 7769）报错的索引。而且丢失的叶子节点还不止一个，117440849的下一个叶子节点：kdxlenxt 113254652=0×6c020fc 在Treedump中也找不到：
$ cat tadba_ora_3477.trc | grep 113254652
既然叶子节点都找不到，那通过全索引扫描，准得报错了吧：
SQL> set autot trace exp
SQL> select count(*)
2 from ROCKEY.EC_IOM_OCMDRDER_TBL
3 where order_type = 'AAA';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=76 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'EC_IOM_OCMDRDER_TYPE_IX' (NON
-UNIQUE) (Cost=76 Card=102000 Bytes=714000)
SQL> set autotrace off
SQL> select count(*)
2 from ROCKEY.EC_IOM_OCMDRDER_TBL
3 where order_type = 'AAA';
select count(*)
*
ERROR at line 1:
ORA-01410: invalid ROWID
为了便于观察读取到哪个BLOCK时报错，我们可以走Index full scan，再通过10046来TRACE，郁闷的是，无论怎么改条件，加HINT都没能走Index full scan，只好转变方法，将db_file_multiblock_read_count改为1,再来观察：
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events 'immediate trace name flush_cache';
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> select /*+ INDEX_FFS(T,EC_IOM_OCMDRDER_TYPE_IX) */ count(*)
2 from ROCKEY.EC_IOM_OCMDRDER_TBL T
3 where order_type='AAA';
from ROCKEY.EC_IOM_OCMDRDER_TBL T
*
ERROR at line 2:
ORA-01410: invalid ROWID
$ cat tadba_ora_14561.trc | grep "db file s"
WAIT #1: nam='db file sequential read' ela= 8505 p1=14 p2=531604 p3=1
WAIT #1: nam='db file sequential read' ela= 288 p1=14 p2=531606 p3=1
WAIT #1: nam='db file sequential read' ela= 871 p1=14 p2=531607 p3=1
WAIT #1: nam='db file sequential read' ela= 26731 p1=14 p2=531608 p3=1
WAIT #1: nam='db file sequential read' ela= 458 p1=15 p2=6777 p3=1
WAIT #1: nam='db file sequential read' ela= 268 p1=15 p2=6778 p3=1
WAIT #1: nam='db file sequential read' ela= 267 p1=15 p2=6779 p3=1
WAIT #1: nam='db file sequential read' ela= 267 p1=15 p2=6780 p3=1
WAIT #1: nam='db file sequential read' ela= 266 p1=15 p2=6781 p3=1
WAIT #1: nam='db file sequential read' ela= 268 p1=15 p2=6782 p3=1
WAIT #1: nam='db file sequential read' ela= 269 p1=15 p2=6783 p3=1
WAIT #1: nam='db file sequential read' ela= 838 p1=15 p2=6784 p3=1
WAIT #1: nam='db file sequential read' ela= 7501 p1=16 p2=548242 p3=1
WAIT #1: nam='db file sequential read' ela= 6170 p1=16 p2=548243 p3=1
WAIT #1: nam='db file sequential read' ela= 514 p1=16 p2=548244 p3=1
WAIT #1: nam='db file sequential read' ela= 268 p1=16 p2=548245 p3=1
WAIT #1: nam='db file sequential read' ela= 270 p1=16 p2=548246 p3=1
WAIT #1: nam='db file sequential read' ela= 267 p1=16 p2=548247 p3=1
WAIT #1: nam='db file sequential read' ela= 10240 p1=16 p2=548248 p3=1
WAIT #1: nam='db file sequential read' ela= 11294 p1=20 p2=115841 p3=1
WAIT #1: nam='db file sequential read' ela= 1742 p1=20 p2=115842 p3=1
WAIT #1: nam='db file sequential read' ela= 267 p1=20 p2=115843 p3=1
WAIT #1: nam='db file sequential read' ela= 3839 p1=20 p2=115844 p3=1
WAIT #1: nam='db file sequential read' ela= 267 p1=20 p2=115845 p3=1
WAIT #1: nam='db file sequential read' ela= 270 p1=20 p2=115846 p3=1
WAIT #1: nam='db file sequential read' ela= 268 p1=20 p2=115847 p3=1
WAIT #1: nam='db file sequential read' ela= 270 p1=20 p2=115848 p3=1
WAIT #1: nam='db file sequential read' ela= 365 p1=27 p2=8394 p3=1
STAT #1 id=1 cnt=0 pid=0 pos=1 bj=0 p='SORT AGGREGATE '
STAT #1 id=2 cnt=0 pid=1 pos=1 bj=6958 p='INDEX FAST FULL SCAN OBJ#(6958)
SQL> select segment_name, extent_id, file_id, block_id, blocks
2 from dba_extents
3 where segment_name = 'EC_IOM_OCMDRDER_TYPE_IX' and rownum<10;
SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
-------------------------- ---------- ---------- ---------- ----------
EC_IOM_OCMDRDER_TYPE_IX 0 14 531601 8
EC_IOM_OCMDRDER_TYPE_IX 1 15 6777 8
EC_IOM_OCMDRDER_TYPE_IX 2 16 548241 8
EC_IOM_OCMDRDER_TYPE_IX 3 20 115841 8
EC_IOM_OCMDRDER_TYPE_IX 4 27 8393 8
EC_IOM_OCMDRDER_TYPE_IX 5 32 1083433 8
EC_IOM_OCMDRDER_TYPE_IX 6 37 1298841 8
EC_IOM_OCMDRDER_TYPE_IX 7 38 1324905 8
EC_IOM_OCMDRDER_TYPE_IX 8 42 575817 8
9 rows selected.
SQL> select tablespace_name,segment_space_management
2 from dba_tablespaces
3 where tablespace_name='EPPR1_DATA';
TABLESPACE_NAME SEGMEN
------------------------------ ------
EPPR1_DATA AUTO
SQL> select SEGMENT_NAME,SEGMENT_TYPE,HEADER_BLOCK
2 from dba_segments
3 where SEGMENT_NAME='EC_IOM_OCMDRDER_TYPE_IX';
SEGMENT_NAME SEGMENT_TYPE HEADER_BLOCK
-------------------------- ------------------ ------------
EC_IOM_OCMDRDER_TYPE_IX INDEX 531604
读到file id为27，block id为8394的块时，就中断了，而这个BLOCK正是报错的提示的BLOCK，dba 113254602。
注：INDEX FAST FULL SCAN是按照dba_extents来读取的，虽然有的块在10046中没观察到，不过大部份还是能对应上的。
至此，大致可以判断原因就是113254602叶子节点损坏，其下一个叶子节点117440849丢失造成的了。
看来要重建索引了。既然通过INDEX FAST FULL SCAN会报错，那么直接rebuild肯定不行了，因为rebuild走的就是INDEX FAST FULL SCAN：
SQL> alter index ROCKEY.EC_IOM_OCMDRDER_TYPE_IX rebuild nologging;
alter index ROCKEY.EC_IOM_OCMDRDER_TYPE_IX rebuild nologging
*
ERROR at line 1:
ORA-01410: invalid ROWID
索引比较小，直接rebuild online：
SQL> alter index ROCKEY.EC_IOM_OCMDRDER_TYPE_IX rebuild
compute statistics online;
Index altered.
SQL> select /*+ index_ffs(t EC_IOM_OCMDRDER_TYPE_IX) */ count(*)
2 from ROCKEY.EC_IOM_OCMDRDER_TBL
3 where order_type = 'AAA';
COUNT(*)
----------
0
SQL> set autot trace exp
SQL> select /*+ index_ffs(t EC_IOM_OCMDRDER_TYPE_IX) */ count(*)
2 from ROCKEY.EC_IOM_OCMDRDER_TBL
3 where order_type = 'AAA';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=102 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'EC_IOM_OCMDRDER_TYPE_IX' (NON
-UNIQUE) (Cost=102 Card=111819 Bytes=559095)
重建完索引后，再执行truncate partition，没有再报错。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/13165828/viewspace-607755/ ，如需转载，请注明出处，否则将追究法律责任。
