Call (800) 766-1884 for Oracle support & training

Configuring the dbms_repair Utility

Oracle Tips by Burleson

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan). The following is an excerpt from the book.

The dbms_repair Utility

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 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!

Oracle Consulting

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books

Oracle® is the registered trademark of Oracle Corporation.