1. 内容概述
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. 故障模拟
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! 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. 问题分析
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
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
--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分析
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 索引键值与表对比
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 分析小结
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! 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. 验证测试
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>复制
文章转载自数据库技术笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
