使用dbms_repair修复受损数据块

1、根据在全表扫描中出现的受损数据块的绝对文件号和数据块号的提示确定受损对象：

select segment_name,segment_type,owner,tablespace_name,partition_name

  from dba_extents

  where file_id = &AFN and &BL between block_id and block_id + blocks - 1;

2、使用dbms_repair包创建两个数据表：

   declare

   begin

    dbms_repair.admin_tables(table_name => 'REPAIR_TABLE',

                             table_type => dbms_repair.REPAIR_TABLE,

                             action     => dbms_repair.CREATE_ACTION,

                             tablespace => 'TESTDATA');

   end;

  

   declare

   begin

    dbms_repair.admin_tables(table_type => dbms_repair.ORPHAN_TABLE,

                             action     => dbms_repair.CREATE_ACTION,

                             tablespace => 'TESTDATA');

   end;

3、检查受损对象，并将检查结果存入之前创建的表中：

set serveroutput on;

  declare

    rpr_count int;

  begin

    rpr_count := 0;

    dbms_repair.check_object(schema_name       => 'TEST',

                             object_name       => 'B',

                             repair_table_name => 'REPAIR_TABLE',

                             corrupt_count     => rpr_count);

    dbms_output.put_line('repair count:'||to_char(rpr_count));

  end;

4、查看检查结果：

    select t.object_name,

         t.block_id,

         t.corrupt_type,

         t.marked_corrupt,

         t.corrupt_description,

         t.repair_description

from repair_table t;

5、如果受损数据块还没有被标记为“software corrupt”,使用dbms_repair包将受损数据块标记为”software corrupt”：

set serveroutput on;

declare

    fix_count int;

  begin

    fix_count := 0;

    dbms_repair.fix_corrupt_blocks(schema_name       => 'TEST',

                                   object_name       => 'B',

                                   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;

6、查看有多少索引项指向了坏块：

  select index_name
    from dba_indexes
   where table_name in (select distinct object_name from repair_table);

  

 set serveroutput on;

  declare
    key_count int;
  begin
    key_count := 0;
    dbms_repair.dump_orphan_keys(schema_name       => 'TEST',
                                 object_name       => 'TEST_PK',
                                 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;

 

select index_name,count(1) from orphan_key_table group by index_name;

7、 使用dbms_repair包将受损数据块设置为skip，或使用10231事件，使在全表扫描中屏蔽ora-1578错误：

  begin
    dbms_repair.skip_corrupt_blocks(schema_name => 'TEST',
                                    object_name => 'B',
                                    object_type => dbms_repair.TABLE_OBJECT,
                                    flags       => dbms_repair.SKIP_FLAG);
  end;

 

8、  重建表和索引：

使用CTAS方法重建表，或者使用dbms_repair包重建对象的freelists

 declare
 begin
    dbms_repair.rebuild_freelists(schema_name => 'TEST',
                                  object_name => 'B',
                                  object_type => dbms_repair.TABLE_OBJECT);

 end;

使用alter index indexname rebuild online命令重建索引