Oracle 恢复坏块方法（ORA-01578)—跳过坏块

不念不忘少年蓝@ 2022-06-02 11:53 712阅读 0赞

一、创建测试表

SQL> create table test as select * from all_objects;
    
    Table created.
    
    SQL> select count(*) from test;
    
      COUNT(*)
    ----------
         80441

二、人为制造坏块

具体请参考：

http://blog.csdn.net/shiyu1157758655/article/details/79043034

和

http://blog.csdn.net/shiyu1157758655/article/details/79043618

三、错误现象

1.sqlplus窗口

SQL> select count(*) from test;
    select count(*) from test
                         *
    ERROR at line 1:
    ORA-01578: ORACLE data block corrupted (file # 5, block # 147)
    ORA-01110: data file 5: '/data/CEBPM/datafile/test01.dbf'

2.alter.log文件中

Hex dump of (file 5, block 147) in trace file /u01/app/oracle/diag/rdbms/cebpm/cebpm/trace/cebpm_ora_8880.trc
    Corrupt block relative dba: 0x01400093 (file 5, block 147)
    Bad check value found during user buffer read
    Data in bad block:
     type: 6 format: 2 rdba: 0x01400093
     last change scn: 0x0000.0012c1e9 seq: 0x2 flg: 0x04
     spare1: 0x0 spare2: 0x0 spare3: 0x0
     consistency value in tail: 0xc1e90602
     check value in block header: 0x4f1e
     computed block checksum: 0x33c4
    Reading datafile '/data/CEBPM/datafile/test01.dbf' for corruption at rdba: 0x01400093 (file 5, block 147)
    Reread (file 5, block 147) found same corrupt data (no logical check)
    Mon Jan 15 08:44:21 2018
    Corrupt Block Found
             TSN = 5, TSNAME = TEST
             RFN = 5, BLK = 147, RDBA = 20971667
             OBJN = 83326, OBJD = 83326, OBJECT = TEST, SUBOBJECT = 
             SEGMENT OWNER = TEST, SEGMENT TYPE = Table Segment
    Errors in file /u01/app/oracle/diag/rdbms/cebpm/cebpm/trace/cebpm_ora_8880.trc  (incident=52714):
    ORA-01578: ORACLE data block corrupted (file # 5, block # 147)
    ORA-01110: data file 5: '/data/CEBPM/datafile/test01.dbf'
    Incident details in: /u01/app/oracle/diag/rdbms/cebpm/cebpm/incident/incdir_52714/cebpm_ora_8880_i52714.trc
    Mon Jan 15 08:44:24 2018
    Dumping diagnostic data in directory=[cdmp_20180115084424], requested by (instance=1, osid=8880), summary=[incident=52714].
    Mon Jan 15 08:44:24 2018
    Sweep [inc][52714]: completed
    Sweep [inc2][52714]: completed

四、验证是否坏块

1.dbv 验证

cebpm:/home/oracle@cebpm>dbv file=/data/CEBPM/datafile/test01.dbf 
    
    DBVERIFY: Release 11.2.0.4.0 - Production on Mon Jan 15 08:46:29 2018
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    DBVERIFY - Verification starting : FILE = /data/CEBPM/datafile/test01.dbf
    Page 147 is marked corrupt
    Corrupt block relative dba: 0x01400093 (file 5, block 147)
    Bad check value found during dbv: 
    Data in bad block:
     type: 6 format: 2 rdba: 0x01400093
     last change scn: 0x0000.0012c1e9 seq: 0x2 flg: 0x04
     spare1: 0x0 spare2: 0x0 spare3: 0x0
     consistency value in tail: 0xc1e90602
     check value in block header: 0x4f1e
     computed block checksum: 0x33c4
    
    
    
    DBVERIFY - Verification complete
    
    Total Pages Examined         : 4096
    Total Pages Processed (Data) : 1158
    Total Pages Failing   (Data) : 0
    Total Pages Processed (Index): 0
    Total Pages Failing   (Index): 0
    Total Pages Processed (Other): 161
    Total Pages Processed (Seg)  : 0
    Total Pages Failing   (Seg)  : 0
    Total Pages Empty            : 2775
    Total Pages Marked Corrupt   : 2
    Total Pages Influx           : 0
    Total Pages Encrypted        : 0
    Highest block SCN            : 1229337 (0.1229337)

2.rman验证

RMAN> backup check logical validate datafile 5;
    
    Starting backup at 2018/01/15 08:49:17
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=53 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=00005 name=/data/CEBPM/datafile/test01.dbf
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
    List of Datafiles
    =================
    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    ---- ------ -------------- ------------ --------------- ----------
    5    FAILED 0              2775         4096            1229337   
      File Name: /data/CEBPM/datafile/test01.dbf
      Block Type Blocks Failing Blocks Processed
      ---------- -------------- ----------------
      Data       2              1160            
      Index      0              0               
      Other      0              161             
    
    validate found one or more corrupt blocks
    See trace file /u01/app/oracle/diag/rdbms/cebpm/cebpm/trace/cebpm_ora_9284.trc for details
    Finished backup at 2018/01/15 08:49:25

SQL> select file#,block#,blocks from v$database_block_corruption;
    
         FILE#     BLOCK#	  BLOCKS
    ---------- ---------- ----------
    	 5	  147	       1

五、跳过坏块读取其他数据

SQL>  exec dbms_repair.skip_corrupt_blocks('TEST','TEST');
    
    PL/SQL procedure successfully completed.

SQL> select skip_corrupt from user_tables where table_name='TEST';
    
    SKIP_COR
    --------
    ENABLED

SQL> select count(*) from test;
    
      COUNT(*)
    ----------
         80353

说明：数据发生丢失5号文件的147块中的数据丢失