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