联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在table遇到的各种坏块中，大部分情况，我们都可以通过设置event 10231或者dbms_repair来跳过坏块，抢救其他数据；但是在部分情况下，我们设置了他们依然不能跳过坏块，数据库依然报ORA-01578,本文测试了table中各种类型的block，证明在哪些blog出现异常之后不能被跳过.
如果是事务数据块出现坏块可以通过dbms_repair.skip_corrupt_blocks来标记坏块(也可以使用event 10231)来实现；对于BITMAP BLOCK如果出现坏块不会对于读取数据无影响(至于其他操作,请见后续blog)；SEGMENT HEADER如果出现坏块，无法通过跳过坏块来实现获取其他数据(正常block)
创建测试表
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table t_xifenfei
2 tablespace users
3 as
4 select * from dba_objects;
Table created.
SQL> select count(*) from chf.t_xifenfei;
COUNT(*)
----------
74663
查询相关block信息
SQL> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,blocks,extents from DBA_SEGMENTS
2 WHERE OWNER='CHF' AND SEGMENT_NAME='T_XIFENFEI';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
--------------- ----------- ------------ ---------- ----------
T_XIFENFEI 4 378 1152 24
SQL> select
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 max(dbms_rowid.rowid_block_number(rowid)) max_block,
4 min(dbms_rowid.rowid_block_number(rowid)) min_block
5 from chf.t_xifenfei
6 group by dbms_rowid.rowid_relative_fno(rowid);
REL_FNO MAX_BLOCK MIN_BLOCK
---------- ---------- ----------
4 1728 379
SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,blocks from dba_extents where owner='CHF'
2 AND SEGMENT_NAME='T_XIFENFEI';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 4 376 8
1 4 640 8
2 4 648 8
3 4 656 8
4 4 664 8
5 4 672 8
6 4 680 8
7 4 688 8
8 4 696 8
9 4 704 8
10 4 712 8
11 4 720 8
12 4 728 8
13 4 736 8
14 4 744 8
15 4 752 8
16 4 768 128
17 4 896 128
18 4 1024 128
19 4 1152 128
20 4 1280 128
21 4 1408 128
22 4 1536 128
23 4 1664 128
通过这里可以知道:真正的存储数据是从block 379开始,至于block 376、377、378是什么，使用dump block分析
验证block类型
SQL> alter system dump datafile 4 block 376; System altered. SQL> alter system dump datafile 4 block 377; System altered. SQL> alter system dump datafile 4 block 378; System altered. SQL> alter system dump datafile 4 block 379; System altered. --该block是另外extent的开始，所以也尝试分析是否有特殊之处 SQL> alter system dump datafile 4 block 640; System altered. --dump 文件header信息 Start dump data blocks tsn: 4 file#:4 minblk 376 maxblk 376 Block dump from cache: Dump of buffer cache at level 4 for tsn=4 rdba=16777592 Block dump from disk: buffer tsn: 4 rdba: 0x01000178 (4/376) scn: 0x0b8c.3bfc6517 seq: 0x04 flg: 0x04 tail: 0x65172004 frmt: 0x02 chkval: 0xc8b3 type: 0x20=FIRST LEVEL BITMAP BLOCK Start dump data blocks tsn: 4 file#:4 minblk 377 maxblk 377 Block dump from cache: Dump of buffer cache at level 4 for tsn=4 rdba=16777593 Block dump from disk: buffer tsn: 4 rdba: 0x01000179 (4/377) scn: 0x0b8c.3bfc6517 seq: 0x18 flg: 0x04 tail: 0x65172118 frmt: 0x02 chkval: 0x9e8c type: 0x21=SECOND LEVEL BITMAP BLOCK Start dump data blocks tsn: 4 file#:4 minblk 378 maxblk 378 Block dump from cache: Dump of buffer cache at level 4 for tsn=4 rdba=16777594 BH (0x2a7f7f0c) file#: 4 rdba: 0x0100017a (4/378) class: 4 ba: 0x2a742000 set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0 dbwrid: 0 obj: 76372 objn: 76372 tsn: 4 afn: 4 hint: f hash: [0x3150a748,0x3150a748] lru: [0x2a7f8094,0x2a7f7ee4] lru-flags: hot_buffer ckptq: [NULL] fileq: [NULL] objq: [0x2f72dc34,0x2f72dc34] objaq: [0x2f72dc2c,0x2f72dc2c] st: XCURRENT md: NULL fpin: 'ktewh25: kteinicnt' tch: 1 flags: LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535] Block dump from disk: buffer tsn: 4 rdba: 0x0100017a (4/378) scn: 0x0b8c.3bfc651b seq: 0x01 flg: 0x04 tail: 0x651b2301 frmt: 0x02 chkval: 0xb2ae type: 0x23=PAGETABLE SEGMENT HEADER Start dump data blocks tsn: 4 file#:4 minblk 379 maxblk 379 Block dump from cache: Dump of buffer cache at level 4 for tsn=4 rdba=16777595 Block dump from disk: buffer tsn: 4 rdba: 0x0100017b (4/379) scn: 0x0b8c.3bfc6494 seq: 0x01 flg: 0x04 tail: 0x64940601 frmt: 0x02 chkval: 0x0567 type: 0x06=trans data Start dump data blocks tsn: 4 file#:4 minblk 640 maxblk 640 Block dump from cache: Dump of buffer cache at level 4 for tsn=4 rdba=16777856 Block dump from disk: buffer tsn: 4 rdba: 0x01000280 (4/640) scn: 0x0b8c.3bfc6496 seq: 0x01 flg: 0x04 tail: 0x64960601 frmt: 0x02 chkval: 0x0efe type: 0x06=trans data
这里可以知道：
1.block 376、377为BITMAP BLOCK
2.block 378为SEGMENT HEADER(和dba_segments视图中一致)
3.除extent 0中有特殊(含BITMAP BLOCK和SEGMENT HEADER)block，其他extent只包含事务数据
测试block 640
--block 640包含条数
SQL> select count(rowid)
2 from chf.t_xifenfei
3 where dbms_rowid.rowid_block_number(rowid)=640
4 and dbms_rowid.rowid_relative_fno(rowid)=4;
COUNT(ROWID)
------------
79
--bbed修改tailchk
BBED> set filename '/u01/oracle/oradata/ora11g/users01.dbf'
FILENAME /u01/oracle/oradata/ora11g/users01.dbf
BBED> set block 640
BLOCK# 640
BBED> set mode edit
MODE Edit
BBED> p tailchk
ub4 tailchk @8188 0x64960601
BBED> m /x 64960602
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 640 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
64960602
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 640:
current = 0xf80b, required = 0xf80b
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 640
Block 640 is corrupt
Corrupt block relative dba: 0x01000280 (file 0, block 640)
Fractured block found during verification
Data in bad block:
type: 6 format: 2 rdba: 0x01000280
last change scn: 0x0b8c.3bfc6496 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x02069664
check value in block header: 0xf80b
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 chf.t_xifenfei;
select count(*) from chf.t_xifenfei
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 640)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
--跳过坏块
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');
PL/SQL procedure successfully completed.
SQL> select skip_corrupt from dba_tables where table_name='T_XIFENFEI' AND OWNER='CHF';
SKIP_COR
--------
ENABLED
SQL> select count(*) from chf.t_xifenfei;
COUNT(*)
----------
74584
--修复坏块
BBED> m /x 01069664
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 640 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
01069664
<32 bytes per line>
BBED> p tailchk
ub4 tailchk @8188 0x64960601
BBED> sum apply
Check value for File 0, Block 640:
current = 0x0efe, required = 0x0efe
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 640
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 => 'CHF',
4 OBJECT_NAME => 'T_XIFENFEI',
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='T_XIFENFEI' AND OWNER='CHF';
SKIP_COR
--------
DISABLED
--查询表记录正常
SQL> select count(*) from chf.t_xifenfei;
COUNT(*)
----------
74663
在后续的操作中，也是按照类似步骤操作，考虑到篇幅有限，部分过程不再贴出来
测试block 379
SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 379)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');
PL/SQL procedure successfully completed.
SQL> select count(*) from chf.t_xifenfei;
COUNT(*)
----------
74575
测试block 378
BBED> set block 378
BLOCK# 378
--segment header 不支持bbed查看结构
BBED> p tailchk
BBED-00400: invalid blocktype (35)
BBED> map
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 378 Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (35)
BBED> set offset 8188
OFFSET 8188
BBED> d
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 378 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
01231b65
<32 bytes per line>
BBED> m /x 651b2302
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 378 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
651b2302
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 378:
current = 0xedf2, required = 0xedf2
--验证坏块
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 378
Block 378 is corrupt
Corrupt block relative dba: 0x0100017a (file 0, block 378)
Fractured block found during verification
Data in bad block:
type: 35 format: 2 rdba: 0x0100017a
last change scn: 0x0b8c.3bfc651b seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x02231b65
check value in block header: 0xedf2
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 chf.t_xifenfei;
select count(*) from chf.t_xifenfei
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 378)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
--标记跳过坏块
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');
PL/SQL procedure successfully completed.
--查询依然失败
SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 378)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
测试block 377
BBED> m /x 18211766
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 377 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
18211766
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 377:
current = 0x9d8c, required = 0x9d8c
--bbed验证为坏块
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 377
Block 377 is corrupt
Corrupt block relative dba: 0x01000179 (file 0, block 377)
Fractured block found during verification
Data in bad block:
type: 33 format: 2 rdba: 0x01000179
last change scn: 0x0b8c.3bfc6517 seq: 0x18 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x66172118
check value in block header: 0x9d8c
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
--dbv验证为坏块
[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/ora11g/users01.dbf
DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jan 18 03:32:18 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/users01.dbf
Page 377 is influx - most likely media corrupt
Corrupt block relative dba: 0x01000179 (file 4, block 377)
Fractured block found during dbv:
Data in bad block:
type: 33 format: 2 rdba: 0x01000179
last change scn: 0x0b8c.3bfc6517 seq: 0x18 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x66172118
check value in block header: 0x9d8c
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Pages Examined : 2560
Total Pages Processed (Data) : 1434
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 10
Total Pages Failing (Index): 0
Total Pages Processed (Other): 213
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 902
Total Pages Marked Corrupt : 1
Total Pages Influx : 1
Total Pages Encrypted : 0
Highest block SCN : 1006486374 (2956.1006486374)
--查询表记录
SQL> select skip_corrupt from dba_tables where table_name='T_XIFENFEI' AND OWNER='CHF';
SKIP_COR
--------
DISABLED
SQL> select count(*) from chf.t_xifenfei;
COUNT(*)
----------
74663
测试block 376
BBED> m /x 04201766
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 376 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
04201766
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 376:
current = 0xcbb3, required = 0xcbb3
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 376
Block 376 is corrupt
Corrupt block relative dba: 0x01000178 (file 0, block 376)
Fractured block found during verification
Data in bad block:
type: 32 format: 2 rdba: 0x01000178
last change scn: 0x0b8c.3bfc6517 seq: 0x4 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x66172004
check value in block header: 0xcbb3
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 chf.t_xifenfei;
COUNT(*)
----------
74663
通过测试证明，如果是事务数据块出现坏块可以通过dbms_repair.skip_corrupt_blocks来标记坏块(也可以使用event 10231)来实现；对于BITMAP BLOCK如果出现坏块不会对于读取数据无影响(至于其他操作,请见table中各种坏块对select/dml操作影响)；SEGMENT HEADER如果出现坏块，无法通过跳过坏块来实现获取其他数据(正常block)
加我QQ
