oracle 11g跳过坏块,oracle 使用Dbms_Repair跳过坏块

原博文：

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_TABLEDeclare

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包只能标记坏块,但不能真正修复坏块.