联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在春节前写过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就可以跳过,在以前的文章中说过,不再讲述