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

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题: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就可以跳过,在以前的文章中说过,不再讲述

此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

评论功能已关闭。