table中各种坏块对select/dml操作影响

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：table中各种坏块对select/dml操作影响

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在春节前写过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就可以跳过,在以前的文章中说过，不再讲述

发表评论

邮箱地址不会被公开。 必填项已用*标注

2 × 1 =