How to Detect Block Corruption in Oracle?

April 12, 2012 | By

Block Corruption in Oracle

oracle block corruption, block corruption, oracle table

Analysis and Investigation of Corrupt Block in Oracle Database

Oracle has four methods for detecting corruption. The analyze commend with the validate structure option can be used to verify the structure of a table, index, or cluster, and to confirm that a table and its indexes are in sync. The external DB_VERIFY utility (dbf in Unix/Linux) is used to validate blocks in data files that are offline. The init.ora parameter DB_BLOCK_CHECKING=true forces verification when a block is changed, and any errors detected are written to the alert.log.

The DBMS_REPAIR package gives you the ability to detect corrupt blocks in tables, partitions, and indexes. You can detect and report corrupt blocks to a repair table, make the object usable, and then repair the blocks and rebuild lost data. During the repair, data might be lost and logical inconsistencies might be introduced; therefore, it is important that you understand the significance of the data before making repair decisions.

DBMS_REPAIR Package in Oracle

The procedures in the DBMS_REPAIR package are used to discover, repair, or ignore corrupt blocks. These procedures are also used to rebuild freelists, report on orphaned indexes, and perform administrative tasks on corrupt blocks.

ADMIN_TABLES

Grants administrative privileges such as the ability to delete rows and drop objects. Use this procedure to create repair table.

CHECK_OBJECT

Detects corrupt blocks in an object, and reports the corruptions and fixes to the repair table.

FIX_CORRUPT_BLOCKS

Fixes corrupt blocks based on data gathered with CHECK_OBJECT.

SKIP_CORRUPT_BLOCKS

Indicates whether to ignore blocks marked corrupt during table or index scans, or to write an ORA-1578 error message to the alert.log when a marked corrupt block is read.

REBUILD_FREELISTS

Rebuilds the object’s freelists if they are corrupted.

DUMP_ORPHAN_KEYS

Lists the index keys that point to rows in corrupt data blocks.

Next time we will explain about more of DBMS_REPAIR package and its limitations. During database services it is very essential to take care of corruption in 24/7 running large databases.

Wish you all the best.

Be Sociable, Share!

Tags: , , , ,

Category: blog, blogging, database, oracle 11g, oracle 11g dba, oracle 9i dba, oracle database, oracle dba, oracle utilities, oracle9i, technology

Comments are closed.