Block Corruption in Oracle
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.
Grants administrative privileges such as the ability to delete rows and drop objects. Use this procedure to create repair table.
Detects corrupt blocks in an object, and reports the corruptions and fixes to the repair table.
Fixes corrupt blocks based on data gathered with CHECK_OBJECT.
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.
Rebuilds the object’s freelists if they are corrupted.
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.