We have two different kinds of block corruption:
Physical corruption (media corrupt) : Physical corruption can be caused by defected memory boards, controllers or broken sectors on a hard disk.
Logical corruption (soft corrupt) : Logical corrution can among other reasons be caused by an attempt to recover through a NOLOGGING action.
When RMAN encounters a corrupt datafile block during a backup, the behavior depends upon whether RMAN has encountered this corrupt block during a previous backup. If the block is already identified as corrupt, then it is included in the backup. If the block is not previously identified as corrupt, then RMAN's default behavior is to stop the backup. We can override this behavior using the SET MAXCORRUPT command with BACKUP in a RUN block. Setting MAXCORRUPT allows a specified number of previously undetected block corruptions in datafiles during the execution of an RMAN BACKUP command. Here is the example of set maxcorrupt example.
Syntax : set maxcorrupt for datafile <dataFileSpec> TO <integer>
Example :
i.) RMAN>
run {
set maxcorrupt for datafile 3,4,5,6 to 1 ;
backup check logical database ;
}
In the above example datafile 3,4,5,6 may not more than 1 corruption datafile block otherwise backup will fail.
ii.)RMAN>
run {
set maxcorrupt for datafile 1 to 10;
backup database;
skip inaccessible;
skip readonly
}
If RMAN detects more than this number of new corrupt blocks while taking the backup, then the backup job aborts, and no backup is created. As RMAN finds corrupt blocks during the backup process, it writes the corrupt blocks to the backup with a special header indicating that the block has media corruption. If the backup completes without exceeding the specified MAXCORRUPT limit, then the database records the address of the corrupt blocks and the type of corruption found (logical or physical) in the control file. We can access these records through the V$DATABASE_BLOCK_CORRUPTION view.
Detecting Physical Block Corruption With RMAN BACKUP : RMAN checks only for physically corrupt blocks with every backup it takes and every image copy it makes. RMAN depends upon database server sessions to perform backups, and the database server can detect many types of physically corrupt blocks during the backup process. Each new corrupt block not previously encountered in a backup is recorded in the control file and in the alert.log. By default, error checking for physical corruption is enabled. At the end of a backup, RMAN stores the corruption information in the recovery catalog and control file.
How to detect block corruption ?
1.) DBVERIFY utility : DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. we use DBVERIFY primarily when we need to ensure that a backup database (or datafile) is valid before it is restored
2.) Block checking parameters : There are two initialization parameters for dealing with block corruption :
- DB_BOCK_CHECKSUM (calculates a checksum for each block before it is written to disk, every time) causes 1-2% performance overhead .
- DB_BLOCK_CHECKING (server process checks block for internal consistency after every DML) causes 1-10% performance overhead .
For more about
db_block_checking parameter
click here3.) ANALYZE TABLE :
ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE SQL statement Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF) . More about Analyze:Report Corruption
click here
4.) RMAN BACKUP command with VALIDATE option : We can use the VALIDATE option of the BACKUP command to verify that database files exist and are in the correct locations, and have no physical or logical corruptions that would prevent RMAN from creating backups of them. When performing a BACKUP... VALIDATE, RMAN reads the files to be backed up in their entirety, as it would during a real backup. It does not, however, actually produce any backup sets or image copies.
RMAN>
RESTORE DATABASE VALIDATE;
RMAN>
RESTORE ARCHIVELOG ALL VALIDATE;
To check for logical corruptions in addition to physical corruptions, run the following variation of the preceding command:
RMAN>
BACKUP VALIDATE
CHECK LOGICAL DATABASE
ARCHIVELOG ALL;
Detection of Logical Block Corruption : Besides testing for media corruption, the database can also test data and index blocks for logical corruption, such as corruption of a row piece or index entry. If RMAN finds logical corruption, then it logs the block in the alert.log. If CHECK LOGICAL was used,the block is also logged in the server session trace file. By default, error checking for logical corruption is disabled.
1.) RMAN found any block corruption in database then following
Data Dictionary view populated.
V$COPY_CORRUPTION
V$BACKUP_CORRUPTION
V$DATABASE_BLOCK_CORRUPTION
2.) EXPORT/IMPORT command line utility
Full database EXPORT/IMPORT show=y is another method.
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 4, block # 43)
ORA-01110: data file 4: 'D:\app\Neerajs\oradata\orcl\USERS01.DBF'
3.) DBMS_REPAIR package
dbms_repair is a utility that can detect and repair block corruption within Oracle. It is provided by Oracle as part of the standard database installation.For detail about dbms_repair Package
click here .
Enjoy
:-)
