本文主要是介绍oracle坏块跳过限制，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
oracle坏块跳过限制
- 测试环境准备
- 验证block类型
- 测试block
- 测试block 1416 --普通data block(extent 1)
- 测试block 1411 --普通data block(extent 0)
- 测试block 1410 --segment header(extent 0)
- 测试block 1408 --L1 bitmap(extent 0)
- 测试block 1408 --L2 bitmap(extent 0)
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 as3 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 25SQL> 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_block5 from zhuo.zhuo6 group by dbms_rowid.rowid_relative_fno(rowid);REL_FNO MAX_BLOCK MIN_BLOCK
---------- ---------- ----------5 2581 1411SQL> 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 81 5 1416 82 5 1424 83 5 1432 84 5 1440 85 5 1448 86 5 1456 87 5 1464 88 5 1472 89 5 1480 810 5 1488 811 5 1496 812 5 1504 813 5 1512 814 5 1520 815 5 1528 816 5 1536 12817 5 1664 12818 5 1792 12819 5 1920 12820 5 2048 12821 5 2176 12822 5 2304 12823 5 2432 12824 5 2560 12825 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.zhuo3 where dbms_rowid.rowid_block_number(rowid)=14164 and dbms_rowid.rowid_relative_fno(rowid)=5;COUNT(ROWID)
------------80--bbed修改tailchkBBED> info allFile# Name Size(blks)----- ---- ----------1 /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_system_gxd20h1 02 /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_sysaux_gxd20k1 03 /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_undotbs1_gxd20 04 /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_users_gxd20pxk 05 /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_ 0BBED> set file 5 block 1416FILE# 5BLOCK# 1416BBED> p tailchk
ub4 tailchk @8188 0xc6660602BBED> d /v count 4File: /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 020666c7File: /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 = 0xd7afBBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1416Block 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: 0x01400588last change scn: 0x0000.000bc666 seq: 0x2 flg: 0x04spare1: 0x0 spare2: 0x0 spare3: 0x0consistency value in tail: 0xc7660602check value in block header: 0xd7afcomputed block checksum: 0x0DBVERIFY - Verification completeTotal 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
--------
ENABLEDSQL> 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 020666c6File: /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 = 0xd6afBBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1416DBVERIFY - Verification completeTotal 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> BEGIN2 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
--------
DISABLEDSQL> 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> BEGIN2 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;
BEGINnum_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: 1PL/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
ZHUOmark block software corrupt
TRUE 04-NOV-20SQL> DECLARE num_fix INT;2 BEGIN3 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: 0PL/SQL procedure successfully completed.SQL> BEGIN2 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> BEGIN2 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 1410FILE# 5BLOCK# 1410
--segment header 不支持bbed查看结构
BBED> map /vFile: /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 8188File: /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 012396c7File: /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 = 0x95f2BBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1410Block 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: 0x01400582last change scn: 0x0000.000bc696 seq: 0x1 flg: 0x04spare1: 0x0 spare2: 0x0 spare3: 0x0consistency value in tail: 0xc7962301check value in block header: 0x95f2computed block checksum: 0x0DBVERIFY - Verification completeTotal 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=BBEDSQL> 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 1408FILE# 5BLOCK# 1408BBED> map /vFile: /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 8188File: /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 032092c7File: /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 = 0xbdddBBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1408Block 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: 0x01400580last change scn: 0x0000.000bc692 seq: 0x3 flg: 0x04spare1: 0x0 spare2: 0x0 spare3: 0x0consistency value in tail: 0xc7922003check value in block header: 0xbdddcomputed block checksum: 0x0DBVERIFY - Verification completeTotal 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(*)
----------80067SQL> 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 1409FILE# 5BLOCK# 1409BBED> d /v offset 8188File: /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 192192c7File: /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 = 0xbcd9BBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1409Block 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: 0x01400581last change scn: 0x0000.000bc692 seq: 0x19 flg: 0x04spare1: 0x0 spare2: 0x0 spare3: 0x0consistency value in tail: 0xc7922119check value in block header: 0xbcd9computed block checksum: 0x0DBVERIFY - Verification completeTotal 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(*)
----------80067SQL>
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
这篇关于oracle坏块跳过限制的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
