oracle坏块跳过限制
table中各种类型block坏块是否能被跳过
在table遇到的各种坏块中，大部分情况，我们都可以通过设置event 10231或者dbms_repair来跳过坏块，抢救其他数据；但是在部分情况下，我们设置了他们依然不能跳过坏块，数据库依然报ORA-01578,本文测试了table中各种类型的block，证明在哪些block出现异常之后不能被跳过.
如果是事务数据块出现坏块可以通过dbms_repair.skip_corrupt_blocks来标记坏块(也可以使用event 10231)来实现；对于BITMAP BLOCK如果出现坏块不会对于读取数据无影响(至于其他操作,请见后续blog)；SEGMENT HEADER如果出现坏块，无法通过跳过坏块来实现获取其他数据(正常block)
测试环境准备
SQL> create table zhuo.zhuo
2 as
3 select * from dba_objects;
Table created.
SQL> select count(*) from zhuo.zhuo;
COUNT(*)
----------
80067
查询相关block信息
SQL> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,blocks,extents from DBA_SEGMENTS
2 WHERE OWNER='ZHUO' AND SEGMENT_NAME='ZHUO';
SQL> set pages 1000 lines 1000
SQL> col SEGMENT_NAME for a10
SQL> /
SEGMENT_NA HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
---------- ----------- ------------ ---------- ----------
ZHUO 5 1410 1280 25
SQL> select
dbms_rowid.rowid_relative_fno(rowid)rel_fno,
2 3 max(dbms_rowid.rowid_block_number(rowid)) max_block,
4 min(dbms_rowid.rowid_block_number(rowid)) min_block
5 from zhuo.zhuo
6 group by dbms_rowid.rowid_relative_fno(rowid);
REL_FNO MAX_BLOCK MIN_BLOCK
---------- ---------- ----------
5 2581 1411
SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,blocks from dba_extents where owner='ZHUO'
2 AND SEGMENT_NAME='ZHUO';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 5 1408 8
1 5 1416 8
2 5 1424 8
3 5 1432 8
4 5 1440 8
5 5 1448 8
6 5 1456 8
7 5 1464 8
8 5 1472 8
9 5 1480 8
10 5 1488 8
11 5 1496 8
12 5 1504 8
13 5 1512 8
14 5 1520 8
15 5 1528 8
16 5 1536 128
17 5 1664 128
18 5 1792 128
19 5 1920 128
20 5 2048 128
21 5 2176 128
22 5 2304 128
23 5 2432 128
24 5 2560 128
25 rows selected.
通过这里可以知道:真正的存储数据是从block 1411开始,至于block 1408、1409、1410是什么，使用dump block分析
验证block类型
dump 块：
SQL> oradebug setmypid
Statement processed.
SQL> alter session set tracefile_identifier='1408';
Session altered.
SQL> alter system dump datafile 5 block 1408;
System altered.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/zhuo/zhuo/trace/zhuo_ora_2297_1408.trc
SQL> alter session set tracefile_identifier='1409';
Session altered.
SQL> alter system dump datafile 5 block 1409;
System altered.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/zhuo/zhuo/trace/zhuo_ora_2297_1409.trc
SQL> alter session set tracefile_identifier='1410';
Session altered.
SQL> alter system dump datafile 5 block 1410;
System altered.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/zhuo/zhuo/trace/zhuo_ora_2297_1410.trc
SQL> alter session set tracefile_identifier='1411';
Session altered.
SQL> alter system dump datafile 5 block 1411;
System altered.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/zhuo/zhuo/trace/zhuo_ora_2297_1411.trc
--该block是另外extent的开始，所以也尝试分析是否有特殊之处
SQL> alter session set tracefile_identifier='1416';
Session altered.
SQL> alter system dump datafile 5 block 1416;
System altered.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/zhuo/zhuo/trace/zhuo_ora_2297_1416.trc
查看dump文件的header信息
Start dump data blocks tsn: 5 file#:5 minblk 1408 maxblk 1408
Block dump from cache:
Dump of buffer cache at level 4 for tsn=5 rdba=20972928
BH (0x753eb2e8) file#: 5 rdba: 0x01400580 (5/1408) class: 8 ba: 0x7522e000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 122,28
dbwrid: 0 obj: 80625 objn: 80625 tsn: 5 afn: 5 hint: f
hash: [0x7cbc7460,0x7cbc7460] lru: [0x753eb510,0x753eb2a0]
ckptq: [NULL] fileq: [NULL] objq: [0x753eb058,0x753eb538] objaq: [0x753eb548,0x753eb068]
st: XCURRENT md: NULL fpin: ‘ktspfwh6: ktspffbmb’ tch: 1
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 5 rdba: 0x01400580 (5/1408)
scn: 0x0000.000bc692 seq: 0x03 flg: 0x04 tail: 0xc6922003
frmt: 0x02 chkval: 0xbcdd type: 0x20=FIRST LEVEL BITMAP BLOCK
Start dump data blocks tsn: 5 file#:5 minblk 1409 maxblk 1409
Block dump from cache:
Dump of buffer cache at level 4 for tsn=5 rdba=20972929
BH (0x753eb420) file#: 5 rdba: 0x01400581 (5/1409) class: 9 ba: 0x75230000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 122,28
dbwrid: 0 obj: 80625 objn: 80625 tsn: 5 afn: 5 hint: f
hash: [0x7c4ae630,0x7c4ae630] lru: [0x753eb648,0x753eb3d8]
ckptq: [NULL] fileq: [NULL] objq: [0x753eb400,0x753eb670] objaq: [0x753eb680,0x753eb410]
st: XCURRENT md: NULL fpin: ‘ktspswh4: ktspfsbmb’ tch: 1
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 5 rdba: 0x01400581 (5/1409)
scn: 0x0000.000bc692 seq: 0x19 flg: 0x04 tail: 0xc6922119
frmt: 0x02 chkval: 0xbdd9 type: 0x21=SECOND LEVEL BITMAP BLOCK
Start dump data blocks tsn: 5 file#:5 minblk 1410 maxblk 1410
Block dump from cache:
Dump of buffer cache at level 4 for tsn=5 rdba=20972930
BH (0x753eb558) file#: 5 rdba: 0x01400582 (5/1410) class: 4 ba: 0x75232000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 122,28
dbwrid: 0 obj: 80625 objn: 80625 tsn: 5 afn: 5 hint: f
hash: [0x7dbf2450,0x747ea9e0] lru: [0x753eb780,0x753eb510]
ckptq: [NULL] fileq: [NULL] objq: [0x753eb538,0x6b3df5e8] objaq: [0x6b3df5d8,0x753eb548]
st: XCURRENT md: NULL fpin: ‘ktswh03: ktscts’ tch: 7
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 5 rdba: 0x01400582 (5/1410)
scn: 0x0000.000bc696 seq: 0x01 flg: 0x04 tail: 0xc6962301
frmt: 0x02 chkval: 0x94f2 type: 0x23=PAGETABLE SEGMENT HEADER
Start dump data blocks tsn: 5 file#:5 minblk 1411 maxblk 1411
Block dump from cache:
Dump of buffer cache at level 4 for tsn=5 rdba=20972931
Block dump from disk:
buffer tsn: 5 rdba: 0x01400583 (5/1411)
scn: 0x0000.000bc666 seq: 0x02 flg: 0x04 tail: 0xc6660602
frmt: 0x02 chkval: 0x1b89 type: 0x06=trans data
Start dump data blocks tsn: 5 file#:5 minblk 1416 maxblk 1416
Block dump from cache:
Dump of buffer cache at level 4 for tsn=5 rdba=20972936
Block dump from disk:
buffer tsn: 5 rdba: 0x01400588 (5/1416)
scn: 0x0000.000bc666 seq: 0x02 flg: 0x04 tail: 0xc6660602
frmt: 0x02 chkval: 0xd6af type: 0x06=trans data
这里可以知道：
1.block 1408、1409为BITMAP BLOCK
2.block 1410为SEGMENT HEADER(和dba_segments视图中一致)
3.除extent 0中有特殊(含BITMAP BLOCK和SEGMENT HEADER)block，其他extent只包含事务数据
测试block
测试block 1416 --普通data block(extent 1)
--block 80包含条数
SQL> select count(rowid)
2 from zhuo.zhuo
3 where dbms_rowid.rowid_block_number(rowid)=1416
4 and dbms_rowid.rowid_relative_fno(rowid)=5;
COUNT(ROWID)
------------
80
--bbed修改tailchk
BBED> info all
File# Name Size(blks)
----- ---- ----------
1 /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_system_gxd20h1 0
2 /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_sysaux_gxd20k1 0
3 /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_undotbs1_gxd20 0
4 /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_users_gxd20pxk 0
5 /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_ 0
BBED> set file 5 block 1416
FILE# 5
BLOCK# 1416
BBED> p tailchk
ub4 tailchk @8188 0xc6660602
BBED> d /v count 4
File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
Block: 1416 Offsets: 8188 to 8191 Dba:0x01400588
-------------------------------------------------------
020666c6 l ..f.
<16 bytes per line>
BBED> m /x 020666c7
File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
Block: 1416 Offsets: 8188 to 8191 Dba:0x01400588
------------------------------------------------------------------------
020666c7
<32 bytes per line>
BBED> sum apply
Check value for File 5, Block 1416:
current = 0xd7af, required = 0xd7af
BBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1416
Block 1416 is corrupt
Corrupt block relative dba: 0x01400588 (file 0, block 1416)
Fractured block found during verification
Data in bad block:
type: 6 format: 2 rdba: 0x01400588
last change scn: 0x0000.000bc666 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xc7660602
check value in block header: 0xd7af
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 2
Message 531 not found; product=RDBMS; facility=BBED
--查询坏块
SQL> select count(*) from zhuo.zhuo;
select count(*) from zhuo.zhuo
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1416)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'
--跳过坏块
SQL> exec dbms_repair.skip_corrupt_blocks('ZHUO','ZHUO');
PL/SQL procedure successfully completed.
SQL> select skip_corrupt from dba_tables where table_name='ZHUO' AND OWNER='ZHUO';
SKIP_COR
--------
ENABLED
SQL> select count(*) from zhuo.zhuo;
COUNT(*)
----------
79987
只是标记，alert日志里面还是有坏块报错
Wed Nov 04 15:37:47 2020
Hex dump of (file 5, block 1416) in trace file /u01/app/oracle/diag/rdbms/zhuo/zhuo/trace/zhuo_ora_2202.trc
Corrupt block relative dba: 0x01400588 (file 5, block 1416)
Fractured block found during user buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x01400588
last change scn: 0x0000.000bc666 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xc7660602
check value in block header: 0xd7af
computed block checksum: 0x0
Reading datafile ‘/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf’ for corruption at rdba: 0x01400588 (file 5, block 1416)
Reread (file 5, block 1416) found same corrupt data (no logical check)
Wed Nov 04 15:37:47 2020
Corrupt Block Found
TSN = 5, TSNAME = ZHUO
RFN = 5, BLK = 1416, RDBA = 20972936
OBJN = 80625, OBJD = 80625, OBJECT = ZHUO, SUBOBJECT =
SEGMENT OWNER = ZHUO, SEGMENT TYPE = Table Segment
SQL> select 80067-79987 from dual;
80067-79987
-----------
80
损失了这个块里面的80条数据。
修复坏块（前提是我们知道如何修复）
BBED> m /x 020666c6
File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
Block: 1416 Offsets: 8188 to 8191 Dba:0x01400588
------------------------------------------------------------------------
020666c6
<32 bytes per line>
BBED> sum apply
Check value for File 5, Block 1416:
current = 0xd6af, required = 0xd6af
BBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1416
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
--除掉标记表坏块
SQL> BEGIN
2 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
3 SCHEMA_NAME => 'ZHUO',
4 OBJECT_NAME => 'ZHUO',
5 OBJECT_TYPE => dbms_repair.table_object,
6 FLAGS => dbms_repair.NOSKIP_FLAG);
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> select skip_corrupt from dba_tables where table_name='ZHUO' AND OWNER='ZHUO';
SKIP_COR
--------
DISABLED
SQL> select count(*) from zhuo.zhuo;
COUNT(*)
----------
80067
测试block 1411 --普通data block(extent 0)
坏块出现
SQL> select count(*) from zhuo.zhuo;
select count(*) from zhuo.zhuo
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1411)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'
完整的dbms_repair标记一个坏块的过程
SQL> BEGIN
2 DBMS_REPAIR.ADMIN_TABLES (
3 TABLE_NAME => 'REPAIR_TABLE',
4 TABLE_TYPE => dbms_repair.repair_table,
5 ACTION => dbms_repair.create_action,
6 TABLESPACE => '&tablespace_name');
7 END;
8 /
Enter value for tablespace_name: zhuo
old 6: TABLESPACE => '&tablespace_name');
new 6: TABLESPACE => 'zhuo');
SQL> select count(*) from zhuo.zhuo;
select count(*) from zhuo.zhuo
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1411)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'
SQL> set serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
SQL> 2 3 4 DBMS_REPAIR.CHECK_OBJECT (
5 SCHEMA_NAME => '&schema_name',
6 OBJECT_NAME => '&object_name',
7 REPAIR_TABLE_NAME => 'REPAIR_TABLE',
8 corrupt_count => num_corrupt);
9 DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
10 END;
11 /
Enter value for schema_name: ZHUO
old 5: SCHEMA_NAME => '&schema_name',
new 5: SCHEMA_NAME => 'ZHUO',
Enter value for object_name: ZHUO
old 6: OBJECT_NAME => '&object_name',
new 6: OBJECT_NAME => 'ZHUO',
number corrupt: 1
PL/SQL procedure successfully completed.
SQL> select * from REPAIR_TABLE;
OBJECT_ID TABLESPACE_ID RELATIVE_FILE_ID BLOCK_ID CORRUPT_TYPE SCHEMA_NAME
---------- ------------- ---------------- ---------- ------------ ------------------------------
OBJECT_NAME BASEOBJECT_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
CORRUPT_DESCRIPTION
------------------------------------------------------------------------------------------------------
REPAIR_DESCRIPTION
------------------------------------------------------------------------------------------------------
MARKED_COR CHECK_TIMEST FIX_TIMESTAM REFORMAT_TIM
---------- ------------ ------------ ------------
80625 5 5 1411 6148 ZHUO
ZHUO
mark block software corrupt
TRUE 04-NOV-20
SQL> DECLARE num_fix INT;
2 BEGIN
3 num_fix := 0;
4 DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
5 SCHEMA_NAME => '&schema_name',
6 OBJECT_NAME=> '&object_name',
7 OBJECT_TYPE => dbms_repair.table_object,
8 REPAIR_TABLE_NAME => 'REPAIR_TABLE',
9 FIX_COUNT=> num_fix);
10 DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));
11 END;
12 /
Enter value for schema_name: ZHUO
old 5: SCHEMA_NAME => '&schema_name',
new 5: SCHEMA_NAME => 'ZHUO',
Enter value for object_name: ZHUO
old 6: OBJECT_NAME=> '&object_name',
new 6: OBJECT_NAME=> 'ZHUO',
num fix: 0
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
3 SCHEMA_NAME => '&schema_name',
4 OBJECT_NAME => '&object_name',
5 OBJECT_TYPE => dbms_repair.table_object,
6 FLAGS => dbms_repair.SKIP_FLAG);
7 END;
8 /
Enter value for schema_name: ZHUO
old 3: SCHEMA_NAME => '&schema_name',
new 3: SCHEMA_NAME => 'ZHUO',
Enter value for object_name: ZHUO
old 4: OBJECT_NAME => '&object_name',
new 4: OBJECT_NAME => 'ZHUO',
PL/SQL procedure successfully completed.
SQL> select skip_corrupt from dba_tables where table_name='ZHUO' AND OWNER='ZHUO';
SKIP_COR
--------
ENABLED
查询正常：
SQL> select count(*) from zhuo.zhuo;
COUNT(*)
----------
79979
不管怎么查询，alert日志里面不在报错，说明和创建repair_table有关。但是本质是一样的。
取消标记
SQL> BEGIN
2 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
3 SCHEMA_NAME => '&schema_name',
4 OBJECT_NAME => '&object_name',
5 OBJECT_TYPE => dbms_repair.table_object,
6 FLAGS => dbms_repair.NOSKIP_FLAG);
7 END;
8 /
Enter value for schema_name: ZHUO
old 3: SCHEMA_NAME => '&schema_name',
new 3: SCHEMA_NAME => 'ZHUO',
Enter value for object_name: ZHUO
old 4: OBJECT_NAME => '&object_name',
new 4: OBJECT_NAME => 'ZHUO',
PL/SQL procedure successfully completed.
SQL> select skip_corrupt from dba_tables where table_name='ZHUO' AND OWNER='ZHUO';
SKIP_COR
--------
DISABLED
测试block 1410 --segment header(extent 0)
破坏段头：
BBED> set file 5 block 1410
FILE# 5
BLOCK# 1410
--segment header 不支持bbed查看结构
BBED> map /v
File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
Block: 1410 Dba:0x01400582
------------------------------------------------------------
BBED-00400: invalid blocktype (35)
BBED> p tailchk
BBED-00400: invalid blocktype (35)
BBED> d /v count 4 offset 8188
File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
Block: 1410 Offsets: 8188 to 8191 Dba:0x01400582
-------------------------------------------------------
012396c6 l .#..
<16 bytes per line>
BBED> m /x 012396c7
File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
Block: 1410 Offsets: 8188 to 8191 Dba:0x01400582
------------------------------------------------------------------------
012396c7
<32 bytes per line>
BBED> sum apply
Check value for File 5, Block 1410:
current = 0x95f2, required = 0x95f2
BBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1410
Block 1410 is corrupt
Corrupt block relative dba: 0x01400582 (file 0, block 1410)
Fractured block found during verification
Data in bad block:
type: 35 format: 2 rdba: 0x01400582
last change scn: 0x0000.000bc696 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xc7962301
check value in block header: 0x95f2
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 2
Message 531 not found; product=RDBMS; facility=BBED
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from zhuo.zhuo;
select count(*) from zhuo.zhuo
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'
–标记跳过坏块
SQL> exec dbms_repair.skip_corrupt_blocks('ZHUO','ZHUO');
PL/SQL procedure successfully completed.
–查询依然失败
SQL> select count(*) from zhuo.zhuo;
select count(*) from zhuo.zhuo
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'
测试block 1408 --L1 bitmap(extent 0)
破坏L1 bitmap
BBED> set file 5 block 1408
FILE# 5
BLOCK# 1408
BBED> map /v
File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
Block: 1408 Dba:0x01400580
------------------------------------------------------------
BBED-00400: invalid blocktype (32) --bitmap不支持查询结构
BBED> d /v count 4 offset 8188
File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
Block: 1408 Offsets: 8188 to 8191 Dba:0x01400580
-------------------------------------------------------
032092c6 l . ..
<16 bytes per line>
BBED> m /x 032092c7
File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
Block: 1408 Offsets: 8188 to 8191 Dba:0x01400580
------------------------------------------------------------------------
032092c7
<32 bytes per line>
BBED> sum apply
Check value for File 5, Block 1408:
current = 0xbddd, required = 0xbddd
BBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1408
Block 1408 is corrupt
Corrupt block relative dba: 0x01400580 (file 0, block 1408)
Fractured block found during verification
Data in bad block:
type: 32 format: 2 rdba: 0x01400580
last change scn: 0x0000.000bc692 seq: 0x3 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xc7922003
check value in block header: 0xbddd
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 2
Message 531 not found; product=RDBMS; facility=BBED
查询数据，不报错
SQL> select count(*) from zhuo.zhuo;
COUNT(*)
----------
80067
SQL> alter system flush buffer_Cache;
System altered.
SQL> select count(*) from zhuo.zhuo;
COUNT(*)
----------
80067
测试block 1408 --L2 bitmap(extent 0)
破坏L2 bitmap
BBED> set file 5 block 1409
FILE# 5
BLOCK# 1409
BBED> d /v offset 8188
File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
Block: 1409 Offsets: 8188 to 8191 Dba:0x01400581
-------------------------------------------------------
192192c6 l .!..
<16 bytes per line>
BBED> m /x 192192c7
File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
Block: 1409 Offsets: 8188 to 8191 Dba:0x01400581
------------------------------------------------------------------------
192192c7
<32 bytes per line>
BBED> sum apply
Check value for File 5, Block 1409:
current = 0xbcd9, required = 0xbcd9
BBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1409
Block 1409 is corrupt
Corrupt block relative dba: 0x01400581 (file 0, block 1409)
Fractured block found during verification
Data in bad block:
type: 33 format: 2 rdba: 0x01400581
last change scn: 0x0000.000bc692 seq: 0x19 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xc7922119
check value in block header: 0xbcd9
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 2
Message 531 not found; product=RDBMS; facility=BBED
查询数据不报错
SQL> select count(*) from zhuo.zhuo;
COUNT(*)
----------
80067
SQL>
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from zhuo.zhuo;
COUNT(*)
----------
80067
总结：
1、学习了制造坏块的方法；
2、查询dba_extents内容，extent 0里面不全是data block，前面还有位图块和段头块。
3、跳过坏块的方法有好几种。本文展示了2种，创建repair table和不创建repair table的方法。
4、通过测试证明，如果是事务数据块出现坏块可以通过dbms_repair.skip_corrupt_blocks来标记坏块(也可以使用event 10231)来实现，标记坏块推荐用创建表的方式，最后注意，删除repair_table和去标淮浍标记；
5、对于BITMAP BLOCK如果出现坏块不会对于读取数据无影响(至于其他操作,请见table中各种坏块对select/dml操作影响)；
6、SEGMENT HEADER如果出现坏块，无法通过跳过坏块来实现获取其他数据(正常block)
参考：
https://www.xifenfei.com/2013/02/table%E4%B8%AD%E5%90%84%E7%A7%8D%E7%B1%BB%E5%9E%8Bblock%E5%9D%8F%E5%9D%97%E6%98%AF%E5%90%A6%E8%83%BD%E8%A2%AB%E8%B7%B3%E8%BF%87.html
374
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
