ITPub博客

首页 > 数据库 > Oracle > oracle 使用Dbms_Repair跳过坏块

oracle 使用Dbms_Repair跳过坏块

Oracle 作者:宋祖强 时间:2016-01-19 12:58:46 0 删除 编辑
原博文:
      http://blog.chinaunix.net/uid-77311-id-3051382.html


使用Dbms_Repair跳过坏块
 
步骤1:表tb_test中有坏块(模拟坏块同方法1)
SQL> select count(1) from hxl.tb_test;
select count(1) from hxl.tb_test
                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 12)
ORA-01110: data file 5: '/u01/app/oracle/oradata/oracl/hxl01.dbf'
 
步骤2:创建 REPAIR_TABLE 表
Declare
Begin
  -- create repair table 
  Dbms_Repair.Admin_Tables(Table_Name => 'REPAIR_TABLE',
                           Table_Type => Dbms_Repair.Repair_Table,
                           Action     => Dbms_Repair.Create_Action,
                           Tablespace => 'SYSTEM');
End;

步骤3:创建 ORPHAN_KEY_TABLE
Declare
Begin
  -- Create orphan key table 
  Dbms_Repair.Admin_Tables(Table_Type => Dbms_Repair.Orphan_Table,
                           Action     => Dbms_Repair.Create_Action,
                           Tablespace => 'SYSTEM');
End;

步骤4:找出坏块
执行过程Check_Object后会将关于损坏和修补的指导信息装入Repair Table.
Declare
  Rpr_Count Int;
Begin
  Rpr_Count := 0;
  Dbms_Repair.Check_Object(Schema_Name       => 'HXL',
                           Object_Name       => 'TB_TEST',
                           Repair_Table_Name => 'REPAIR_TABLE',
                           Corrupt_Count     => Rpr_Count);
  Dbms_Output.Put_Line('repair count: ' || To_Char(Rpr_Count));
End;

该过程执行完成后,坏块的信息会加载到repair_table表中.
SQL> select object_id,tablespace_id,relative_file_id,block_id from repair_table;
 OBJECT_ID TABLESPACE_ID RELATIVE_FILE_ID   BLOCK_ID
---------- ------------- ---------------- ----------
     51663             6                5         12

步骤5:修正坏块
FIX_CORRUPT_BLOCKS procedure用来根据repair table中的信息修正指定objects中的坏块.
当这个块被标识为坏了以后,做全表扫描将引起ORA-1578.

Declare
  Fix_Count Int;
Begin
  Fix_Count := 0;
  Dbms_Repair.Fix_Corrupt_Blocks(Schema_Name       => 'HXL',
                                 Object_Name       => 'TB_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;

步骤6:找出坏块中记录的index entries(因为该测试表TB_TEST没有任何索引,该步骤跳过)
-- DUMP_ORPHAN_KEYS将会显示指向数据坏块中记录的index entries 
Declare
  Key_Count Int;
Begin
  Key_Count := 0;
  Dbms_Repair.Dump_Orphan_Keys(Schema_Name       => 'HXL',
                               Object_Name       => 'TB_A_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;
步骤7:跳过坏块
-- 使用DBMS_REPAIR.SKIP_CORRUPT_BLOCKS来跳过坏块
Declare
Begin
  Dbms_Repair.Skip_Corrupt_Blocks(Schema_Name => 'HXL',
                                  Object_Name => 'TB_TEST',
                                  Object_Type => Dbms_Repair.Table_Object,
                                  Flags       => Dbms_Repair.Skip_Flag);
End;

过程执行完成后,可以全扫描该表.
SQL> select count(1) from hxl.tb_test;

  COUNT(1)
----------
      1568

备注:Dbms_Repair包只能标记坏块,但不能真正修复坏块.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29289867/viewspace-1978930/,如需转载,请注明出处,否则将追究法律责任。

下一篇: oracle常用操作
请登录后发表评论 登录
全部评论

注册时间:2014-08-13

  • 博文量
    176
  • 访问量
    277192