ORA-08102: index key not found

关注

原创最新推荐文章于 2026-03-01 02:09:37 发布 3.1k 阅读 AI 写同款· GEO 优化 ›

创建索引时出现这问题：

SQL> alter table t add primary key(id);
alter table t add primary key(id)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 52, file 1, block 77798 (2)

官方解释：

[oracle@centos6 ~]$ oerr ora 08102
08102, 00000, "index key not found, obj# %s, file %s, block %s (%s)"
// *Cause:  Internal error: possible inconsistency in index
// *Action:  Send trace file to your customer support representative, along
//           with information on reproducing the error

MOS(ID 1088018.1)上对ORA-08102错误的定义为：

An ORA-08102 indicates that there is a mismatch between the key(s) stored in the index and the values stored in the table.Wh
at typically happens in the index is built and at some future time,some type of corruption occurs,either in the table or index,to cause the mismatch.

ORA-08102常见于索引键值与表上存的值不一致。

产生原因：

a. Oracle bug

b. Block corruption in the index or in the table

c. Hard ware /IO

d. Function-based indexes(FBI)

接下来记录下对此错误的分析过程

1. 查看产生的trace文件

[oracle@centos6 trace]$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@centos6 trace]$ cat alert_orcl.log
Sat Nov 19 12:06:36 2016
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3576.trc:
[oracle@centos6 trace]$ vim /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3576.trc

trace文件内容分析：

oer 8102.2 - obj# 52, rdba: 0x00412fe6(afn 1, blk# 77798)
kdk key 8102.2:
  ncol: 1, len: 5
  key: (5):  04 c3 02 10 5a
  mask: (4096):
 81 00 00 00 00 60 61 1a 8e 00 00 00 00 00 00 00 00 00 00 00 00 e0 c9 0c 0c

obj# 52：发生错误的对象为52号对象

