You can hit this error on your alert.log. Error could be similar as:

ORA-01578: ORACLE data block corrupted (file # 12, block # 4207)

ORA-01110: data file 11: ‘/xx/sytem.dbf’

There are many possible causes of a block corruption including:

– Bad IO hardware / firmware
– OS problems
– Oracle problems
– Recovering through “UNRECOVERABLE” or “NOLOGGING” database actions
(in which case ORA-1578 is expected behavior)

Behavior in 9i and 10g, the view v$database_block_corruption used to get populated only when RMAN Backup validate /check logical validate command was run.

The populated information used to get refreshed only once the corruption was repaired (media recovery/Object dropped) and on re-run of the Rman Backup validate /check logical validate command on the database or the affected datafile.

With 11g this behavior has Changed.When any database utility or process encounters an intrablock corruption, it automatically records it in V$DATABASE_BLOCK_CORRUPTION.

The repair removes metadata about corrupt blocks from the view.

Repair techniques include:
– block media recovery,
– restoring datafiles,
– recovering by means of incremental backups, and block newing.

Do not forget, Block media recovery can repair physical corruptions, but not logical corruptions.

Database utilities which populates V$DATABASE_BLOCK_CORRUPTION on detecting corruption:

– Analyze table .. Validate structure
– Dbverify
– CTAS(Create table as Select)
– Export

Checking for Block Corruption with the VALIDATE Command
———————————————————
Syntax for Rman Validate Command :-

For Database :
RMAN > Validate database;

For Datafile :
RMAN > Validate datafile <file no>,<file no> ;

For Data block :
RMAN > Validate datafile <file no> block <Block no> ;

Archivelog restores for BMR can be run in parallel on multiple channels, but datafile/backupset scans and the recovery session must all run in the same server session.

To allow selection of which backup will be used to select the desired blocks,the blockrecover command supports options used in the restore command:

FROM BACKUPSET–> restore blocks from backupsets only
FROM DATAFILECOPY–> restore blocks from datafile copies only
FROM TAG–>restore blocks from tagged backup
RESTORE UNTIL TIME|SCN|LOGSEQ

So after validate how we can recover related corruptions? Here is the some examples:

Recovery using Explicit File/Block:

$ rman target / log=rman1.log

RMAN> run {blockrecover datafile 12 block 4207;}

Recovery using Corruption list : 

$ rman target / log=rman1.log

RMAN> run {blockrecover corruption list;}

There are too many documents available at metalink which are covers deeply explain concept with corruptions examples. So, I strongly suggest to review below docs while you are hitting similar errors on your system:

Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g [ID 28814.1]
Master Note for Handling Oracle Database Corruption Issues [ID 1088018.1]
Data Recovery Advisor – Corruption Reference Guide [ID 1317849.1]
RMAN : Block-Level Media Recovery – Concept & Example [ID 144911.1]
OERR: ORA-1578 “ORACLE data block corrupted (file # %s, block # %s)” Master Note [ID 1578.1]
HOW TO TROUBLESHOOT AND RESOLVE an ORA-1110 [ID 434013.1]
11g New Feature V$Database_block_corruption Enhancements and Rman Validate Command [ID 471716.1]

Advertisements