### 1. 内容概述
```c
   ORA-8102问题常见于索引键值与表上存的值不一致，可能是ORACLE的bug或硬件I/O错误所引起，
本文模拟I_OBJ4与obj$索引键值与表不一致，造成create table失败的场景。
[oracle@sourcedb enmo]$ oerr ora 8102
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
[oracle@sourcedb enmo]$
```
### 2. 故障模拟
```c
SQL> select max(dataobj#) from obj$;

MAX(DATAOBJ#)
-------------
	13780

SQL> col NAME for a20
SQL> select obj#,dataobj#,name from obj$ where dataobj#=13780;

OBJ#   DATAOBJ# NAME
---------- ---------- --------------------
	 1	13780 _NEXT_OBJECT

SQL>
SQL> select 
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) row_number from obj$ where dataobj#=13780;  2    3    4    5

OBJECT_ID    FILE_ID	BLOCK_ID ROW_NUMBER
---------- ---------- ---------- ----------
	18	    1	     241	 27

SQL>

[oracle@sourcedb bbed]$ ./l_bbed.sh

BBED: Release 2.0.0.0.0 - Limited Production on Sun Jun 14 21:50:34 2020

************* !!! For Oracle Internal Use only !!! ***************

BBED> set dba 1,241
	DBA            	0x004000f1 (4194545 1,241)

BBED> p *kdbr[27]
rowdata[0]
----------
ub1 rowdata[0]                              @1159     0x2c

BBED> x /rnnncn
rowdata[0]                                  @1159    
----------
flag@1159: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1160: 0x01
cols@1161:   18

col    0[2] @1162: 1 
col    1[4] @1165: 13780     <--  原值： _NEXT_OBJECT(DATAOBJ#): 13780
col    2[1] @1170: 0 
col   3[12] @1172: _NEXT_OBJECT
col    4[2] @1185: 1 
col    5[0] @1188: *NULL*
col    6[1] @1189: 0 
col    7[7] @1191: ######################################### 
col    8[7] @1199: ######################################### 
col    9[7] @1207: ######################################### 
col   10[1] @1215: 0 
col   11[0] @1217: *NULL*
col   12[0] @1218: *NULL*
col   13[1] @1219: 0 
col   14[0] @1221: *NULL*
col   15[1] @1222: 0 
col   16[4] @1224: 65535 
col   17[1] @1229: 0

BBED>

BBED> d offset 1165 count 12
 File: /data2/enmo/system01.dbf (1)
 Block: 241              Offsets: 1165 to 1176           Dba:0x004000f1
------------------------------------------------------------------------
 04c30226 5101800c 5f4e4558

<32 bytes per line>

BBED> d offset 1169 count 12
 File: /data2/enmo/system01.dbf (1)
 Block: 241              Offsets: 1169 to 1180           Dba:0x004000f1
------------------------------------------------------------------------
 5101800c 5f4e4558 545f4f42

<32 bytes per line>

BBED> m /x 50 offset 1169
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /data2/enmo/system01.dbf (1)
 Block: 241              Offsets: 1169 to 1180           Dba:0x004000f1
------------------------------------------------------------------------
 5001800c 5f4e4558 545f4f42

<32 bytes per line>

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

BBED> p *kdbr[27]
rowdata[0]
----------
ub1 rowdata[0]                              @1159     0x2c

BBED> x /rnnncn
rowdata[0]                                  @1159    
----------
flag@1159: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1160: 0x01
cols@1161:   18

col    0[2] @1162: 1 
col    1[4] @1165: 13779   <--  修改值： _NEXT_OBJECT(DATAOBJ#): 13780
col    2[1] @1170: 0 
col   3[12] @1172: _NEXT_OBJECT
col    4[2] @1185: 1 
col    5[0] @1188: *NULL*
col    6[1] @1189: 0 
col    7[7] @1191: ######################################### 
col    8[7] @1199: ######################################### 
col    9[7] @1207: ######################################### 
col   10[1] @1215: 0 
col   11[0] @1217: *NULL*
col   12[0] @1218: *NULL*
col   13[1] @1219: 0 
col   14[0] @1221: *NULL*
col   15[1] @1222: 0 
col   16[4] @1224: 65535 
col   17[1] @1229: 0

BBED>

alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush shared_pool;
alter system flush buffer_cache;

SQL> create table test8102 as select * from tab$ where rownum=1;
create table test8102 as select * from tab$ where rownum=1
                                       *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 39, file 1, block 27923 (2)

SQL>
```
### 3. 问题分析
```c
SQL> alter session set db_file_multiblock_read_count=1;
oradebug setmypid
oradebug event 10046 trace name context forever,level 12
Session altered.

SQL> Statement processed.
SQL> 
Statement processed.
SQL> create table test8102 as select * from tab$ where rownum=1;
create table test8102 as select * from tab$ where rownum=1
                                       *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 39, file 1, block 27923 (2)

SQL> oradebug event 10046 trace name context off
oradebug tracefile_nameStatement processed.
SQL> 
/u01/app/oracle/diag/rdbms/enmo/enmo/trace/enmo_ora_3295.trc
SQL>
```
#### 3.1 获取obj$表ddl
```c
SQL> set long 10000
set linesize 200 pagesize 1000
SELECT DBMS_METADATA.GET_DDL('TABLE','OBJ$','SYS') FROM DUAL;SQL> SQL>

DBMS_METADATA.GET_DDL('TABLE','OBJ$','SYS')
-----------------------------------------------

CREATE TABLE "SYS"."OBJ$"
  
   (	"OBJ#" NUMBER NOT NULL ENABLE,
	"DATAOBJ#" NUMBER,
	"OWNER#" NUMBER NOT NULL ENABLE,
	"NAME" VARCHAR2(30) NOT NULL ENABLE,
	"NAMESPACE" NUMBER NOT NULL ENABLE,
	"SUBNAME" VARCHAR2(30),
	"TYPE#" NUMBER NOT NULL ENABLE,
	"CTIME" DATE NOT NULL ENABLE,
	"MTIME" DATE NOT NULL ENABLE,
	"STIME" DATE NOT NULL ENABLE,
	"STATUS" NUMBER NOT NULL ENABLE,
	"REMOTEOWNER" VARCHAR2(30),
	"LINKNAME" VARCHAR2(128),
	"FLAGS" NUMBER,
	"OID$" RAW(16),
	"SPARE1" NUMBER,
	"SPARE2" NUMBER,
	"SPARE3" NUMBER,
	"SPARE4" VARCHAR2(1000),
	"SPARE5" VARCHAR2(1000),
	"SPARE6" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

SQL>
```
#### 3.2 获取I_OBJ4索引ddl
```c
--SELECT DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,'SYS') FROM dba_indexes where owner='SYS' AND TABLE_NAME='OBJ$';
SQL> select obj#,sql_text from bootstrap$ where obj#=39;

OBJ#   SQL_TEXT
----------   --------------------------------------------------------------------------------
   39
CREATE INDEX I_OBJ4 ON OBJ$(DATAOBJ#,TYPE#,OWNER#) PCTFREE 10 INITRANS 2 MAXTRANS 255 
STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTIN
CREASE 0 OBJNO 39 EXTENTS (FILE 1 BLOCK 360))
```
#### 3.3 10046分析
```c
 PARSING IN CURSOR #46998499789648 len=235 dep=1 uid=0 oct=6 lid=0 tim=1592143427229931 hv=159997841 ad='a67b9b88' sqlid='4yyb4104skrwj'
    179 update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteow        ner is null and linkname is null and subname is null
    180 END OF STMT

242  Bind#12
    243   oacdty=01 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00
    244   oacflg=10 fl2=0001 frm=01 csi=01 siz=32 off=0
    245   kxsbbbfp=a67d8706  bln=32  avl=12  flg=09
    246   value="_NEXT_OBJECT"
    247  Bind#13
    248   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
    249   oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
    250   kxsbbbfp=2abeb0ce74f0  bln=22  avl=02  flg=05
    251   value=1
    252 oer 8102.2 - obj# 39, rdba: 0x00406d13(afn 1, blk# 27923)
    253 kdk key 8102.2:
    254   ncol: 4, len: 16
    255   key: (16):  04 c3 02 26 50 01 80 01 80 06 00 40 00 f1 00 1b   <--下面对这行进行解释
    256   mask: (4096):
    
04(DATAOBJ# 长度) c3 02 26 50: 
[oracle@sourcedb ~]$ ora_num c3 02 26 50
13779

[oracle@sourcedb ~]$

01(TYPE# 长度): 80

[oracle@sourcedb ~]$ ora_num 80
0
[oracle@sourcedb ~]$

01(OWNER# 长度): 80

[oracle@sourcedb ~]$ ora_num 80
0
[oracle@sourcedb ~]$

06 00 40 00 f1 00 1b   <-- rdba: 0x004000f1   row_number: 001b  (十进制： 27)

[oracle@sourcedb ~]$ ora_rdba 0x004000f1
*******Welcome to use ora_rdba tool authored by orastar.*******
*******weixin: xidoublestar*******
rdba is: 0x4000f1
datafile# is: 1
datablock is: 241
dump command:alter system dump datafile 1 block 241;
[oracle@sourcedb ~]$
```

