repair failure修复坏块

刚刚逛论坛，看到一个网友说他dd破坏的块用repair failure修复不了，我这里也实验一把试试

测试之前先做一个备份
RMAN> backup database;
Starting backup at 12-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 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=00001 name=/opt/app/oracle/oradata/goolen/system01.dbf
input datafile file number=00002 name=/opt/app/oracle/oradata/goolen/sysaux01.dbf
input datafile file number=00003 name=/opt/app/oracle/oradata/goolen/undotbs01.dbf
input datafile file number=00006 name=/opt/app/oracle/oradata/goolen/goolen01.dbf
channel ORA_DISK_1: starting piece 1 at 12-MAR-14
channel ORA_DISK_1: finished piece 1 at 12-MAR-14
piece handle=/opt/app/oracle/product/11.2.0/dbs/1rp3029l_1_1 tag=TAG20140312T112124 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 12-MAR-14
channel ORA_DISK_1: finished piece 1 at 12-MAR-14
piece handle=/opt/app/oracle/product/11.2.0/dbs/1sp302ao_1_1 tag=TAG20140312T112124 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-MAR-14

创建一个测试表
SQL> create table scott.rcsy tablespace goolen as select empno,ename from scott.emp where rownum <=2;
Table created.

SQL> set lines 120
SQL> select owner,table_name,tablespace_name from dba_tables where owner='SCOTT' and table_name='RCSY';
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT                          RCSY                           GOOLEN

SQL> col file_name for a55
SQL> select file_name,file_id,TABLESPACE_NAME from dba_data_files where tablespace_name='GOOLEN';
FILE_NAME                                                  FILE_ID TABLESPACE_NAME
------------------------------------------------------- ---------- ------------------------------
/opt/app/oracle/oradata/goolen/goolen01.dbf                      6 GOOLEN

SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.rowid_block_number(rowid) block# from scott.rcsy;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)     BLOCK#
------------------------------------ ----------
                                   6         51
                                   6         51
现在我们来破坏6号文件的51号块
[oracle@localhost xiaoming]$ dd if=/dev/zero of=/opt/app/oracle/oradata/goolen/goolen01.dbf count=1 seek=51 bs=8192 conv=notrunc    
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 3.9785e-05 s, 206 MB/s

SQL> select * from scott.rcsy;
     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN

SQL> alter system flush buffer_cache;
System altered.

再次查询报错，有坏块
SQL> select * from scott.rcsy;
select * from scott.rcsy
                    *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 51)
ORA-01110: data file 6: '/opt/app/oracle/oradata/goolen/goolen01.dbf'

使用rman来恢复这个坏块
RMAN>  list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
8102       HIGH     OPEN      12-MAR-14     Datafile 6: '/opt/app/oracle/oradata/goolen/goolen01.dbf' contains one or more corrupt blocks

RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
8102       HIGH     OPEN      12-MAR-14     Datafile 6: '/opt/app/oracle/oradata/goolen/goolen01.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Perform block media recovery of block 51 in file 6  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /opt/app/oracle/diag/rdbms/goolen/goolen/hm/reco_697173025.hm

RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /opt/app/oracle/diag/rdbms/goolen/goolen/hm/reco_697173025.hm
contents of repair script:
   # block media recovery
   recover datafile 6 block 51;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting recover at 12-MAR-14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /opt/app/oracle/product/11.2.0/dbs/1rp3029l_1_1
channel ORA_DISK_1: piece handle=/opt/app/oracle/product/11.2.0/dbs/1rp3029l_1_1 tag=TAG20140312T112124
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 12-MAR-14
repair failure complete

RMAN> exit

Recovery Manager complete.

SQL> alter system flush buffer_cache;
System altered.

SQL> select * from scott.rcsy;
     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
 

可以看到我们这里的测试是可以恢复的，我估计是他之前的备份集有问题，或者恢复完后没有flush buffercache，查询还是有可能报错的