当前位置: DBARoad > 故障案例, 本站推荐 > 文章正文

一次ORA-08102原因分析及处理

                    作者 ：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
                    链接：http://www.dbaroad.me/archives/2009/06/ora_08102.html

执行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: opcode=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: opcode=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 Optimizer=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 obj=0 op='SORT AGGREGATE '
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=6958 op='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 Optimizer=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，没有再报错。

— The End —

关键字:
上一篇 只读用户造成的阻塞
拓展小记 下一篇
喜欢DBARoad的文章，那就通过 RSS Feed 功能订阅阅读吧！

相关文章

随机文章

我要评论

*

* 绝不会泄露


相关文章

最新文章

  • 订阅


      抓虾
      google reader
      my yahoo
      bloglines
      鲜果
      有道

站内搜索



返回首页 | 关于我 | 联系我 | 广告合作 | 网站地图 | 友情链接 | 版权声明 |