使用DBMS_REPAIR修复坏块(二)
)
ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'
4、使用DBMS_REPAIR修复坏块
[sql]
Step a 创建相应的表对象
--使用DBMS_REPAIR.ADMIN_TABLES过程创建一个表对象，用于记录需要被修复的表
sys@USBO> BEGIN
2 DBMS_REPAIR.ADMIN_TABLES (
3 TABLE_NAME => 'REPAIR_TABLE',
4 TABLE_TYPE => dbms_repair.repair_table,
5 ACTION => dbms_repair.create_action,
6 TABLESPACE => 'USERS');
7 END;
8 /
PL/SQL procedure successfully completed.
--使用DBMS_REPAIR.ADMIN_TABLES过程创建一个表对象，用于记录在表块损坏后那些孤立索引，也就是指向坏块的那些索引
sys@USBO> BEGIN
2 DBMS_REPAIR.ADMIN_TABLES
3 (
4 TABLE_NAME => 'ORPHAN_KEY_TABLE',
5 TABLE_TYPE => DBMS_REPAIR.ORPHAN_TABLE,
6 ACTION => DBMS_REPAIR.CREATE_ACTION,
7 TABLESPACE => 'USERS'
8 );
9 END;
10 /
PL/SQL procedure successfully completed.
Step b 校验受损的对象
--使用DBMS_REPAIR.CHECK_OBJECT来检测对象上受损的情形，并返回受损块数
sys@USBO> SET SERVEROUTPUT ON
sys@USBO> DECLARE num_corrupt INT;
2 BEGIN
3 num_corrupt := 0;
4 DBMS_REPAIR.CHECK_OBJECT (
5 SCHEMA_NAME => 'SYS',
6 OBJECT_NAME => 'TB_OBJ',
7 REPAIR_TABLE_NAME => 'REPAIR_TABLE',
8 CORRUPT_COUNT => num_corrupt);
9 DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
10 END;
11 /
number corrupt: 3
PL/SQL procedure successfully completed.
--下面我们可以从repair_table查询到受损的块
--从下面的查询中可以看出列marked_corrupt全部为true，表明我们在CHECK_OBJECT已经标注了坏块
--有一点不太清楚的是为什么块131在查询中被列出3次？
sys@USBO> COLUMN object_name FORMAT a10
sys@USBO> COLUMN repair_description FORMAT a28
sys@USBO> SET LINES 10000
sys@USBO> SELECT object_name, block_id, corrupt_type,marked_corrupt,repair_description FROM repair_table;
OBJECT_NAM BLOCK_ID CORRUPT_TYPE MARKED_COR REPAIR_DESCRIPTION
---------- ---------- ------------ ---------- ----------------------------
TB_OBJ 131 6148 TRUE mark block software corrupt
TB_OBJ 131 6148 TRUE mark block software corrupt
TB_OBJ 131 6148 TRUE mark block software corrupt
TB_OBJ 141 6148 TRUE mark block software corrupt
TB_OBJ 151 6148 TRUE mark block software corrupt
Step c 标记坏块
--过程FIX_CORRUPT_BLOCKS用于标记坏块，在这个演示中，我们在CHECK_OBJECT已经被标注了，如没有执行下面的过程
--由于上一步已经标注，所以下面的输出为0
sys@USBO> SET SERVEROUTPUT ON
sys@USBO> DECLARE num_fix INT;
2 BEGIN
3 num_fix := 0;
4 DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
5 SCHEMA_NAME => 'SYS',
6 OBJECT_NAME=> 'TB_OBJ',
7 OBJECT_TYPE => dbms_repair.table_object,
8 REPAIR_TABLE_NAME => 'REPAIR_TABLE',
9 FIX_COUNT=> num_fix);
10 DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
11 END;
12 /
num fix: 0
PL/SQL procedure successfully completed.
Step d DUMP孤立的索引键值
--使用DUMP_ORPHAN_KEYS过程将那些指向坏块的索引键值填充到ORPHAN_KEY_TABLE
sys@USBO> SET SERVEROUTPUT ON
sys@USBO> DECLARE num_orphans INT;
2 BEGIN
3 num_orphans := 0;
4 DBMS_REPAIR.DUMP_ORPHAN_KEYS (
5 SCHEMA_NAME => 'SYS',
6 OBJECT_NAME => 'I_TB_OBJ',
7 OBJECT_TYPE => dbms_repair.index_object,