How to Perform Block Recovery without having RMAN backup

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

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

It’s possible to perform Block Media Recovery with having only OS based “hot” backups and having NO RMAN backups.

Step 1. Create a new user and a table in that schema
[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 4. Confirm created user's tablespace and datafile
SQL> COLUMN segment_name FORMAT a45
SQL> SELECT segment_name, tablespace_name from dba_segments WHERE segment_name='TBS_CORRUPTION';
SEGMENT_NAME                                  TABLESPACE_NAME
--------------------------------------------- ------------------------------
TBS_CORRUPTION                                    USERS

SQL> COLUMN segment_name FORMAT a15
SQL> COLUMN tablespace_name FORMAT a15
SQL> COLUMN name FORMAT a45
SQL> SELECT segment_name, a.tablespace_name, b.name FROM dba_segments a, v$datafile b
  2  WHERE a.header_file=b.file# AND a.segment_name=
  3  'TBS_CORRUPTION';

SEGMENT_NAME    TABLESPACE_NAME NAME
--------------- --------------- ---------------------------------------------
TBS_CORRUPTION  USERS           /u01/app/oracle/oradata/prim/users01.dbf


Step 5. Take hot backup of users01.dbf datafile.
SQL> ALTER TABLESPACE users BEGIN BACKUP;
Tablespace altered.

SQL> ! cp /u01/app/oracle/oradata/prim/users01.dbf /u01/app/oracle/oradata/prim/users01_bkp.dbf

SQL> ALTER TABLESPACE users END BACKUP;

Tablespace altered.

Step 6. Find out the header block where the below table exists.
SQL> SELECT header_block FROM dba_segments WHERE segment_name='TBS_CORRUPTION ';

HEADER_BLOCK
------------
         706

[oracle@server1 ~]$ dd of=/u01/app/oracle/oradata/prim/users01.dbf bs=8192 conv=notrunc seek=707  <<EOF
> corruption
> EOF
0+1 records in
0+1 records out
11 bytes (11 B) copied, 7.5949e-05 s, 145 kB/s

[oracle@server1 ~]$ sqlplus /  as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 19 19:01:19 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn 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 # 707)
ORA-01110: data file 4: '/u01/app/oracle/oradata/prim/users01.dbf'


SQL> exit

       
Step 7. Now we will try to recover the corrupted datablock.
[oracle@server1 ~]$ rman target sys

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jan 19 19:02:33 2015

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

target database Password:
connected to target database: PRIM (DBID=4130009889)

RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 707;

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/19/2015 19:03:08
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore

Step 8.Catalog the “hot backup”
to the RMAN repository
RMAN> CATALOG DATAFILECOPY '/u01/app/oracle/oradata/prim/users01_bkp.dbf';

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/prim/users01_BKP.DBF RECID=2 STAMP=869425424

RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 707;

Starting recover at 19-JAN-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile copy /u01/app/oracle/oradata/prim/users01_BKP.DBF

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 19-JAN-15

RMAN> EXIT

Step 9.Verify the data.
[oracle@server1 ~]$ sqlplus SOUMYA/SOUMYA

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 19 19:04:08 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from TBS_CORRUPTION;

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