BBED simulates and fixes ORA-08102 error (Oracle 19c)
方法一使用了bbed修改 index key的方法， 因为表列上只有这一个索引，所以只改一个索引就可以。这里还使用相同的方法模拟ora-8102,使用第二种方法，删除bootstrap$中的index I_OBJ4 记录解决。
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> @cc pdb1
ALTER SESSION SET container = pdb1;
Session altered.
USERNAME INST_NAME HOST_NAME I# SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS PDB1-anbob19c oel7db1 1 390 22700 19.0.0.0.0 20200531 2179 33 2068 0000000077881028 00000000785069A8
SQL> select file#,rfile#,name from v$datafile;
FILE# RFILE# NAME
---------- ---------- ----------------------------------------------------------------------
9 1 /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf
10 4 /u01/app/oracle/oradata/ANBOB19C/pdb1/sysaux01.dbf
11 9 /u01/app/oracle/oradata/ANBOB19C/pdb1/undotbs01.dbf
12 12 /u01/app/oracle/oradata/ANBOB19C/pdb1/users01.dbf
SQL> select max(DATAOBJ#)from obj$ t;
MAX(DATAOBJ#)
-------------
73523
SQL> select /*+ index(t i_obj4) */ dump(rowid,16) from obj$ t where dataobj#=73523;
DUMP(ROWID,16)
----------------------------------------------------
Typ=69 Len=10: 0,0,0,12,0,40,0,f1,0,2c
SQL> select dump(73523,16) from dual;
DUMP(73523,16)
-----------------------
Typ=2 Len=4: c3,8,24,18
BBED> d
File: /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf (0)
Block: 25268 Offsets: 1933 to 2444 Dba:0x00000000
------------------------------------------------------------------------
18018001 80060040 00f1002c 000404c3 08240e02 c10303c2 02070600 4073b300
10010304 c3082413 01800180 06004000 f1002c00 0004c308 240602c1 0303c202
BBED> m /x 1a
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf (0)
Block: 25268 Offsets: 1933 to 2444 Dba:0x00000000
------------------------------------------------------------------------
1a018001 80060040 00f1002c 000404c3 08240e02 c10303c2 02070600 4073b300
BBED> sum apply
Check value for File 0, Block 25268:
current = 0x0126, required = 0x0126
SQL> alter pluggable database pdb1 close;
Pluggable database altered.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> create table anbob.test1 as select 1 id from dual;
create table anbob.test1 as select 1 id from dual
*
ERROR at line 1:
ORA-08102: index key not found, obj# 39, file 9, block 25268 (2)
bbed fixed
SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from bootstrap$ ;
FILE# BLOCK#
---------- ----------
1 521
1 523
1 522
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,
dbms_rowid.rowid_row_number(rowid) row# from bootstrap$ where obj#=39;
FILE# BLOCK# ROW#
---------- ---------- ----------
1 523 7
BBED> set block 523
BLOCK# 523
BBED> map
File: /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf (0)
Block: 523 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 48 bytes @20
struct kdbh, 14 bytes @68
struct kdbt[1], 4 bytes @82
sb2 kdbr[14] @86
ub1 freespace[1557] @114
ub1 rowdata[6517] @1671
ub4 tailchk @8188
BBED> p * kdbr[7]
rowdata[3766]
-------------
ub1 rowdata[3766] @5437 0x2c
BBED> set offset 5437
OFFSET 5437
BBED> d count 32
File: /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf (0)
Block: 523 Offsets: 5437 to 5468 Dba:0x00000000
------------------------------------------------------------------------
2c000302 c12802c1 28cd4352 45415445 20494e44 45582049 5f4f424a 34204f4e
<32 bytes per line>
SQL> @desc bootstrap$
Name Null? Type
------------------------------- -------- ----------------------------
1 LINE# NOT NULL NUMBER
2 OBJ# NOT NULL NUMBER
3 SQL_TEXT NOT NULL VARCHAR2(4000)
BBED> x /rnnc
rowdata[3766] @5437
-------------
flag@5437: 0x2c (KDRHFL, KDRHFF, KDRHFH) ## row flag
lock@5438: 0x00
cols@5439: 3 ## columns
col 0[2] @5440: 39
col 1[2] @5443: 39
col 2[205] @5446: CREATE INDEX I_OBJ4 ON OBJ$(DATAOBJ#,TYPE#,OWNER#) PCTF
REE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS
1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 39 EXTENTS (FILE 1 BLOCK 360))
BBED> m /x 3c
File: /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf (0)
Block: 523 Offsets: 5437 to 5468 Dba:0x00000000
------------------------------------------------------------------------
3c000302 c12802c1 28cd4352 45415445 20494e44 45582049 5f4f424a 34204f4e
<32 bytes per line>
BBED> x /rnnc
rowdata[3766] @5437
-------------
flag@5437: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@5438: 0x00
cols@5439: 0
BBED> sum apply
Check value for File 0, Block 523:
current = 0x7186, required = 0x7186
Note:
delete will change ‘row flag’ from 0x2c to 32+16+8+4 = 60 or 0x3c.
verify
SQL> select * from bootstrap$ where obj#=39;
no rows selected
SQL> @ind obj$
Display indexes where table or index name matches %obj$%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
OBJ$ I_OBJ1 1 OBJ#
2 OWNER#
3 TYPE#
I_OBJ2 1 OWNER#
2 NAME
3 NAMESPACE
4 REMOTEOWNER
5 LINKNAME
6 SUBNAME
7 TYPE#
8 SPARE3
9 OBJ#
I_OBJ3 1 OID$
I_OBJ4 1 DATAOBJ#
2 TYPE#
3 OWNER#
I_OBJ5 1 SPARE3
2 NAME
3 NAMESPACE
4 TYPE#
5 OWNER#
6 REMOTEOWNER
7 LINKNAME
8 SUBNAME
9 OBJ#
SQL> create table anbob.test1 as select 1 id from dual;
Table created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL> select max(DATAOBJ#)from obj$ t;
MAX(DATAOBJ#)
-------------
73530
1 row selected.
— over —