19
Detecting and Repairing Data
Block Corruption

Oracle provides different methods for detecting and correcting data block corruption. One method is to drop and re-create an object after the corruption is detected; however, this is not always possible or desirable. If data block corruption is limited to a subset of rows, another option is to rebuild the table by selecting all data except for the corrupt rows.

Yet another way to manage data block corruption is to use the DBMS_REPAIR package. You can use DBMS_REPAIR to detect and repair corrupt blocks in tables and indexes. Using this approach, you can address corruptions where possible, and also continue to use objects while you attempt to rebuild or repair them. DBMS_REPAIR uses the following approach to address corruptions:

• Step 1: Detect and Report Corruptions

• Step 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR

• Step 3: Make Objects Usable

• Step 4: Repair Corruptions and Rebuild Lost Data

  Note: Any corruption that involves the loss of data requires analysis and understanding of how that data fits into the overall database system. Hence, DBMS_REPAIR is not a magic wand--you must still determine whether the repair approach provided by this package is the appropriate tool for each specific corruption problem. Depending on the nature of the repair, you may lose data and logical inconsistencies can be introduced; therefore you need to weigh the gains and losses associated with using DBMS_REPAIR.

DBMS_REPAIR Package Contents

Table 19-1 describes the procedures that make up the DBMS_REPAIR package.

Table 19-1 DBMS_REPAIR Procedures

Procedure Name	Description
check_object	Detects and reports corruptions in a table or index.
fix_corrupt_blocks	Marks blocks (that were previously identified by the check_object procedure) as corrupt.
dump_orphan_keys	Reports index entries that point to rows in corrupt data blocks.
rebuild_freelists	Rebuilds an object's freelists.
skip_corrupt_blocks	When used, ignores blocks marked corrupt during table and index scans. If not used, you get error ORA-1578 when encountering blocks marked corrupt.
admin_tables	Provides administrative functions (create, drop, purge) for DBMS_REPAIR repair and orphan key tables. Note: These tables are always created in the SYS schema.

Step 1: Detect and Report Corruptions

Your first task, before using DBMS_REPAIR, should be the detection and reporting of 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 19-2 describes the different detection methodologies.

Table 19-2 Comparison of Corruption Detection Methods

Detection Method	Description
DBMS_REPAIR	Performs block checking for a specified table, partition or index. Populates a repair table with results.
DB_VERIFY	External command-line utility that performs block checking on an offline database.
ANALYZE	Used with the VALIDATE STRUCTURE option, verifies the integrity of the structure of an index, table or cluster; checks or verifies that your tables and indexes are in sync.
DB_BLOCK_CHECKING	Identifies corrupt blocks before they actually are marked corrupt. Checks are performed when changes are made to a block.

DBMS_REPAIR: Using the check_object and admin_tables Procedures

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 respectively.

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.

See Also: For more information about DB_VERIFY, see Oracle8i Utilities.

ANALYZE: Corruption Reporting

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

See Also: For more information about the ANALYZE statement, see the Oracle8i SQL Reference.

DB_BLOCK_CHECKING (Block Checking Initialization Parameter)

You can set block checking for instances via the DB_BLOCK_CHECKING parameter (the default value is TRUE); this checks data and index blocks whenever they are modified. DB_BLOCK_CHECKING is a dynamic parameter, modifiable by the ALTER SYSTEM SET statement.

Step 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.

A first step is to answer the following questions:

1. 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.

2. What other options are available for addressing block corruptions?

   Assuming the data is available from another source, drop, re-create and re-populate the object. Another option is to issue the CREATE TABLE...AS SELECT statement from the corrupt table to create a new one.

   You can ignore the corruption by excluding corrupt rows from select statements.

   Perform media recovery.

3. What logical corruptions or side effects will be introduced when you use DBMS_REPAIR to make an object usable? Can these be addressed? What is the effort required to do so?

   You may not have access to rows in blocks marked corrupt. However, a block may be marked corrupt even though there are still rows that you can validly access.

   Referential integrity constraints may be broken when blocks are marked corrupt. If this occurs, disable and re-enable the constraint; any inconsistencies will be reported. After fixing all issues, you should be able to successfully re-enable the constraint.

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

   Freelist blocks may be inaccessible. If a corrupt block is at the head or tail of a freelist, space management reinitializes the freelist. There then may be blocks that should be on a freelist, that aren't. You can address this by running the rebuild_freelists procedure.

   Indexes and tables may be 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.

4. 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 procedures 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.

Step 3: Make Objects Usable

In this step 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's 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 will skip 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 may 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 will 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 giving different results.

Step 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 Freelists Using the rebuild_freelists Procedure

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

You can use the rebuild_freelists procedure to reinitialize the freelists. The object is scanned, and if it is appropriate for a block to be on the freelist, it is added to the master freelist. Freelist groups are handled by meting out the blocks in an equitable fashion--a block at a time. Any blocks marked "corrupt" in the object are ignored during the rebuild.

Limitations and Restrictions

DBMS_REPAIR procedures have the following limitations:

