DBMS_REPAIR只是把table及index的corruption blocks标识后忽略,在下次查询或操作时不会对corruption blocks操作,不会对corruption block进行数据恢复
The DBMS_REPAIR package contains data corruption repair procedures that enable you to detect and repair corrupt blocks in tables and indexes.
(一)DBMS_REPAIR Security Model
The package is owned by SYS. Execution privilege is not granted to other users.
(二)DBMS_REPAIR Constants
The DBMS_REPAIR package defines several enumerated constants that should be used for specifying parameter values. Enumerated constants must be prefixed with the package name. For example, DBMS_REPAIR.TABLE_OBJECT.
Table 131-1 DBMS_REPAIR Parameters with Enumerated Constants
Parameter | Option | Type | Description |
object_type | TABLE_OBJECT INDEX_OBJECT CLUSTER_OBJECT | BINARY_INTEGER | - |
action | CREATE_ACTION DROP_ACTION PURGE_ACTION | BINARY_INTEGER | - |
table_type | REPAIR_TABLE ORPHAN_TABLE | BINARY_INTEGER | - |
flags | SKIP_FLAG NOSKIP_FLAG | BINARY_INTEGER | - |
object_id | ALL_INDEX_ID := 0 | BINARY_INTEGER | Clean up all objects that qualify |
wait_for_lock | LOCK_WAIT := 1 LOCK_NOWAIT := 0 | BINARY_INTEGER | Specifies whether to try getting DML locks on underlying table [[sub]partition] object |
Note: The default table_name will be REPAIR_TABLE when table_type is REPAIR_TABLE, and will be ORPHAN_KEY_TABLE when table_type is ORPHAN_TABLE.
(三)DBMS_REPAIR Operating Notes
使用DBMS_REPAIR前要先创建repair table与orphan keys table
The DBA would create the repair and orphan keys tables once. Subsequent executions of the CHECK_OBJECT Procedure would add rows into the appropriate table indicating the types of errors found.
Repair Table
A repair table provides information about the corruptions that were found by the CHECK_OBJECT procedure and how these will be addressed if the FIX_CORRUPT_BLOCKS procedure is run. Further, it is used to drive the execution of the FIX_CORRUPT_BLOCKS procedure.
Orphan Key Table
Orphan Key Table provides information about index entries that point to corrupt rows. An orphan key table is used when the DUMP_ORPHAN_KEYS 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.
通过ADMIN_TABLES过程管理repair table与orphan key table
ADMIN_TABLES procedure is used to create, purge, or drop a repair table or an orphan key table.
CONNECT / AS SYSDBA;
EXEC DBMS_REPAIR.ADMIN_TABLES('ORPHAN_KEYS_TABLE', DBMS_REPAIR.ORPHAN_TABLE,
DBMS_REPAIR.CREATE_ACTION);
EXEC DBMS_REPAIR.ADMIN_TABLES('REPAIR_TABLE', DBMS_REPAIR.REPAIR_TABLE,
DBMS_REPAIR.CREATE_ACTION);
DESCRIBE ORPHAN_KEYS_TABLE;
DESCRIBE REPAIR_TABLE;
SELECT * FROM ORPHAN_KEYS_TABLE;
SELECT * FROM REPAIR_TABLE;
也可以不使用默认表名
The name of the repair and orphan keys tables can be chosen by the user, with the following restriction: the name of the repair table must begin with the 'REPAIR_' prefix, and the name of the orphan keys table must begin with the 'ORPHAN_' prefix. The following code is also legal:
CONNECT / AS SYSDBA;
EXEC DBMS_REPAIR.ADMIN_TABLES('ORPHAN_FOOBAR', DBMS_REPAIR.ORPHAN_TABLE,
DBMS_REPAIR.CREATE_ACTION);
EXEC DBMS_REPAIR.ADMIN_TABLES('REPAIR_ABCD', DBMS_REPAIR.REPAIR_TABLE,
DBMS_REPAIR.CREATE_ACTION);
DESCRIBE ORPHAN_FOOBAR;
DESCRIBE REPAIR_ABCD;
SELECT * FROM ORPHAN_FOOBAR;
SELECT * FROM REPAIR_ABCD;
(四)DBMS_REPAIR Exceptions
The table in this topic describes the exceptions raised by the DDBMS_REPAIR subprograms.
Table 131-2 DBMS_REPAIR Exceptions
Exception | Description | Action |
942 | Reported by DBMS_REPAIR.ADMIN_TABLES during a DROP_ACTION when the specified table doesn't exist. | - |
955 | Reported by DBMS_REPAIR. CREATE_ACTION when the specified table already exists. | - |
24120 | An invalid parameter was passed to the specified DBMS_REPAIR procedure. | Specify a valid parameter value or use the parameter's default. |
24122 | An incorrect block range was specified. | Specify correct values for the BLOCK_START and BLOCK_END parameters. |
24123 | An attempt was made to use the specified feature, but the feature is not yet implemented. | Do not attempt to use the feature. |
24124 | An invalid ACTION parameter was specified. | Specify CREATE_ACTION, PURGE_ACTION or DROP_ACTION for the ACTION parameter. |
24125 | An attempt was made to fix corrupt blocks on an object that has been dropped or truncated since DBMS_REPAIR.CHECK_OBJECT was run. | Use DBMS_REPAIR.ADMIN_TABLES to purge the repair table and run DBMS_REPAIR.CHECK_OBJECT to determine whether there are any corrupt blocks to be fixed. |
24127 | TABLESPACE parameter specified with an ACTION other than CREATE_ACTION. | Do not specify TABLESPACE when performing actions other than CREATE_ACTION. |
24128 | A partition name was specified for an object that is not partitioned. | Specify a partition name only if the object is partitioned. |
24129 | An attempt was made to pass a table name parameter without the specified prefix. | Pass a valid table name parameter. |
24130 | An attempt was made to specify a repair or orphan table that does not exist. | Specify a valid table name parameter. |
24131 | An attempt was made to specify a repair or orphan table that does not have a correct definition. | Specify a table name that refers to a properly created table. |
24132 | An attempt was made to specify a table name is greater than 30 characters long. | Specify a valid table name parameter. |
(五)Summary of DBMS_REPAIR Subprograms
This table lists the DBMS_REPAIR subprograms and briefly describes them.
Subprogram | Description |
Provides administrative functions for the DBMS_REPAIR package repair and orphan key tables, including create, purge, and drop functions | |
Detects and reports corruptions in a table or index | |
Reports on index entries that point to rows in corrupt data blocks | |
Marks blocks software corrupt that have been previously detected as corrupt by CHECK_OBJECT | |
Performs a manual cleanup of failed or interrupted online index builds or rebuilds | |
Rebuilds an object's freelists | |
Fixes the corrupted state of a bitmap entry | |
Sets whether to ignore blocks marked corrupt during table and index scans or to report ORA-1578 when blocks marked corrupt are encountered |
This procedure provides administrative functions for the DBMS_REPAIR package repair and orphan key tables.
DBMS_REPAIR.ADMIN_TABLES (
table_name IN VARCHAR2,
table_type IN BINARY_INTEGER,
action IN BINARY_INTEGER,
tablespace IN VARCHAR2 DEFAULT NULL);
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 either ORPHAN_TABLE or REPAIR_TABLE.
Action:Indicates what administrative action to perform.
Must be either CREATE_ACTION, PURGE_ACTION, or DROP_ACTION. If the table already exists, and if 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 if 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:Indicates the tablespace to use when creating a table.
By default, the SYS default tablespace is used. An error is returned if the tablespace is specified and if the action is not CREATE_ACTION.
- CHECK_OBJECT Procedure
This 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.
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);
object_type:Type of the object to be processed. This must be either TABLE_OBJECT (default) or INDEX_OBJECT.
Flags:Reserved for future use.
relative_fno:Relative file number: Used when specifying a block range.
block_start: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: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:Number of corruptions reported.
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);
Flags:Reserved for future use.
key_count:Number of index entries processed.
- FIX_CORRUPT_BLOCKS Procedure
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);
- ONLINE_INDEX_CLEAN Function
This function 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.
This function returns TRUE if all indexes specified were cleaned up and FALSE if one or more indexes could not be cleaned up.
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;
object_id:Object id of index to be cleaned up. The default cleans up all object ids that qualify.
wait_for_lock:This parameter specifies whether to try getting DML locks on underlying table [[sub]partition] object. The default retries up to an internal retry limit, after which the lock get will give up. If LOCK_NOWAIT is specified, then the lock get does not retry.
This procedure 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.
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);
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.
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,);
file_number:(optional) The tablespace-relative file number of the data block whose status has to be fixed. If omitted, all the blocks in the segment will be checked for state correctness and fixed.
block_number:(optional) The file-relative block number of the data block whose status has to be fixed. If omitted, all the blocks in the segment will be checked for state correctness and fixed.
status_value:(optional) The value to which the block status described by the file_number and block_number will be set. If omitted, the status will be set based on the current state of the block. This is almost always the case, but if there is a bug in the calculation algorithm, the value can be set manually. Status values:
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
The status for bitmap blocks, segment headers, and extent map blocks cannot be altered. The status for blocks in a fixed hash area cannot be altered. For index blocks, there are only two possible states: 1 = block is full and 3 = block has free space.
- SKIP_CORRUPT_BLOCKS Procedure
This procedure 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.
Note: When Oracle performs an index range scan on a corrupt index after DBMS_REPAIR.SKIP_CORRUPT_BLOCKS has been set for the base table, corrupt branch blocks and root blocks are not skipped. Only corrupt non-root leaf blocks are skipped.
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);
object_type:Type of the object to be processed. This must be either TABLE_OBJECT (default) or CLUSTER_OBJECT.
Flags:If SKIP_FLAG is specified, then it turns on the skip of software corrupt blocks for the object during index and table scans. If NOSKIP_FLAG is specified, then scans that encounter software corrupt blocks return an ORA-1578.
(六)DBMS_REPAIR Examples
1. Examples:Building a Repair Table or Orphan Key Table
The following example creates a repair table for the users tablespace.
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
/
For each repair or orphan key table, a view is also created that eliminates any rows that pertain to objects that no longer exist. The name of the view corresponds to the name of the repair or orphan key table and is prefixed by DBA_ (for example, DBA_REPAIR_TABLE or DBA_ORPHAN_KEY_TABLE).
This example illustrates the creation of an orphan key table for the users tablespace.
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'ORPHAN_KEY_TABLE',
TABLE_TYPE => dbms_repair.orphan_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
/
查看表结构
DESC REPAIR_TABLE
DESC ORPHAN_KEY_TABLE
An example illustrates detecting corruption with the CHECK_OBJECT procedure.You can optionally specify a range, partition name, or subpartition name when you want to check a portion of an object.
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 skipped.
The following is an example of executing the CHECK_OBJECT procedure for the scott.dept table.
SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'DEPT',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/
SQL*Plus outputs the following line, indicating one corruption:
number corrupt: 1
Querying the repair table produces information describing the corruption and suggesting a repair action.
SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
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
- Example: Fixing Corrupt Blocks
此步只是标识出corruption blocks
Use the FIX_CORRUPT_BLOCKS procedure to fix the corrupt blocks in specified objects based on information in the repair table that was generated by the CHECK_OBJECT procedure.
Before changing a block, the block is checked to ensure that the block is still corrupt.
Corrupt blocks are repaired by marking the block software corrupt. When a repair is performed, the associated row in the repair table is updated with a timestamp.
This example fixes the corrupt block in table scott.dept that was reported by the CHECK_OBJECT procedure.
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 following line:
num fix: 1
The following query confirms that the repair was done.
SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT FROM REPAIR_TABLE;
OBJECT_NAME BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
DEPT 3 TRUE
- Example: Finding Index Entries Pointing to Corrupt Data Blocks
An example illustrates finding index entries pointing to corrupt data blocks using the DUMP_ORPHAN_KEYS procedure.
The DUMP_ORPHAN_KEYS procedure reports on index entries that point to rows in corrupt data blocks. For each index entry, a row is inserted into the specified orphan key table. The orphan key table must have been previously created.
This information can be useful for rebuilding lost rows in the table and for diagnostic purposes.
Note: This should be run for every index associated with a table identified in the repair table.
In this example, pk_dept is an index on the scott.dept table. It is scanned to determine if there are any index entries pointing to rows in the corrupt data block.
SET SERVEROUTPUT ON
DECLARE num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'PK_DEPT',
OBJECT_TYPE => dbms_repair.index_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',
KEY_COUNT => num_orphans);
DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
END;
/
The following output indicates that there are three orphan keys:
orphan key count: 3
Index entries in the orphan key table implies that the index should be rebuilt. This guarantees that a table probe and an index probe return the same result set.
An example illustrates skipping corrupt blocks using the SKIP_CORRUPT_BLOCKS procedure.
The SKIP_CORRUPT_BLOCKS procedure enables or disables the skipping of corrupt blocks during index and table scans of the specified object. When the object is a table, skipping 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.
The following example enables the skipping of software corrupt blocks for the scott.dept table:
BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'DEPT',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.skip_flag);
END;
/
Querying scott's tables using the DBA_TABLES view shows that SKIP_CORRUPT is enabled for table scott.dept.
SELECT OWNER, TABLE_NAME, SKIP_CORRUPT FROM DBA_TABLES WHERE OWNER = 'SCOTT';
OWNER TABLE_NAME SKIP_COR
------------------------------ ------------------------------ --------
SCOTT ACCOUNT DISABLED
SCOTT BONUS DISABLED
SCOTT DEPT ENABLED
SCOTT DOCINDEX DISABLED
SCOTT EMP DISABLED
SCOTT RECEIPT DISABLED
SCOTT SALGRADE DISABLED
SCOTT SCOTT_EMP DISABLED
SCOTT SYS_IOT_OVER_12255 DISABLED
SCOTT WORK_AREA DISABLED
10 rows selected.