BLOCK RECOVERY Without Having RMAN Backup

Block media recovery recovers an individual corrupt datablock or set of datablocks within a datafile. In cases when a small number of blocks require media recovery, you can selectively restore and recover damaged blocks rather than whole datafiles.

More theoretical information read

It’s possible to perform Block Media Recovery with having only OS based “hot” backups and having NO RMAN backups.
Look at the following demonstration. Here:
1. Create a new user antony and a table corrupt_test in that schema.
2. Take OS backup (hot backup) of the users01.dbf where the table resides
3. Corrupt the data in that table and get block corruption error.
4. Connect with RMAN and try to use BLOCKRECOVER command. As we haven’t any backup, we get an error.
5. Catalog the “hot backup” to the RMAN repository.
6. Use BLOCKRECOVER command and recover the corrupted data block using cataloged “hot backup” of the datafile.
7. Query the table and get the data back!
Here is the scenario

SQL> CREATE USER antony IDENTIFIED BY antony;

User created.

SQL> GRANT DBA TO antony;

Grant succeeded.

SQL> CONN antony/antony;
Connected.
SQL> CREATE TABLE corrupt_test (id NUMBER);

Table created.

SQL> INSERT INTO corrupt_test VALUES(123);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> COLUMN segment_name format a15
SQL>  SELECT segment_name, tablespace_name from dba_segments
  2  WHERE segment_name='CORRUPT_TEST';

SEGMENT_NAME    TABLESPACE_NAME
--------------- ------------------------------
CORRUPT_TEST    USERS

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

SEGMENT_NAME    TABLESPACE_NAME NAME
--------------- --------------- -------------------------------------------
CORRUPT_TEST    USERS           /u01/app/oracle/oradata/orcl/users01.dbf

SQL> ALTER TABLESPACE USERS BEGIN BACKUP;

Tablespace altered.

SQL> host cp  /u01/app/oracle/oradata/orcl/users01.dbf  /u01/app/oracle/oradata/backup/users01_backup.dbf

SQL> ALTER TABLESPACE USERS END BACKUP;

Tablespace altered.

SQL> SELECT header_block FROM dba_segments WHERE segment_name='CORRUPT_TEST';

HEADER_BLOCK
------------
          67

SQL>

 [oracle@cdbs1 ~]$ dd of=/u01/app/oracle/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=68 << EOF
> rajeshkumar testing block corruption
> EOF
0+1 records in
0+1 records out
[oracle@cdbs1 ~]$

SQL> Conn antony/antony
Connected.


 SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

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

SQL> EXIT

 [oracle@cdbs1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 6 01:41:46 2010

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

connected to target database: ORCL (DBID=1245940166)
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 68;

Starting blockrecover at 06-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 05/06/2010 01:42:25
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore

RMAN>
RMAN> CATALOG DATAFILECOPY '/u01/app/oracle/oradata/backup/users01_backup.dbf';

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/backup/users01_backup.dbf recid=1 stamp=718249432
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 68;

Starting blockrecover at 06-MAY-10
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile copy /u01/app/oracle/oradata/backup/users01_backup.dbf

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

Finished blockrecover at 06-MAY-10

RMAN> EXIT


Recovery Manager complete.
[oracle@cdbs1 ~]$sqlplus 

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 6 01:45:04 2010

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> conn antony/antony
Connected.
SQL> select * from CORRUPT_TEST;

        ID
----------
       123

SQL>

regards,
rajeshkumar govindarajan.
 reference:
 http://kamranagayev.wordpress.com