21 Detecting and Repairing Data Block Corruption

Using ADMIN_TABLES to Build a Repair Ta ble or Orphan Key Table

A repair table provides information about what corruptions were found by the CHECK_OBJEC T procedure and how these will be addressed if the FIX_CORRUPT_BLOCKS procedure is run. Further, it is used to dr ive the execution of the FIX_CORRUPT_BLOCKS procedure.

An orphan key table is used when the DUMP_ORPHAN_KEY S procedure is executed and it discovers index entries that point to corrupt rows. The DUMP_ORPHAN_KEYS procedure populates the orphan key table by logging its activity and providing the index information in a usable manner.

The ADMI N_TABLE procedure is used to create, purge, or drop a repair table or an orphan key table.

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
     TABLE_NAME => 'REPAIR_TABLE',
     TABLE_TYPE => dbms_repair.repair_table,
     ACTION     => dbms_repair.create_action,
     TABLESPACE => 'USERS');
END;

/

DESC REPAIR_TABLE

 Name                         Null?    Type
 ------
---------------------- -------- --------------
 OBJECT_ID                    NOT NULL NUMBER
 TABLESPACE_ID                NOT NULL N
UMBER
 RELATIVE_FILE_ID             NOT NULL NUMBER
 BLOCK_ID                     NOT NULL NUMBER
 CORRUPT_TYPE                 NOT N
ULL NUMBER
 SCHEMA_NAME                  NOT NULL VARCHAR2(30)
 OBJECT_NAME                  NOT NULL VARCHAR2(30)
 BASEOBJECT_NAME
                     VARCHAR2(30)
 PARTITION_NAME                        VARCHAR2(30)
 CORRUPT_DESCRIPTION                   VARCHAR2
(2000)
 REPAIR_DESCRIPTION                    VARCHAR2(200)
 MARKED_CORRUPT               NOT NULL VARCHAR2(10)
 CHECK_TIMESTAMP
         NOT NULL DATE
 FIX_TIMESTAMP                         DATE
 REFORMAT_TIMESTAMP                    DATE

BEGI
N
DBMS_REPAIR.ADMIN_TABLES (
     TABLE_NAME => 'ORPHAN_KEY_TABLE',
     TABLE_TYPE => dbms_repair.orphan_table,
     ACTION
  => dbms_repair.create_action,
     TABLESPACE => 'USERS');
END;
/

DESC ORPHAN_KEY_TABLE

 Name                         Null?    Type
 ------------------------
---- -------- -----------------
 SCHEMA_NAME                  NOT NULL VARCHAR2(30)
 INDEX_NAME                   NOT NULL VARCHAR2(3
0)
 IPART_NAME                            VARCHAR2(30)
 INDEX_ID                     NOT NULL NUMBER
 TABLE_NAME                   NO
T NULL VARCHAR2(30)
 PART_NAME                             VARCHAR2(30)
 TABLE_ID                     NOT NULL NUMBER
 KEYROWID
               NOT NULL ROWID
 KEY                          NOT NULL ROWID
 DUMP_TIMESTAMP               NOT NULL DATE

Using the CHECK_OBJECT Procedure to Detect Corrupt ion

The CHECK_OBJECT procedure checks the specified objects, and populates the repair table with information about corruptions and repair directives. You can optionally specify a range, partition n ame, or subpartition name when you would like to check a portion of an object.

Validation consists of checking all blocks in t he object that have not previously been marked corrupt. For each block, the transaction and data layer portions are checked for self consistency. During CHECK_OBJECT, if a block is encountered that has a corrupt buffer cache header, then that block is s kipped.

Here is an example of executing the CHECK_OBJECT procedure for the scott.dept table.

number corrupt: 1

SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CO
RRUPT,
       CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
     FROM REPAIR_TABLE;

OBJECT_NAME                      BLOCK_ID CORRUPT_TYPE
 MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
-----------------------------------
-------------------------------------------
REPAIR_DESCRIPTION
----------------------------------------------------------------------
--------
DEPT                                    3            1 FALSE
kdbchk: row locked by non-existent transaction
        table=0
  slot=0
        lockid=32   ktbbhitc=1
mark block software corrupt

Fixing Corrupt Blocks with the FIX_CORRUPT_BLOCKS Procedure< /h3>

Use the FIX_CORRUPT_BLOCKS procedure to fix the corrupt blocks in speci fied 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 markin g the block software corrupt. When a repair is performed, the associated row in the repair table is updated with a fix timestamp.

This example fixes the corrupt block in table scott.dept that was reported by the CHECK_OBJECT procedur e.

SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN 
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'SCOTT',
     OBJECT_NAME=> 'DEPT',
     OBJECT_TYPE => dbms_repair.table_object,
     REPAIR_TABLE_NAME =>
; 'REPAIR_TABLE',
     FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
END;
/

SQL*Plus outputs the f
ollowing line:

num fix: 1

To following query confirms that the repair was done.

SELECT OBJ
ECT_NAME, BLOCK_ID, MARKED_CORRUPT
     FROM REPAIR_TABLE;

OBJECT_NAME                      BLOCK_ID MARKED_COR
--------------------
---------- ---------- ----------
DEPT                                    3 TRUE

Finding Index Entries Pointing into Corrupt Data Blocks: DUMP_ORPHAN_KEYS

The DUMP_ORPHAN_KEYS procedure reports on index entries that point to rows in corrupt data blocks. For each s uch index entry encountered, a row is inserted into the specified orphan key table. The orphan key table must have been previously cr eated.

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

In thi s example, pk_dept is an index on the scott.dept table. It is scanned to determine if there are any index e ntries pointing to rows in the corrupt data block.

SET SERVEROUTPUT ON
DECLARE num_orphans INT;
BEGIN
n
um_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
     SCHEMA_NAME => 'SCOTT',
     OBJECT_NAME => 'PK_DEPT',
     OBJECT_TYPE =&
gt; dbms_repair.index_object,
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',
     KEY_C
OUNT => num_orphans);
DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
END;
/

The following line is output, indicating there are three orphan keys:

orphan key count: 3

Index entries in the o rphan key table implies that the index should be rebuilt. This guarantees that a table probe and an index probe return the same resul t set.

Rebuilding Free Lists Using the REBUILD_FREELISTS Procedure

BEGIN
DBMS_REPAIR.REBUI
LD_FREELISTS (
     SCHEMA_NAME => 'SCOTT',
     OBJECT_NAME => 'DEPT',
     OBJECT_TYPE => dbms_repair.table_object);
END;
/