在春节前写过table中各种类型block坏块是否能被跳过,本来准备节前写完它的姊妹篇关于table中各种blog如果出现坏块,对select/dml操作影响,因为回家一些事情给耽误了,今天补上该文章,这篇文章主要基于试验测试为主,没有从相关block原理上进行分析,如果有时间,后续文章从原理上来分析为什么这些select/dml操作不能执行
创建测试表
SQL> create table t_xifenfei as
2 select * from dba_objects where rownum<10;
Table created.
SQL> select count(*) from chf.t_xifenfei;
COUNT(*)
----------
9
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 171 171
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 168 8
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 170 8 1
通过alter system dump datafile 4 block n得出相关block数据块类型
168为FIRST LEVEL BITMAP BLOCK
169为SECOND LEVEL BITMAP BLOCK
170为PAGETABLE SEGMENT HEADER
171为trans data
处理block 168
--制造坏块
BBED> set block 168
BLOCK# 168
BBED> set offset 8188
OFFSET 8188
BBED> d
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 168 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
0320d14f
<32 bytes per line>
BBED> m /x 0320d14e
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 168 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
0320d14e
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 168:
current = 0xf60b, required = 0xf60b
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 168
Block 168 is corrupt
Corrupt block relative dba: 0x010000a8 (file 0, block 168)
Fractured block found during verification
Data in bad block:
type: 32 format: 2 rdba: 0x010000a8
last change scn: 0x0b8c.3bff4fd1 seq: 0x3 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x4ed12003
check value in block header: 0xf60b
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
--select操作
SQL> select count(*) from chf.t_xifenfei;
COUNT(*)
----------
9
--dml操作
SQL> delete from chf.t_xifenfei where rownum<3;
2 rows deleted.
----注意update操作
SQL> update chf.t_xifenfei set object_name='www.xifenfei.com';
7 rows updated.
SQL> insert into chf.t_xifenfei select * from dba_objects where rownum=1;
insert into chf.t_xifenfei select * from dba_objects where rownum=1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 168)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
这里证明对于FIRST LEVEL BITMAP BLOCK,在delete,select操作正常，insert操作异常，update操作待定(update操作不一定能够立马展示效果)
处理block 169
--标记坏块
BBED> set block 169
BLOCK# 169
BBED> set offset 8188
OFFSET 8188
BBED> d
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 169 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
0221ce4f
<32 bytes per line>
BBED> m /x 0221ce4e
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 169 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
0221ce4e
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 169:
current = 0x9d2f, required = 0x9d2f
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 169
Block 169 is corrupt
Corrupt block relative dba: 0x010000a9 (file 0, block 169)
Fractured block found during verification
Data in bad block:
type: 33 format: 2 rdba: 0x010000a9
last change scn: 0x0b8c.3bff4fce seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x4ece2102
check value in block header: 0x9d2f
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
--select操作
SQL> select count(*) from chf.t_xifenfei;
COUNT(*)
----------
9
--dml操作
SQL> delete from chf.t_xifenfei where rownum<2;
1 row deleted.
----注意update操作
SQL> update chf.t_xifenfei set object_name='www.xifenfei.com';
9 rows updated.
SQL> alter table t_xifenfei modify EDITION_NAME varchar2(4000);
Table altered.
SQL> update t_xifenfei set EDITION_NAME=lpad('www.xifenfei.com', 4000, '0');
update t_xifenfei set EDITION_NAME=lpad('www.xifenfei.com', 4000, '0')
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 169)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
SQL> insert into chf.t_xifenfei
2 select * from dba_objects where rownum<2;
insert into chf.t_xifenfei
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 169)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
SECOND LEVEL BITMAP BLOCK在delete,select操作正常，insert操作异常，update操作分情况(如果更新的列字符串交短，可能不报错，如果更新的字符串较长可能报错)
处理block 170
--标记坏块
BBED> SET BLOCK 170
BLOCK# 170
BBED> set offset 8188
OFFSET 8188
BBED> d
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 170 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
0223b91b
<32 bytes per line>
BBED> m /x 0223b91a
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 170 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
0223b91a
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 170:
current = 0xb7d4, required = 0xb7d4
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 170
Block 170 is corrupt
Corrupt block relative dba: 0x010000aa (file 0, block 170)
Fractured block found during verification
Data in bad block:
type: 35 format: 2 rdba: 0x010000aa
last change scn: 0x0b8c.3c001bb9 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x1ab92302
check value in block header: 0xb7d4
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
--select操作
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 # 170)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
--dml操作
SQL> update chf.t_xifenfei where object_name='www.xifenfei.com';
update chf.t_xifenfei where object_name='www.xifenfei.com'
*
ERROR at line 1:
ORA-00971: missing SET keyword
SQL> update chf.t_xifenfei set object_name='www.xifenfei.com';
update chf.t_xifenfei set object_name='www.xifenfei.com'
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 170)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
SQL> delete from chf.t_xifenfei where rownum<2;
delete from chf.t_xifenfei where rownum<2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 170)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
PAGETABLE SEGMENT HEADER异常的时候，数据库包括select,update,delete,insert操作都不能进行
结果汇总
1.BITMAP BLOCK异常的时候，select/delete操作可以正常进行，insert操作异常，update操作可能异常也可能正常
2.SEGMENT HEADER异常的时候，数据库包括select,update,delete,insert操作都不能进行
3.对于这些特殊的block出现坏块，如果有rman备份，从10g开始可以通过rman blockrecover来修复
4.如果没有rman备份,可以BITMAP BLOCK可以类似ctas重建,SEGMENT HEADER可以通过dul scan extent抽取数据
5.对于trans data太过于常见，而且event就可以跳过,在以前的文章中说过，不再讲述
