[20160325]ORA-08102 index key not found

[20160325]ORA-08102 index key not found.txt

-- 一直沒機會測試如果表與索引不一致會出現什麼情況，今天測試看看。做複雜一點，選擇1個系統表con$。

$ 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

1.測試環境：
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> SELECT ROWID, owner#,name,con#  FROM sys.con$ where name='_NEXT_CONSTRAINT';
ROWID                  OWNER# NAME                       CON#
------------------ ---------- -------------------- ----------
AAAAAcAABAAAAEhAAM          0 _NEXT_CONSTRAINT          11940

SCOTT@book> @ &r/rowid AAAAAcAABAAAAEhAAM
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
        28          1        289         12 1,289                alter system dump datafile 1 block 289 ;

SCOTT@book> select owner,index_name,table_owner,table_name from dba_indexes where table_name='CON$';
OWNER  INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
------ ------------------------------ ------------------------------ ----------
SYS    I_CON1                         SYS                            CON$
SYS    I_CON2                         SYS                            CON$

SCOTT@book> select obj#,dataobj#,owner#,name,type# from sys.obj$ where name in ('CON$','I_CON1','I_CON2');
      OBJ#   DATAOBJ#     OWNER# NAME                      TYPE#
---------- ---------- ---------- -------------------- ----------
        28         28          0 CON$                          2
        51         51          0 I_CON1                        1
        52         52          0 I_CON2                        1

--其中索引I_CON2，包含欄位con#。如果破壞這個NAME='_NEXT_CONSTRAINT',讓CON#=11941，這樣索引與表存在不一致，這樣
--如果建立約束時，會報錯。另外注意這些物件obj#<59.不能透過重建來修復。

2.使用bbed修改：
SCOTT@book> select dump(11940,16),dump(11941,16) from dual ;
DUMP(11940,16)          DUMP(11941,16)
----------------------- -----------------------
Typ=2 Len=4: c3,2,14,29 Typ=2 Len=4: c3,2,14,2a

BBED> set dba  1,289
        DBA             0x00400121 (4194593 1,289)

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

BBED> x /rncnnnncct
rowdata[0]                                  @806
----------
flag@806:  0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@807:  0x02
cols@808:     4

col    0[1] @809: 0
col   1[16] @811: _NEXT_CONSTRAINT
col    2[4] @828: 11940
col    3[1] @833: 0

SCOTT@book> @ &r/bbvi    1        289
BVI_COMMAND
------------------------------------------------------------------------------------------
bvi -b 2367488 -s 8192 /mnt/ramdisk/book/system01.dbf

--尋找c3,2,14,29，換成c3,2,14,2a。

BBED> set dba  1,289
        DBA             0x00400121 (4194593 1,289)

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

BBED> x /rncnnnncct
rowdata[0]                                  @806
----------
flag@806:  0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@807:  0x02
cols@808:     4

col    0[1] @809: 0
col   1[16] @811: _NEXT_CONSTRAINT
col    2[4] @828: 11941
col    3[1] @833: 0

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

BBED> sum apply
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Check value for File 1, Block 289:
current = 0x5cb8, required = 0x5cb8

3.測試：
SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> alter table emp modify(ename  not null);
alter table emp modify(ename  not null)
                       *
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)

--已經提示錯誤。obj#=52 就是索引I_CON2。

4.修復：
--因為是系統物件不能重建。
SYS@book> alter index sys.i_con2 rebuild ;
alter index sys.i_con2 rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered

SYS@book> alter index sys.i_con2 unusable;
alter index sys.i_con2 unusable
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered

--不知道是否在upgrade模式可以rebuild，以後測試。
--先確定是那條記錄：

select /*+ full(a) */ con# from sys.con$ a
minus
select /*+ index(b i_con2) */ con# from sys.con$ b

      CON#
----------
     11941

select /*+ index(b i_con2) */ con# from sys.con$ b
minus
select /*+ full(a) */ con# from sys.con$ a

      CON#
----------
     11940

--從以上查詢可以確定表裡面記錄的CON#=11941,索引記錄的是11940.我選擇修改索引看看。

5.bbed修改一致：
SCOTT@book> @ &r/bbvi 1 77798
BVI_COMMAND
-------------------------------------------------------
bvi -b 637321216 -s 8192 /mnt/ramdisk/book/system01.dbf

檢索c3 02 14 29 ，修改為c3 02 14 2a。

BBED> sum
Check value for File 1, Block 77798:
current = 0x39fe, required = 0x39fd

BBED> sum apply
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Check value for File 1, Block 77798:
current = 0x39fd, required = 0x39fd

SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> alter table emp modify(ename  not null);
Table altered.

SCOTT@book> alter table emp modify(ename   null);
Table altered.

--OK現在現在修復了。

SCOTT@book> SELECT ROWID, owner#,name,con#  FROM sys.con$ where name='_NEXT_CONSTRAINT';
ROWID                  OWNER# NAME                       CON#
------------------ ---------- -------------------- ----------
AAAAAcAABAAAAEhAAM          0 _NEXT_CONSTRAINT          11942

--已經向前推進+1.