We can perform BLOCK RECOVERY with or without RMAN BACKUP, Here is the demonstration of block media recovery with HOT BACKUP and RMAN BACKUP as well.

1)  Create tablespace, user and table in same schema.
2)  Take the backup of datafile.
a) HOT BACKUP.
b) RMAN BACKUP.
3)  Corrupt the data in datafile.
4)  Connect with RMAN and try to use BLOCKRECOVER command.
a) Perform Block Recovery with HOTBACKUP.
b) Perform Block Recovery with RMAN BACKUP.

Here you go…

Create tablespace, user and table in same schema:

$sql> create tablespace TS1 datafile ‘/d01/oradata/ts1.dbf’ size 100m;
$Create user U1 identified by U1 default tablespace TS1;
$sql> grant connect,resource to U1;
$sql> conn U1/U1
$sql> create table TEST_CORRUPT (no number);
$sql> insert into TEST_CORRUPT values(1);
$sql> coomit;
$sql> SELECT segment_name, a.tablespace_name, b.name FROM dba_segments a, v$datafile b WHERE a.header_file=b.file# AND a.segment_name=’TEST_CORRUPT’;
SEGMENT_NAME     TABLESPACE_NAME    NAME
—————                      —————                           ————–
TEST_CORRUPT         TS1                                     /d01/oradata/ts1.dbf

HOTBACKUP of datafile:

$sql> ALTER TABLESPACE TS1 BEGIN BACKUP;
[oracle@localhost]$cp /d01/oradata/ts1.dbf /d01/oradata/ts1_bkp.dbf
$sql> ALTER TABLESPACE TS1 END BACKUP;

RMAN BACKUP of datafile 4:

[oracle@localhost]$rman target /
RMAN> backup datafile 4;

Corrupt the data in datafile with dd command:

Make sure that dd command given below is just for learning purposes and should only be used on testing systems

$sql>select segment_name , header_file , header_block  from dba_segments where segment_name = ‘TEST_CORRUPT’
and owner = ‘U1’;
SEGMENT_NAME         HEADER_FILE   HEADER_BLOCK
—————————- ———–                ————
TEST_CORRUPT                      4                         16

[oracle@localhost]$cd /d01/oradata
[oracle@localhost]$dd of=ts1.dbf bs=8192 conv=notrunc seek=17 << EOF
> Bingo! Corrupted.
> EOF
0+1 records in
0+1 records out
18 bytes (18 B) copied, 0.000684 seconds, 27.0 kB/s

This command successfully executed block 17 in the data file “/d01/oradata/ts1.dbf” is corrupt

Check the data block curroption:

$sql> sqlplus / as sysdba
$sql> alter system flush buffer_cache;
$sql> conn u1/u1
$sql> select count(*) from TEST_CORRUPT;
select count(*) from test_corrupt
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 17)
ORA-01110: data file 4: ‘/d01/oradata/ts1.dbf’

Please perform below any one of the method, Here I am showing two methods with or without RMAN Backup.

Performing Block Recovery without having RMAN BACKUP:

As we have HOTBACKUP for datafile ts1.dbf, Catalog the “hot backup” to the RMAN repository

[oracle@localhost]$rman target /
RMAN> CATALOG DATAFILECOPY ‘/d01/oradata/ts1_bkp.dbf’;
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 17;

Performing Block Recovery with RMAN BACKUP:

As we have datafile 4 RMAN BACKUP, Perform recovey…

[oracle@localhost]$rman target /
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 17;

BLOCK MEDIA RECOVERY Complete we should now get the block 17 recovered back:

[oracle@localhost]$ sqlplus U1/U1
$sql> select count(*) from TEST_CORRUPT;
COUNT(*)
———-
1
$SQL> exit

HAPPY LEARNING !!