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

最新推荐文章于 2021-04-08 08:18:24 发布

P10ZHUO

最新推荐文章于 2021-04-08 08:18:24 发布

阅读量152

点赞数

分类专栏： bbed 文章标签： oracle

本文链接：https://blog.csdn.net/fanzhuozhuo/article/details/110648837

版权

bbed 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

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

测试环境准备
测试block

上一篇写的是table中各种类型block坏块是否能被跳过,本篇关于table如果出现坏块,对select/dml操作影响。接上个测似表的数据，来进行测试

测试环境准备

SQL> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,blocks,extents from DBA_SEGMENTS 
  2  WHERE OWNER='ZHUO' AND SEGMENT_NAME='ZHUO';
SQL> set pages 1000 lines 1000 
SQL> col SEGMENT_NAME for a10
SQL> /

SEGMENT_NA HEADER_FILE HEADER_BLOCK     BLOCKS    EXTENTS
---------- ----------- ------------ ---------- ----------
ZHUO                 5         1410       1280         25

SQL> select  
dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  2    3  max(dbms_rowid.rowid_block_number(rowid)) max_block,
  4  min(dbms_rowid.rowid_block_number(rowid)) min_block
  5  from zhuo.zhuo
  6  group by dbms_rowid.rowid_relative_fno(rowid);

   REL_FNO  MAX_BLOCK  MIN_BLOCK
---------- ---------- ----------
         5       2581       1411

SQL>  select EXTENT_ID,FILE_ID,BLOCK_ID,blocks from dba_extents where owner='ZHUO'
  2   AND SEGMENT_NAME='ZHUO';

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          5       1408          8
         1          5       1416          8
         2          5       1424          8
         3          5       1432          8
         4          5       1440          8
         5          5       1448          8
         6          5       1456          8
         7          5       1464          8
         8          5       1472          8
         9          5       1480          8
        10          5       1488          8
        11          5       1496          8
        12          5       1504          8
        13          5       1512          8
        14          5       1520          8
        15          5       1528          8
        16          5       1536        128
        17          5       1664        128
        18          5       1792        128
        19          5       1920        128
        20          5       2048        128
        21          5       2176        128
        22          5       2304        128
        23          5       2432        128
        24          5       2560        128

25 rows selected.

通过这里可以知道:真正的存储数据是从block 1411开始,至于block 1408、1409、1410是什么，使用dump block分析。通过alter system dump datafile 4 block n得出相关block数据块类型
1408为FIRST LEVEL BITMAP BLOCK
1409为SECOND LEVEL BITMAP BLOCK
1410为PAGETABLE SEGMENT HEADER
1411为trans data
在这里插入图片描述

测试block

测试block 1408–L1 bitmap(extent 0)

破坏块，出现坏块：

BBED> set file 5 block 1408
        FILE#           5
        BLOCK#          1408

BBED> map /v
 File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
 Block: 1408                                  Dba:0x01400580
------------------------------------------------------------
BBED-00400: invalid blocktype (32)


BBED> d /v offset 8188
 File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
 Block: 1408    Offsets: 8188 to 8191  Dba:0x01400580
-------------------------------------------------------
 032092c6                            l . ..

 <16 bytes per line>

BBED> m /x  032092c7
 File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
 Block: 1408             Offsets: 8188 to 8191           Dba:0x01400580
------------------------------------------------------------------------
 032092c7 

 <32 bytes per line>

BBED> sum apply
Check value for File 5, Block 1408:
current = 0xbddd, required = 0xbddd

BBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1408

Block 1408 is corrupt
Corrupt block relative dba: 0x01400580 (file 0, block 1408)
Fractured block found during verification
Data in bad block:
 type: 32 format: 2 rdba: 0x01400580
 last change scn: 0x0000.000bc692 seq: 0x3 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xc7922003
 check value in block header: 0xbddd
 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操作
测试dml操作，注意update操作

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from zhuo.zhuo;

  COUNT(*)
----------
     80067

SQL> delete from zhuo.zhuo where rownum<10;

9 rows deleted.

SQL> update zhuo.zhuo set object_name='www.oracle.com' where rownum<10;

9 rows updated.

SQL> insert into zhuo.zhuo select * from dba_objects where rownum=1;
insert into zhuo.zhuo select * from dba_objects where rownum=1
                 *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1409)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'


SQL> commit;

Commit complete.

这里证明对于FIRST LEVEL BITMAP BLOCK,在delete,select操作正常，insert操作异常，update操作待定(update操作不一定能够立马展示效果)

测试block 1409–L2 bitmap(extent 0)

出现L2坏块

BBED> set file 5 block 1409
        FILE#           5
        BLOCK#          1409

BBED> map /v
 File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
 Block: 1409                                  Dba:0x01400581
------------------------------------------------------------
BBED-00400: invalid blocktype (33)


