Using the DBMS_REPAIR Package - Oracle 10g

The following approach is recommended when considering DBMS _REPAIR for addressing data block corruption:

  • Task 1: Detect and Report Corruptions
  • Task 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR
  • Task 3: Make Objects Usable
  • Task 4: Repair Corruptions and Rebuild Lost Data

These tasks are discussed in succeeding sections.

Task 1: Detect and Report Corruptions

Your first task, before using DBMS _REPAIR, should be the detection and reportingof corruptions. Reporting not only indicates what is wrong with a block, but also identifies the associated repair directive. You have several options, in addition to DBMS _REPAIR, for detecting corruptions. Table describes the different detection methodologies.

Comparison of Corruption Detection Methods

Comparison of Corruption Detection Methods


The CHECK _OBJECT procedure checks and reports block corruptions for a specified object. Similar to the ANALYZE ... VALIDATE STRUCTURE statement for indexes and tables, block checking is performed for index and data blocks. Not only does CHECK _OBJECT report corruptions, but it also identifies any fixes that would occur if FIX _CORRUPT _BLOCKS is subsequently run on the object. This information is made available by populating a repair table, which must first be created by the ADMIN_TABLES procedure.

After you run the CHECK _OBJECT procedure, a simple query on the repair table shows the corruptions and repair directives for the object. With this information, you can assess how best to address the problems reported.

DB _VERIFY: Performing an Offline Database Check

Typically, you use DB_VERIFY as an offline diagnostic utility when you encounter data corruption problems.

ANALYZE: Corruption Reporting

The ANALYZE TABLE ... VALIDATE STRUCTURE statement validates the structure of the analyzed object. If the database successfully validates the structure, a message confirming its validation is returned to you. If the database encounters corruption in the structure of the object, an error message is returned to you. In this case, drop and re -create the object.

DB _BLOCK _CHECKING (Block Checking Initialization Parameter)

You can set block checking for instances using the DB _BLOCK _CHECKING initialization parameter (the default value is FALSE). This checks data and index blocks whenever they are modified. DB _BLOCK _CHECKING is a dynamic parameter, modifiable by the ALTER SYSTEM SET statement. Block checking is always enabled for the system tablespace.

Task 2: Evaluate the Costs and Benefits of Using DBMS _REPAIR

Before using DBMS_REPAIR you must weigh the benefits of its use in relation to the liabilities. You should also examine other options available for addressing corrupt objects. Begin by answering the following questions:

  • What is the extent of the corruption?

    To determine if there are corruptions and repair actions, execute the CHECK_OBJECT procedure, and query the repair table.

  • What other options are available for addressing block corruptions? Consider the following:
    • If the data is available from another source, then drop, re -create, and repopulate the object.
    • Issue the CREATE TABLE ... AS SELECT statement from the corrupt table to create a new one.
    • Ignore the corruption by excluding corrupt rows from select statements.
    • Perform media recovery.
  • What logical corruptions or side effects are introduced when you use DBMS_REPAIR to make an object usable? Can these be addressed? What is the effort required to do so?

It is possible that you do not have access to rows in blocks marked corrupt. However, a block could be marked corrupt even though there are still rows that you can validly access.

It is also possible that referential integrity constraints are broken when blocks are marked corrupt. If this occurs, disable and reenable the constraint; any inconsistencies are reported. After fixing all problems, you should be able to

successfully reenable the constraint. Logical corruption can occur when there are triggers defined on the table. For example, if rows are reinserted, should insert triggers be fired or not? You can address these issues only if you understand triggers and their use in your installation.

Free list blocks can become inaccessible. If a corrupt block is at the head or tail of a free list, space management reinitializes the free list. There then can be blocks that should be on a free list, but are not. You can address this by running the REBUILD _FREELISTS procedure.

Indexes and tables are out of sync. You can address this by first executing the DUMP _ORPHAN _KEYS procedure (to obtain information from the keys that might be useful in rebuilding corrupted data). Then issue the ALTER INDEX... REBUILD ONLINE statement to get the table and its indexes back in sync.

  • If repair involves loss of data, can this data be retrieved?

    You can retrieve data from the index when a data block is marked corrupt. The DUMP _ORPHAN _KEYS procedure can help you retrieve this information. Of course, retrieving data in this manner depends on the amount of redundancy between the indexes and the table.

Task 3: Make Objects Usable

In this task DBMS _REPAIR makes the object usable by ignoring corruptions during table and index scans.

Corruption Repair: Using the FIX _CORRUPT _BLOCKS and SKIP _CORRUPT _BLOCKS Procedures

You make a corrupt object usable by establishing an environment that skips corruptions that remain outside the scope of DBMS _REPAIR capabilities.

If corruptions involve a loss of data, such as a bad row in a data block, all such blocks are marked corrupt by the FIX _CORRUPT _BLOCKS procedure. Then, you can run the SKIP _CORRUPT _BLOCKS procedure, which skips blocks marked corrupt for the object. When skip is set, table and index scans skip all blocks marked corrupt. This applies to both media and software corrupt blocks.

Implications when Skipping Corrupt Blocks

If an index and table are out of sync, then a SET TRANSACTION READ ONLY transaction can be inconsistent in situations where one query probes only the index, and then a subsequent query probes both the index and the table. If the table block is marked corrupt, then the two queries return different results, thereby breaking the rules of a read-only transaction. One way to approach this is to not skip corruptions when in a SET TRANSACTION READ ONLY transaction. A similar issue occurs when selecting rows that are chained. Essentially, a query of the same row may or may not access the corruption, thereby producing different results.

Task 4: Repair Corruptions and Rebuild Lost Data

After making an object usable, you can perform the following repair activities.

Recover Data Using the DUMP _ORPHAN _KEYS Procedures
The DUMP _ORPHAN _KEYS procedure reports on index entries that point to rows in corrupt data blocks. All such index entries are inserted into an orphan key table that stores the key and rowid of the corruption.

After the index entry information has been retrieved, you can rebuild the index using the ALTER INDEX ... REBUILD ONLINE statement.

Repair Free Lists Using the REBUILD _FREELISTS Procedure

Use this procedure if free space in segments is being managed using free lists (SEGMENT SPACE MANAGEMENT MANUAL).

When a block marked "corrupt" is found at the head or tail of a free list, the free list is reinitialized and an error is returned. Although this takes the offending block off the free list, it causes you to lose free list access to all blocks that followed the corrupt block.

You can use the REBUILD _FREELISTS procedure to reinitialize the free lists. The object is scanned, and if it is appropriate for a block to be on the free list, it is added to the master free list. Free list groups are handled by distributing blocks in an equitable fashion, one block at a time. Any blocks marked "corrupt" in the object are ignored during the rebuild.

Fix Segment Bitmaps Using the SEGMENT _FIX _STATUS Procedure

Use this procedure if free space in segments is being managed using bitmaps (SEGMENT SPACE MANAGEMENT AUTO).

This procedure either recalculates the state of a bitmap entry based on the current contents of the corresponding block, or you can specify that a bitmap entry be set to a specific value. Usually, the state is recalculated correctly and there is no need to force a setting.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 10g Topics