Simulation and handling of ORA-8102 Problems

Simulation and handling of ORA-8102 Problems
SQL> SELECT NAME, TYPE # FROM OBJ $ WHERE OBJ # = 49;

Name type #
----------------------------------------
I _CON2 1

SQL> SELECT TABLE_NAME FROM DBA_INDEXES WHERE INDEX_NAME = 'I _ con2 ';

TABLE_NAME
------------------------------
CON $


Select owner #, NAME, CON # from con $ where name = '_ NEXT_CONSTRAINT ';


SQL> SELECT OWNER #, NAME, CON # FROM CON $ WHERE NAME = '_ NEXT_CONSTRAINT ';

OWNER # name con #
--------------------------------------------------
0 _ NEXT_CONSTRAINT 5213

-- Con # worth changing when creating a table with a primary key

SQL> alter session set events '10046 trace name context forever, level 10 ';

Session altered.

SQL> create table test (id int primary key, value varchar2 (20 ));

Table created.

SQL> select 'sunhailong' from dual;

'Sunhailon
----------
Sunhailong

SQL> SELECT OWNER #, NAME, CON # FROM CON $ WHERE NAME = '_ NEXT_CONSTRAINT ';

OWNER # name con #
--------------------------------------------------
0 _ NEXT_CONSTRAINT 5214 -- we can see that con # increased from 5213 to 5214

SQL> oradebug tracefile_name
ORA-00074: no process has been specified
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/Oracle/app/admin/orcl/udump/orcl_ora_22472.trc
SQL> alter session set events '10046 trace name context off ';

Session altered.

-- Dump block
SQL> SELECT OWNER #, NAME, CON #,
2 dbms_rowid.ROWID_RELATIVE_FNO (rowid) fno,
3 dbms_rowid.ROWID_BLOCK_NUMBER (rowid) bno,
4 dbms_rowid.ROWID_ROW_NUMBER (rowid) rno from con $ where name = '_ NEXT_CONSTRAINT ';

OWNER # name con # FNO BNO RNO
--------------------------------------------------------------------------------
0 _ NEXT_CONSTRAINT 5214 1 170 12


SQL> alter system dump datafile 1 blocks 170;

System altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/Oracle/app/admin/orcl/udump/orcl_ora_22549.trc

Start dump data blocks tsn: 0 file #: 1 minblk 170 maxblk 170
Buffer tsn: 0 rdba: 0x004000aa (1/170)
Scn: 0x0000. 000ac089 seq: 0x01 flg: 0x06 tail: 0xc0890601
Frmt: 0x02 chkval: 0xf13c type: 0x06 = trans data
Hex dump of block: st = 0, typ_found = 1
.........
Block header dump: 0x004000aa
Object id on Block? Y
Seg/obj: 0x1c csc: 0x00. ac088 itc: 1 flg:-typ: 1-DATA
Fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002. 022.00000145 0x008002cf. 00c7. 2c -- U-1 fsc 0x0000. 000ac089