• Tables with LOBS, nested tables, and VARRAYS are supported, but the out of line columns are ignored.

• Clusters are supported in the skip_corrupt_blocks and rebuild_freelist procedures, but not in the check_object procedure.

• Index-organized tables and LOB indexes are not supported.

• The dump_orphan_keys procedure does not operate on bitmap indexes or function-based indexes.

• The dump_orphan_keys procedure processes keys that are, at most, 3,950 bytes long.

DBMS_REPAIR Procedures

This sections contains detailed descriptions of the DBMS_REPAIR procedures.

check_object

The check_object procedure checks the specified objects, and populates the repair table with information about corruptions and repair directives. Validation consists of block checking all blocks in the object. You may optionally specify a range, partition name, or subpartition name when you wish to check a portion of an object.

procedure check_object(schema_name IN varchar2,
   object_name IN varchar2,
   partition_name IN varchar2 DEFAULT NULL,
   object_type IN binary_integer  DEFAULT  TABLE_OBJECT,
   repair_table_name IN varchar2 DEFAULT 'REPAIR_TABLE',
   flags  IN binary_integer  DEFAULT NULL,
   relative_fno IN binary_integer  DEFAULT NULL,
   block_start IN binary_integer DEFAULT NULL,
   block_end IN binary_integer DEFAULT NULL,
   corrupt_count OUT binary_integer)

Table 19-3 The check_object Procedure

Argument	Description
schema_name	Schema name of the object to be checked.
object_name	Name of the table or index to be checked.
partition_name (optional)	Partition or subpartition name to be checked. If this is a partitioned object, and partition_name is not specified, then all partitions and subpartitions are checked. If this is a partitioned object, and the specified partition contains subpartitions, then all subpartitions are checked.
object_type (optional)	Type of the object to be processed. Must be either TABLE_OBJECT or INDEX_OBJECT. The default is TABLE_OBJECT.
repair_table_name (optional)	Name of the repair table to be populated. The table must exist in the SYS schema. Use the admin_tables procedure to create a repair table. The default name is 'REPAIR_TABLE'.
flags (optional)	Reserved for future use.
relative_fno (optional)	Relative file number. Used when specifying a block range.
block_start (optional)	The first block to process if specifying a block range. May be specified only if the object is a single table, partition, or subpartition.
block_end (optional)	The last block to process if specifying a block range. May be specified only if the object is a single table, partition, or subpartition. If only one of block_start or block_end is specified, then the other defaults to the first or last block in the file respectively.
corrupt_count	The number of corruptions reported.

fix_corrupt_blocks

Use this procedure to fix the corrupt blocks in specified objects based on information in the repair table that was previously generated by the check_object procedure. Prior to effecting any change to a block, the block is checked to ensure the block is still corrupt. Corrupt blocks are repaired by marking the block software corrupt. When a repair is effected, the associated row in the repair table is updated with a fix timestamp.

procedure fix_corrupt_blocks(
   schema_name IN varchar2,
   object_name IN varchar2,
   partition_name IN varchar2 DEFAULT NULL,
   object_type IN binary_integer DEFAULT TABLE_OBJECT,
   repair_table_name IN varchar2 DEFAULT 'REPAIR_TABLE', 
   flags  IN boolean DEFAULT NULL,
   fix_count OUT binary_integer)

Table 19-4 The fix_corrupt_blocks Procedure

Argument	Description
schema_name	Schema name.
object_name	Name of the object with corrupt blocks to be fixed.
partition_name (optional)	Partition or subpartition name to be processed. If this is a partitioned object, and partition_name is not specified, then all partitions and subpartitions are processed. If this is a partitioned object, and the specified partition contains subpartitions, then all subpartitions are processed.
object_type (optional)	Type of the object to be processed. Must be either TABLE_OBJECT or INDEX_OBJECT. The default is TABLE_OBJECT.
repair_table_name (optional)	Name of the repair table with the repair directives. Must exist in the SYS schema.
flags (optional)	Reserved for future use.
fix_count	The number of blocks fixed.

dump_orphan_keys

Reports on index entries that point to rows in corrupt data blocks. For each such index entry encountered, a row is inserted into the specified orphan table.

If the repair table is specified, then any corrupt blocks associated with the base table are handled in addition to all data blocks that are marked software corrupt. Otherwise, only blocks that are marked corrupt are handled.

This information may be useful for rebuilding lost rows in the table and for diagnostic purposes.

procedure dump_orphan_keys(
   schema_name IN varchar2,
   object_name IN varchar2,
   partition_name IN varchar2 DEFAULT NULL,
   object_type IN binary_integer DEFAULT INDEX_OBJECT,
   repair_table_name IN varchar2 DEFAULT  'REPAIR_TABLE',
   orphan_table_name IN varchar2 DEFAULT 'ORPHAN_KEY_TABLE',
   key_count OUT binary_integer)

Table 19-5 The dump_orphan_keys Procedure

