Oracle DBMS_REPAIR
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Data corruption repair procedures that enable detection and repair of corrupt blocks in tables and indexes. It can be used to address corruptions
where possible and continue to use objects while attempting to rebuild or repair them.
AUTHID DEFINER
Constants
Name Data Type Value
Actions
CREATE_ACTION BINARY_INTEGER 1
DROP_ACTION BINARY_INTEGER 3
PURGE_ACTION BINARY_INTEGER 2
Flags
NOSKIP_FLAG BINARY_INTEGER 2
SKIP_FLAG BINARY_INTEGER 1
Lock Wait
LOCK_NOWAIT BINARY_INTEGER 0
LOCK_WAIT BINARY_INTEGER 1
Object ID
ALL_INDEX_ID BINARY_INTEGER 0
Object Types
CLUSTER_OBJECT BINARY_INTEGER 4
INDEX_OBJECT BINARY_INTEGER 2
TABLE_OBJECT BINARY_INTEGER 1
Table Types
ORPHAN_TABLE BINARY_INTEGER 2
REPAIR_TABLE BINARY_INTEGER 1
Dependencies
DBMS_ASSERT DBMS_SQL DBMS_SYS_ERROR
DBMS_REPAIR_LIB    
Documented Yes
Exceptions
Error Code Reason
ORA-00942 Reported by DBMS_REPAIR.ADMIN_TABLES during a DROP_ACTION when the specified table doesn't exist
ORA-00955 Reported by DBMS_REPAIR.CREATE_ACTION but specified table already exists
ORA-24120 Invalid parameter passed
ORA-24122 Incorrect block range was specified
ORA-24123 Feature not yet implemented
ORA-24124 Invalid ACTION parameter was specified
ORA-24125 Object dropped or truncated since DBMS_REPAIR.CHECK_OBJECT was run
ORA-24127 Tablespace parameter specified with an ACTION other than CREATE_ACTION
ORA-24128 Object is not partitioned
ORA-24129 Table name parameter without the specified prefix
ORA-24130 Attempt was made to specify a repair or orphan table that does not exist
ORA-24131 Attempt  to specify and repair or orphan table that does not have a correct definition
ORA-24132 Table names do not exceed 30 characters
First Available 8.1.5
Security Model Owned by SYS with no granted privileges
Source {ORACLE_HOME}/rdbms/admin/dbmsrpr.sql
Subprograms
 
ADMIN_TABLES
Create Orphan Key Table dbms_repair.admin_tables(
table_name IN VARCHAR2 DEFAULT 'GENERATE_DEFAULT_TABLE_NAME',
table_type IN BINARY_INTEGER,
action     IN BINARY_INTEGER,
tablespace IN VARCHAR2 DEFAULT NULL);
conn sys@pdbdev as sysdba

exec dbms_repair.admin_tables('ORPHAN_KEYS_TABLE', dbms_repair.orphan_table, dbms_repair.create_action);

desc orphan_keys_table

-- after demo
exec dbms_repair.admin_tables('ORPHAN_KEYS_TABLE', dbms_repair.orphan_table, dbms_repair.drop_action);
Admin Tables Demo conn sys@pdbdev as sysdba

exec dbms_repair.admin_tables('REPAIR_TABLE', dbms_repair.repair_table, dbms_repair.create_action, 'UWDATA');

desc repair_table

-- after demo
exec dbms_repair.admin_tables('REPAIR_TABLE',1,3);
 
CHECK_OBJECT
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 DBA range, partition name, or subpartition name when you want to check a portion of an object.

Before this demo is run the ADMIN_TABLES procedure must be used to create the repair table.
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);
conn uwclass/uwclass@pdbdev

CREATE TABLE badtab AS
SELECT DISTINCT object_name
FROM all_objects;

conn / as sysdba

set serveroutput on

DECLARE
 i BINARY_INTEGER;
BEGIN
  dbms_repair.check_object(schema_name=>'UWCLASS', object_name=>'SERVERS', corrupt_count=>i);
  dbms_output.put_line(i);
END;
/
 
DUMP_ORPHAN_KEYS
This procedure 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.
dbms_repair.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_KEYS_TABLE',
flags             IN  BINARY_INTEGER DEFAULT NULL,
key_count         OUT BINARY_INTEGER);
set serveroutput on

DECLARE
 x  BINARY_INTEGER;
BEGIN
  dbms_repair.dump_orphan_keys('UWCLASS', 'SERVERS_PK', NULL, 2, 'REPAIR_TABLE', 'ORPHAN_KEYS_TABLE', 1, x);
  dbms_output.put_line(x);
END;
/
 
FIX_CORRUPT_BLOCKS
This procedure fixes 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.
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);
set serveroutput on

DECLARE
 x BINARY_INTEGER;
BEGIN
  dbms_repair.fix_corrupt_blocks('UWCLASS', 'SERVERS', NULL, 1, 'REPAIR_TABLE', 1, x);
  dbms_output.put_line(x);
