bootstrap$核心物件資料不一致導致ORA-08102
原帖：[url]http://www.killdb.com/?p=201[/url] 本文參考了dbsnake和老白的部分方法，再次感謝兩位！
昨天準備研究11g的query cache result 特性，準備用10g的老方法來直接通過
show parameter xxxx的方式來檢視隱含引數，發現下面的建立檢視語句居然報錯ora-08102
如下是建立檢視的指令碼，後面是錯誤：
create or replace view show_hidden_v$parameter
(INST_ID, NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE,
ISMODIFIED , ISADJUSTED , ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH)
as
select x.inst_id,
x.indx + 1,
ksppinm,
ksppity,
ksppstvl,
ksppstdvl,
ksppstdf,
decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'),
decode(bitand(ksppiflg / 65536, 3),
1,
'IMMEDIATE',
2,
'DEFERRED',
3,
'IMMEDIATE',
'FALSE'),
decode(bitand(ksppiflg, 4),
4,
'FALSE',
decode(bitand(ksppiflg / 65536, 3), 0, 'FALSE', 'TRUE')),
decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE'),
decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE'),
decode(bitand(ksppilrmflg / 64, 1), 1, 'TRUE', 'FALSE'),
ksppdesc,
ksppstcmnt,
ksppihash
from x$ksppi x, x$ksppcv y
where (x.indx = y.indx);
ORA-08102: index key not found, obj# 39, file 1, block 59847 (2)
從上面的8102錯誤來看，很明顯是資料字典資訊不一致了，也就是說該記錄在ind$可能已經被清除了，
而在obj$中還存在。我們來看看obj# 39是什麼物件？
SQL> SELECT relative_fno, owner, segment_name, segment_type
2 FROM dba_extents
3 WHERE file_id = 1
4 AND 59847 BETWEEN block_id AND block_id + blocks - 1;
RELATIVE_FNO OWNER SEGMENT_NAME SEGMENT_TYPE
------------ ------- ------------------------------ ------------------
1 SYS I_OBJ4 INDEX
SQL>
SQL> select owner,object_name,object_type,object_id from
2 dba_objects where object_name='I_OBJ4';
OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
-------------------- ------------------------- ------------------- ----------
SYS I_OBJ4 INDEX 39
SQL>
對於ora-08102錯誤，如果是發生在index上，那麼我們直接drop index然後重建就ok了。
那我們來試試直接重建會怎麼樣？
SQL> alter system set event='38003 trace name context forever, level 10' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167395328 bytes
Fixed Size 1335220 bytes
Variable Size 104857676 bytes
Database Buffers 58720256 bytes
Redo Buffers 2482176 bytes
Database mounted.
Database opened.
SQL> alter index I_OBJ4 rebuild;
alter index I_OBJ4 rebuild
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00060: deadlock detected while waiting for resource
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup migrate;
ORACLE instance started.
Total System Global Area 167395328 bytes
Fixed Size 1335220 bytes
Variable Size 104857676 bytes
Database Buffers 58720256 bytes
Redo Buffers 2482176 bytes
Database mounted.
Database opened.
SQL> alter index I_OBJ4 rebuild;
alter index I_OBJ4 rebuild
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00060: deadlock detected while waiting for resource
SQL> drop index I_OBJ4;
drop index I_OBJ4
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
SQL> alter index I_OBJ4 rebuild online;
alter index I_OBJ4 rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 39, file 1, block 59847 (2)
SQL> analyze table obj$ VALIDATE STRUCTURE CASCADE;
analyze table obj$ VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
到這裡，可能有人會問，為什麼使用event 38003或migrate 模式無法rebuild 該index呢？
很簡單，該index的obj# <56, 換句話說，也就是對於bootstrap$核心物件是無法通過上面
的2種方式來完成重建的。
通常來說到這個地步，如果不使用其他手段的話，那麼只能使用ODU或DUL進行抽取資料然後重建資料庫了。
其實對於這個問題，我們可以藉助BBED來進行修復。
既然是資料不一致，那麼我就想知道到底是哪兒不一致了？metalink 提供處理ora-8102的方法：
SQL> SELECT /*+ FULL(t1) */ DATAOBJ#, TYPE#,OWNER#,rowid
2 FROM obj$ t1
3 MINUS
4 SELECT /*+ index(t I_OBJ4) */ DATAOBJ#, TYPE#,OWNER#,rowid
5 FROM obj$ t;
DATAOBJ# TYPE# OWNER# ROWID
---------- ---------- ---------- ------------------
73416 2 0 AAAAASAABAAAPt8AAB
73419 0 0 AAAAASAABAAAADxAAb
SQL> select obj#,OWNER#,NAME,TYPE#,STATUS,FLAGS from obj$ where rowid='AAAAASAABAAAPt8AAB';
OBJ# OWNER# NAME TYPE# STATUS FLAGS
---------- ---------- ------------------------------ ---------- ---------- ----------
73416 0 TEST01 2 1 0
SQL> select obj#,OWNER#,NAME,TYPE#,STATUS,FLAGS from obj$ where rowid='AAAAASAABAAAADxAAb';
OBJ# OWNER# NAME TYPE# STATUS FLAGS
---------- ---------- ------------------------------ ---------- ---------- ----------
1 0 _NEXT_OBJECT 0 0 0
SQL>
SQL> select case when (NextObjNum - MaxObjNum) > 0
2 then 'GOOD'
else 'BAD'
end "OBJ_NUM_STATE"
from (select (select dataobj#
3 4 5 6 from sys.obj$
7 where name = '_NEXT_OBJECT') NextObjNum,
8 (select max(obj#)
9 from sys.obj$) MaxObjNum
10 from dual);
OBJ_
----
GOOD
從這裡來看，_NEXT_OBJECT是ok的。那麼我們重點就放在TEST01上了。
到這裡，看到test01，我才想起這是很久以前做關於手工構造某個由於資料字典資訊不一致而引發的某個600錯誤
而留下的隱患。
根據前面的報錯，我們找到相應的trace，發現如下資訊：
oer 8102.2 - obj# 39, rdba: 0x0040e9c7(afn 1, blk# 59847)
kdk key 8102.2:
ncol: 4, len: 16
key: (16): 04 c3 08 23 14 01 80 01 80 06 00 40 00 f1 00 1b
mask: (2048):
這裡簡單的進行說明：
ncol ---表示列數目
len ---表示長度
key: ():
關於ora-08012錯誤，大家可以參考 OERR: ORA-8102 "index key not found, obj# %s, file %s, block %s (%s)" [ID 8102.1]
下面我們繼續，既然該block有問題，那麼我就dump該block。
alter system dump datafile 1 block 59847
確定為如下2行資料：
row#131[2131] flag: ---D--, lock: 3, len=18
col 0; len 4; (4): c3 08 23 0a
col 1; len 1; (1): 80
col 2; len 1; (1): 80
col 3; len 6; (6): 00 40 00 f1 00 1b
row#133[2113] flag: ------, lock: 3, len=18
col 0; len 4; (4): c3 08 23 0f
col 1; len 1; (1): 80
col 2; len 1; (1): 80
col 3; len 6; (6): 00 40 00 f1 00 1b
SQL> select 2131+44+24*3 from dual;
2131+44+24*3
------------
2247
BBED> set file 1 block 59847
FILE# 1
BLOCK# 59847
BBED> set offset 2247
OFFSET 2247
BBED> d /v
File: /oracle/product/oradata/roger/system01.dbf (1)
Block: 59847 Offsets: 2247 to 2758 Dba:0x0040e9c7
-------------------------------------------------------
010304c3 08230a01 80018006 004000f1 l .....#.......@..
001b0000 04c30823 0202c102 01800600 l .......#........
40fb7c00 1a010003 c3082302 c1020180 l @.|.......#.....
060040fb 7c001b00 0004c308 226402c1 l ..@.|......."d..
03018006 00402750 00090100 04c30822 l .....@'P......."
6202c102 01800600 40fb7c00 1a010004 l b.......@.|.....
c3082263 02c10201 80060040 fb7c001c l .."c.......@.|..
010004c3 08230501 80018006 004000f1 l .....#.......@..
001b0100 04c30822 6002c102 01800600 l ......."`.......
40fb7c00 1a010004 c3082264 01800180 l @.|......."d....
06004000 f1001b01 0004c308 225e02c1 l ..@........."^..
02018006 0040fb7c 001c0100 04c30822 l .....@.|......."
5d02c102 01800600 40fb7c00 1b010004 l ].......@.|.....
c308225b 02c10201 80060040 fb7c001c l .."[.......@.|..
010004c3 08225a02 c1020180 060040fb l ....."Z.......@.
7c001a01 0004c308 225f0180 01800600 l |......."_......
4000f100 1b010004 c3082256 02c11501 l @........."V....
80060040 fb7c0019 000004c3 08225702 l ...@.|......."W.
c1150180 060040fb 7c001801 0004c308 l ......@.|.......
225502c1 14018006 0040fb7b 000f0000 l "U.......@.{....
04c30822 5402c114 01800600 40fb7c00 l ..."T.......@.|.
17010004 c308225a 01800180 06004000 l ......"Z......@.
f1001b01 0004c308 225202c1 15018006 l ........"R......
0040fb7c 00160000 04c30822 5302c115 l .@.|......."S...
01800600 40fb7c00 15010004 c3082251 l ....@.|......."Q
02c11401 80060040 fb7b000c 000004c3 l .......@.{......
08225002 c1140180 060040fb 7c001401 l ."P.......@.|...
0004c308 22550180 01800600 4000f100 l ...."U......@...
1b010004 c308224e 02c11501 80060040 l ......"N.......@
fb7c0013 000004c3 08224f02 c1150180 l .|......."O.....
060040fb 7c001201 0004c308 224d02c1 l ..@.|......."M..
14018006 0040fb7b 00090000 04c30822 l .....@.{......."
<16 bytes per line>
BBED>
BBED> modify /x 14 offset 2253
File: /oracle/product/oradata/roger/system01.dbf (1)
Block: 59847 Offsets: 2253 to 2764 Dba:0x0040e9c7
------------------------------------------------------------------------
14018001 80060040 00f1001b 000004c3 08230202 c1020180 060040fb 7c001a01
0003c308 2302c102 01800600 40fb7c00 1b000004 c3082264 02c10301 80060040
27500009 010004c3 08226202 c1020180 060040fb 7c001a01 0004c308 226302c1
02018006 0040fb7c 001c0100 04c30823 05018001 80060040 00f1001b 010004c3
08226002 c1020180 060040fb 7c001a01 0004c308 22640180 01800600 4000f100
1b010004 c308225e 02c10201 80060040 fb7c001c 010004c3 08225d02 c1020180
060040fb 7c001b01 0004c308 225b02c1 02018006 0040fb7c 001c0100 04c30822
5a02c102 01800600 40fb7c00 1a010004 c308225f 01800180 06004000 f1001b01
0004c308 225602c1 15018006 0040fb7c 00190000 04c30822 5702c115 01800600
40fb7c00 18010004 c3082255 02c11401 80060040 fb7b000f 000004c3 08225402
c1140180 060040fb 7c001701 0004c308 225a0180 01800600 4000f100 1b010004
c3082252 02c11501 80060040 fb7c0016 000004c3 08225302 c1150180 060040fb
7c001501 0004c308 225102c1 14018006 0040fb7b 000c0000 04c30822 5002c114
01800600 40fb7c00 14010004 c3082255 01800180 06004000 f1001b01 0004c308
224e02c1 15018006 0040fb7c 00130000 04c30822 4f02c115 01800600 40fb7c00
12010004 c308224d 02c11401 80060040 fb7b0009 000004c3 08224c02 c1140180
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 59847:
current = 0xe5a9, required = 0xe5a9
BBED> modify /x 14 offset 2235
File: /oracle/product/oradata/roger/system01.dbf (1)
Block: 59847 Offsets: 2235 to 2746 Dba:0x0040e9c7
------------------------------------------------------------------------
14018001 80060040 00f1001b 010304c3 08231401 80018006 004000f1 001b0000
04c30823 0202c102 01800600 40fb7c00 1a010003 c3082302 c1020180 060040fb
7c001b00 0004c308 226402c1 03018006 00402750 00090100 04c30822 6202c102
01800600 40fb7c00 1a010004 c3082263 02c10201 80060040 fb7c001c 010004c3
08230501 80018006 004000f1 001b0100 04c30822 6002c102 01800600 40fb7c00
1a010004 c3082264 01800180 06004000 f1001b01 0004c308 225e02c1 02018006
0040fb7c 001c0100 04c30822 5d02c102 01800600 40fb7c00 1b010004 c308225b
02c10201 80060040 fb7c001c 010004c3 08225a02 c1020180 060040fb 7c001a01
0004c308 225f0180 01800600 4000f100 1b010004 c3082256 02c11501 80060040
fb7c0019 000004c3 08225702 c1150180 060040fb 7c001801 0004c308 225502c1
14018006 0040fb7b 000f0000 04c30822 5402c114 01800600 40fb7c00 17010004
c308225a 01800180 06004000 f1001b01 0004c308 225202c1 15018006 0040fb7c
00160000 04c30822 5302c115 01800600 40fb7c00 15010004 c3082251 02c11401
80060040 fb7b000c 000004c3 08225002 c1140180 060040fb 7c001401 0004c308
22550180 01800600 4000f100 1b010004 c308224e 02c11501 80060040 fb7c0013
000004c3 08224f02 c1150180 060040fb 7c001201 0004c308 224d02c1 14018006
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 59847:
current = 0xfea9, required = 0xfea9
BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/product/oradata/roger/system01.dbf
BLOCK = 59847
Block Checking: DBA = 4254151, Block Type = KTB-managed data block
**** row 132: key out of order
---- end index block validation
Block 59847 failed with check code 6401
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 1
Total Blocks Failing (Index): 1
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED>
最後重啟後，建立成功，再次檢查發現一切ok。
BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/product/oradata/roger/system01.dbf
BLOCK = 59847
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 1
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
最後嘗試建立檢視，發現一切正常，如下：
SQL> create or replace view show_hidden_v$parameter
2 (INST_ID, NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE,
3 ISMODIFIED , ISADJUSTED , ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH)
4 as
5 select x.inst_id,
6 x.indx + 1,
7 ksppinm,
8 ksppity,
9 ksppstvl,
10 ksppstdvl,
11 ksppstdf,
12 decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'),
13 decode(bitand(ksppiflg / 65536, 3),
14 1,
15 'IMMEDIATE',
16 2,
17 'DEFERRED',
18 3,
19 'IMMEDIATE',
20 'FALSE'),
21 decode(bitand(ksppiflg, 4),
22 4,
23 'FALSE',
24 decode(bitand(ksppiflg / 65536, 3), 0, 'FALSE', 'TRUE')),
25 decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE'),
26 decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE'),
27 decode(bitand(ksppilrmflg / 64, 1), 1, 'TRUE', 'FALSE'),
28 ksppdesc,
29 ksppstcmnt,
30 ksppihash
31 from x$ksppi x, x$ksppcv y
32 where (x.indx = y.indx);
View created.
SQL >
其實方法沒啥奇特的，這種情況畢竟太少太少，覺得沒啥看的，請一笑了之！
昨天準備研究11g的query cache result 特性，準備用10g的老方法來直接通過
show parameter xxxx的方式來檢視隱含引數，發現下面的建立檢視語句居然報錯ora-08102
如下是建立檢視的指令碼，後面是錯誤：
create or replace view show_hidden_v$parameter
(INST_ID, NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE,
ISMODIFIED , ISADJUSTED , ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH)
as
select x.inst_id,
x.indx + 1,
ksppinm,
ksppity,
ksppstvl,
ksppstdvl,
ksppstdf,
decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'),
decode(bitand(ksppiflg / 65536, 3),
1,
'IMMEDIATE',
2,
'DEFERRED',
3,
'IMMEDIATE',
'FALSE'),
decode(bitand(ksppiflg, 4),
4,
'FALSE',
decode(bitand(ksppiflg / 65536, 3), 0, 'FALSE', 'TRUE')),
decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE'),
decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE'),
decode(bitand(ksppilrmflg / 64, 1), 1, 'TRUE', 'FALSE'),
ksppdesc,
ksppstcmnt,
ksppihash
from x$ksppi x, x$ksppcv y
where (x.indx = y.indx);
ORA-08102: index key not found, obj# 39, file 1, block 59847 (2)
從上面的8102錯誤來看，很明顯是資料字典資訊不一致了，也就是說該記錄在ind$可能已經被清除了，
而在obj$中還存在。我們來看看obj# 39是什麼物件？
SQL> SELECT relative_fno, owner, segment_name, segment_type
2 FROM dba_extents
3 WHERE file_id = 1
4 AND 59847 BETWEEN block_id AND block_id + blocks - 1;
RELATIVE_FNO OWNER SEGMENT_NAME SEGMENT_TYPE
------------ ------- ------------------------------ ------------------
1 SYS I_OBJ4 INDEX
SQL>
SQL> select owner,object_name,object_type,object_id from
2 dba_objects where object_name='I_OBJ4';
OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
-------------------- ------------------------- ------------------- ----------
SYS I_OBJ4 INDEX 39
SQL>
對於ora-08102錯誤，如果是發生在index上，那麼我們直接drop index然後重建就ok了。
那我們來試試直接重建會怎麼樣？
SQL> alter system set event='38003 trace name context forever, level 10' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167395328 bytes
Fixed Size 1335220 bytes
Variable Size 104857676 bytes
Database Buffers 58720256 bytes
Redo Buffers 2482176 bytes
Database mounted.
Database opened.
SQL> alter index I_OBJ4 rebuild;
alter index I_OBJ4 rebuild
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00060: deadlock detected while waiting for resource
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup migrate;
ORACLE instance started.
Total System Global Area 167395328 bytes
Fixed Size 1335220 bytes
Variable Size 104857676 bytes
Database Buffers 58720256 bytes
Redo Buffers 2482176 bytes
Database mounted.
Database opened.
SQL> alter index I_OBJ4 rebuild;
alter index I_OBJ4 rebuild
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00060: deadlock detected while waiting for resource
SQL> drop index I_OBJ4;
drop index I_OBJ4
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
SQL> alter index I_OBJ4 rebuild online;
alter index I_OBJ4 rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 39, file 1, block 59847 (2)
SQL> analyze table obj$ VALIDATE STRUCTURE CASCADE;
analyze table obj$ VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
到這裡，可能有人會問，為什麼使用event 38003或migrate 模式無法rebuild 該index呢？
很簡單，該index的obj# <56, 換句話說，也就是對於bootstrap$核心物件是無法通過上面
的2種方式來完成重建的。
通常來說到這個地步，如果不使用其他手段的話，那麼只能使用ODU或DUL進行抽取資料然後重建資料庫了。
其實對於這個問題，我們可以藉助BBED來進行修復。
既然是資料不一致，那麼我就想知道到底是哪兒不一致了？metalink 提供處理ora-8102的方法：
SQL> SELECT /*+ FULL(t1) */ DATAOBJ#, TYPE#,OWNER#,rowid
2 FROM obj$ t1
3 MINUS
4 SELECT /*+ index(t I_OBJ4) */ DATAOBJ#, TYPE#,OWNER#,rowid
5 FROM obj$ t;
DATAOBJ# TYPE# OWNER# ROWID
---------- ---------- ---------- ------------------
73416 2 0 AAAAASAABAAAPt8AAB
73419 0 0 AAAAASAABAAAADxAAb
SQL> select obj#,OWNER#,NAME,TYPE#,STATUS,FLAGS from obj$ where rowid='AAAAASAABAAAPt8AAB';
OBJ# OWNER# NAME TYPE# STATUS FLAGS
---------- ---------- ------------------------------ ---------- ---------- ----------
73416 0 TEST01 2 1 0
SQL> select obj#,OWNER#,NAME,TYPE#,STATUS,FLAGS from obj$ where rowid='AAAAASAABAAAADxAAb';
OBJ# OWNER# NAME TYPE# STATUS FLAGS
---------- ---------- ------------------------------ ---------- ---------- ----------
1 0 _NEXT_OBJECT 0 0 0
SQL>
SQL> select case when (NextObjNum - MaxObjNum) > 0
2 then 'GOOD'
else 'BAD'
end "OBJ_NUM_STATE"
from (select (select dataobj#
3 4 5 6 from sys.obj$
7 where name = '_NEXT_OBJECT') NextObjNum,
8 (select max(obj#)
9 from sys.obj$) MaxObjNum
10 from dual);
OBJ_
----
GOOD
從這裡來看，_NEXT_OBJECT是ok的。那麼我們重點就放在TEST01上了。
到這裡，看到test01，我才想起這是很久以前做關於手工構造某個由於資料字典資訊不一致而引發的某個600錯誤
而留下的隱患。
根據前面的報錯，我們找到相應的trace，發現如下資訊：
oer 8102.2 - obj# 39, rdba: 0x0040e9c7(afn 1, blk# 59847)
kdk key 8102.2:
ncol: 4, len: 16
key: (16): 04 c3 08 23 14 01 80 01 80 06 00 40 00 f1 00 1b
mask: (2048):
這裡簡單的進行說明：
ncol ---表示列數目
len ---表示長度
key: (
關於ora-08012錯誤，大家可以參考 OERR: ORA-8102 "index key not found, obj# %s, file %s, block %s (%s)" [ID 8102.1]
下面我們繼續，既然該block有問題，那麼我就dump該block。
alter system dump datafile 1 block 59847
確定為如下2行資料：
row#131[2131] flag: ---D--, lock: 3, len=18
col 0; len 4; (4): c3 08 23 0a
col 1; len 1; (1): 80
col 2; len 1; (1): 80
col 3; len 6; (6): 00 40 00 f1 00 1b
row#133[2113] flag: ------, lock: 3, len=18
col 0; len 4; (4): c3 08 23 0f
col 1; len 1; (1): 80
col 2; len 1; (1): 80
col 3; len 6; (6): 00 40 00 f1 00 1b
SQL> select 2131+44+24*3 from dual;
2131+44+24*3
------------
2247
BBED> set file 1 block 59847
FILE# 1
BLOCK# 59847
BBED> set offset 2247
OFFSET 2247
BBED> d /v
File: /oracle/product/oradata/roger/system01.dbf (1)
Block: 59847 Offsets: 2247 to 2758 Dba:0x0040e9c7
-------------------------------------------------------
010304c3 08230a01 80018006 004000f1 l .....#.......@..
001b0000 04c30823 0202c102 01800600 l .......#........
40fb7c00 1a010003 c3082302 c1020180 l @.|.......#.....
060040fb 7c001b00 0004c308 226402c1 l ..@.|......."d..
03018006 00402750 00090100 04c30822 l .....@'P......."
6202c102 01800600 40fb7c00 1a010004 l b.......@.|.....
c3082263 02c10201 80060040 fb7c001c l .."c.......@.|..
010004c3 08230501 80018006 004000f1 l .....#.......@..
001b0100 04c30822 6002c102 01800600 l ......."`.......
40fb7c00 1a010004 c3082264 01800180 l @.|......."d....
06004000 f1001b01 0004c308 225e02c1 l ..@........."^..
02018006 0040fb7c 001c0100 04c30822 l .....@.|......."
5d02c102 01800600 40fb7c00 1b010004 l ].......@.|.....
c308225b 02c10201 80060040 fb7c001c l .."[.......@.|..
010004c3 08225a02 c1020180 060040fb l ....."Z.......@.
7c001a01 0004c308 225f0180 01800600 l |......."_......
4000f100 1b010004 c3082256 02c11501 l @........."V....
80060040 fb7c0019 000004c3 08225702 l ...@.|......."W.
c1150180 060040fb 7c001801 0004c308 l ......@.|.......
225502c1 14018006 0040fb7b 000f0000 l "U.......@.{....
04c30822 5402c114 01800600 40fb7c00 l ..."T.......@.|.
17010004 c308225a 01800180 06004000 l ......"Z......@.
f1001b01 0004c308 225202c1 15018006 l ........"R......
0040fb7c 00160000 04c30822 5302c115 l .@.|......."S...
01800600 40fb7c00 15010004 c3082251 l ....@.|......."Q
02c11401 80060040 fb7b000c 000004c3 l .......@.{......
08225002 c1140180 060040fb 7c001401 l ."P.......@.|...
0004c308 22550180 01800600 4000f100 l ...."U......@...
1b010004 c308224e 02c11501 80060040 l ......"N.......@
fb7c0013 000004c3 08224f02 c1150180 l .|......."O.....
060040fb 7c001201 0004c308 224d02c1 l ..@.|......."M..
14018006 0040fb7b 00090000 04c30822 l .....@.{......."
<16 bytes per line>
BBED>
BBED> modify /x 14 offset 2253
File: /oracle/product/oradata/roger/system01.dbf (1)
Block: 59847 Offsets: 2253 to 2764 Dba:0x0040e9c7
------------------------------------------------------------------------
14018001 80060040 00f1001b 000004c3 08230202 c1020180 060040fb 7c001a01
0003c308 2302c102 01800600 40fb7c00 1b000004 c3082264 02c10301 80060040
27500009 010004c3 08226202 c1020180 060040fb 7c001a01 0004c308 226302c1
02018006 0040fb7c 001c0100 04c30823 05018001 80060040 00f1001b 010004c3
08226002 c1020180 060040fb 7c001a01 0004c308 22640180 01800600 4000f100
1b010004 c308225e 02c10201 80060040 fb7c001c 010004c3 08225d02 c1020180
060040fb 7c001b01 0004c308 225b02c1 02018006 0040fb7c 001c0100 04c30822
5a02c102 01800600 40fb7c00 1a010004 c308225f 01800180 06004000 f1001b01
0004c308 225602c1 15018006 0040fb7c 00190000 04c30822 5702c115 01800600
40fb7c00 18010004 c3082255 02c11401 80060040 fb7b000f 000004c3 08225402
c1140180 060040fb 7c001701 0004c308 225a0180 01800600 4000f100 1b010004
c3082252 02c11501 80060040 fb7c0016 000004c3 08225302 c1150180 060040fb
7c001501 0004c308 225102c1 14018006 0040fb7b 000c0000 04c30822 5002c114
01800600 40fb7c00 14010004 c3082255 01800180 06004000 f1001b01 0004c308
224e02c1 15018006 0040fb7c 00130000 04c30822 4f02c115 01800600 40fb7c00
12010004 c308224d 02c11401 80060040 fb7b0009 000004c3 08224c02 c1140180
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 59847:
current = 0xe5a9, required = 0xe5a9
BBED> modify /x 14 offset 2235
File: /oracle/product/oradata/roger/system01.dbf (1)
Block: 59847 Offsets: 2235 to 2746 Dba:0x0040e9c7
------------------------------------------------------------------------
14018001 80060040 00f1001b 010304c3 08231401 80018006 004000f1 001b0000
04c30823 0202c102 01800600 40fb7c00 1a010003 c3082302 c1020180 060040fb
7c001b00 0004c308 226402c1 03018006 00402750 00090100 04c30822 6202c102
01800600 40fb7c00 1a010004 c3082263 02c10201 80060040 fb7c001c 010004c3
08230501 80018006 004000f1 001b0100 04c30822 6002c102 01800600 40fb7c00
1a010004 c3082264 01800180 06004000 f1001b01 0004c308 225e02c1 02018006
0040fb7c 001c0100 04c30822 5d02c102 01800600 40fb7c00 1b010004 c308225b
02c10201 80060040 fb7c001c 010004c3 08225a02 c1020180 060040fb 7c001a01
0004c308 225f0180 01800600 4000f100 1b010004 c3082256 02c11501 80060040
fb7c0019 000004c3 08225702 c1150180 060040fb 7c001801 0004c308 225502c1
14018006 0040fb7b 000f0000 04c30822 5402c114 01800600 40fb7c00 17010004
c308225a 01800180 06004000 f1001b01 0004c308 225202c1 15018006 0040fb7c
00160000 04c30822 5302c115 01800600 40fb7c00 15010004 c3082251 02c11401
80060040 fb7b000c 000004c3 08225002 c1140180 060040fb 7c001401 0004c308
22550180 01800600 4000f100 1b010004 c308224e 02c11501 80060040 fb7c0013
000004c3 08224f02 c1150180 060040fb 7c001201 0004c308 224d02c1 14018006
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 59847:
current = 0xfea9, required = 0xfea9
BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/product/oradata/roger/system01.dbf
BLOCK = 59847
Block Checking: DBA = 4254151, Block Type = KTB-managed data block
**** row 132: key out of order
---- end index block validation
Block 59847 failed with check code 6401
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 1
Total Blocks Failing (Index): 1
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED>
最後重啟後，建立成功，再次檢查發現一切ok。
BBED> verify
DBVERIFY - Verification starting
FILE = /oracle/product/oradata/roger/system01.dbf
BLOCK = 59847
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 1
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
最後嘗試建立檢視，發現一切正常，如下：
SQL> create or replace view show_hidden_v$parameter
2 (INST_ID, NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE,
3 ISMODIFIED , ISADJUSTED , ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH)
4 as
5 select x.inst_id,
6 x.indx + 1,
7 ksppinm,
8 ksppity,
9 ksppstvl,
10 ksppstdvl,
11 ksppstdf,
12 decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'),
13 decode(bitand(ksppiflg / 65536, 3),
14 1,
15 'IMMEDIATE',
16 2,
17 'DEFERRED',
18 3,
19 'IMMEDIATE',
20 'FALSE'),
21 decode(bitand(ksppiflg, 4),
22 4,
23 'FALSE',
24 decode(bitand(ksppiflg / 65536, 3), 0, 'FALSE', 'TRUE')),
25 decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE'),
26 decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE'),
27 decode(bitand(ksppilrmflg / 64, 1), 1, 'TRUE', 'FALSE'),
28 ksppdesc,
29 ksppstcmnt,
30 ksppihash
31 from x$ksppi x, x$ksppcv y
32 where (x.indx = y.indx);
View created.
SQL >
其實方法沒啥奇特的，這種情況畢竟太少太少，覺得沒啥看的，請一笑了之！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22990797/viewspace-710513/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 小程式渲染層核心不一致導致Array.flat()不可用
- ZooKeeper 避坑指南： ZooKeeper 3.6.4 版本 BUG 導致的資料不一致問題
- 故障分析 | replace into 導致主備不一致
- flink上下游並行度不一致導致的資料亂序問題並行
- 時區不一致導致spring應用異常Spring
- 資料庫同步 Elasticsearch 後資料不一致，怎麼辦？資料庫Elasticsearch
- 資料庫主從不一致，怎麼解？資料庫
- 快取與資料庫不一致，咋辦？快取資料庫
- MySQL主從資料不一致，怎麼辦？MySql
- 關於 Laravel mix 導致 Bootstrap 失效的問題Laravelboot
- Git因換行符不一致導致反覆有修改記錄Git
- 【案例】Oracle報錯ORA-01194 ORA-01110 由於資料庫SCN不一致導致無法啟動Oracle資料庫
- PHP 核心技術 --物件導向PHP物件
- 重溫物件導向核心上物件
- 物件導向思想的核心概念物件
- netcore服務程式暴力退出導致的業務資料不一致的一種解決方案（優雅退出）NetCore
- MySQL MGR如何修復資料不一致的節點MySql
- BUG—Nuget包版本不一致導致程式行為與預期不符
- gson-plugin告別Json資料型別不一致（一）PluginJSON資料型別
- mysql雙寫造成主從資料不一致的實驗MySql
- 糟了，生產環境資料竟然不一致，人麻了！
- [求助帖] Grafana 統計資料與 Jmeter 統計資料不一致的問題。GrafanaJMeter
- ORA-21561: OID generation failed hostname與/etc/hosts不一致導致安裝報錯AI
- 三年之久的 etcd3 資料不一致 bug 分析
- 如何解決MySQL 主從複製資料不一致問題MySql
- HDFS 清理資料導致進入安全模式模式
- 導致資料洩露的 6 個疏忽
- Jtti：redis主從延遲資料不一致問題如何解決JttiRedis
- 兄弟連go教程（19）資料 - ⾯物件導向Go物件
- 煩人的資料不一致問題到底怎麼解決？——透過“共識”達成資料一致性
- 快取與資料庫雙寫，不一致問題及解決方案快取資料庫
- Oracle優化案例-分頁語句返回資料順序不一致（十一）Oracle優化
- poi讀取表格資料和表格顯示不一致問題解決
- ~~核心程式設計（五）：物件導向——多繼承~~程式設計物件繼承
- 看完就能掌握的PHP核心技術 - 物件導向PHP物件
- LF和CRLF換行符不一致導致的Git顯示修改問題分析及解決Git
- 大資料學習之路——java物件導向（二）大資料Java物件
- MySQL 5.6因為OOM導致資料庫重啟MySqlOOM資料庫
- ASM空間爆滿導致資料庫掛起ASM資料庫