Recovery from Block Corruption
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
> testing corruption
> EOF
0+1 records in
0+1 records out
Step 7 ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
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>
Responses