坏境:
OS:Red Hat Linux AS 4
DB:10GR2
方法1:设置内部事件跳过坏块
步骤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:查询坏块影响的对象
Select 'Block 12 of file '||f.file_name||' :
'||decode(e.owner, NULL, e.segment_name,
e.owner||'.'||e.segment_name)||
decode(e.partition_name, NULL, '', '.'||e.partition_name)||
' ('||e.segment_type||')' obj
from dba_extents e, dba_data_files f
where f.file_id = 5
and e.file_id = 5
and 12 between e.block_id and e.block_id+e.blocks-1;
OBJ
---------
Block 12 of file /u01/app/oracle/oradata/oracl/hxl01.dbf :
HXL.TB_TEST (TABLE)
-- 这里损失的是数据
步骤3:试着导出数据
[oracle@hxl ~]$ exp hxl/hxl file=TB_TEST.dmp tables=TB_TEST
Export: Release 10.2.0.1.0 - Production on Thu Dec 29 23:39:52 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table TB_TEST
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 5, block # 12)
ORA-01110: data file 5: '/u01/app/oracle/oradata/oracl/hxl01.dbf'
Export terminated successfully with warnings.
步骤4:设置内部事件,使exp跳过这些损坏的block
alter system set events='10231 trace name context forever,level 10';
步骤5:再次导出
[oracle@hxl ~]$ exp hxl/hxl file=TB_TEST.dmp tables=TB_TEST
Export: Release 10.2.0.1.0 - Production on Thu Dec 29 23:45:49 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table TB_TEST 1568 rows exported
Export terminated successfully without warnings.
步骤6:这个时候可以重建表后导入数据恢复
imp hxl/hxl TB_TEST.dmp tables=TB_TEST
步骤7:完成恢复后需要关闭跟踪事件
alter system set events '10231 trace name context off';
结束完成恢复.
方法2:使用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包只能标记坏块,但不能真正修复坏块.
