dbms_repair

dbms_repair is a utility that can detect and repair block corruption within Oracle. It is provided by Oracle as part of the standard database installation.

Configuring the Environment

Two tables must first be created under the SYS schema before the dbms_repair utility can be used. Fortunately, a procedure in the package itself (admin_tables) creates these tables and eliminates the need to hunt for a script in $ORACLE_HOME/rdbms/admin.

dbms_repair.ADMIN_TABLES (

table_name IN VARCHAR2,

table_type IN BINARY_INTEGER,

action IN BINARY_INTEGER,

tablespace IN VARCHAR2 DEFAULT NULL);

· table_name – The name of the table to be processed, as determined by the action.

· table_type – Either orphan_table or repair_table.

· action – Either create_action, purge_action or drop_action. When create_action is specified, the table will be created in the SYS schema. purge_action deletes all rows in the table that apply to objects that no longer exist. drop_action will drop the table.

· tablespace – The tablespace in which the newly created table will reside. This tablespace must already exist.

The following command will be used to create the two tables needed. The command will be executed twice with different parameters, once for the repair table and once for the orphan table.

begin

dbms_repair.admin_tables(

table_name => 'REPAIR_TEST',

table_type => dbms_repair.repair_table,

action => dbms_repair.create_action,

tablespace => 'SCOTTWORK'

);

end;

begin

dbms_repair.admin_tables(

table_name => 'ORPHAN_TEST',

table_type => dbms_repair.orphan_table,

action => dbms_repair.create_action,

tablespace => 'SCOTTWORK'

);

end;

The two tables are now created. A describe of the two tables reveals the following:

SQL> desc repair_test;

Name Null? Type

----------------------------------------- -------- ---------------

OBJECT_ID NOT NULL NUMBER

TABLESPACE_ID NOT NULL NUMBER

RELATIVE_FILE_ID NOT NULL NUMBER

BLOCK_ID NOT NULL NUMBER

CORRUPT_TYPE NOT NULL 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

SQL> desc orphan_test

Name Null? Type

----------------------------------------- -------- ---------------

SCHEMA_NAME NOT NULL VARCHAR2(30)

INDEX_NAME NOT NULL VARCHAR2(30)

IPART_NAME VARCHAR2(30)

INDEX_ID NOT NULL NUMBER

TABLE_NAME NOT 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

Repair tables will contain those objects that have corrupted blocks. Orphan tables, on the other hand, are used to contain indexes that point to corrupted data

Finding Corrupt Blocks

The dbms_repair utility provides a mechanism to search for corrupt database blocks.

Below is the syntax for the check_objects procedure. Note that the only OUT parameter is the corrupt_count.

dbms_repair.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);

· schema_name – Schema name of the object to be checked for corruption.

· object_name – Name of the table or index that will be checked for corruption.

· partition_name – Partition or sub-partition name to be checked.

· object_type – Either TABLE_OBJECT or INDEX_OBJECT as specified as an enumeration (dbms_repair.table_object).

· repair_table_name – The name of the repair table to be populated in the SYS schema.

· flags – Not used.

· relative_fno – The relative file number to be used when specifying a block range to be checked.

· block_start – The first block in the block range to begin checking.

· block_end – The last block in the block range to check.

· corrupt_count – The number of corrupt blocks discovered.

The code below will check the scott.employee table for corruption and report the number of corrupted blocks.

< dbms_repair.sql

set serveroutput on

declare corr_count binary_integer;

begin

corr_count := 0;

dbms_repair.CHECK_OBJECT (

schema_name => 'SCOTT',

object_name => 'EMPLOYEE',

partition_name => null,

object_type => dbms_repair.table_object,

repair_table_name => 'REPAIR_TEST',

flags => null,

relative_fno => null,

block_start => null,

block_end => null,

corrupt_count => corr_count

);

dbms_output.put_line(to_char(corr_count));

end;

# Corrupt Blocks =0

PL/SQL procedure successfully completed.

Once executed, the table repair_test can be queried in order to find out more about corrupt blocks. In this case, no rows exist in the table. The repair table is only populated if the check_object procedure did indeed find corrupt blocks, so no rows in this table is good news!

Repairing Corrupt Blocks

The dbms_repair utility provides a mechanism to repair the corrupt database blocks, the fix_corrupt_blocks procedure. Corrupt blocks are not really repaired, but instead are simply marked as corrupt.

Below is the syntax for the fix_corrupt_blocks procedure. Note that the only OUT parameter is the fix_count.

dbms_repair.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 BINARY_INTEGER DEFAULT NULL,

fix_count OUT BINARY_INTEGER);

· schema_name - The name of the schema containing the object with corrupt blocks.

· object_name – The name of the object needing repair.

· partition_name – The name of the partition or subpartition to process. If none is specified and the object is partitioned, all partitions will be processed.

· object_type - Either table_object or index_object as specified as an enumeration.

· repair_table_name – The name of the repair table.

· flags – Not used.

· fix_count – The number of blocks fixed. This should equal the same number of corrupt blocks reported.

If the object repaired is a table, then any corresponding index also needs to be fixed. The dump_orphan_keys procedure will indicate if any keys are broken. If they are, the index will need to be rebuilt.

Rebuilding Freelists

The dbms_repair utility provides a mechanism to rebuild the impacted freelists and bitmap entries after fixing block corruption. This procedure recreates the header portion of the datafile, allowing Oracle to use the newly repaired blocks.

Below is the syntax for the rebuild_freelists procedure:

dbms_repair.REBUILD_FREELISTS (

schema_name IN VARCHAR2,

partition_name IN VARCHAR2 DEFAULT NULL,

object_type IN BINARY_INTEGER DEFAULT TABLE_OBJECT);

· schema_name – The name of the schema containing the object whose freelists need rebuilding.

· partition_name – The name of the partition or subpartition whose freelists are to be rebuilt.

· object_type – Either TABLE_OBJECT or INDEX_OBJECT as specified as an enumeration.

dbms_repair provides a new method of addressing ORA-600 errors dealing with block corruption. The utility is very easy to use and very functional. As described earlier, it is one of many potential solutions when resolving block corruption. dbms_repair does basically the same thing as analyze table … validate structure.