Argument	Description
schema_name	Schema name.
object_name	Object name.
partition_name (optional)	Partition or subpartition name to be processed. If this is a partitioned object, and partition_name is not specified, then all partitions and subpartitions are processed. If this is a partitioned object, and the specified partition contains subpartitions, then all subpartitions are processed.
object_type (optional)	Type of the object to be processed. The default is INDEX_OBJECT.
repair_table_name (optional)	Name of the repair table that has information regarding corrupt blocks in the base table. The specified table must exist in the SYS schema. The admin_tables procedure is used to create the table.
orphan_table_name (optional)	Name of the orphan key table to populate with information regarding each index entry that refers to a row in a corrupt data block. The specified table must exist in the SYS schema. The admin_tables procedure is used to create the table.
key_count	Number of index entries processed.

rebuild_freelists

Rebuilds the freelists for the specified object. All free blocks are placed on the master freelist. All other freelists are zeroed. If the object has multiple freelist groups, then the free blocks are distributed among all freelists, allocating to the different groups in round-robin fashion.

procedure rebuild_freelists(
   schema_name IN varchar2,
   object_name IN varchar2,
   partition_name IN varchar2 DEFAULT  NULL,
   object_type IN binary_integer DEFAULT TABLE_OBJECT);

Table 19-6 The rebuild_freelists Procedure

Argument	Description
schema_name	Schema name.
object_name	Name of the object whose freelists are to be rebuilt.
partition_name (optional)	Partition or subpartition name whose freelists are to be rebuilt. If this is a partitioned object, and partition_name is not specified, then all partitions and subpartitions are processed. If this is a partitioned object, and the specified partition contains subpartitions, then all subpartitions are processed.
object_type (optional)	Type of the object to be processed. Must be either TABLE_OBJECT or INDEX_OBJECT. The default is TABLE_OBJECT.

skip_corrupt_blocks

Enables or disables the skipping of corrupt blocks during index and table scans of the specified object. When the object is a table, skip applies to the table and its indexes. When the object is a cluster, it applies to all of the tables in the cluster, and their respective indexes.

procedure skip_corrupt_blocks(
   schema_name IN varchar2,
   object_name IN varchar2,
   partition_name IN varchar2 DEFAULT NULL,
   object_type IN binary_integer DEFAULT TABLE_OBJECT	,
   flags IN boolean DEFAULT SKIP_FLAG);

Table 19-7 The skip_corrupt_blocks Procedure

Argument	Description
schema_name	Schema name of the object to be processed.
object_name	Name of the object.
partition_name (optional)	Partition or subpartition name to be processed. If this is a partitioned object, and partition_name is not specified, then all partitions and subpartitions are processed. If this is a partitioned object, and the specified partition contains subpartitions, then all subpartitions are processed.
object_type (optional)	Type of the object to be processed. Must be either TABLE_OBJECT or CLUSTER_OBJECT. The default is TABLE_OBJECT.
flags (optional)	If SKIP_FLAG is specified, turns on the skip of software corrupt blocks for the object during index and table scans. If NOSKIP_FLAG is specified, scans that encounter software corrupt blocks return an ORA-1578.

admin_tables

Provides administrative functions for repair and orphan key tables.

procedure admin_tables(
   table_name IN varchar2, 
   table_type IN binary_integer, 
   action IN binary_integer,
   tablespace IN varchar2 DEFAULT NULL);
 

Table 19-8 The admin_tables Procedure

Argument	Description
table_name	Name of the table to be processed. Defaults to 'ORPHAN_KEY_TABLE' or 'REPAIR_TABLE' based on the specified table_type. When specified, the table name must have the appropriate prefix, 'ORPHAN_' or 'REPAIR_'.
table_type	Type of table, must be one of ORPHAN_TABLE or REPAIR_TABLE.
action	Indicates what administrative action to perform. Must be CREATE_ACTION, PURGE_ACTION, or DROP_ACTION. If the table already exists, and CREATE_ACTION is specified, then an error is returned. PURGE_ACTION indicates to delete all rows in the table that are associated with non-existent objects. If the table does not exist, and DROP_ACTION is specified, then an error is returned. When CREATE_ACTION and DROP_ACTION are specified, an associated view named DBA_<table_name> is created and dropped respectively. The view is defined so that rows associated with non-existent objects are eliminated. Created in the SYS schema.
tablespace (optional)	Indicates the tablespace to use when creating a table. By default, SYS's default tablespace is used. An error is returned if the tablespace is specified and the action is not CREATE_ACTION.

DBMS_REPAIR Exceptions

942	repair table doesn't exist
1418	specified index doesn't exist
24120	invalid parameter
24121	can't specify CASCADE_FLAG and a block range
24122	invalid block range
24124	invalid action parameter specified
24126	CASCADE_FLAG specified and object is not a table
24127	tablespace specified and action is not CREATE_ACTION
24128	partition specified for non-partitioned object
24129	invalid orphan key table name - must have 'ORPHAN_' prefix
24129	specified repair table does not start with 'REPAIR_' prefix
24131	repair table has incorrect columns
24132	repair table name is too long

---