oracle i_obj4,oracle I_OBJ4 ORA-8102问题分析及异常恢复

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

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. 问题分析

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

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>