Data_block_dump, data header at 0x1c1ba444
====================
Tsiz: 0x1fb8
Hsiz: 0x2ba
Pbl: 0x1c1ba444
Bdba: 0x004000aa
76543210
Flag = --------
Ntab = 1
Nrow= 340
Frre =-1
Fsbo = 0x2ba
Fseo = 0x5a8
Avsp = 0x33a
Tosp = 0x33a
0xe: pti [0] nrow = 340 offs = 0
0x12: pri [0] offs = 0x1fa7
0x14: pri [1] offs = 0x1f95
0x16: pri [2] offs = 0x1f84
0x18: pri [3] offs = 0x1f70
0x1a: pri [4] offs = 0x1f5c
0x1c: pri [5] offs = 0x1f48
0x1e: pri [6] offs = 0x1f36
0x20: pri [7] offs = 0x1f24
0x22: pri [8] offs = 0x1f10
0x24: pri [9] offs = 0x1efc
0x26: pri [10] offs = 0x1eea
0x28: pri [11] offs = 0x1ed8
0x2a: pri [12] offs = 0x5a8
0x2c: pri [13] offs = 0x1ec4
0x2e: pri [14] offs = 0x1eb0
0x30: pri [15] offs = 0x1e9e
0x32: pri [16] offs = 0x1e8c
......
0x2b8: pri [339] offs = 0x60e
Block_row_dump:
Tab 0, row 0, @ 0x1fa7
Tl: 17 fb: -- H-FL -- lb: 0x0 cc: 3
Col 0: [1] 80
Col 1: [8] 53 59 53 5f 43 30 30 33
Col 2: [2] c1 04
Tab 0, row 1, @ 0x1f95
Tl: 18 fb: -- H-FL -- lb: 0x0 cc: 3
Col 0: [1] 80
Col 1: [9] 53 59 53 5f 43 30 30 37 31
Col 2: [2] c1 48
Tab 0, row 2, @ 0x1f84
Tl: 17 fb: -- H-FL -- lb: 0x0 cc: 3
Col 0: [1] 80
Col 1: [8] 53 59 53 5f 43 30 30 34
Col 2: [2] c1 05
Tab 0, row 3, @ 0x1f70
Tl: 20 fb: -- H-FL -- lb: 0x0 cc: 3
Col 0: [1] 80
Col 1: [10] 53 59 53 5f 43 30 30 31 34 39
Col 2: [3] c2 02 32
Tab 0, row 4, @ 0x1f5c
Tl: 20 fb: -- H-FL -- lb: 0x0 cc: 3
Col 0: [1] 80
Col 1: [10] 53 59 53 5f 43 30 30 31 34 35
Col 2: [3] c2 02 2e
Tab 0, row 5, @ 0x1f48
Tl: 20 fb: -- H-FL -- lb: 0x0 cc: 3
Col 0: [1] 80
Col 1: [10] 53 59 53 5f 43 30 30 31 33 30
Col 2: [3] c2 02 1f
Tab 0, row 6, @ 0x1f36
Tl: 18 fb: -- H-FL -- lb: 0x0 cc: 3
Col 0: [1] 80
Col 1: [9] 53 59 53 5f 43 30 30 34 32
Col 2: [2] c1 2b
Tab 0, row 7, @ 0x1f24
Tl: 18 fb: -- H-FL -- lb: 0x0 cc: 3
Col 0: [1] 80
Col 1: [9] 53 59 53 5f 43 30 30 39 33
Col 2: [2] c1 5e
Tab 0, row 8, @ 0x1f10
Tl: 20 fb: -- H-FL -- lb: 0x0 cc: 3
Col 0: [1] 80
Col 1: [10] 53 59 53 5f 43 30 30 31 34 37
Col 2: [3] c2 02 30
Tab 0, row 9, @ 0x1efc
Tl: 20 fb: -- H-FL -- lb: 0x0 cc: 3
Col 0: [1] 80
Col 1: [10] 53 59 53 5f 43 30 30 31 34 33
Col 2: [3] c2 02 2c
Tab 0, row 10, @ 0x1eea
Tl: 18 fb: -- H-FL -- lb: 0x0 cc: 3
Col 0: [1] 80
Col 1: [9] 53 59 53 5f 43 30 30 34 34
Col 2: [2] c1 2d
Tab 0, row 11, @ 0x1ed8
Tl: 18 fb: -- H-FL -- lb: 0x0 cc: 3
Col 0: [1] 80
Col 1: [9] 53 59 53 5f 43 30 30 38 36
Col 2: [2] c1 57
Tab 0, row 12, @ 0x5a8
Tl: 26 fb: -- H-FL -- lb: 0x1 cc: 3
Col 0: [1] 80
Col 1: [16] 5f 4e 45 58 54 5f 43 4f 4e 53 54 52 41 49 4e 54
Col 2: [3] c2 35 0f
Tab 0, row 13, @ 0x1ec4
Tl: 20 fb: -- H-FL -- lb: 0x0 cc: 3
Col 0: [1] 80
Col 1: [10] 53 59 53 5f 43 30 30 31 35 30
Col 2: [3] c2 02 33
......
Tab 0, row 339, @ 0x60e
Tl: 20 fb: -- H-FL -- lb: 0x0 cc: 3
Col 0: [1] 80
Col 1: [10] 53 59 53 5f 43 30 30 33 33 35
Col 2: [3] c2 04 24
End_of_block_dump
End dump data blocks tsn: 0 file #: 1 minblk 170 maxblk 170

