12 oracle 数据库坏块--物理坏块-ORA-01578/ORA-01110

客官°小女子只卖身不卖艺 2021-11-29 08:40 223阅读 0赞

oracle 数据库坏块--物理坏块

数据坏块的类型  
物理坏块：通常是由于硬件损坏如磁盘异常导致、内存有问题、存储链有问题、 IO有问题、文件系统有问题、 Oracle本身的问题等  
逻辑坏块：可能都是软件问题导致通常是由于oracle bug导致，比如data block和index block数据不一致  
第三方软件或者硬件造成的物理损坏  
物理数据坏块的场景  
常见的物理坏块（Physical Block Corruptions）有块头和块尾信息不一致(Fractured/Incomplete)，checksum值无效，数据块信息全部为0等情况，  
并且可能伴随错误ORA-1578和ORA-1110

**1.Bad header - the beginning of the block (cache header) is corrupt with invalid values**

**2.The block is Fractured/Incomplete - header and footer of the block do not match**

**3.The block checksum is invalid**

**4.The block is misplaced**

**5.Zeroed out blocks/ORA-8103模拟**

物理坏块的模拟--分别模拟5中情况  
**1.Bad header - the beginning of the block (cache header) is corrupt with invalid values**

create tablespace yhqt datafile '/u01/app/oracle/oradata/orcl/yhqt01.dbf' size 50M;
    create user yhqt identified by yhqt default tablespace yhqt;
    grant dba to yhqt;
    SYS@ orcl >conn yhqt/***
    Connected.
    YHQT@ orcl >create table yhqtest_1(id int,name varchar2(100));
    Table created.
    
    YHQT@ orcl >insert into yhqtest_1 values(1,'yhq');
    1 row created.
    
    YHQT@ orcl >commit;
    Commit complete.
    
    YHQT@ orcl >select
    dbms_rowid.rowid_relative_fno(rowid) rel_fno,
    dbms_rowid.rowid_block_number(rowid) blockno
    from yhqtest_1;  2    3    4  
    
       REL_FNO    BLOCKNO
    ---------- ----------
        10      135

\--使用bbed，手动修改数据块

BBED> set file 10 block 135
        FILE#              10
        BLOCK#             135
    
    BBED> map /v
     File: /u01/app/oracle/oradata/orcl/yhqt01.dbf (10)
     Block: 135                                   Dba:0x02800087
    ------------------------------------------------------------
    BBED-00400: invalid blocktype (00)
    YHQT@ orcl >alter system flush buffer_cache;
    
    System altered.
    BBED> set file 10 block 135
        FILE#              10
        BLOCK#             135
    
    BBED> map /v
     File: /u01/app/oracle/oradata/orcl/yhqt01.dbf (10)
     Block: 135                                   Dba:0x02800087
    ------------------------------------------------------------
    BBED> p kcbh
    struct kcbh, 20 bytes                       @0       
       ub1 type_kcbh                            @0        0x06
       ub1 frmt_kcbh                            @1        0xa2
       ub1 spare1_kcbh                          @2        0x00
       ub1 spare2_kcbh                          @3        0x00
       ub4 rdba_kcbh                            @4        0x02800087
       ub4 bas_kcbh                             @8        0x009e4d78
       ub2 wrp_kcbh                             @12       0x0000
       ub1 seq_kcbh                             @14       0x01 >>01 修改为ff
       ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)
       ub2 chkval_kcbh                          @16       0xf4f8
       ub2 spare3_kcbh                          @18       0x0000
    BBED> modify /x ff offset 14
    Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
     File: /u01/app/oracle/oradata/orcl/yhqt01.dbf (10)
     Block: 135              Offsets:   14 to  525           Dba:0x02800087
    ------------------------------------------------------------------------
     ff06f8f4 00000100 00003d59 0100764d 9e000000 000002f8 32008000 80020500 
    BBED> sum apply
    Check value for File 10, Block 135:
    current = 0xf406, required = 0xf406

\--重启db，并查询

SYS@ orcl >conn yhqt/***
    Connected.
    YHQT@ orcl >select * from yhqtest_1;
    select * from yhqtest_1
                  *
    ERROR at line 1:
    ORA-01578: ORACLE data block corrupted (file # 10, block # 135)
    ORA-01110: data file 10: '/u01/app/oracle/oradata/orcl/yhqt01.dbf'
    --报错ORA-01578: ORACLE data block corrupted
    [oracle@DSI ~]$ tail -n 20 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log 
    Data in bad block:
     type: 6 format: 2 rdba: 0x02800087
     last change scn: 0x0000.009e4d78 seq: 0xff flg: 0x06
     spare1: 0x0 spare2: 0x0 spare3: 0x0
     consistency value in tail: 0x4d780601
     check value in block header: 0xf406
     computed block checksum: 0x0
    Reread of blocknum=135, file=/u01/app/oracle/oradata/orcl/yhqt01.dbf. found same corrupt data
    Reread of blocknum=135, file=/u01/app/oracle/oradata/orcl/yhqt01.dbf. found same corrupt data
    Reread of blocknum=135, file=/u01/app/oracle/oradata/orcl/yhqt01.dbf. found same corrupt data
    Reread of blocknum=135, file=/u01/app/oracle/oradata/orcl/yhqt01.dbf. found same corrupt data
    Reread of blocknum=135, file=/u01/app/oracle/oradata/orcl/yhqt01.dbf. found same corrupt data
    Checker run found 1 new persistent data failures
    Fri Jul 12 10:46:57 2019
    Dumping diagnostic data in directory=[cdmp_20190712104657], requested by (instance=1, osid=2812), summary=[incident=63755].
    Dumping diagnostic data in directory=[cdmp_20190712104658], requested by (instance=1, osid=2812), summary=[incident=63756].
    Fri Jul 12 10:47:16 2019
    Sweep [inc][63756]: completed
    Sweep [inc2][63756]: completed
    Sweep [inc2][63755]: completed
    
    YHQT@ orcl >insert into yhqtest_1 values(1,'yhq');
    insert into yhqtest_1 values(1,'yhq')
                *
    ERROR at line 1:
    ORA-01578: ORACLE data block corrupted (file # 10, block # 135)
    ORA-01110: data file 10: '/u01/app/oracle/oradata/orcl/yhqt01.dbf'

[oracle@DSI ~]$ dbv file=/u01/app/oracle/oradata/orcl/yhqt01.dbf
    
    DBVERIFY: Release 11.2.0.4.0 - Production on Fri Jul 12 10:50:42 2019
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/yhqt01.dbf
    Page 135 is influx - most likely media corrupt
    Corrupt block relative dba: 0x02800087 (file 10, block 135)
    Fractured block found during dbv: 
    Data in bad block:
     type: 6 format: 2 rdba: 0x02800087
     last change scn: 0x0000.009e4d78 seq: 0xff flg: 0x06
     spare1: 0x0 spare2: 0x0 spare3: 0x0
     consistency value in tail: 0x4d780601
     check value in block header: 0xf406
     computed block checksum: 0x0
    
    
    DBVERIFY - Verification complete
    
    Total Pages Examined         : 6400
    Total Pages Processed (Data) : 4
    Total Pages Failing   (Data) : 0
    Total Pages Processed (Index): 0
    Total Pages Failing   (Index): 0
    Total Pages Processed (Other): 130
    Total Pages Processed (Seg)  : 0
    Total Pages Failing   (Seg)  : 0
    Total Pages Empty            : 6265
    Total Pages Marked Corrupt   : 1
    Total Pages Influx           : 1
    Total Pages Encrypted        : 0
    Highest block SCN            : 10374518 (0.10374518)
    YHQT@ orcl >select * from v$database_block_corruption;
    
         FILE#     BLOCK#      BLOCKS CORRUPTION_CHANGE# CORRUPTIO
    ---------- ---------- ---------- ------------------ ---------
        10      135           1          0 FRACTURED ##发现数据块物理损坏
    
    用rman恢复，发现没有datafile=10的备份，无法恢复
    RMAN> run {blockrecover datafile 10 block 135;}
    
    Starting recover at 12-JUL-19
    starting full resync of recovery catalog
    full resync complete
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=142 device type=DISK
    
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 07/12/2019 10:52:22
    RMAN-06026: some targets not found - aborting restore
    RMAN-06023: no backup or copy of datafile 10 found to restore
    
    --跳过坏块的修复，但是坏的数据丢失了
    YHQT@ orcl >alter session set db_file_multiblock_read_count=1;
    Session altered.
    YHQT@ orcl >execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('YHQT','YHQTEST_1');
    BEGIN DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('YHQT','YHQTEST_1'); END;
    
          *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00201: identifier 'DBMS_REPAIR.SKIP_CORRUPT_BLOCKS' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    
    YHQT@ orcl >conn / as sysdba
    Connected.
    SYS@ orcl >execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('YHQT','YHQTEST_1');
    
    PL/SQL procedure successfully completed.
    
    SYS@ orcl >create table YHQT.yhqtest_1_new as select * from YHQT.yhqtest_1;
    Table created.
    
    SYS@ orcl >conn yhqt/***
    Connected.
    YHQT@ orcl >select * from yhqtest_1_new;
    no rows selected
    
    YHQT@ orcl >select * from yhqtest_1;
    no rows selected

**2.The block is Fractured/Incomplete - header and footer of the block do not match**

YHQT@ orcl >create table yhqtest_2(id int,name varchar2(100));
    Table created.
    YHQT@ orcl >insert into yhqtest_2 values(1,'yhq');
    1 row created.
    YHQT@ orcl >commit;
    Commit complete.
    YHQT@ orcl >alter system flush buffer_cache;
    
    System altered.
    
    YHQT@ orcl >select
    dbms_rowid.rowid_relative_fno(rowid) rel_fno,
    dbms_rowid.rowid_block_number(rowid) blockno
    from yhqtest_2;  2    3    4  
    
       REL_FNO    BLOCKNO
    ---------- ----------
        10      143
    BBED> set file 10 block 143;
        FILE#              10
        BLOCK#             143
    
    BBED> dump /v offset 8188
     File: /u01/app/oracle/oradata/orcl/yhqt01.dbf (10)
     Block: 143     Offsets: 8188 to 8191  Dba:0x0280008f
    -------------------------------------------------------
     0106f553                            l ..
    
     <16 bytes per line>
    
    BBED> modify /x 0106f554   
    Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
     File: /u01/app/oracle/oradata/orcl/yhqt01.dbf (10)
     Block: 143              Offsets: 8188 to 8191           Dba:0x0280008f
    ------------------------------------------------------------------------
     0106f554 
    
     <32 bytes per line>
    
    BBED> sum apply
    Check value for File 10, Block 143:
    current = 0x46ca, required = 0x46ca
    --刷新并查询
    YHQT@ orcl >alter system flush buffer_cache;
    
    System altered.
    
    YHQT@ orcl >select * from yhqtest_2;
    select * from yhqtest_2
                  *
    ERROR at line 1:
    ORA-01578: ORACLE data block corrupted (file # 10, block # 143)
    ORA-01110: data file 10: '/u01/app/oracle/oradata/orcl/yhqt01.dbf'
    --报错01578 >>>Fractured block
    ALTER SYSTEM: Flushing buffer cache
    Hex dump of (file 10, block 143) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2890.trc
    Corrupt block relative dba: 0x0280008f (file 10, block 143)
    Fractured block found during multiblock buffer read
    Data in bad block:
     type: 6 format: 2 rdba: 0x0280008f
     last change scn: 0x0000.009e53f5 seq: 0x1 flg: 0x06
     spare1: 0x0 spare2: 0x0 spare3: 0x0
     consistency value in tail: 0x54f50601
     check value in block header: 0x46ca
     computed block checksum: 0x0

**3.The block checksum is invalid**

checksum是oracle写入后，其他外部因素导致块checksum改变的情况  
checksum只有DBWR进程写入，或者直接从磁盘上读取

YHQT@ orcl >create table yhqtest_3(id int,name varchar2(100));
    
    Table created.
    
    YHQT@ orcl >insert into yhqtest_3 values(1,'yhq33333');
    
    1 row created.
    
    YHQT@ orcl >commit;
    
    Commit complete.
    
    YHQT@ orcl >select
    dbms_rowid.rowid_relative_fno(rowid) rel_fno,
    dbms_rowid.rowid_block_number(rowid) blockno
    from yhqtest_3;  2    3    4  
    
       REL_FNO    BLOCKNO
    ---------- ----------
        10      151
    YHQT@ orcl >alter system flush buffer_cache;
    
    System altered.
    BBED> set file 10 block 151
        FILE#              10
        BLOCK#             151
    
    BBED> p chkval_kcbh
    ub2 chkval_kcbh                             @16       0xc428
    
    BBED> sum
    Check value for File 10, Block 151:
    current = 0xc428, required = 0xc428 ###当前的checksum=0xc428，请求的checksum也是=0xc428，二者一致
    --用dd命令导出10号文件的151号块
    [oracle@DSI ~]$ dd if=/u01/app/oracle/oradata/orcl/yhqt01.dbf of=/tmp/yhqt01.dd count=1 skip=151 bs=8192
    1+0 records in
    1+0 records out
    8192 bytes (8.2 kB) copied, 8.8344e-05 s, 92.7 MB/s
    [oracle@DSI ~]$ sz /tmp/yhqt01.dd 
    --sz到windows下，用editplus工具打开（十六进制）--用UE工具能修改，editplus好像只能看不能改

![1530246-20190712173543731-608887760.png][]

现在我们将将ID为1的那条记录的ID值由1改为2，即将C1 02改成C1 03

![1530246-20190712173623805-1352147045.png][]

YHQT@ orcl >select dump(1,16) from dual;
    
    DUMP(1,16)
    -----------------
    Typ=2 Len=2: c1,2--1十六进制对应c102
    修改之后上传到linux，用dd
    [oracle@DSI ~]$ dd if=/home/oracle/yhqt01.dd of=/u01/app/oracle/oradata/orcl/yhqt01.dbf bs=8192 seek=151 count=1 conv=notrunc
    1+0 records in
    1+0 records out
    8192 bytes (8.2 kB) copied, 0.000101711 s, 80.5 MB/s
    重启db
    SYS@ orcl >conn yhqt/yhqt
    Connected.
    YHQT@ orcl >select * from yhqtest_3;
    select * from yhqtest_3
                  *
    ERROR at line 1:
    ORA-01578: ORACLE data block corrupted (file # 10, block # 151)
    ORA-01110: data file 10: '/u01/app/oracle/oradata/orcl/yhqt01.dbf'
    查看alertlog
    Hex dump of (file 10, block 151) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_64960/orcl_m000_3145_i64960_a.trc
    Corrupt block relative dba: 0x02800097 (file 10, block 151)
    Bad check value found during validation
    Data in bad block:
     type: 6 format: 2 rdba: 0x02800097
     last change scn: 0x0000.009e57b3 seq: 0x1 flg: 0x06
     spare1: 0x0 spare2: 0x0 spare3: 0x0
     consistency value in tail: 0x57b30601
     check value in block header: 0xc428 >>>块头记录的checksum值是0xc428
     computed block checksum: 0x1 >>>oracle这里做异或操作后的checksum值是01
    Reread of blocknum=151, file=/u01/app/oracle/oradata/orcl/yhqt01.dbf. found same corrupt data
    怎样计算出正确的checksum值（cont..）
    BBED> set file 10 block 151
        FILE#              10
        BLOCK#             151
    
    BBED> verify
    DBVERIFY - Verification starting
    FILE = /u01/app/oracle/oradata/orcl/yhqt01.dbf
    BLOCK = 151
    
    Block 151 is corrupt
    Corrupt block relative dba: 0x02800097 (file 0, block 151)
    Bad check value found during verification
    Data in bad block:
     type: 6 format: 2 rdba: 0x02800097
     last change scn: 0x0000.009e57b3 seq: 0x1 flg: 0x06
     spare1: 0x0 spare2: 0x0 spare3: 0x0
     consistency value in tail: 0x57b30601
     check value in block header: 0xc428 ======>>>> a
     computed block checksum: 0x1   =====>>>>>b
    
    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           : 0
    Message 531 not found;  product=RDBMS; facility=BBED
    BBED> sum
    Check value for File 10, Block 151:
    current = 0xc428, required = 0xc429
    BBED> p chkval_kcbh
    ub2 chkval_kcbh                             @16       0xc428
    
    这里的换算规则 ,上面如果a、b两个值不同，则异或结果为1，如果a、b两个值相同，异或结果为0
    checksum=0xc428 XOR 0x1
    0xc428=c 4 2 8 =1100  0100 0010 1000
    0x1=1 =0001
    上面两个值进行异或
    1100 0100 0010 1001=c429
    
    BBED> modify /x c429 offset 16  ###将current的checksum值直接修改到请求后的值0xc429
    Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
     File: /u01/app/oracle/oradata/orcl/yhqt01.dbf (10)
     Block: 151              Offsets:   16 to  527           Dba:0x02800097
    
    BBED> sum apply
    Check value for File 10, Block 151:
    current = 0xc429, required = 0xc429
    
    YHQT@ orcl >set pagesize 999
    YHQT@ orcl >set linesize 999
    YHQT@ orcl >select * from yhqtest_3;
    
        ID NAME
    ---------- ------------------
         2 yhq33333  >>>这里的id值之前是1，修改到了2 ，即C1 02改成C1 03

数据坏块相关参数-db\_block\_checksum

YHQT@ orcl >show parameter db_block_checksum
    
    NAME                     TYPE     VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_checksum             string     TYPICAL
    --OFF 关闭block checksum 机制,该参数即使设置为off，针对system表空间的block checksum机制也永远都是开启的，仅仅是针对非system表空间。
    --TYPICAL 开启基本的block checksum机制，读取的时候检查checksum值，并在最后一个write时记录checksum值
    --FULL 在typical模式的基础之上，在进行dml操作时候也会进行checksum比较，针对该block的操作，其对应的redo log在进行写入时也会进行checksum比较。
    该参数注意是控制dbwn进程在将block写入到disk时，是否根据存储在block的byte大小进行估算一个checksum值，
    并将其写入到data block的cache header中
    该参数可以在很大程度上避免坏块的产生，但是会产生一定的额外资源消耗,
    当设置typical时，会增加1~2%的资源消耗，当设置为full时，会增加4~5%的资源消耗

数据坏块相关参数- db\_block\_checking

YHQT@ orcl >show parameter db_block_checking
    
    NAME                     TYPE     VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_checking             string     FALSE
    --OFF 关闭block checking机制（注意仅仅是关于非system表空间),对于system表空间的block checking机制，是由参数_db_always_check_system_ts控制的，默认是true.
    --LOW 较低级别的block 校验检查，仅仅是block header.(注意是block 内容在内存中发生改变时)
    --MEDIUM 除包含low级别的校验外，还包括其他的所有非IOT表 block,即我们普通的堆table 数据块.
    --FULL 除包含medium级别的检查外，还包括所有的索引块
    SQL> alter system set db_block_checking=FULL;
    SQL> alter system set db_block_checksum=FULL;

**4.The block is misplaced**

行锁错位的模拟
    BBED> set file 10 block 151
        FILE#              10
        BLOCK#             151
    
    BBED> p *kdbr
    rowdata[0]
    ----------
    ub1 rowdata[0]                              @8173     0x2c
    
    BBED> x/ rnccc
    rowdata[0]                                  @8173    
    ----------
    flag@8173: 0x2c (KDRHFL, KDRHFF, KDRHFH)
    lock@8174: 0x01  --->>>行锁
    cols@8175:    2
    
    col    0[2] @8176: 2 ---记录内容
    col    1[8] @8179: yhq33333
    BBED> modify /x 00 offset 8174
     File: /u01/app/oracle/oradata/orcl/yhqt01.dbf (10)
     Block: 151              Offsets: 8174 to 8191           Dba:0x02800097
    ------------------------------------------------------------------------
     000202c1 03087968 71333333 33330106 b357 
    
     <32 bytes per line>
    
    BBED> sum apply
    Check value for File 10, Block 151:
    current = 0xc428, required = 0xc428
    
    BBED> verify
    DBVERIFY - Verification starting
    FILE = /u01/app/oracle/oradata/orcl/yhqt01.dbf
    BLOCK = 151
    
    Block Checking: DBA = 41943191, Block Type = KTB-managed data block
    data header at 0x926864
    kdbchk: xaction header lock count mismatch -----报错
            trans=1 ilk=1 nlo=0
    Block 151 failed with check code 6108
    
    DBVERIFY - Verification complete
    
    Total Blocks Examined         : 1
    Total Blocks Processed (Data) : 1
    Total Blocks Failing   (Data) : 1
    Total Blocks Processed (Index): 0
    Total Blocks Failing   (Index): 0
    Total Blocks Empty            : 0
    Total Blocks Marked Corrupt   : 0
    Total Blocks Influx           : 0
    Message 531 not found;  product=RDBMS; facility=BBED

**5.Zeroed out blocks/ORA-8103模拟**

YHQT@ orcl >create table yhqtest_4(id int,name varchar2(100));
    
    Table created.
    
    begin
      for i in 1 .. 5000 loop
      insert into yhqtest_4 values(i,'yhq'||i);
      commit;
      end loop;
    end;
    /
    YHQT@ orcl >SELECT owner, segment_name, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS
    FROM dba_extents
    WHERE segment_name='YHQTEST_4' AND owner='YHQT';  2    3  
    
    OWNER                   SEGMENT_NAME         EXTENT_ID    FILE_ID     BLOCK_ID     BLOCKS
    ------------------------------ ------------------------------- ---------- ---------- ---------- ----------
    YHQT                   YHQTEST_4          0        10          152       8
    YHQT                   YHQTEST_4          1        10          160       8
    YHQT@ orcl >SELECT DISTINCT dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk#
    FROM YHQTEST_4 ORDER BY 1,2;  2  
    
         FILE#     BLK#
    ---------- ----------
        10      155
        10      156
        10      157
        10      158
        10      159
        10      160
        10      161
        10      163
        10      164
        10      165
        10      166
        10      167
    
    12 rows selected.
    YHQT@ orcl >conn / as sysdba
    Connected.
    SYS@ orcl >shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    使用dd命令
    [oracle@DSI ~]$ dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/yhqt01.dbf bs=8192 seek=160 count=1 conv=notrunc
    1+0 records in
    1+0 records out
    8192 bytes (8.2 kB) copied, 9.5966e-05 s, 85.4 MB/s
    SYS@ orcl >startup
    YHQT@ orcl >select count(*) from yhqtest_4;
    select count(*) from yhqtest_4
                         *
    ERROR at line 1:
    ORA-01578: ORACLE data block corrupted (file # 10, block # 160)
    ORA-01110: data file 10: '/u01/app/oracle/oradata/orcl/yhqt01.dbf'
    
    ---坏块检查
    YHQT@ orcl >select * from v$database_block_corruption;
    
         FILE#     BLOCK#      BLOCKS CORRUPTION_CHANGE# CORRUPTIO
    ---------- ---------- ---------- ------------------ ---------
        10      135           1          0 FRACTURED
        10      143           1          0 FRACTURED
        10      160           1          0 ALL ZERO
    RMAN> backup validate datafile 10;
    
    Starting backup at 12-JUL-19
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=145 device type=DISK
    channel ORA_DISK_1: starting full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    input datafile file number=00010 name=/u01/app/oracle/oradata/orcl/yhqt01.dbf
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    List of Datafiles
    =================
    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    ---- ------ -------------- ------------ --------------- ----------
    10   FAILED 0              6233         6400            10394764  
      File Name: /u01/app/oracle/oradata/orcl/yhqt01.dbf
      Block Type Blocks Failing Blocks Processed
      ---------- -------------- ----------------
      Data       2              27              
      Index      0              0               
      Other      1              140             
    
    validate found one or more corrupt blocks
    See trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4271.trc for details
    Finished backup at 12-JUL-19
    [oracle@DSI ~]$ dbv file=/u01/app/oracle/oradata/orcl/yhqt01.dbf 
    
    DBVERIFY: Release 11.2.0.4.0 - Production on Fri Jul 12 16:45:43 2019
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/yhqt01.dbf
    Page 135 is influx - most likely media corrupt
    Corrupt block relative dba: 0x02800087 (file 10, block 135)
    Fractured block found during dbv: 
    Data in bad block:
     type: 6 format: 2 rdba: 0x02800087
     last change scn: 0x0000.009e4d78 seq: 0xff flg: 0x06
     spare1: 0x0 spare2: 0x0 spare3: 0x0
     consistency value in tail: 0x4d780601
     check value in block header: 0xf406
     computed block checksum: 0x0
    
    Page 143 is influx - most likely media corrupt
    Corrupt block relative dba: 0x0280008f (file 10, block 143)
    Fractured block found during dbv: 
    Data in bad block:
     type: 6 format: 2 rdba: 0x0280008f
     last change scn: 0x0000.009e53f5 seq: 0x1 flg: 0x06
     spare1: 0x0 spare2: 0x0 spare3: 0x0
     consistency value in tail: 0x54f50601
     check value in block header: 0x46ca
     computed block checksum: 0x0
    
    Block Checking: DBA = 41943191, Block Type = KTB-managed data block
    data header at 0x7fdde45ab064
    kdbchk: xaction header lock count mismatch
            trans=1 ilk=1 nlo=0
    Page 151 failed with check code 6108
    Page 160 is marked corrupt
    Corrupt block relative dba: 0x028000a0 (file 10, block 160)
    Completely zero block found during dbv: 
    
    
    
    DBVERIFY - Verification complete
    
    Total Pages Examined         : 6400
    Total Pages Processed (Data) : 49
    Total Pages Failing   (Data) : 1
    Total Pages Processed (Index): 0
    Total Pages Failing   (Index): 0
    Total Pages Processed (Other): 145
    Total Pages Processed (Seg)  : 0
    Total Pages Failing   (Seg)  : 0
    Total Pages Empty            : 6203
    Total Pages Marked Corrupt   : 3
    Total Pages Influx           : 2
    Total Pages Encrypted        : 0
    Highest block SCN            : 10396346 (0.10396346)
    --rman修复，没有备份是不行的
    RMAN> run {blockrecover datafile 10 block 143;}
    
    Starting recover at 12-JUL-19
    using channel ORA_DISK_1
    
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 07/12/2019 16:33:16
    RMAN-06026: some targets not found - aborting restore
    RMAN-06023: no backup or copy of datafile 10 found to restore
    --修复
    YHQT@ orcl >conn / as sysdba
    Connected.
    SYS@ orcl >alter session set db_file_multiblock_read_count=1;
    
    Session altered.
    
    SYS@ orcl >execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('YHQT','YHQTEST_4');
    
    PL/SQL procedure successfully completed.
    
    SYS@ orcl >create table YHQT.YHQTEST_4_new as select * from YHQT.YHQTEST_4;
    
    Table created.
    
    SYS@ orcl >select count(*) from yhqt.yhqtest_4_new;
    
      COUNT(*)
    ----------
          4573
    SYS@ orcl >select count(*) from yhqt.yhqtest_4;
    
      COUNT(*)
    ----------
          4573

下一篇介绍逻辑坏块以及坏块的恢复

转载于:https://www.cnblogs.com/yhq1314/p/11177525.html

[1530246-20190712173543731-608887760.png]: /images/20211129/a092ecec95d8430495de5b3522e11b16.png
[1530246-20190712173623805-1352147045.png]: /images/20211129/b7921063519c40ddbebe216ead3302b5.png