BBED simulates and fixes ORA-08102 error (Oracle 19c)
Sometimes due to sudden power failure and other reasons, the database data dictionary is inconsistent, such as hits ora-8102, the indexes key and the table key value does not match, often delete the index, rebuild the index can be resolved, but if the object_id <60 bootstrap$ internal The index is damaged, and the normal situation needs to be backed up and restored, because some of the indexes in these bootstrap $ cannot be rebuilt by setting event 38003, which results in the database not being used properly, and the rebuilt index reports an error:
ORA-00701: object necessaryforwarmstartingdatabasecannot be altered
Do not to test in the production environment!
env 19.3 pdb, to fix corrupted index I_OBJ4.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> @desc bootstrap$ ;
Name Null? Type
------------------------------- -------- ----------------------------
1 LINE# NOT NULL NUMBER
2 OBJ# NOT NULL NUMBER
3 SQL_TEXT NOT NULL VARCHAR2(4000)
SQL> select max(obj#),count(*) from bootstrap$ ;
MAX(OBJ#) COUNT(*)
---------- ----------
59 60
SQL> select * from bootstrap$ where obj#=39;
LINE# OBJ#
---------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
39 39
CREATE INDEX I_OBJ4 ON OBJ$(DATAOBJ#,TYPE#,OWNER#) PCTFREE 10 INITRANS 2 MAXTRAN
S 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTIN
CREASE 0 OBJNO 39 EXTENTS (FILE 1 BLOCK 360))
backup system datafile
SQL> select file#,name from v$datafile;
FILE# NAME
---------- -----------------------------------------------
1 /u01/app/oracle/oradata/ANBOB19C/system01.dbf
3 /u01/app/oracle/oradata/ANBOB19C/sysaux01.dbf
4 /u01/app/oracle/oradata/ANBOB19C/undotbs01.dbf
5 /u01/app/oracle/oradata/ANBOB19C/pdbseed/system01.dbf
6 /u01/app/oracle/oradata/ANBOB19C/pdbseed/sysaux01.dbf
7 /u01/app/oracle/oradata/ANBOB19C/users01.dbf
8 /u01/app/oracle/oradata/ANBOB19C/pdbseed/undotbs01.dbf
9 /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf
10 /u01/app/oracle/oradata/ANBOB19C/pdb1/sysaux01.dbf
11 /u01/app/oracle/oradata/ANBOB19C/pdb1/undotbs01.dbf
12 /u01/app/oracle/oradata/ANBOB19C/pdb1/users01.dbf
11 rows selected.
[oracle@oel7db1 admin]$ rman target sys/oracle@cdb1pdb1
Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 28 09:59:56 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ANBOB19C:PDB1 (DBID=2777603868, not open)
RMAN> backup datafile 9;
Starting backup at 28-MAY-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf
channel ORA_DISK_1: starting piece 1 at 28-MAY-20
channel ORA_DISK_1: finished piece 1 at 28-MAY-20
piece handle=/u01/app/oracle/product/19.2.0/db_1/dbs/02v1antb_1_1 tag=TAG20200528T100219 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 28-MAY-20
simulates ora-8102
SQL> @desc obj$
Name Null? Type
------------------------------- -------- ----------------------------
1 OBJ# NOT NULL NUMBER
2 DATAOBJ# NUMBER
3 OWNER# NOT NULL NUMBER
4 NAME NOT NULL VARCHAR2(128)
5 NAMESPACE NOT NULL NUMBER
6 SUBNAME VARCHAR2(128)
7 TYPE# NOT NULL NUMBER
8 CTIME NOT NULL DATE
9 MTIME NOT NULL DATE
10 STIME NOT NULL DATE
11 STATUS NOT NULL NUMBER
12 REMOTEOWNER VARCHAR2(128)
13 LINKNAME VARCHAR2(128)
14 FLAGS NUMBER
15 OID$ RAW(16)
16 SPARE1 NUMBER
17 SPARE2 NUMBER
18 SPARE3 NUMBER
19 SPARE4 VARCHAR2(1000)
20 SPARE5 VARCHAR2(1000)
21 SPARE6 DATE
22 SIGNATURE RAW(16)
23 SPARE7 NUMBER
24 SPARE8 NUMBER
25 SPARE9 NUMBER
26 DFLCOLLID NUMBER
27 CREAPPID NUMBER
28 CREVERID NUMBER
29 CREPATCHID NUMBER
30 MODAPPID NUMBER
31 MODVERID NUMBER
32 MODPATCHID NUMBER
33 SPARE10 NUMBER
34 SPARE11 NUMBER
35 SPARE12 VARCHAR2(1000)
36 SPARE13 VARCHAR2(1000)
37 SPARE14 TIMESTAMP(6)
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> alter system dump datafile 9 block 241;
System altered.
SQL> @t
TRACEFILE
-------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_4735.trc
SQL> select max(dataobj#) from OBJ$;
MAX(DATAOBJ#)
-------------
73509
SQL> select dump(73509,16) from dual;
DUMP(73509,16)
----------------------
Typ=2 Len=4: c3,8,24,a
block dump trace file
tab 0, row 44, @0x3b9 tl: 87 fb: --H-FL-- lb: 0x1 cc: 26 col 0: [ 2] c1 02 col 1: [ 4] c3 08 24 0a <<< data object id col 2: [ 1] 80 col 3: [12] 5f 4e 45 58 54 5f 4f 42 4a 45 43 54 col 4: [ 2] c1 02
bbed modify table block
[oracle@oel7db1 ~]$ bbed blocksize=8192 mode=edit filename='/u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf'
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sat May 30 06:08:23 2020
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show all;
FILE# 0
BLOCK# 1
OFFSET 0
DBA 0x00000000 (0 0,1)
FILENAME /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf
BIFILE bifile.bbd
LISTFILE
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> set block 241
BLOCK# 241
BBED> p *kdbr[44]
rowdata[202]
------------
ub1 rowdata[202] @1021 0x2c
BBED> dump /v offset 1021 count 16
File: /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf (0)
Block: 241 Offsets: 1021 to 1036 Dba:0x00000000
-------------------------------------------------------
2c011a02 c10204c3 08240a01 800c5f4e l ,...▒..▒.$...._N
BBED> x /rnnncncnt
rowdata[202] @1021
------------
flag@1021: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1022: 0x01
cols@1023: 26
col 0[2] @1024: 1
col 1[4] @1027: 73509
col 2[1] @1032: 0
col 3[12] @1034: _NEXT_OBJECT
col 4[2] @1047: 1
col 5[0] @1050: *NULL*
col 6[1] @1051: 0
col 7[7] @1053: 17-APR-19
col 8[7] @1061: 23-MAY-20
col 9[7] @1069: 17-APR-19
col 10[1] @1077: 0x80
col 11[0] @1079: *NULL*
col 12[0] @1080: *NULL*
col 13[1] @1081: 0x80
col 14[0] @1083: *NULL*
col 15[1] @1084: 0x80
col 16[4] @1086: 0xc3 0x07 0x38 0x24
col 17[1] @1091: 0x80
col 18[0] @1093: *NULL*
col 19[0] @1094: *NULL*
col 20[0] @1095: *NULL*
col 21[0] @1096: *NULL*
col 22[1] @1097: 0x80
col 23[1] @1099: 0x80
col 24[1] @1101: 0x80
col 25[4] @1103: 0xc3 0x02 0x40 0x53
BBED> set block 241 offset 1028
BLOCK# 241
OFFSET 1028
BBED> d
File: /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf (0)
Block: 241 Offsets: 1028 to 1043 Dba:0x00000000
------------------------------------------------------------------------
c308240a 01800c5f 4e455854 5f4f424a
<32 bytes per line>
BBED> m /x c308240e
BBED-00209: invalid number (c308240b)
BBED> set offset +3
OFFSET 1031
BBED> d
File: /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf (0)
Block: 241 Offsets: 1031 to 1046 Dba:0x00000000
------------------------------------------------------------------------
0a01800c 5f4e4558 545f4f42 4a454354
<32 bytes per line>
BBED> m /x e
File: /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf (0)
Block: 241 Offsets: 1031 to 1046 Dba:0x00000000
------------------------------------------------------------------------
0e01800c 5f4e4558 545f4f42 4a454354
BBED> set block 241 offset 1028
BLOCK# 241
OFFSET 1028
BBED> d
File: /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf (0)
Block: 241 Offsets: 1028 to 1043 Dba:0x00000000
------------------------------------------------------------------------
c308240e 01800c5f 4e455854 5f4f424a
BBED> sum apply
Check value for File 0, Block 241:
current = 0xc5d5, required = 0xc5d5
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf
BLOCK = 241
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
Note:
To modify c308240a(73509) to c308240e(73513).
restart pdb verify
SQL> alter pluggable database pdb1 close; Pluggable database altered. SQL> alter pluggable database pdb1 open; Pluggable database altered. SQL> @cc pdb1 ALTER SESSION SET container = pdb1; Session altered. SQL> create table anbob.t2 (id int); create table anbob.t2 (id int) * ERROR at line 1: ORA-08102: index key not found, obj# 39, file 9, block 25268 (2) SQL> @ind I_OBJ4 Display indexes where table or index name matches %I_OBJ4%... TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC -------------------- ------------------------------ ------------------------------ ---- ------------------------------ ---- SYS OBJ$ I_OBJ4 1 DATAOBJ# 2 TYPE# 3 OWNER# SQL> select /*+ index(t i_obj4) */ DATAOBJ#,type#,owner# from obj$ t minus 3 select /*+ full(t1) */ DATAOBJ#,type#,owner# from obj$ t1; DATAOBJ# TYPE# OWNER# ---------- ---------- ---------- 73509 0 0 SQL> select /*+ full(t1) */ DATAOBJ#,type#,owner# from obj$ t1 minus 3 select /*+ index(t i_obj4) */ DATAOBJ#,type#,owner# from obj$ t ; DATAOBJ# TYPE# OWNER# ---------- ---------- ---------- 73513 0 0 SQL> select /*+full(t)*/ rowid from obj$ t where DATAOBJ#=73509; no rows selected SQL> select /*+full(t)*/ dump(rowid,16) from obj$ t where DATAOBJ#=73513; DUMP(ROWID,16) --------------------------------------------------------------------------- Typ=69 Len=10: 0,0,0,12,0,40,0,f1,0,2c 1 row selected.
db alert log
Opening pdb with no Resource Manager plan active
joxcsys_required_dirobj_exists: directory object exists with required path /u01/app/oracle/product/19.2.0/db_1/javavm/admin/, pid 6791 cid 3
Pluggable database PDB1 opened read write
Completed: alter pluggable database pdb1 open
2020-05-30 06:41:31.465000 -04:00
Errors in file /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_6791.trc:
2020-05-30 06:41:32.981000 -04:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
trace file anbob19c_ora_6791
you can do 10046 event trace also.
*** 2020-05-30T06:49:11.706513-04:00 (PDB1(3)) oer 8102.2 - obj# 39, rdba: 0x004062b4(afn 9, blk# 25268) kdk key 8102.2: ncol: 4, len: 16 key: (16): 04 c3 08 24 0e 01 80 01 80 06 00 40 00 f1 00 2c --table block key mask: (4096): a1 02 7f 00 00 d0 cd 4a b7 2f 7f 00 00 70 02 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 20 cb 4a b7 2f 7f 00 00 20 74 c9 12 00 00 00 00 98 79 64 6a 00 00 00 00 c8 b2 4a b7 2f 7f 00 00 a0 59 4a b7 2f 7f 00 00 00 20 00 00 00 00 00 00 10 70 ad e3 ff 7f 00 00 e3 0f 04 12 00 00 00 00 98 24 20 60 00 00 00 00 e8 0f 00 00 00 00 00 00 88 31 17 60 00 00 00 00 b0 79 64 6a 00 00 00 00 0e 00 00 00 00 00 00 00 03 00 00 00 2f 7f 00 00 ac 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 02 00 00 00 00 00 00 00 a0 59 4a b7 2f 7f 00 00 00 00 00 00 00 00 00 00 e8 0f 00 00 00 00 00 00 ff 2f 07 90 ff 7f 00 00 c0 63 4f b7 2f 7f 00 00 01 00 00 00 ff 7f 00 00 00 00 00 00 00 00 00 00 00 20 00 00 ff 7f 00 00 20 74 c9 12 00 00 00 00 88 31 17 60 00 00 00 00 28 1c 30 67 00 00 00 00 a0 59 4a b7 2f 7f 00 00 00 20 00 00 00 00 00 00 90 71 ad e3 ff 7f 00 00 00 18 82 12 00 00 00 00 34 1c 30 67 00 00 00 00 4a 21 82 12 00 00 00 00 b0 1e 00 60 00 00 00 00 58 02 00 00 00 00 00 00 f0 0f 00 00 ff 7f 00 00 00 00 00 00 00 00 00 00 20 cb 4a b7 2f 7f 00 00 c8 b2 4a b7 2f 7f 00 00 d0 a6 34 60 00 00 00 00 03 00 ----- Current SQL Statement for this session (sql_id=c3utnxsnrx8tk) ----- 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,spare3=:15,signature=:16,spare7=:17,spare8=:18,spare9=:19, dflcollid=decode(:20,0,null,:20),creappid=:21,creverid=:22, modappid=:23,modverid=:24,crepatchid=:25,modpatchid=:26 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null ... Block header dump: 0x004062b4 Object id on Block? Y seg/obj: 0x27 csc: 0x000000000039bd2e itc: 4 flg: - typ: 2 - INDEX fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0008.001.000002a9 0x02402c9c.010e.01 CB-- 0 scn 0x000000000022ac9c 0x02 0x0006.006.00000339 0x024005c2.00e2.39 C--- 0 scn 0x000000000039bd2c 0x03 0x0003.009.00000396 0x02401c52.01c2.23 --U- 1 fsc 0x0000.0039bd42 0x04 0x0005.012.00000338 0x02400221.014f.5a --U- 1 fsc 0x0000.0039bd37 Leaf block dump =============== header address 2206580876=0x8385c08c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 4 kdxcosdc 1 kdxconro 302 kdxcofbo 640=0x280 kdxcofeo 1844=0x734 kdxcoavs 1618 kdxlespl 0 kdxlende 0 kdxlenxt 4201842=0x401d72 kdxleprv 4219571=0x4062b3 kdxledsz 0 kdxlebksz 7984 row#0[7965] flag: -------, lock: 0, len=19 ... row#282[1844] flag: -------, lock: 3, len=21 col 0; len 4; (4): c3 08 24 06 col 1; len 2; (2): c1 03 col 2; len 3; (3): c2 02 07 col 3; len 6; (6): 00 40 73 b3 00 0f row#283[1886] flag: -------, lock: 0, len=18 col 0; len 4; (4): c3 08 24 0a <<< index key diff table key col 1; len 1; (1): 80 col 2; len 1; (1): 80 col 3; len 6; (6): 00 40 00 f1 00 2c ---### rowid row#284[2657] flag: -------, lock: 0, len=15 col 0; NULL col 1; len 2; (2): c1 02 col 2; len 1; (1): 80 col 3; len 6; (6): 00 40 11 c2 00 14 row#285[2642] flag: -------, lock: 0, len=15
bbed fixed index block
SQL> SELECT utl_raw.cast_to_number(replace('c3 08 24 0a',' ')) value FROM dual;
VALUE
----------
73509
1 row selected.
offset = 1886 + 44 + 24* number of itls (in the case itl is 4)= 2026
BBED> set block 25268 offset 2026
BLOCK# 25268
OFFSET 2026
BBED> d count 32
File: /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf (0)
Block: 25268 Offsets: 2026 to 2057 Dba:0x00000000
------------------------------------------------------------------------
000004c3 08240a01 80018006 004000f1 002c0000 04c30823 5b02c103 03c20207
<32 bytes per line>
BBED> set offset +6
OFFSET 2032
BBED> d
File: /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf (0)
Block: 25268 Offsets: 2032 to 2063 Dba:0x00000000
------------------------------------------------------------------------
0a018001 80060040 00f1002c 000004c3 08235b02 c10303c2 02070600 4073b300
BBED> m /x e
File: /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf (0)
Block: 25268 Offsets: 2032 to 2063 Dba:0x00000000
------------------------------------------------------------------------
0e018001 80060040 00f1002c 000004c3 08235b02 c10303c2 02070600 4073b300
<32 bytes per line>
BBED> d
File: /u01/app/oracle/oradata/ANBOB19C/pdb1/system01.dbf (0)
Block: 25268 Offsets: 2032 to 2063 Dba:0x00000000
------------------------------------------------------------------------
0e018001 80060040 00f1002c 000004c3 08235b02 c10303c2 02070600 4073b300
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 25268:
current = 0xfb9e, required = 0xfb9e
restart pdb to verify
SQL> alter pluggable database pdb1 close;
Pluggable database altered.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> create table anbob.t2 (id int);
Table created.
SQL> select max(data_object_id) from dba_objects;
MAX(DATA_OBJECT_ID)
-------------------
73513
1 row selected.
— over —
如果您遇到了问题，自己解决不了，请联系 www.anbob.com 的首页上的联系方式。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。