BBED> p * kdbr [12]
Rowdata [0]
----------
Ub1 rowdata [0] @ 1516 0x2c

BBED> x/rccnn
Rowdata [0] @ 1516
----------
Flag @ 1516: 0x2c (KDRHFL, KDRHFF, KDRHFH)
Lock @ 1517: 0x01
Cols @ 1518: 3

Col 0 [1] @ 1519 :.
Col 1 [16] @ 1521: _ NEXT_CONSTRAINT
Col 2 [3] @ 1538: 5214



BBED> dump/v offset 1538 count 16
File:/data/orcl/system01.dbf (1)
Block: 170 Offsets: 1538 to 1553 Dba: 0x004000aa
-------------------------------------------------------
03c2350f 2c000301 80105f4e 4558545f l... 5,... _ NEXT _

<16 bytes per line>



BBED> modify/x 03c23519
File:/data/orcl/system01.dbf (1)
Block: 170 Offsets: 1538 to 1553 Dba: 0x004000aa
------------------------------------------------------------------------
03c23519 2c000301 80105f4e 4558545f

<32 bytes per line>



BBED> p * kdbr [12]
Rowdata [0]
----------
Ub1 rowdata [0] @ 1516 0x2c

BBED> x/rccnn
Rowdata [0] @ 1516
----------
Flag @ 1516: 0x2c (KDRHFL, KDRHFF, KDRHFH)
Lock @ 1517: 0x01
Cols @ 1518: 3

Col 0 [1] @ 1519 :.
Col 1 [16] @ 1521: _ NEXT_CONSTRAINT
Col 2 [3] @ 1538: 5224



