Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 4 November 2012

Block Media Recovery using RMAN

Tested: Oracle 10g Database
OS: Windows 2003

Overview: Block Media Recovery (BMR) is one of the most powerful features of RMAN using BMR you can recover only those few blocks instead of recovering whole datafile. In case you are getting the below error. That means a data block has been corrupted. In this situation you have to restore and recover your full datafile if you do not have RMAN backup. 
ORA-01578: ORACLE data block corrupted (file # 9, block # 15)
ORA-01110: data file 9: ‘C:\ORACLE1\ORADATA\USERS01.DBF’
Check the object details get corrupted:
Login with sysdba account
SQL>SELECT tablespace_name, segment_type, owner, segment_name
 FROM  dba_extents
 WHERE file_id = 9  AND 15 between block_id AND block_id +  blocks-1;
Otherwise you directly query with V$DATABASE_BLOCK_CORRUPTION
SQL>SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
SQL>SELECT segment_name , header_file , header_block
from dba_segments
where segment_name = 'EMPLOYEE'  and   owner = 'PERFORMANCE';

If the block belongs to an index segment, drop and recreate the index and if the block belonged to a data segment either use Rman Blockrecover or restore and recover the complete datafile to fix the corruption.
C:\rman target sys/oracle@orcl3 catalog catalog/catalog@rman
RMAN> BLOCKRECOVER DATAFILE 9 BLOCK 15;
You can also able to report list of corruption from v$backup_corruption view and can recover all those corrupted block with one command. By default start recovery with the last backup. So it is important to know the backup tag to recover with particular backup.
SQL>Select piece#, file#, block# , blocks , marked_corrupt
from v$backup_corruption;
RMAN> BLOCKRECOVER CORRUPTION LIST FROM TAG "DAILY_ORCL3";
RMAN> list backup summary;
RMAN> BLOCKRECOVER corruption list from tag "DAILY_ORCL3";
Starting blockrecover at 02-NOV-12
using channel ORA_DISK_1
starting media recovery
media recovery complete
Finished blockrecover at 02-NOV-12
RMAN> exit;
/*
We need to flush the buffer_cache because if the block 15 is in the buffer_cache already it will not be read from the data file.
*/
 SQL> alter system flush buffer_cache;
Thus you will have successfully restored the corrupted block with minimum required time with the help of RMAN.
SQL> SELECT COUNT(*) FROM EMPLOYEES;

0 comments:

Post a Comment