Data Block corruption Detect and solving Techniques

Below are the possible causes of a block
corruption:
– 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)
There are 2 types of Data corruptions.,
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.
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;
Below are techniques to solve the data
corruption:
– 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
For more information, see My Oracle Support
Note 840978.1.

  • July 5, 2018 | 18 views
  • Comments