SQL> create table T_SUNHAILONG (id int primary key, value varchar2 (20) tablespace USERS;
Create table T_SUNHAILONG (id int primary key, value varchar2 (20) tablespace USERS
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj #49, file 1, block 27255 (2)


Udump trace:
* ** Action name :() 18:40:47. 118
* Module name :( sqlplus @ bogon (TNS V1-V3) 18:40:47. 118
* ** Service name :( SYS $ USERS) 18:40:47. 118
* ** Session id: (159.3) 18:40:47. 118
Tkcrrsarc: (WARN) Failed to find ARCH for message (message: 0x1)
Tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message: 0x1)
Kwqmnich: current time: 2: 40: 51
Kwqmnich: instance no 0 check_only flag 1
Kwqmnich: initialized job cache structure
Oer 8102.2-obj #49, rdba: 0x00406a77 (afn 1, blk #27255)
Kdk key 8102.2:
Ncol: 1, len: 4
Key: (4): 03 c2 35 19
Mask: (4096 ):


Fault:
SQL> select obj #, name, owner #, type # from obj $ where obj # = 49;

OBJ # name owner # TYPE #
------------------------------------------------------------
49 I _CON2 0 1

SQL> select/* + FULL (T1) */OWNER #, NAME, CON # FROM CON $ T1
2 minus
3 select/* + index (T I _CON2) */owner #, name, con # from con $ T;

OWNER # name con #
--------------------------------------------------
0 _ NEXT_CONSTRAINT 5224

SQL> SELECT/* + index (t I _CON2) */owner #, NAME, con # FROM CON $ t where t. NAME = '_ NEXT_CONSTRAINT ';

OWNER # name con #
--------------------------------------------------
0 _ NEXT_CONSTRAINT 5214


SQL> select/* + FULL (T1) */OWNER #, NAME, CON # FROM CON $ T1 where t1.NAME = '_ NEXT_CONSTRAINT ';

OWNER # name con #
--------------------------------------------------
0 _ NEXT_CONSTRAINT 5224

SQL> select dbms_rowid.rowid_relative_fno (rowid) file #, dbms_rowid.rowid_block_number (rowid) block #,
Dbms_rowid.rowid_row_number (rowid) row #
From con $
Where name = '_ NEXT_CONSTRAINT'; 2 3 4

FILE # BLOCK # ROW #
------------------------------
1 170 12

SQL> select object_name, owner, object_type from dba_objects where object_id = 49;

OBJECT_NAME OWNER OBJECT_TYPE
-------------------------------------------------------------------------------
I _CON2 SYS INDEX


Perform dump on block according to the error message
Oer 8102.2-obj #49, rdba: 0x00406a77 (afn 1, blk #27255)
Kdk key 8102.2:
Ncol: 1, len: 4
Key: (4): 03 c2 35 19
Mask: (4096 ):

Alter system dump datafile 1 blocks 27255;

Buffer tsn: 0 rdba: 0x00406a77 (1/27255)
Scn: 0x0000. 000ac09d seq: 0x01 flg: 0x06 tail: 0xc09d0601
Frmt: 0x02 chkval: 0xb28f type: 0x06 = trans data
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0x0000000000001d44e400 to 0x0000000000001d0000400

Block header dump: 0x00406a77
Object id on Block? Y
Seg/obj: 0x31 csc: 0x00. ac09c itc: 2 flg: O typ: 2-INDEX
Fsl: 0 fnx: 0x406a78 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009. 00d. 0000011e 0x0080610f. 00b3. 01 CB -- 0 scn 0x0000.00089531
0x02 0x0002. 021.00000145 0x008002d0. 00c7. 3c -- U-1 fsc 0x0000. 000ac09d

Leaf block dump
====================
Header address 491054172 = 0x1d44e45c
Kdxcolev0
Kdxcolevflags = ---
Kdxcolok 0
Kdxcoopc 0x80: opcode = 0: iot flags = --- is converted = Y
Kdxconco 1
Kdxcosdc 1
Kdxconro 1, 240
Kdxcofbo 516 = 0x204
Kdxcofeo 4712 = 0x1268
Kdxcoavs 4638
Kdxlespl 0
Kdxlende 0
Kdxlenxt 0 = 0x0
Kdxleprv 4221558 = 0x406a76
Kdxledsz 6
Kdxlebksz8032
Row #0 [8020] flag: ------, lock: 0, len = 12, data :( 6): 00 40 e2 fa 00 ea
Col 0; len 3; (3): c2 32 39
Row #1 [8008] flag: ------, lock: 0, len = 12, data :( 6): 00 40 e2 fa 00 eb
Col 0; len 3; (3): c2 32 3a
Row #2 [7996] flag: ------, lock: 0, len = 12, data :( 6): 00 40 e2 fa 00 ec
Col 0; len 3; (3): c2 32 3b
......
Row #237 [5178] flag: ------, lock: 0, len = 12, data :( 6): 00 40 e2 fb 00 dd
Col 0; len 3; (3): c2 34 5f
Row #238 [4712] flag: ------, lock: 2, len = 12, data :( 6): 00 40 e2 fb 00 e4
Col 0; len 3; (3): c2 35 0e
Row #239 [4724] flag: ------, lock: 0, len = 12, data :( 6): 00 40 00 aa 00 0c
Col 0; len 3; (3): c2 35 0f
----- End of leaf block dump -----
End dump data blocks tsn: 0 file #: 1 minblk 27255 maxblk 27255

SQL> SELECT OWNER #, NAME, CON #,
2 dbms_rowid.ROWID_RELATIVE_FNO (rowid) fno,
3 dbms_rowid.ROWID_BLOCK_NUMBER (rowid) bno,
4 dbms_rowid.ROWID_ROW_NUMBER (rowid) rno from con $ where name = '_ NEXT_CONSTRAINT ';

OWNER # name con # FNO BNO RNO
--------------------------------------------------------------------------------
0 _ NEXT_CONSTRAINT 5214 1 170 12

Note that ROWID = file number + block number + row number in the Index
File number: 1 0000 0000 0000 0100 take the top 10-> 0x0040
Block number: 170 00aa-> 0x00aa
Row number: 12 000c-> 0x000c

Rowid = 00 40 00 aa 00 0c
Search the rowid string in the index block dump file and locate row #239 [4724].

That is, you can change c2 35 0f to c2 35 19 to be consistent with the table data.
Row #239 [4724] flag: ------, lock: 0, len = 12, data :( 6): 00 40 00 aa 00 0c
Col 0; len 3; (3): c2 35 0f
C2350f

You can find the number of itl slots Based on index block dump.
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009. 00d. 0000011e 0x0080610f. 00b3. 01 CB -- 0 scn 0x0000.00089531
0x02 0x0002. 021.00000145 0x008002d0. 00c7. 3c -- U-1 fsc 0x0000. 000ac09d

Offset = kd_off + 44 + 8 + 24 * itl_cnt
= 4724 + 44 + 8 + 24*2
= 4724 + 52 + 48
= 4824

BBED> set dba 1,27255
DBA 0x00406a77 (4221559, 27255)

BBED> dump offset 4824 count 16
File:/data/orcl/system01.dbf (1)
Block: 27255 Offsets: 4824 to 4839 Dba: 0x00406a77
------------------------------------------------------------------------
03c2350f 01000040 e2fb00e3 03c23508

BBED> set offset 4828
OFFSET 4828

BBED> dump
File:/data/orcl/system01.dbf (1)
Block: 27255 Offsets: 4828 to 5339 Dba: 0x00406a77
------------------------------------------------------------------------
01000040 e2fb00e3 03c23508 01000040 e2fb00e2 03c23509 01000040 e2fb00e1
03c2350a 01000040 e2fb00e0 03c2350b 01000040 e2fb00df 03c2350c 01000040


<32 bytes per line>

BBED> set offset 4827
OFFSET 4827

BBED> modify/x 19
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File:/data/orcl/system01.dbf (1)
Block: 27255 Offsets: 4827 to 5338 Dba: 0x00406a77
------------------------------------------------------------------------
19010000 40e2fb00 e303c235 08010000 40e2fb00 e203c235 09010000 40e2fb00
E103c235 0a010000 40e2fb00 e003c235 0b010000 40e2fb00 df03c235 0c010000

<32 bytes per line>

BBED> sum apply
Check value for File 1, Block 27255:
Current = 0xa48f, required = 0xa48f

SQL> create table T_SUNHAILONG (id int primary key, value varchar2 (20) tablespace USERS;

Table created.
The table with the primary key is successfully created.
SQL> select/* + FULL (T1) */OWNER #, NAME, CON # FROM CON $ T1
2 minus
3 select/* + index (T I _CON2) */owner #, name, con # from con $ T;

No rows selected

SQL>
SQL> SELECT/* + index (t I _CON2) */owner #, NAME, con # FROM CON $ t where t. NAME = '_ NEXT_CONSTRAINT ';

OWNER # name con #
--------------------------------------------------
0 _ NEXT_CONSTRAINT 5225

SQL> select/* + FULL (T1) */OWNER #, NAME, CON # FROM CON $ T1 where t1.NAME = '_ NEXT_CONSTRAINT ';

OWNER # name con #
--------------------------------------------------
0 _ NEXT_CONSTRAINT 5225

Query and verify the data dictionary again, which is already consistent.