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 free lists, 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 a 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 free lists 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 24/7 running large databases.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.
This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.
