2012-11-6 11gR2 "ADMINISTRATOR'S GUIDE" page 699 - 762

 

 

DBMS_REPAIR Procedures

 

Task 1: Detect and Report Corruptions -- 检测坏块

 

Task 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR -- 评估消耗与使用价值
1. What is the extent of the corruption? -- 损坏的范围
2. What other options are available for addressing block corruptions? -- 还有其他方法可以定位问题吗？
3. What logical corruptions or side effects are introduced when you use DBMS_
REPAIR to make an object usable? Can these be addressed? What is the effort
required to do so? -- 在使用DBMS_REPAIR之后是否有副作用？
4. If repair involves loss of data, can this data be retrieved? -- 如果修复意味着有数据的丢失，这些数据能被找回吗？

Task 3: Make Objects Usable -- 使对象有效（就是REPAIR对象）
Corruption Repair: Using the FIX_CORRUPT_BLOCKS and SKIP_CORRUPT_
BLOCKS Procedures
You can make a corrupt object usable by establishing an environment that skips
corruptions that remain outside the scope of DBMS_REPAIR capabilities.
If corruptions involve a loss of data, such as a bad row in a data block, all such blocks
are marked corrupt by the FIX_CORRUPT_BLOCKS procedure. Then you can run the
SKIP_CORRUPT_BLOCKS procedure, which skips blocks that are marked as corrupt.
When the SKIP_FLAG parameter in the procedure is set, table and index scans skip all
blocks marked corrupt. This applies to both media and software corrupt blocks.
其实，很多时候，坏块修复的本质只是把坏块跳过。

 

Task 4: Repair Corruptions and Rebuild Lost Data -- 修复坏块重建丢失的数据
Recover Data Using the DUMP_ORPHAN_KEYS Procedures
The DUMP_ORPHAN_KEYS procedure reports on index entries that point to rows in
corrupt data blocks. All such index entries are inserted into an orphan key table that
stores the key and rowid of the corruption.
After the index entry information has been retrieved, you can rebuild the index using
the ALTER INDEX...REBUILD ONLINE statement.

DBMS_REPAIR Examples -- DBMS_REPAIR 修复坏块的例子

 

Example: Creating a Repair Table -- 创建REPAIR 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;
/

 

Example: Creating an Orphan Key Table -- 创建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;
/

 

Example: Detecting Corruption -- 检测坏块
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
The corrupted block has not yet been marked corrupt, so this is the time to extract any
meaningful data. After the block is marked corrupt, the entire block must be skipped.

Example: Fixing Corrupt Blocks -- 修复坏块
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 -- 坏块被置为TRUE

Example: Finding Index Entries Pointing to Corrupt Data Blocks -- 找出索引指向的坏块
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.
发现有坏块时，此例建议你重建索引。

Example: Skipping Corrupt Blocks -- 跳过坏块
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;
/

 

Creating a New Maintenance Window -- 创建一个新的维护窗口
The following example creates a maintenance window named EARLY_MORNING_
WINDOW. This window runs for one hour daily between 5 a.m. and 6 a.m.
BEGIN
dbms_scheduler.create_window(
window_name => 'EARLY_MORNING_WINDOW',
duration => numtodsinterval(1, 'hour'),
resource_plan => 'DEFAULT_MAINTENANCE_PLAN',
repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0');
dbms_scheduler.add_group_member(
group_name => 'MAINTENANCE_WINDOW_GROUP',
member => 'EARLY_MORNING_WINDOW');
END;
/

Automated Maintenance Tasks Database Dictionary Views -- 与自动维护任务的数据字典相关视图