Block Recovery using 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

Here , I have done block recover using rman with datafile number and corrupted block number. using this there is no need of taking datafile offline.

you can perform this with smile steps,

for experiment i have created a user KIRAN and created a table DEMO_CORRUPT, corrupting datafile of tablespace users using operating system dd command, then i have recovered the corrupted blocks using RMAN Blockrecover command.

step 1: creating user and table in that schema.

step 2: taking rman backup for that particular tablespace or datafile.

step 3: corrupting datafile block using linux command dd(testing purpose only).

step 4: recover corrupted block using rman.

step 5: check the recovered table in that schema.

SQL> CREATE USER kiran IDENTIFIED BY kiran;

User created.

SQL> GRANT DBA TO kiran;

Grant succeeded.

SQL> CONN kiran/kiran;
Connected.
SQL> CREATE TABLE demo_corrupt(id NUMBER);

Table created.

SQL> INSERT INTO  demo_corrupt VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> COLUMN segment_name format a15
SQL>
SQL> SELECT segment_name,tablespace_name
  2  FROM dba_segments
  3  WHERE segment_name='DEMO_CORRUPT';

SEGMENT_NAME    TABLESPACE_NAME
--------------- ------------------------------
DEMO_CORRUPT    USERS

SQL>

SQL> COLUMN tablespace_name FORMAT a10
SQL> COL name FORMAT a43
SQL>
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='DEMO_CORRUPT'
  5  ;

SEGMENT_NAME    TABLESPACE NAME
--------------- ---------- -------------------------------------------
DEMO_CORRUPT    USERS      /u01/app/oracle/oradata/orcl/users01.dbf

SQL>

[oracle@cdbs1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 5 18:46:01 2010

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

connected to target database: ORCL (DBID=1245940166)
RMAN> BACKUP TABLESPACE USERS;

Starting backup at 05-MAY-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-MAY-10
channel ORA_DISK_1: finished piece 1 at 05-MAY-10
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_05_05/o1_mf_nnndf_TAG20100505T185519_5y2wg0gj_.bkp tag=TAG20100505T185519 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 05-MAY-10
RMAN> exit


Recovery Manager complete.


SQL> SELECT header_block from dba_segments
  2  WHERE segment_name='DEMO_CORRUPT';

HEADER_BLOCK
------------
          59

SQL>  

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

SQL> CONN kiran/kiran;
Connected.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

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


SQL> 

oracle@cdbs1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 5 19:03:38 2010

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

bconnected to target database: ORCL (DBID=1245940166)

RMAN>
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 60;

Starting blockrecover at 05-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=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/flash_recovery_area/ORCL/backupset/2010_05_05/o1_mf_nnndf_TAG20100505T185519_5y2wg0gj_.bkp
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_05_05/o1_mf_nnndf_TAG20100505T185519_5y2wg0gj_.bkp tag=TAG20100505T185519
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02

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

Finished blockrecover at 05-MAY-10

RMAN> 

SQL> CONN kiran/kiran;
Connected.
SQL> SELECT * FROM demo_corrupt;

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

Regards,
Rajeshkumar Govindarajan.