How to check Database corrupt Block

This short article describes several features of Oracle which may be
used on new databases to help detect problems as close to when they
occur as possible. Some of these features are publicly documented
and some are not. All should be used with extreme care.

[@more@]

DBVERIFY
~~~~~~~~
DBVERIFY (also known as DBV) performs similar checks to the block
checking described above but it is a stand alone tool which can
be run against an Oracle datafile. Most Unix platforms allow DBV
to be run on an open database file but NT, VMS and other platforms
that use file level locking prevents this.

DBVERIFY checks data and index blocks and is supplied as standard
from Oracle 7.3 onwards. As of 8.1.6 addition block types are also
checked.

ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This SQL command allows Oracle to perform a health check on the
named table and any related indexes.

What does it do ?
The 'ANALYZE ... VALIDATE ..' command performs the same block checks
as the logical block checking above but does NOT mark blocks as
corrupt. It also checks that table and index entries match.
Any problems found are reported into the user session trace file
in USER_DUMP_DEST.

You can specify that you want to perform structure validation online
while DML is occurring against the object being validated.
There can be a slight performance impact when validating with ongoing
DML affecting the object, but this is offset by the flexibility of being
able to perform ANALYZE online.

ANALYZE TABLE VALIDATE STRUCTURE CASCADE ONLINE;

When you validate the structure of an object ONLINE, Oracle Database
does not collect any statistics, as it does when you validate the
structure of the object OFFLINE.

For a table, Oracle Database verifies the integrity of each of the data
blocks and rows.
For an index-organized table, the database also generates compression
statistics (optimal prefix compression count) for the primary key index
on the table.

For a cluster, Oracle Database automatically validates the structure of
the cluster tables.
For a partitioned table, Oracle Database also verifies that each row belongs
to the correct partition.
If a row does not collate correctly, then its rowid is inserted into the
INVALID_ROWS table.
Script UTLVALID.SQL is used to create invalid_rows table.

ANALYZE TABLE VALIDATE STRUCTURE into invalid_rows;

For an index, Oracle Database verifies the integrity of each data block
in the index and checks for block corruption.
This clause does not confirm that each row in the table has an index
entry or that each index entry points to a row in the table.
You can perform these operations by validating the
structure of the table with the CASCADE clause.

Benefits
This allows important tables to be regularly checked for corruptions
during a convenient timeslot.

Disadvantages
The command takes system resource and time to run.
When ran OFFLINE is locking the object.

Using ANALYZE
This is a standard SQL command so can be issued against any table
whenever required. It is advisable to have a script pre-prepared to
check all the major application tables using this feature.

DBMS_REPAIR
~~~~~~~~~~~
DBMS_REPAIR is a package introduced in Oracle 8.1.5.

The DBMS_REPAIR.CHECK_OBJECT procedure can be used to perform
the same type of checking that ANALYZE TABLE ... VALIDATE STRUCTURE
performs.

The DBMS_REPAIR package is used to work with corruption in the
transaction layer and the data layer only (software corrupt blocks).
Blocks with physical corruption (ex. fractured block) are marked as
the block is read into the buffer cache and DBMS_REPAIR ignores all
blocks marked corrupt.

DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM must both be set to FALSE.

DBMS_REPAIR procedures have the following limitations:

a.Tables with LOB datatypes, nested tables, and varrays are supported,
but the out of line columns are ignored.

b.Clusters are supported in the SKIP_CORRUPT_BLOCKS and REBUILD_FREELISTS procedures,
but not in the CHECK_OBJECT procedure.

c.Index-organized tables and LOB indexes are not supported.
d.The DUMP_ORPHAN_KEYS procedure does not operate on bitmap indexes or function-based indexes.
e.The DUMP_ORPHAN_KEYS procedure processes keys that are no more than 3,950 bytes long.

f.DBMS_REPAIR makes the object usable by ignoring corruptions during table and index scans,
Using the FIX_CORRUPT_BLOCKS and SKIP_CORRUPT_BLOCKS Procedures

RMAN
~~~~
RMAN can be used to check for both logical and physical corruption.

During a RMAN backup or RMAN 'backup validate' every block currently
used or previously used is read into memory then written to another
portion of memory.
During this memory to memory write the block is checked for corruption.
Therefore RMAN's BACKUP command with the VALIDATE and CHECK LOGICAL
clauses allow a Database Adminstrator to quickly check for both physical
and logical corruption.