http://blog.chinaunix.net/uid-77311-id-3051382.html
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'
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;
-- 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;
-- 使用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博客 ” ，链接：https://blog.itpub.net/29289867/viewspace-1978930/ ，如需转载，请注明出处，否则将追究法律责任。
