如何使用dbms_repair包标记坏块

参考博文：http://www.cnblogs.com/myrunning/p/4525917.html

--利用dbms_repair包必须先创建repair table两个表：

SQL> begin dbms_repair.admin_tables( table_name=>'REPAIR_TABLE', table_type=>dbms_repair.repair_table, action=>dbms_repair.create_action, tablespace=>'LIVAN_TBS');    end; /PL/SQL procedure successfully completed.SQL>  col object_name for a20SQL> select owner,object_name,object_type from dba_objects where object_name like '%REPAIR_TABLE%';OWNER                          OBJECT_NAME          OBJECT_TYPE------------------------------ -------------------- -------------------SYS                            REPAIR_TABLE         TABLESYS                            DBA_REPAIR_TABLE     VIEW

----再创建orphan key table

SQL> begin dbms_repair.admin_tables( table_type=>dbms_repair.orphan_table, action=>dbms_repair.create_action, tablespace=>'LIVAN_TBS');    end; /PL/SQL procedure successfully completed.SQL> select owner,object_name,object_type from dba_objects where object_name like '%ORPHAN_KEY_TABLE%';OWNER                          OBJECT_NAME          OBJECT_TYPE------------------------------ -------------------- -------------------SYS                            ORPHAN_KEY_TABLE     TABLESYS                            DBA_ORPHAN_KEY_TABLE VIEW

--检查对象，检查结果会放到我们之前创建的repair_table中

SQL> set serveroutput onSQL> declare rpr_count int; begin rpr_count:=0; dbms_repair.check_object( schema_name=>'LIVAN', object_name=>'TEST', repair_table_name=>'REPAIR_TABLE', corrupt_count=>rpr_count);   dbms_output.put_line('repair count:'||to_char(rpr_count)); end; /repair count:1PL/SQL procedure successfully completed.

--检查校验的坏块结果

SQL> select object_name,block_id,corrupt_type,marked_corrupt, corrupt_description,repair_description from repair_table;OBJECT_NAM   BLOCK_ID CORRUPT_TYPE MARKED_COR CORRUPT_DESCRIP REPAIR_DESCRIPTION---------- ---------- ------------ ---------- --------------- ------------------------------TEST               76         6148 TRUE                       mark block software corrupt

我们知道当marked_corrupt为TRUE时，标识这个数据块已经被标识过software corrupt

---标识坏块为software corrupt（重新演示一下）

SQL> declare fix_count int; begin fix_count:=0; dbms_repair.fix_corrupt_blocks( schema_name=>'LIVAN', object_name=>'TEST', object_type=>dbms_repair.table_object, repair_table_name=>'REPAIR_TABLE', fix_count=>fix_count); dbms_output.put_line('fix count:'||to_char(fix_count)); end; /fix count:0PL/SQL procedure successfully completed.

--再次检查，因为已经被标志为software corrupt，所以在此标志也没什么变化

SQL> select object_name,block_id,corrupt_type,marked_corrupt, corrupt_description,repair_description from repair_table;OBJECT_NAM   BLOCK_ID CORRUPT_TYPE MARKED_COR CORRUPT_DESCRIP REPAIR_DESCRIPTION---------- ---------- ------------ ---------- --------------- ------------------------------TEST               76         6148 TRUE                       mark block software corrupt

未被标志为oftware corrupt ，marked_corrupt列会显示FALSE

--查看表有多少索引

SQL> select index_name from dba_indexes  2  where table_name in (select distinct object_name from repair_table);INDEX_NAME------------------------------INDEX_TEST

--检查有多少索引数据

SQL> set serveroutput onSQL> declare key_count int; begin key_count:=0; dbms_repair.dump_orphan_keys( schema_name=>'LIVAN', object_name=>'INDEX_TEST', object_type=>dbms_repair.index_object, repair_table_name=>'REPAIR_TABLE', orphan_table_name=>'ORPHAN_KEY_TABLE', key_count=>key_count); dbms_output.put_line('orphan key count:'||to_char(key_count)); end; /orphan key count:146PL/SQL procedure successfully completed.SQL> select index_name,count(*) from orphan_key_table group by index_name;INDEX_NAME                       COUNT(*)------------------------------ ----------INDEX_TEST                            146

--使用dbms_repair.skip_corrupt_blocks或10231事件方式跳过坏块

SQL> select count(*) from livan.test;select count(*) from livan.test                           *ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 6, block # 76)ORA-01110: data file 6: '/u02/app/oradata/PSDB/livan_tbs01.dbf'SQL> begin dbms_repair.skip_corrupt_blocks( schema_name=>'LIVAN', object_name=>'TEST', object_type=>dbms_repair.table_object, flags=>dbms_repair.skip_flag); end; /PL/SQL procedure successfully completed.SQL> select count(*) from livan.test;  COUNT(*)----------

可以看到当执行完dbms_repair.skip_corrupt_blocks数据可以正常访问了，只是统计出来的数据比原先
少了146条(50604-50458),也就是我们坏块上的数据没有统计，被跳过了。

--使用10231事件跳过

SQL> select count(*) from livan.test;  COUNT(*)----------SQL> begin dbms_repair.skip_corrupt_blocks( schema_name=>'LIVAN', object_name=>'TEST', object_type=>dbms_repair.table_object, flags=>dbms_repair.noskip_flag); end; /PL/SQL procedure successfully completed.SQL> select count(*) from livan.test;select count(*) from livan.test                           *ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 6, block # 76)ORA-01110: data file 6: '/u02/app/oradata/PSDB/livan_tbs01.dbf'SQL> alter session set events '10231 trace name context forever,level 10'; Session altered.SQL> select count(*) from livan.test;  COUNT(*)----------          SQL> alter session set events '10231 trace name context off';Session altered.

--使用CTAS方式重建表及索引

SQL> create table test_bak as select * from test;Table created.SQL> create index idx_test_bak on test_bak(object_id);Index created.--重建索引语句SQL> alter index index_test rebuild online;Index altered.

--使用重建对象的freelists方式修复原表

使用这种方式防止坏块以后被加入到freelists中 注意这个方法只适用于段空间手动管理的表空间(SEGMENT SPACE MANAGEMENT MANUAL), 否则会报ORA-10614: Operation not allowed on this segment 错误

SQL> begin dbms_repair.rebuild_freelists( schema_name=>'LIVAN', object_name=>'TEST', object_type=>dbms_repair.table_object); end; /begin*ERROR at line 1:ORA-10614: Operation not allowed on this segmentORA-06512: at "SYS.DBMS_REPAIR", line 401ORA-06512: at line 2