Performing Block Media Recovery using RMAN

Oracle Version :- 11g
Os Version:-    Rhel 6.4

To demonstrate this scenario we need some corrupted datablocks on some data files.

So here we will corrupt a data block manually in order to test the block recovery feature of RMAN.

Step 1:-We will create a user and will grant him dba privilge.

[oracle@server1 ~]$ sqlplus /  as sysdba

SQL> create user soumya identified by soumya;
User created.

SQL> grant dba to soumya;
Grant succeeded.


Step 2:- Now create a table inside the newly created user
SQL> conn soumya/soumya
Connected.
SQL> create table tbs_corruption (id number);
Table created.

SQL> insert into tbs_corruption values (1);
1 row created.

SQL> commit;
Commit complete.

Step 3:-Now we will take rman backup of database
[oracle@server1 ~]$ rman target /

RMAN> backup database plus archivelog;
Starting backup at 17-JAN-15
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=1 STAMP=869264964
input archived log thread=1 sequence=7 RECID=2 STAMP=869265416
channel ORA_DISK_1: starting piece 1 at 17-JAN-15
channel ORA_DISK_1: finished piece 1 at 17-JAN-15
piece handle=/u01/app/oracle/rman_backup/PRIM_DB_07psvs08_7_1 tag=TAG20150117T223656 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-JAN-15

Starting backup at 17-JAN-15
using channel ORA_DISK_1
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=/u01/app/oracle/oradata/prim/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/prim/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/prim/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prim/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/prim/users01.dbf
channel ORA_DISK_1: starting piece 1 at 17-JAN-15
channel ORA_DISK_1: finished piece 1 at 17-JAN-15
piece handle=/u01/app/oracle/rman_backup/PRIM_DB_08psvs0a_8_1 tag=TAG20150117T223657 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 17-JAN-15

Starting backup at 17-JAN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=3 STAMP=869265483
channel ORA_DISK_1: starting piece 1 at 17-JAN-15
channel ORA_DISK_1: finished piece 1 at 17-JAN-15
piece handle=/u01/app/oracle/rman_backup/PRIM_DB_09psvs2c_9_1 tag=TAG20150117T223804 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-JAN-15

Starting Control File and SPFILE Autobackup at 17-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/autobackup/2015_01_17/o1_mf_s_869265485_bco5qp79_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-JAN-15

Step 4:-Now we will find out the datablock number which we need to restore
[oracle@server1 ~]$ sqlplus /  as sysdba
SQL> conn soumya/soumya
Connected.
SQL>  select HEADER_BLOCK from dba_segments where segment_name='TBS_CORRUPTION';

HEADER_BLOCK
------------
         522

The header block of the segment is 522.We are going to corrupt the next data block which is 523.

Step 5:- We will check in which datafile the above data block exists.
SQL> select a.name from v$datafile a, dba_segments b
  2  where a.file#=b.header_file and b.segment_name='TBS_CORRUPTION';

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/users01.dbf

So the table is  stored inside users01.dbf datafile.

Step 6:-Now lets corrupt the datafile
[oracle@server1 ~]$ dd of=/u01/app/oracle/oradata/prim/users01.dbf bs=8192 conv=notrunc seek=523 <<EOF
> testing corruption
> EOF
0+1 records in
0+1 records out
19 bytes (19 B) copied, 5.8942e-05 s, 322 kB/s

Now we have successfully corrupted the datafile

Step 7:- Now we need to flush the database buffer cache
[oracle@server1 ~]$ sqlplus soumya/soumya
SQL> alter system flush buffer_cache;
System altered.

SQL> select * from tbs_corruption;
select * from tbs_corruption
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 523)
ORA-01110: data file 4: '/u01/app/oracle/oradata/prim/users01.dbf'

Step 8:-Now we will do block media recovery using rman
[oracle@server1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 17 23:15:40 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRIM (DBID=4130009889)

RMAN> blockrecover datafile 4 block 523;

Starting recover at 17-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rman_backup/PRIM_DB_0cpsvs3b_12_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/rman_backup/PRIM_DB_0cpsvs3b_12_1 tag=TAG20150117T223835
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 17-JAN-15

RMAN> exit

Now recovery has been done. we will check the table's data now
[oracle@server1 ~]$ sqlplus soumya/soumya
SQL> select * from tbs_corruption;

        ID
----------
         1

So we have successfully recovered the corrupted data block .