Oracle Database Block corruptions

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-01578&ORA-01110 is expected behavior)

Before start to post, let me remind you how many corruptions we have… There are 2 types of corruptions. Data corruption can manifest itself as a physical or a logical corruptions.

So Let us define all those,

Physical Corruption of a block manifests as an invalid checksum or header, or when the block contains all zeroes. When that happens, the database will not recognize the block as a valid Oracle block, regardless of its content. Physical corruptions (media corrupt blocks) are blocks that have sustained obvious physical damage. When Oracle detects an inconsistency between the CSN in the block header and the CSN in the block footer, or the expected header and footer structures are not present or are mangled, then the Oracle session raises an exception upon read of the block (ORA-01578: ORACLE data block corrupted…). A physical corruption is also called a media corruption.

Logical Corruption happens when a data block has a valid checksum, etc., but the block contents are logically inconsistent. Logical block corruption can also occur when the structure below the beginning of the block (below the block header) is corrupt. In this case, the block checksum is correct but the block structures may be corrupt. Logical corruption can also result from a lost write.

For more information, see My Oracle Support Note 840978.1.

So…When you can get this error messages? You may not got error message during access corrupted blocks until access related blocks

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

All those Database utilities are populates V$DATABASE_BLOCK_CORRUPTION on detecting corruption:

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.

You can identify the objects containing a corrupt block using a query like this

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;

or you can use :

select * from V$DATABASE_BLOCK_CORRUPTION;   << get file#&block from here

SELECT segment_type, segment_name
FROM dba_extents
WHERE file_id = 1
AND block_id < 3840
AND block_id + blocks >= 3840;

SEGMENT_TYPE      SEGMENT_NAME
—————– —————————————-
INDEX             I_SOURCE1

or use:

SELECT segment_type, segment_name
FROM dba_extents
WHERE file_id = 1
AND 3840 BETWEEN block_id AND block_id + blocks -1;

or

select ‘SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = ‘ || FILE# || ‘ and ‘ || BLOCK# || ‘ between block_id AND block_id + blocks – 1;’  from v$database_block_corruption order by 1;

Repair techniques include:

– block media recovery,
– restoring datafiles,
– recovering by means of incremental backups, and block newing,
– Export&import
– CREATE TABLE .. AS SELECT command.

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

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 Block Media Recovery (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 our db than how we can recover related corruptions? Here is the some examples:

Recovery using Explicit File/Block:

$ rman target / log=rman1.log

RMAN> blockrecover datafile 12 block 4207;

Recovery using Corruption list :

$ rman target / log=rman1.log

RMAN> blockrecover corruption list;

The key approach to detecting and preventing corrupted data is to perform the following MAA Best Practices.

• Use Oracle Data Guard
• Set the Oracle Database block corruption detection parameters
• Implement a backup and recovery strategy with Recovery Manager (RMAN)

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]
http://www.oracle.com/technetwork/database/availability/maa-datacorruption-bestpractices-396464.pdf
Advertisements