Recovery from block corruption

It is possible to recover corrupted blocks using RMAN backups. This is a somewhat exotic scenario, but it can be useful in certain circumstances, as illustrated by the following example. Here's the situation: a user connected to SQLPlus gets a data block corruption error when she queries a table. Here's a part of the session transcript:

Step 1 SQL> conn scott/anup123;

Connected.

Step 2 SQL> create table test(id number);

Table created.

Step 3 SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

Step 4 SQL> select * from test;

ID

---------

1

Step 5 Check header block.

SQL> conn / as sysdba

Connected.

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

HEADER_BLOCK

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

395

SQL>

Step 6 use dd commands for block corruption.

dd of=/DB/ANUP/users01.dbf bs=8192 conv=notrunc seek=395<< EOF
> testing corruption
> EOF
0+1 records in
0+1 records out

Step 7 ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.

Step 8  select * from test;

select * from test

ERROR at line 1:

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

ORA-01110: data file 4: '/DB/ANUP/users01.dbf'

Step 9 Now connect to the RMAN and recover the data block (not the whole datafile or database) using BLOCKRECOVER command as follows:

RMAN> blockrecover datafile 4 block 395;

Starting blockrecover at 28-MAR-10

…………….

……………

…………..

Finished blockrecover at 28-MAR-10

RMAN> exit

Connect to SQL*Plus and query the table:

SQL> SELECT * FROM test;

ID

1

SQL>