如何使用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 a20
SQL> select owner,object_name,object_type
 from dba_objects
 where object_name like '%REPAIR_TABLE%';

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
SYS                            REPAIR_TABLE         TABLE
SYS                            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     TABLE
SYS                            DBA_ORPHAN_KEY_TABLE VIEW

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

SQL> set serveroutput on
SQL> 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:1

PL/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:0

PL/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 on
SQL> 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:146

PL/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 segment
ORA-06512: at "SYS.DBMS_REPAIR", line 401
ORA-06512: at line 2