Block media recovery using RMAN

SQL> create tablespace ts_corrupt datafile '/u01/app/oracle/oradata/ts_corrupt.dbf' size 100m;

Tablespace created.

SQL> create user usr_corrupt identified by usr_corrupt;

User created.

SQL> alter user usr_corrupt default tablespace
  2  ts_corrupt;

User altered.

SQL> conn / as sysdba
Connected.
SQL> grant connect,resource to usr_corrupt;

Grant succeeded.

SQL> desc dba_data_files;
 Name   Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME    VARCHAR2(513)
 FILE_ID    NUMBER
 TABLESPACE_NAME    VARCHAR2(30)
 BYTES    NUMBER
 BLOCKS    NUMBER
 STATUS    VARCHAR2(9)
 RELATIVE_FNO    NUMBER
 AUTOEXTENSIBLE    VARCHAR2(3)
 MAXBYTES    NUMBER
 MAXBLOCKS    NUMBER
 INCREMENT_BY    NUMBER
 USER_BYTES    NUMBER
 USER_BLOCKS    NUMBER
 ONLINE_STATUS    VARCHAR2(7)

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME
---------- ------------------------------
/u01/app/oracle/oradata/ben/users01.dbf
4 USERS

/u01/app/oracle/oradata/ben/undotbs01.dbf
3 UNDOTBS1

/u01/app/oracle/oradata/ben/sysaux01.dbf
2 SYSAUX


FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME
---------- ------------------------------
/u01/app/oracle/oradata/ben/system01.dbf
1 SYSTEM

/u01/app/oracle/oradata/ben/mill.dbf
5 MILL

/u01/app/oracle/oradata/ts_corrupt.dbf
6 TS_CORRUPT


6 rows selected.

SQL> conn usr_corrupt/usr_corrupt
Connected.
SQL> create table t_corrupt as select * from all_objects;

Table created.

SQL> select * from tab;

TNAME       TABTYPE CLUSTERID
------------------------------ ------- ----------
T_CORRUPT       TABLE

SQL> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name ='T_CORRUPT' and OWNER ='USR_CORRUPT';

SEGMENT_NAME
--------------------------------------------------------------------------------
HEADER_FILE HEADER_BLOCK
----------- ------------
T_CORRUPT
 6     130

[oracle@pansoft104 oradata]$ ls
ben  ts_corrupt01.dbf  ts_corrupt.dbf
[oracle@pansoft104 oradata]$ dd of=ts_corrupt.dbf bs=8192 conv=notrunc seek=135 << EOF

> CORRUPT
> EOF
0+1 records in
0+1 records out
9 bytes (9 B) copied, 7.5293e-05 s, 120 kB/s

SQL> alter system flush buffer_cache;

System altered.

SQL>  conn usr_corrupt/usr_corrupt;
Connected.
SQL>  select count(*) from t_corrupt;
 select count(*) from t_corrupt
                      *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 135)
ORA-01110: data file 6: '/u01/app/oracle/oradata/ts_corrupt.dbf'

RMAN> BLOCKRECOVER DATAFILE 6 BLOCK 135;

Starting recover at 27-OCT-16
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 /u01/app/oracle/fast_recovery_area/BEN/backupset/2016_10_27/o1_mf_nnndf_HEALTHY_d1491s80_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/BEN/backupset/2016_10_27/o1_mf_nnndf_HEALTHY_d1491s80_.bkp tag=HEALTHY
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:03

Finished recover at 27-OCT-16

RMAN> exit


Recovery Manager complete.

select count(*) from t_corrupt;

  COUNT(*)
----------
     68308