Database Technologies‎ > ‎Oracle‎ > ‎

Block Corruption

.../Updation in Progress

Block Corruption :

Block inconsistent or Oracle
is not able to recognize the block

Whenever data from block is read / written to a consistency check is performed,
additionally cheksum check is performed when enabled (by default it is enabled for system tablespace)

Two Types of Corruption :

Logical :
Internal error of oracle software corrupts the block

Physical : Media underlying is corrupt, check for memory,paging space, i/o controllers
Error is generated when corrupted block is found and logged in alert.log
Absolute file number and block number are also  logged
Common ora error for corruption ORA-01578
Tools to check for corruption
 
 
 
RMAN does not detect logical corruption by default, you have to
give the parameter for doing that
 
RMAN> backup validate check logical database;  \\ For checking whole db
RMAN>backup validate check logical datafile 1; \\ For checking particular file
 
It does not backup the database, just check for corruption
feeds that information into the table  v$database_block_corruption
Pull the data from the table
 
select * from v$database_block_corruption;

File#         :     File id of the datafile containing the corrupted block
Block#      :     Block number of the first corrupt block in the range of or corrupted block.
Blocks      :     Number of corrupted block starting from the block#
Corruption_change : SCN at which corruption was detected, 0 indicates physical corruption
Corruption type : ALL ZERO,FRACTURED,CHECKSUM ,CORRUPT ,LOGICAL

 
 
dbverify : External command utility that is used to check
data structure integrity of a datafile, these could
be run againts online as well as offline datafile
syntax
#dbv help=y
#dbv file='/path/to/datafile' blocksize=<block size>



 
 
analyze : Perform logical check for block corruption / validate index
and table entries/Updates statistics

select * from table, is normally used
to perform quick check for corruptions in a specific table data

for table import the data from dr,exp backup
for index create the index again after dropping
for any other object ,drop and re-create


To restore object in corrupted objects
file object in corrupted blocks

select segment_name,segment_type,relative_fno from dba_extents
where file_id=<file_id>
and <block_id> between block_id and block_id + blocks -1;



 
 
bb_block_checking
dbms_repair package






==================================
Database Corruption

DataBlock Corruption :

Two types of block corruption are observed

Software or Logical Corruption :  Error in Oracle Software corrupts the
                                  the block logically
Media or Physical Corruption :   If the block is media corrupt
                                 the block is physically corrupted


For an Media Corrupt Block the information in itis rendered useless
after the read operation from the disk is performed, when an inconsistency
is detected the oracle database marks the software-corrupt blocs
as "corrupt"


ORA-01578 errror, is well known error for Corrupted Blocks due to Hardware Problem.
Whenever such corrupted datablocks are found the error gets logged
in alert.log file, the error also accompanies the file number
and the block number that caused the error


=
If  ORA-01578 error appears repeatedly with identical arguments
it is most likely a media-corrupt block. However if the argument vary each
time there may be a hardware problem.



==
DBVERIFY Utility

is a utility that detects database block corruption.
An offline database file could also be tested for structural integrity by
this utility

This utility checks for block consistency

after the utility is executed we get a report
specifying total blocks scanned the number of
blocks failed

There is one columns for datablocks in flux

Flux is a process when blocks are read and written at the same time and
the dbverify utility fails to get a consistent image of the blocks.

Normally dbverify utility is used to ensure that a backup of database
or specific datafile is valid before it is restored.

For an offline database the integrity checks are faster compared to online
database


==

==Analyze :
Analyze command performs logical check for block corruptions
,validate index and table entries, also it populates table and
index statistics.


Analyze command is used to validate the structure of a table
,a table partition, an index or an index partition.

Command : ANALYZE TABLE <table_name> VALIDATE STRUCTURE CASCADE;
          ANALYZE TABLE <index_name> VALIDATE STRUCTURE CASCADE;

When there is media corruption Analyze command
only reports the block and does not mark it as soft corrupt

=
you could also use select * from <table_name>
to perform quick check for corruptions in your table
==

DB_BLOCK_CHECKING : ALTER SYSTEM set DB_BLOCK_CHECKING = OFF | LOW | MEDIUM | FULL

OFF : No Block Checking is performed for blocks in the user tablespaces, block
            checking for SYSTEM tablespace blocks is always turned on

LOW : Block header checks are performed after block content changes in memory

MEDIUM : All LOW checks are performed,not the index-organized table blocks

FULL :  All LOW checks are performed,including the  index blocks

( FULL=TRUE and,FALSE=OFF , For backward compatibility )


DB_BLOCK_CHECKSUM : ALTER SYSTEM set DB_BLOCK_CHECKSUM = OFF | TYPICAL | FULL

Checksum is stored for every block and is verified every time block is read.
When set to full catches in-memory corruptions and stops them from making it to the disk

=

dbms_repair package :

Detect and repair corrupt blocks in tables and indexes.

While the  object is online

Various Procedure in DBMS_REPAIR Packaged
CHECK_OBJECT     Detects and reports corruption in table or index

FIX_CORRUPT_BLOCK Marks the corrupted blocks as software corrupt
DUMP_ORPHAN_KEYS
REBUILD_FREELISTS
SEGMENT_FIX_STATUS
SKIP_CORRUPT_BLOCKS  : Ignores the blocks that are marked as
                       Corrupt during table and index scans
ADMIN_TABLES



BMR : Block media recovery

Using RMAN's BLOCKRECOVER command you could recover a corrupt block
based on the time,scn or log sequence


BLOCKRECOVER DATAFILE 4 BLOCK 44,66,55;

    =

solarins pkg addition

pkgadd -d . SUNWarc

first go to the folder ./Product


Comments