Friday, February 3, 2012

RMAN BACKUP VALIDATION AND RECOVERY FROM BLOCK CORRUPTION:


The main purpose of RMAN validation is to check for corrupt blocks and missing files

Corruption in block:

Block corruption is while the data is being written to the data blocks, if the write to the block fails abruptly, I mean that there is a partial write in the block, may be because of power disruption or I/O problem, leaving no time for header to be updated, or row data to be populated, oracle leaves the block corrupt.In case of block corruption you can normally use the database unless you try to read that particular block, against which it shoots up the block corruption error.Generally block corruption occurs if write fails on the block, when the transaction is being committed

Physical(media corrupt) and Logical(software corrupt) Block Corruption:

In a physical corruption, which is also called a media corruption, the database does not recognize the block at all: the checksum is invalid.

Checksum:

A number calculated by the database from all the bytes stored in a data or redo block. If the DB_BLOCK_CHECKSUM initialization parameter is enabled, then the database calculates the checksum for every datafile or online redo log block and stores it in the block header when writing to disk. The database can use the checksum value to check consistency.

In a logical corruption, the contents of the block are logically inconsistent.

The logical corruption happens within the blocks , for eg. some index entry pointing towards a null rowid.

Validating Database Files with BACKUP VALIDATE:

You can use the BACKUP VALIDATE command to do the following:

-Check datafiles for physical and logical block corruption.

-Confirm that all database files exist and are in the correct locations.


Validating only physical corruption:

BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Validating both physical and logical corruption:

BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;


Validating Backups Before Restoring Them:

You can run RESTORE ... VALIDATE to test whether RMAN can restore a specific file or set of files from a backup.

RESTORE DATABASE VALIDATE;

RESTORE ARCHIVELOG ALL VALIDATE;


RECOVRING A BLOCK CORRUPTION:

If it finds corrupted blocks(after checking with BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;) it will place the information about the corruption into a view:

v$database_block_corruption

SQL>select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
5 81 4 0 CORRUPT

this is what we find in the alert_.log:

Corrupt block relative dba: 0x014000b1 (file 5, block 177)
Bad header found during backing up datafile
Data in bad block:
type: 67 format: 7 rdba: 0x0a545055
last change scn: 0×0000.0007bc77 seq: 0×3 flg: 0×04
spare1: 0×52 spare2: 0×52 spare3: 0×0
consistency value in tail: 0xbc772003
check value in block header: 0xb32
computed block checksum: 0xe4c1
Reread of blocknum=177, file=/u01/app/oracle/oradata/orcl/
example01.dbf.
found same corrupt data 


Now we can tell RMAN to recover all the blocks which it has found as being corrupt:

RMAN> blockrecover corruption list;

# (all blocks from v$database_block_corruption)
Starting blockrecover at 05-04-2006:10:09:15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s) from datafile copy /u01/app/
oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_236tmb1c_.dbf
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/
flash_recovery_area/ORCL/archivelog/2006_04_05/o1_mf_1_2_236wxbsp_.arc
archive log thread 1 sequence 1 is already on disk as file
/u01/app/oracle/oradata/
orcl/redo01.log
media recovery complete, elapsed time: 00:00:01
Finished blockrecover at 05-04-2006:10:09:24