Saturday, March 12, 2016

Things to know about Block media recovery in Oracle

Block Media Recovery
Oracle9i introduces the concept of block media recovery (BMR) to the recovery process.
You can use block media recovery to recover one or more corrupted data blocks within data files.If you’re using Oracle Database 10g or Oracle9i Database, use the BLOCKRECOVER command to perform block media recovery. In 11g instead of BLOCKRECOVER command we can simply use RECOVER command.

To perform BMR, the database must be open or mounted and in archive log mode and must have a current, usable control file. The database must not be a standby database.You must use level 0 or full backups for the restore. 
All of the required archived redo logs must be available for the recovery process.If you have enabled Flashback Database then RMAN will search the flashback logs for uncorrupted versions of the required blocks (from 11g onwards) . So if your blocks are available in the flashback logs RMAN won't look for any backups and it recover the corrupt block straight from flashback logs and it is probably faster.

Identification of Corrupt Blocks
The V$DATABASE_BLOCK_CORRUPTION view displays blocks marked corrupt by database components such as RMAN, ANALYZE, dbv, and SQL queries. So any SQL or RMAN section that encounter a corrupted block will update the V$DATABASE_BLOCK_CORRUPTION. The following types of corruption result in rows added to this view:

Physical corruption (sometimes called media corruption)
The database does not recognize the block: the checksum is invalid, the block contains all zeros, or the block header is corrupt.BMR can completeley recover physical corruption happened on  a block . 

Logical corruption
The block has a valid checksum, the header and footer match, and so on, but the contents are logically inconsistent. Block media recovery may not be able to repair all logical block corruptions. In these cases, alternate recovery methods, such as tablespace point-in-time recovery, or dropping and re-creating the affected objects, may repair the corruption.

If you are trying to take a RMAN database backup and your database have some corrupted block then your backup will fail. RMAN will stops at the first corrupt block, logging just that single one. To get the completed list of corrupt blocks you should issue a backup validate command: 

So to check the datafiles for any physical or logical corruption and verify whether a datafile is in the correct directory, use the backup validate command.By default, RMAN checks for physical corruption.  By using check logical syntax, we can check for logical corruption as well.

To validate the tablespace, use the following command:  

RMAN> backup validate tablespace USERS;

The above command is doing nothing but just validating USERS tablespace for backup operation., if it found any corrupt blocks,it will update v$database_block_corruption

To validate a specific datafile, use the following command:

RMAN> backup validate datafile 8;

To check the whole database along with archivelog use the following command:

RMAN> backup validate database archivelog all;

The above commands will look only for physical corruption.Add the CHECK LOGICAL clause to include checks for logical corruption.

RMAN> backup validate check logical database archivelog all;

Note:- From 11g onwards instead of backup validate we can use simple VALIDATE command .
RMAN> validate datafile 8;

Any block corruptions are visible in the V$DATABASE_BLOCK_CORRUPTION view. You can identify the objects containing a corrupt block using a query like this.
COLUMN owner FORMAT A20
COLUMN segment_name FORMAT A30

SELECT DISTINCT owner, segment_name
FROM   v$database_block_corruption dbc
JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block# BETWEEN e.block_id and e.block_id+e.blocks-1
ORDER BY 1,2;

Recover specific data block
rman> recover datafile 8 block 13;
rman> recover datafile 8 block 13 datafile 10 block 12;

Recovering All Blocks in V$DATABASE_BLOCK_CORRUPTION
RECOVER CORRUPTION LIST command will recover all the blocks that are marked corrupt .
rman> recover corruption list;
from following output it could see that RMAN recoverd the corrupt block from flashback logs . 
RMAN> blockrecover corruption list;
Starting recover at 16-NOV-10
using channel ORA_DISK_1
searching flashback logs for block images
finished flashback log search, restored 1 blocks
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-NOV-10
Automatic Block Corruption Repair ( in 11g Active data guard )
In addition to the real time query capability of the 11g Active Data Guard feature, we can also add to our high availability capability by using the Automatic Block Media(ABMR) Repair feature whereby data block corruptions on the Primary database can be repaired by obtaining those blocks from the standby site – all performed by a background process (ABMR) transparent to the application. To learn more read below link 

Basic concept:-
At the first encounter of corruption oracle marks a block as media corrupt and it won't keep that block in buffer cache anymore and oracle write that corrupted block into disk .No subsequent read of the block is successful until the block is recovered.You perform block media recovery with the RECOVER ... BLOCK command. By default, RMAN searches the flashback logs for good copies of the blocks, and then searches for the blocks in full or level 0 incremental backups. When RMAN finds good copies, it restores them and performs media recovery on the blocks

Advantages:

  1. With BMR, you can restore individual data blocks from your RMAN backups, and recover those blocks to the point of the failure. During the block recovery process, Oracle will continue to allow access to all blocks that are not corrupted.The only user impact will be to those users who want to access the corrupt blocks, and they will have been impacted anyway.
  2. Enables affected data files to remain online during recovery , that is no need to take the affected tabledpace/datafile offline .
  3. Lowers the mean time to recover (MTTR) because only blocks needing recovery are restored and recovered
  4. Without block media recovery, if even a single block is corrupt, then you must take the data file offline and restore a backup of the data file. You must apply all redo generated for the data file after the backup was created. The entire file is unavailable until media recovery completes. With block media recovery, only the blocks actually being recovered are unavailable during the recovery.
  5. Block media recovery is most useful for physical corruption problems that involve a small, well-known number of blocks.Block media recovery is not intended for cases where the extent of data loss or corruption is unknown and the entire data file requires recovery. In such cases, data file media recovery is the best solution.


No comments:

Post a Comment