### 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

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

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    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>
```

oracle I_OBJ4 ORA-8102問題分析及異常恢復

相關文章