#### 3.4 索引键值与表对比
```c
SQL> select /*+ full(a)*/DATAOBJ#,TYPE#,OWNER# from obj$ a
minus
select /*+ index(b i_obj4)*/DATAOBJ#,TYPE#,OWNER# from obj$ b;  2    3

DATAOBJ#	TYPE#	  OWNER#
---------- ---------- ----------
     13779	    0	       0

SQL>

SQL> select /*+ index(b i_obj4)*/DATAOBJ#,TYPE#,OWNER# from obj$ b
minus
select /*+ full(a)*/DATAOBJ#,TYPE#,OWNER# from obj$ a;  2    3

DATAOBJ#	TYPE#	  OWNER#
---------- ---------- ----------
     13780	    0	       0

SQL>
```
#### 3.5 分析小结
```c
    obj$表中 file:1 block: 241,row_num: 27  中存储值为： 13779，
    i_obj4索引中存储值为： 13780，造成ORA-08102: index key not found报错。

###4. 问题恢复
    将obj$和i_obj4存储的键值修改一致都可以修复该问题，本文采用修改obj$中的值修复该问题，
[oracle@sourcedb bbed]$ ./l_bbed.sh

BBED: Release 2.0.0.0.0 - Limited Production on Sun Jun 14 23:29:18 2020

************* !!! For Oracle Internal Use only !!! ***************

BBED> set dba 1,241   
	DBA            	0x004000f1 (4194545 1,241)

BBED> p *kdbr[27]
rowdata[0]
----------
ub1 rowdata[0]                              @1159     0x2c

BBED> x /rnncnn
rowdata[0]                                  @1159    
----------
flag@1159: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1160: 0x00
cols@1161:   18

col    0[2] @1162: 1 
col    1[4] @1165: 13779 
col    2[1] @1170: .
col   3[12] @1172: ######################################### 
col    4[2] @1185: 1 
col    5[0] @1188: *NULL*
col    6[1] @1189: 0 
col    7[7] @1191: ######################################### 
col    8[7] @1199: ######################################### 
col    9[7] @1207: ######################################### 
col   10[1] @1215: 0 
col   11[0] @1217: *NULL*
col   12[0] @1218: *NULL*
col   13[1] @1219: 0 
col   14[0] @1221: *NULL*
col   15[1] @1222: 0 
col   16[4] @1224: 65535 
col   17[1] @1229: 0

BBED> x /rnnncnn
rowdata[0]                                  @1159    
----------
flag@1159: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1160: 0x00
cols@1161:   18

col    0[2] @1162: 1 
col    1[4] @1165: 13779 
col    2[1] @1170: 0 
col   3[12] @1172: _NEXT_OBJECT
col    4[2] @1185: 1 
col    5[0] @1188: *NULL*
col    6[1] @1189: 0 
col    7[7] @1191: ######################################### 
col    8[7] @1199: ######################################### 
col    9[7] @1207: ######################################### 
col   10[1] @1215: 0 
col   11[0] @1217: *NULL*
col   12[0] @1218: *NULL*
col   13[1] @1219: 0 
col   14[0] @1221: *NULL*
col   15[1] @1222: 0 
col   16[4] @1224: 65535 
col   17[1] @1229: 0

BBED> d offset 1165 count 12
 File: /data2/enmo/system01.dbf (1)
 Block: 241              Offsets: 1165 to 1176           Dba:0x004000f1
------------------------------------------------------------------------
 04c30226 5001800c 5f4e4558

<32 bytes per line>

BBED> d offset 1169 count 12
 File: /data2/enmo/system01.dbf (1)
 Block: 241              Offsets: 1169 to 1180           Dba:0x004000f1
------------------------------------------------------------------------
 5001800c 5f4e4558 545f4f42

<32 bytes per line>

BBED> m /x 51 offset 1169
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /data2/enmo/system01.dbf (1)
 Block: 241              Offsets: 1169 to 1180           Dba:0x004000f1
------------------------------------------------------------------------
 5101800c 5f4e4558 545f4f42

<32 bytes per line>

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

BBED> x /rnnncnn
rowdata[10]                                 @1169    
-----------
flag@1169: 0x51 (KDRHFN, KDRHFD, KDRHFC)
lock@1170: 0x01
cols@1171:    0
ckix@1172:   12

BBED> p *kdbr[27]
rowdata[0]
----------
ub1 rowdata[0]                              @1159     0x2c

BBED> x /rnnncnn
rowdata[0]                                  @1159    
----------
flag@1159: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1160: 0x00
cols@1161:   18

col    0[2] @1162: 1 
col    1[4] @1165: 13780 
col    2[1] @1170: 0 
col   3[12] @1172: _NEXT_OBJECT
col    4[2] @1185: 1 
col    5[0] @1188: *NULL*
col    6[1] @1189: 0 
col    7[7] @1191: ######################################### 
col    8[7] @1199: ######################################### 
col    9[7] @1207: ######################################### 
col   10[1] @1215: 0 
col   11[0] @1217: *NULL*
col   12[0] @1218: *NULL*
col   13[1] @1219: 0 
col   14[0] @1221: *NULL*
col   15[1] @1222: 0 
col   16[4] @1224: 65535 
col   17[1] @1229: 0

BBED>
```

###4. 验证测试
```c
SQL> alter system flush shared_pool;
alter system flush buffer_cache;
System altered.

SQL>

System altered.

SQL> 
SQL> 
SQL> alter system flush shared_pool;
alter system flush buffer_cache;
System altered.

SQL>

System altered.

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> create table test8102 as select * from tab$ where rownum=1;

Table created.

SQL>
```

ITPub博客

oracle I_OBJ4 ORA-8102问题分析及异常恢复

最新文章