BBED> d /v offset 8188
 File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
 Block: 1409    Offsets: 8188 to 8191  Dba:0x01400581
-------------------------------------------------------
 192192c7                            l .!..

 <16 bytes per line>

BBED> m /x 192192c8
 File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
 Block: 1409             Offsets: 8188 to 8191           Dba:0x01400581
------------------------------------------------------------------------
 192192c8 

 <32 bytes per line>

BBED> sum apply
Check value for File 5, Block 1409:
current = 0xb3d9, required = 0xb3d9

BBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1409

Block 1409 is corrupt
Corrupt block relative dba: 0x01400581 (file 0, block 1409)
Fractured block found during verification
Data in bad block:
 type: 33 format: 2 rdba: 0x01400581
 last change scn: 0x0000.000bc692 seq: 0x19 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xc8922119
 check value in block header: 0xb3d9
 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 和dml操作

SQL> alter system flush buffer_cache;

System altered.

SQL>  select count(*) from zhuo.zhuo; 

  COUNT(*)
----------
     80066

SQL> insert into zhuo.zhuo select * from dba_objects where rownum=1;
insert into zhuo.zhuo select * from dba_objects where rownum=1
                 *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1409)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'


SQL> delete from zhuo.zhuo where rownum<10;

9 rows deleted.

SQL> update zhuo.zhuo set object_name='www.oracle.com' where rownum<10;

9 rows updated.

SQL> alter table zhuo.zhuo modify EDITION_NAME varchar2(4000);

Table altered.

SQL> update zhuo.zhuo set EDITION_NAME=lpad('www.oracle.com', 4000, '0');
update zhuo.zhuo set EDITION_NAME=lpad('www.oracle.com', 4000, '0')
            *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1409)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'

SECOND LEVEL BITMAP BLOCK在delete,select操作正常，insert操作异常，update操作分情况(如果更新的列字符串交短，可能不报错，如果更新的字符串较长可能报错)
–注意：
如果此处像上次一样，把人为制造的坏块FIRST LEVEL BITMAP BLOCK，手动修复好坏块，模拟的实验在此处不够准确，尤其是在delete的时候，会报错。

SQL> delete from zhuo.zhuo where rownum<10;
delete from zhuo.zhuo where rownum<10
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01578: ORACLE data block corrupted (file # 5, block # 1409)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'
ORA-01578: ORACLE data block corrupted (file # 5, block # 1409)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'
Process ID: 2650
Session ID: 33 Serial number: 345

所以最好还是还原后，重新做实验。

测试block 1410–segment header(extent 0)

破坏段头块

BBED> set file 5 block 1410
        FILE#           5
        BLOCK#          1410

BBED> map /v
 File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
 Block: 1410                                  Dba:0x01400582
------------------------------------------------------------
BBED-00400: invalid blocktype (35)


BBED> d /v offset 8188 count 4
 File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
 Block: 1410    Offsets: 8188 to 8191  Dba:0x01400582
-------------------------------------------------------
 01235aa8                            l .#Z.

 <16 bytes per line>

BBED> d /v offset 8188
 File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
 Block: 1410    Offsets: 8188 to 8191  Dba:0x01400582
-------------------------------------------------------
 01235aa8                            l .#Z.

 <16 bytes per line>

BBED> m /x  01235aa9
 File: /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf (5)
 Block: 1410             Offsets: 8188 to 8191           Dba:0x01400582
------------------------------------------------------------------------
 01235aa9 

 <32 bytes per line>

BBED> sum apply
Check value for File 5, Block 1410:
current = 0x95ed, required = 0x95ed

BBED> v
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf
BLOCK = 1410

Block 1410 is corrupt
Corrupt block relative dba: 0x01400582 (file 0, block 1410)
Fractured block found during verification
Data in bad block:
 type: 35 format: 2 rdba: 0x01400582
 last change scn: 0x0000.000ba85a seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xa95a2301
 check value in block header: 0x95ed
 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和dml操作

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from zhuo.zhuo;
select count(*) from zhuo.zhuo
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'


SQL> insert into zhuo.zhuo select * from dba_objects where rownum=1;
insert into zhuo.zhuo select * from dba_objects where rownum=1
                 *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'


SQL> delete from zhuo.zhuo where rownum<10;
delete from zhuo.zhuo where rownum<10
                 *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.dbf'


SQL> update zhuo.zhuo set object_name='www.oracle.com' where rownum<10;
update zhuo.zhuo set object_name='www.oracle.com' where rownum<10
            *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1410)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ZHUO/datafile/o1_mf_zhuo_gxdcfr5s_.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就可以跳过。
参考：
https://www.xifenfei.com/2013/02/table%E4%B8%AD%E5%90%84%E7%A7%8D%E5%9D%8F%E5%9D%97%E5%AF%B9selectdml%E6%93%8D%E4%BD%9C%E5%BD%B1%E5%93%8D.html