RMAN DB BLOCK RECOVERY

Performing block recovery using RMAN

1)  First let's corrupt a block.

a)create a user with dba role

creating a user and granting privilege in a single sql statement.

SQL> grant dba to noxi identified by abc123;

Connect to the noxi user.

Creating a table, and inserting a row

SQL> create table tbl_corruption ( id number );

Table created.

SQL> insert into  tbl_corruption values (1);

2) Now let's take full backup plus archive logs  using RMAN .

RMAN>backup database plus archivelog;

backup completed.

3) Now let's check the header_block # from dba_segement

SQL> select header_block from dba_segments where segment_name='TBL_CORRUPTION';

HEADER_BLOCK

------------

          59

The block no corresponding  to the table tbl_corruption

4) Now find out  the datafile belonging to the table 'tbl_corruption'.

SQL> select a.name from v$datafile a,dba_segments b where a.file#=b.header_file and b.segment_name='TBL_CORRUPTION';

NAME

--------------------------------------------------------------------------------

/u14/oradata/stayalive/system01.dbf

5) Now let's corrupt the block belonging to the table tbl_corruption.

production:(stayalive)$  dd of=/u14/oradata/stayalive/users01.dbf bs=8192 conv=notrunc seek=60 <<EOF

6) Now connect to the sql prompt using the username noxi.

Now flush the buffer cache,then try to select from the table 'tbl_corruption'.

If you dont flush the buffer cache and query the table 'tbl_corruption',the output is fed from buffer cache instead of going to the  datafile.

SQL>alter system flush buffer_cache;

7)   SQL> select * from tbl_corruption;

select * from tbl_corruption

              *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 60)

ORA-01110: data file 4: '/u14/oradata/stayalive/users01.dbf'

Now  when  we  query table tbl_corruption, we get error as expected because as we  have manualy  corrupted the block corresponding to the table tbl_corrruption

8) connect to rman for restore and recovery purpose.

production:(stayalive)$ rman target /

restoring and recovering the block using rman with the following command.

RMAN> blockrecover datafile 4 block 60;

 Restore and recovery completed.

9)    Now let's check again the table 'tbl_corruption'

    SQL> select * from tbl_corruption;

        ID

----------

         2