END;
/
 
ONLINE_INDEX_CLEAN
Performs a manual cleanup of failed or interrupted online index builds or rebuilds.

This action is also performed periodically by SMON, regardless of user-initiated cleanup.
dbms_repair.online_index_clean(
object_id     IN BINARY_INTEGER DEFAULT ALL_INDEX_ID,
wait_for_lock IN BINARY_INTEGER DEFAULT LOCK_WAIT)
RETURN BOOLEAN;
DECLARE
 isClean BOOLEAN;
BEGIN
  isClean := FALSE;
  WHILE isClean=FALSE LOOP
    isClean := dbms_repair.online_index_clean(dbms_repair.all_index_iddbms_repair.lock_wait);
    dbms_lock.sleep(10);
  END LOOP;
END;
/
 
REBUILD_FREELISTS
Rebuilds 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.

This is discussed in Jonathan Lewis' Cost-Based Oracle Fundamentals
ISBN: 1-59059-636-6, pg 101
dbms_repair.rebuild_freelists(
schema_name    IN VARCHAR2,
object_name    IN VARCHAR2,
partition_name IN VARCHAR2 DEFAULT NULL,
object_type    IN BINARY_INTEGER DEFAULT TABLE_OBJECT);
exec dbms_repair.rebuild_freelists('UWCLASS', 'SERVERS', NULL, dbms_repair.table_object);

-- tables in ASSM tablespaces do not have freelists
*
ERROR at line 1:
ORA-10614: Operation not allowed on this segment
ORA-06512: at "SYS.DBMS_REPAIR", line 401
ORA-06512: at line 1
 
REBUILD_SHC_INDEX
Rebuilds a sorted hash cluster index. This procedure will not recreate one that has been dropped as demonstrated dbms_repair.rebuild_shc_index(
segment_owner IN VARCHAR2,
cluster_name  IN VARCHAR2);
conn uwclass/uwclass@pdbdev

CREATE CLUSTER sorted_hc (
program_id  NUMBER(3),
line_id     NUMBER(10) SORT,
delivery_dt DATE SORT)
TABLESPACE uwdata
HASHKEYS 9
SIZE 750
HASH IS program_id;

CREATE TABLE shc_airplane (
program_id  NUMBER(3),
line_id     NUMBER(10) SORT,
delivery_dt DATE SORT,
customer_id VARCHAR2(3),
order_dt    DATE)
CLUSTER sorted_hc (program_id, line_id, delivery_dt);

conn sys@pdbdev as sysdba

exec dbms_repair.rebuild_shc_index('UWCLASS', 'SORTED_HC');
 
REPAIR_CLUSTER_INDEX_KEYCOUN (new 12.1)
Undocumented bu the name seems self-explanatory dbms_repair.repair_cluster_index_keycount(
index_owner IN VARCHAR2,
index_name  IN VARCHAR2,
check_level IN BINARY_INTEGER);
CREATE CLUSTER sc_srvr_id (
srvr_id NUMBER(10))
SIZE 1024;

CREATE INDEX idx_sc_srvr_id ON CLUSTER sc_srvr_id;

exec dbms_repair.repair_cluster_index_keycount(USER, 'IDX_SC_SRVR_ID', 1);
 
SEGMENT_FIX_STATUS
With this procedure you can fix the corrupted state of a bitmap entry. The procedure either recalculates the state based on the current contents of the corresponding block or sets the state to a specific value.

For segments with automatic ASSM, Oracle ignores attempts to change the PCTUSED setting. If you alter the PCTFREE setting, then you must subsequently run the DBMS_REPAIR.SEGMENT_FIX_STATUS procedure to implement the new setting on blocks already allocated to the segment.
dbms_repair.segment_fix_status(
segment_owner  IN VARCHAR2,
segment_name   IN VARCHAR2,
segment_type   IN BINARY_INTEGER DEFAULT TABLE_OBJECT,
file_number    IN BINARY_INTEGER DEFAULT NULL,
block_number   IN BINARY_INTEGER DEFAULT NULL,
status_value   IN BINARY_INTEGER DEFAULT NULL,
partition_name IN VARCHAR2       DEFAULT NULL);

Status Value Description
1 block is full
2 block is 0-25% free
3 block is 25-50% free
4 block is 50-75% free
5 block is 75-100% free
exec dbms_repair.segment_fix_status('UWCLASS', 'SERVERS', dbms_repair.table_object);
 
SKIP_CORRUPT_BLOCKS
Enables or disables skipping 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 and indexes in the cluster. dbms_repair.skip_corrupt_blocks(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
object_type IN BINARY_INTEGER DEFAULT TABLE_OBJECT,
flags       IN BINARY_INTEGER DEFAULT SKIP_FLAG);
exec dbms_repair.skip_corrupt_blocks('UWCLASS','SERVERS', dbms_repair.table_object, dbms_repair.noskip_flag);

Related Topics
DBV
Export
Import
Packages
Tablespaces

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved