本文共 4912 字，大约阅读时间需要 16 分钟。
[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 error1.测试环境：
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 ProductionSCOTT@book> SELECT ROWID, owner#,name,con# FROM sys.con$ where name='_NEXT_CONSTRAINT';
ROWID OWNER# NAME CON# ------------------ ---------- -------------------- ---------- AAAAAcAABAAAAEhAAM 0 _NEXT_CONSTRAINT 11940SCOTT@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,2aBBED> set dba 1,289
DBA 0x00400121 (4194593 1,289)BBED> p * kdbr[12]
rowdata[0] ---------- ub1 rowdata[0] @806 0x2cBBED> x /rncnnnncct
rowdata[0] @806 ---------- flag@806: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@807: 0x02 cols@808: 4col 0[1] @809: 0
col 1[16] @811: _NEXT_CONSTRAINT col 2[4] @828: 11940 col 3[1] @833: 0SCOTT@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 0x2cBBED> x /rncnnnncct
rowdata[0] @806 ---------- flag@806: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@807: 0x02 cols@808: 4col 0[1] @809: 0
col 1[16] @811: _NEXT_CONSTRAINT col 2[4] @828: 11941 col 3[1] @833: 0BBED> sum
Check value for File 1, Block 289: current = 0x5cbb, required = 0x5cb8BBED> sum apply
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y Check value for File 1, Block 289: current = 0x5cb8, required = 0x5cb83.测试：
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 alteredSYS@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$ bCON#
---------- 11941select /*+ index(b i_con2) */ con# from sys.con$ b
minus select /*+ full(a) */ con# from sys.con$ aCON#
---------- 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 = 0x39fdBBED> sum apply
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y Check value for File 1, Block 77798: current = 0x39fd, required = 0x39fdSCOTT@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.
转载地址：http://jhlul.baihongyu.com/