ORA-8102問題模擬及處理
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#值得變化
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 --可以看到con#從5213增長為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 block 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:() 2015-01-03 18:40:47.118
*** MODULE NAME:(sqlplus@bogon (TNS V1-V3)) 2015-01-03 18:40:47.118
*** SERVICE NAME:(SYS$USERS) 2015-01-03 18:40:47.118
*** SESSION ID:(159.3) 2015-01-03 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):
故障出現：
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
根據報錯對block 1,27255進行dump
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 block 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 0x000000001D44E400 to 0x000000001D450400
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
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 1
kdxconro 240
kdxcofbo 516=0x204
kdxcofeo 4712=0x1268
kdxcoavs 4638
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 4221558=0x406a76
kdxledsz 6
kdxlebksz 8032
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
注意索引中的ROWID=文件號+塊號+行號
文件號：1 0000 0000 0000 0100取前十位 ->0x0040
塊號：170 00aa ->0x00aa
行號：12 000c ->0x000c
rowid = 00 40 00 aa 00 0c
在索引塊dump文件中搜索rowid的串，定位到row#239[4724]
也就是把c2 35 0f 修改為c2 35 19就可以與表數據保持一致了。
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
根據索引塊dump可以找到itl槽的個數
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 1,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.
創建帶主鍵的表順利完成。
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
再次查詢驗證數據字典，已經是一致的了。
