In the following post, we’ll see how to perform Block level recovery when we have a Corrupt Block in a large datafile BUT we still have online redo log files and we have a backup’s in place & all the archived redo log files from when the backup was taken till the recent.

In such a case we don’t want to recover the whole datafile ( because of the time outage ), hence below are the recovery steps for this case:-

Recovery Steps:-

STEP 1:- Identifying the corrupt Block.

When RMAN detects corrupt blocks, it writes an error to the alert.log file and also populates the V$DATABASE_BLOCK_CORRUPTION view

• Select * from V$DATABASE_BLOCK_CORRUPTION;

Record the file# and block# from the above output.

STEP 2:- Recover the corrupt block.

• recover datafile <file#> block <block#>;

Where file# and block# are form the output of STEP 1.

If there are are several corrupted blocks, which you can see from the output of STEP 1, then alternatively use:-

• recover corruption list;

Block-level corruption is rare and is usually caused by some sort of I/O error. Block-level recovery is useful when a small number of blocks are corrupt within a datafile. RMAN automatically detects corruption in the blocks whenever a Backup Validate (eg:- restore database validate; ) command is used and populate the details in V$DATABASE_BLOCK_CORRUPTION view.

I hope this post would be helpful in scenarios when you know the recovery process but as there is lot of stress on DBAs during recovery scenarios even googling at that time for the syntax is an overhead, hence the idea is to make a repository that cover all possibile recovery scenarios step by step with syntax of commands at a single destination. I would try to cover all the scenarios in my subsequent posts. Please comment if any of the curious readers are interested to know the internals of the recovery process like which process does that, what information is in redo that is applied to the datafiles to recover them to point in time and etc.

PLEASE COMMENT IF YOU FACE ANY ERRORS WHEN PERFORMING THIS RECOVERY SO THAT WE CAN WORK ON THOSE AND ENHANCE THIS REPOSITORY FOR OTHER READERS.