Wednesday, August 5, 2020
dbametrix
More
    Home Oracle Troubleshooting How to Detect Block Corruption in Oracle?

    How to Detect Block Corruption in Oracle?

    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.

    - Advertisement -
    dbametrix

    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.

    1+
    - Advertisment -
    dbametrix

    Most Popular

    What was new in Oracle 12c

    Blog post explains in detail Oracle 12c new features for Developers including in-memory and Advanced Index Compression.

    The Life of DBAs is Changing

    Article explains how The Life of DBAs is Changing in Self-Managing Oracle Database. An detail introduction of Oracle Autonomous Database Cloud

    MySQL as PHP database

    The article illustrates the relationship between data and semantics of data stored in a database with reference to the semantic WEB

    Supply Chain Management in ERP

    The article provides detail of what is supply chain management system and what is the importance.

    Recent Comments