mysql修复坏块_数据文件遇到坏块的处理办法

坏境:

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