rdba: 0x00412fe6(afn 1, blk# 77798)：对象的rba为1号文件，77798号块

ncol:1 ：一共有一个列

len:5 ：列的长度为5个字节

key：(5) ：列的key值为04 c3 02 10 5a，其中04代表行的长度，后面则是列的内容

从列的内容 c3 02 10 5a入手，利用oracle提供的函数将16进制转换为10进制：

SQL> select utl_raw.cast_to_number('c302105a') from dual;

UTL_RAW.CAST_TO_NUMBER('C302105A')
----------------------------------
                             11589

再回看发生错误的信息：

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 52, file 1, block 77798 (2)

oracle在调用递归sql的时候没找到索引键值，因为oracle在创建索引的时候需要找索引键。

2. 产生错误的52#对象

52#对象存在于bootstrap$里面，通过bootstra$找出52号对象

SQL> select * from bootstrap$ where obj#=52;

     LINE#       OBJ#
---------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
        52         52
CREATE UNIQUE INDEX I_CON2 ON CON$(CON#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STOR
AGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 O
BJNO 52 EXTENTS (FILE 1 BLOCK 464))

可以看到52#对象是一个建在Oracle内部表CON$上CON#列上的一个索引。

而且对于52#对象这样的bootstrap$核心对象是无法通过event 38003或migrate模式来重建的。只能使用bbed来修复。

3. 查看CON$

SQL> select user_id,username from dba_users;

   USER_ID USERNAME
---------- ------------------------------
        92 ORACX
        91 APP_SMARTPT_DB
        94 TEST

查出发生错误的用户编号，为94。

SQL> select name,con# from con$ where name='_NEXT_CONSTRAINT';

NAME                                 CON#
------------------------------ ----------
_NEXT_CONSTRAINT                    11589

CON$中，对应着_NEXT_CONSTRAINT，创建索引的时候，要到CON$找出最大的索引键值，即存放在NEXT_CONSTRAINT的这个值11589，也就是再找出这个最大的值时发生了错误。

4. 通过执行计划查看找出11589这个值的过程

使用全表扫描：找出的值为11589

SQL> set autot on
SQL> select  /*+ FULL(t1) */ owner#,name,con# from con$ t1 WHERE NAME='_NEXT_CONSTRAINT';

    OWNER# NAME                                 CON#
---------- ------------------------------ ----------
         0 _NEXT_CONSTRAINT                    11589


Execution Plan
----------------------------------------------------------
Plan hash value: 3767504726

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    24 |    14   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CON$ |     1 |    24 |    14   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME"='_NEXT_CONSTRAINT')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         50  consistent gets
          0  physical reads
          0  redo size
        677  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

使用索引：找到的值为11590

SQL> select  /*+ index(t1 I_CON2) */ owner#,name,con# from con$ t1 WHERE NAME='_NEXT_CONSTRAINT';

    OWNER# NAME                                 CON#
---------- ------------------------------ ----------
         0 _NEXT_CONSTRAINT                    11590


Execution Plan
----------------------------------------------------------
Plan hash value: 1812212496

--------------------------------------------------------------------------------
------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
     |

--------------------------------------------------------------------------------
------

|   0 | SELECT STATEMENT            |        |     1 |    24 |    69   (0)| 00:0
0:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| CON$   |     1 |    24 |    69   (0)| 00:0
0:01 |

|   2 |   INDEX FULL SCAN           | I_CON2 | 11211 |       |    22   (0)| 00:0
0:01 |

--------------------------------------------------------------------------------
------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME"='_NEXT_CONSTRAINT')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         69  consistent gets
          0  physical reads
          0  redo size
        677  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

因为在创建主键的过程中，需要取CON$表CON#列I_CON2索引上面的值，这个值即使我们真正要找的值。

在创建的时候找的是11589，因为11589记录在CON$上面(通过普通的select语句得到)，但获取这个值得时候报错，因为其实需要的是11590。可以理解为CON#这个值由于oracle的bug或者其他原因没有更新进去，导致创建索引时获取到的键值与真正需要的键值不一致而产生ORA-01812这个错误。

接下里的思路就是将11589这个值改成11590。也就是出错的其实不是52号对象这个索引，而是存放11589的CON$这个内部表。

5. 定位CON$位置

找出CON$所在的文件号，块号，行号--》1号文件 289号block 12行

SQL> select con#,name,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,dbms_rowid.rowid_row_number(rowid) row# from con$ where name='_NEXT_CONSTRAINT';

      CON# NAME                                FILE#     BLOCK#       ROW#
---------- ------------------------------ ---------- ---------- ----------
     11589 _NEXT_CONSTRAINT                        1        289         12

6. 查看trace中执行计划

通过搜索找到trace文件中的执行计划

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | UPDATE STATEMENT    |         |       |       |     2 |           |
| 1   |  UPDATE             | CON$    |       |       |       |           |
| 2   |   INDEX UNIQUE SCAN | I_CON1  |     1 |    25 |     1 |  00:00:01 |
--------------------------------------+-----------------------------------+

Content of other_xml column
===========================
  db_version     : 11.2.0.4
  parse_schema   : SYS
  plan_hash      : 2574219287
  plan_hash_2    : 950544504
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OUTLINE_LEAF(@"UPD$1")
      INDEX(@"UPD$1" "CON$"@"UPD$1" ("CON$"."OWNER#" "CON$"."NAME"))
    END_OUTLINE_DATA
  */

在创建主键的时候，oracle是要通过I_CON1索引去更新CON$下的CON#这个字段，然后把这个值赋值给主键，生成一个SYS之类的索引独享。

7. 根据trace文件中的rdba dump出52号对象这个唯一索引

SQL> alter system dump datafile 1 block 77798;

System altered.

SQL> select * from v$diag_info;

trace文件内容：

事务槽

 Object id on Block? Y
 seg/obj: 0x34  csc: 0x00.20835b  itc: 3  flg: O  typ: 2 - INDEX
     fsl: 0  fnx: 0x412fe7 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0007.020.000002bc  0x00c02b8e.0078.01  CB--    0  scn 0x0000.000fd5e0
0x02   0x000a.019.00000574  0x00c007f7.00bd.10  C---    0  scn 0x0000.0020834e
0x03   0x0008.008.00000668  0x00c0084a.01f2.0a  C---    0  scn 0x0000.0020799e

数据内容

row#3[2734] flag: ------, lock: 0, len=13, data:(6):  00 40 01 21 00 0c
col 0; len 4; (4):  c3 02 10 5b
----- end of leaf block dump -----

row#3：行号

data:(6)：00 40 01 21 0c --》实际上这个就为存放数据的rowid。

转换为十进：1号文件，289号块，12行

SQL> select to_number('121','xxxxxxxxxx') from dual;

TO_NUMBER('121','XXXXXXXXXX')
-----------------------------
                          289

c3 02 10 5b即为索引要取的值，转换为十进制为：

SQL> select utl_raw.cast_to_number('c302105b') from dual;

UTL_RAW.CAST_TO_NUMBER('C302105B')
----------------------------------
                             11590

接下来就可以确定，把造成错误的原因就是，CON$中NAME为NEXT_CONSTRAINT，CON#为11590的这个值取不到，因为CON$即1号文件，289号块的第12行记录的为11589。

8. 使用BBED将11589改为11590

将c3 02 10 5a-->c3 02 10 5b即可

BBED> set file 1 block 289
        FILE#           1
        BLOCK#          289

BBED> map /v
 File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
 Block: 289                                   Dba:0x00400121
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       
    ub1 type_kcbh                           @0       
    ub1 frmt_kcbh                           @1       
    ub1 spare1_kcbh                         @2       
    ub1 spare2_kcbh                         @3       
    ub4 rdba_kcbh                           @4       
    ub4 bas_kcbh                            @8       
    ub2 wrp_kcbh                            @12      
    ub1 seq_kcbh                            @14      
    ub1 flg_kcbh                            @15      
    ub2 chkval_kcbh                         @16      
    ub2 spare3_kcbh                         @18      

 struct ktbbh, 72 bytes                     @20      
    ub1 ktbbhtyp                            @20      
    union ktbbhsid, 4 bytes                 @24      
    struct ktbbhcsc, 8 bytes                @28      
    sb2 ktbbhict                            @36      
    ub1 ktbbhflg                            @38      
    ub1 ktbbhfsl                            @39      
    ub4 ktbbhfnx                            @40      
    struct ktbbhitl[2], 48 bytes            @44      

 struct kdbh, 14 bytes                      @92      
    ub1 kdbhflag                            @92      
    sb1 kdbhntab                            @93      
    sb2 kdbhnrow                            @94      
    sb2 kdbhfrre                            @96      
    sb2 kdbhfsbo                            @98      
    sb2 kdbhfseo                            @100     
    sb2 kdbhavsp                            @102     
    sb2 kdbhtosp                            @104     

 struct kdbt[1], 4 bytes                    @106     
    sb2 kdbtoffs                            @106     
    sb2 kdbtnrow                            @108     

 sb2 kdbr[311]                              @110     

 ub1 freespace[302]                         @732     

 ub1 rowdata[7154]                          @1034    

 ub4 tailchk                                @8188    


BBED> p *kdbr[12]
rowdata[0]
----------
ub1 rowdata[0]                              @1034     0x2c

BBED> x/rccnn
rowdata[0]                                  @1034    
----------
flag@1034: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1035: 0x00
cols@1036:    4

col    0[1] @1037: .
col   1[16] @1039: _NEXT_CONSTRAINT
col    2[4] @1056: 11589 
col    3[1] @1061: 0 


BBED> d /v offset 1056 count 16
 File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
 Block: 289     Offsets: 1056 to 1071  Dba:0x00400121
-------------------------------------------------------
 04c30210 5a01802c 00040180 105f4e45 l .?.Z..,....._NE

 <16 bytes per line>

BBED> modify /x 5b offset 1060
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
 Block: 289              Offsets: 1060 to 1075           Dba:0x00400121
------------------------------------------------------------------------
 5b01802c 00040180 105f4e45 58545f43 

 <32 bytes per line>

BBED> sum apply
Check value for File 1, Block 289:
current = 0xba00, required = 0xba00

重启后，即可正常创建索引

附上模拟ORA-08102错误的脚本：

SQL> create table t(id int,name varchar2(100));

Table created.

SQL> begin
  2  for i in 1 .. 5000 loop
  3  insert into t values(i,'test'||i);
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

BBED> set file 1 block 289
        FILE#           1
        BLOCK#          289

BBED> map /v
 File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
 Block: 289                                   Dba:0x00400121
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       
    ub1 type_kcbh                           @0       
    ub1 frmt_kcbh                           @1       
    ub1 spare1_kcbh                         @2       
    ub1 spare2_kcbh                         @3       
    ub4 rdba_kcbh                           @4       
    ub4 bas_kcbh                            @8       
    ub2 wrp_kcbh                            @12      
    ub1 seq_kcbh                            @14      
    ub1 flg_kcbh                            @15      
    ub2 chkval_kcbh                         @16      
    ub2 spare3_kcbh                         @18      

 struct ktbbh, 72 bytes                     @20      
    ub1 ktbbhtyp                            @20      
    union ktbbhsid, 4 bytes                 @24      
    struct ktbbhcsc, 8 bytes                @28      
    sb2 ktbbhict                            @36      
    ub1 ktbbhflg                            @38      
    ub1 ktbbhfsl                            @39      
    ub4 ktbbhfnx                            @40      
    struct ktbbhitl[2], 48 bytes            @44      

 struct kdbh, 14 bytes                      @92      
    ub1 kdbhflag                            @92      
    sb1 kdbhntab                            @93      
    sb2 kdbhnrow                            @94      
    sb2 kdbhfrre                            @96      
    sb2 kdbhfsbo                            @98      
    sb2 kdbhfseo                            @100     
    sb2 kdbhavsp                            @102     
    sb2 kdbhtosp                            @104     

 struct kdbt[1], 4 bytes                    @106     
    sb2 kdbtoffs                            @106     
    sb2 kdbtnrow                            @108     

 sb2 kdbr[311]                              @110     

 ub1 freespace[302]                         @732     

 ub1 rowdata[7154]                          @1034    

 ub4 tailchk                                @8188    


BBED> p *kdbr[12]
rowdata[0]
----------
ub1 rowdata[0]                              @1034     0x2c

BBED> x /rccnn
rowdata[0]                                  @1034    
----------
flag@1034: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1035: 0x02
cols@1036:    4

col    0[1] @1037: .
col   1[16] @1039: _NEXT_CONSTRAINT
col    2[4] @1056: 11590 
col    3[1] @1061: 0 


BBED> d /v offset 1056 count 16
 File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
 Block: 289     Offsets: 1056 to 1071  Dba:0x00400121
-------------------------------------------------------
 04c30210 5b01802c 00040180 105f4e45 l .?.[..,....._NE

 <16 bytes per line>

BBED> modify /x 5a offset 1060
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
 Block: 289              Offsets: 1060 to 1075           Dba:0x00400121
------------------------------------------------------------------------
 5a01802c 00040180 105f4e45 58545f43 

 <32 bytes per line>

BBED> sum apply
Check value for File 1, Block 289:
current = 0xe0de, required = 0xe0de

BBED> p *kdbr[12]
rowdata[0]
----------
ub1 rowdata[0]                              @1034     0x2c

BBED> x /rccnn
rowdata[0]                                  @1034    
----------
flag@1034: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1035: 0x02
cols@1036:    4

col    0[1] @1037: .
col   1[16] @1039: _NEXT_CONSTRAINT
col    2[4] @1056: 11589 
col    3[1] @1061: 0