以前同事问我，能否构造一个逻辑坏块？我们知道坏块通常分为物理坏块和逻辑坏块两种。
物理坏块：通常是由于硬件损坏如磁盘异常导致；
逻辑坏块：通常是由于oracle bug导致，比如data block和index block数据不一致等；
那么如何构造一个逻辑坏块呢？我们这里就来构造一个index block内容跟其对应的data block
内容不一致的情况，其实非常简单，我们随便改下index entry就行。
SQL> show user
USER is "ROGER"
SQL> create table t1 as select * from dba_objects where rownum < 100;
Table created.
SQL> create index idx_id on t1(object_id);
Index created.
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,
2 dbms_rowid.rowid_block_number(rowid) blk#
3 from t1;
FILE# BLK#
---------- ----------
6 28516
6 28516
6 28516
6 28516
. .....
. .....
. .....
. .....
. .....
. .....
6 28517
6 28517
6 28517
6 28517
6 28517
6 28517
99 rows selected.
SQL> select object_id from dba_objects where object_name=upper('idx_id');
OBJECT_ID
----------
54609
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 54609';
Session altered.
++++++++++++++++++ trace file +++++++++++++++++++++++++++++
--- index treedump
*** 2012-03-05 08:59:56.763
*** ACTION NAME:() 2012-03-05 08:59:56.762
*** MODULE NAME:(SQL*Plus) 2012-03-05 08:59:56.762
*** SERVICE NAME:(SYS$USERS) 2012-03-05 08:59:56.762
*** SESSION ID:(159.5) 2012-03-05 08:59:56.762
----- begin tree dump
leaf: 0x1806f9c 25194396 (0: nrow: 99 rrow: 99)
----- end tree dump
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> select dbms_utility.data_block_address_file(TO_NUMBER('1806f9c', 'XXXXXXXX')) file_id,
2 dbms_utility.data_block_address_block(TO_NUMBER('1806f9c', 'XXXXXXXX')) block_id
3 from dual;
FILE_ID BLOCK_ID
---------- ----------
6 28572
++++++ index block structure ++++++
BBED> set file 6 block 28572
FILE# 6
BLOCK# 28572
BBED> p kdxle
struct kdxle, 32 bytes @100
struct kdxlexco, 16 bytes @100
ub1 kdxcolev @100 0x00
ub1 kdxcolok @101 0x00
ub1 kdxcoopc @102 0x80
ub1 kdxconco @103 0x02
ub4 kdxcosdc @104 0x00000000
sb2 kdxconro @108 99
b2 kdxcofbo @110 234
b2 kdxcofeo @112 6844
b2 kdxcoavs @114 6610
b2 kdxlespl @116 0
sb2 kdxlende @118 0
ub4 kdxlenxt @120 0x00000000
ub4 kdxleprv @124 0x00000000
ub1 kdxledsz @128 0x00
ub1 kdxleunuse @129 0x00
++++++ 我们这里修改 kdxlexco 即可，我这里就修改kdxcofbo吧。++++++
BBED> set off 110
OFFSET 110
BBED> modify /x e9
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /home/ora10g/oradata/roger/roger02.dbf (6)
Block: 28572 Offsets: 110 to 621 Dba:0x01806f9c
------------------------------------------------------------------------
e900bc1a d2190000 00000000 00000000 00000000 0000601f 0000541f 481f3c1f
301f241f 181f0c1f 001ff41e e81edc1e d01ec41e b81eac1e a01e941e 881e7c1e
701e641e 581e4c1e 401e341e 281e1c1e 101e041e f81dec1d e01dd41d c81dbc1d
b01da41d 981d8c1d 801d741d 681d5c1d 501d441d 381d2c1d 201d141d 081dfc1c
f01ce41c d81ccc1c c01cb41c a81c9c1c 901c841c 781c6c1c 601c541c 481c3c1c
301c241c 181c0c1c 001cf41b e81bdc1b d01bc41b b81bac1b a01b941b 881b7c1b
701b641b 581b4c1b 401b341b 281b1c1b 101b041b f81aec1a e01ad41a c81abc1a
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 6, Block 28572:
current = 0x7798, required = 0x7798
++++++ check ++++++
SQL> conn /as sysdba
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
6 50403 1 0 ALL ZERO
6 28572 1 2779528 UNKNOWN
6 51684 1 1527408 UNKNOWN
我们可以发现 block 28572 的CORRUPTION_CHANGE# 大于0. 说明该block为逻辑坏块。
++++++ rman check ++++++
RMAN> backup validate check logical database;
Starting backup at 05-MAR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/home/ora10g/oradata/roger/roger01.dbf
input datafile fno=00002 name=/home/ora10g/oradata/roger/undotbs01.dbf
input datafile fno=00006 name=/home/ora10g/oradata/roger/roger02.dbf
input datafile fno=00001 name=/home/ora10g/oradata/roger/system01.dbf
input datafile fno=00003 name=/home/ora10g/oradata/roger/sysaux01.dbf
input datafile fno=00004 name=/home/ora10g/oradata/roger/users01.dbf
input datafile fno=00008 name=/home/ora10g/oradata/roger/sqlt_01.dbf
input datafile fno=00007 name=/home/ora10g/oradata/roger/test1.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 05-MAR-12
++++++ alert log ++++++
Mon Mar 05 18:30:19 PST 2012
Error backing up file 6, block 28572: logical corruption
Mon Mar 05 18:30:38 PST 2012
Hex dump of (file 6, block 50403) in trace file /home/ora10g/admin/roger/udump/roger_ora_9126.trc
Corrupt block relative dba: 0x0180c4e3 (file 6, block 50403)
Completely zero block found during backing up datafile
Reread of blocknum=50403, file=/home/ora10g/oradata/roger/roger02.dbf. found same corrupt data
Reread of blocknum=50403, file=/home/ora10g/oradata/roger/roger02.dbf. found same corrupt data
Reread of blocknum=50403, file=/home/ora10g/oradata/roger/roger02.dbf. found same corrupt data
Reread of blocknum=50403, file=/home/ora10g/oradata/roger/roger02.dbf. found same corrupt data
Reread of blocknum=50403, file=/home/ora10g/oradata/roger/roger02.dbf. found same corrupt data
Error backing up file 6, block 51684: logical corruption
SQL> set lines 150
SQL> col segment_name for a15
SQL> col owner for a20
SQL> SELECT tablespace_name, segment_type, owner, segment_name
2 FROM dba_extents
3 WHERE file_id = 6
4 AND 28572
5 BETWEEN block_id AND block_id + blocks - 1;
TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME
------------------------------ ------------------ -------------------- ---------------
ROGER INDEX ROGER IDX_ID
这里可以看到，目前构造出了2个逻辑坏块。file 6 block 50403/51684 是以前实验留下的，不用管。
发表回复