[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.
