Chinaunix首页 | 论坛 | 博客
  • 博客访问: 5205324
  • 博文数量: 1004
  • 博客积分: 8199
  • 博客等级: 中将
  • 技术积分: 13042
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-25 20:19
个人简介

脚踏实地、勇往直前!

文章分类

全部博文(1004)

文章存档

2020年(1)

2019年(93)

2018年(208)

2017年(81)

2016年(49)

2015年(50)

2014年(170)

2013年(52)

2012年(177)

2011年(93)

2010年(30)

分类: Oracle

2011-12-30 11:27:38

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


阅读(5532) | 评论(4) | 转发(2) |
给主人留下些什么吧!~~

hxl2011-12-31 16:42:50

1.若索引数据块完好的话,可以通过索引访问找回部分数据
2.听说可以通过dump损坏的数据块获取,我没试过.

hxl2011-12-31 16:37:40

这里的方法是标记损坏的数据快,以后的数据写入数据文件就会跳过损坏的数据块.
但损坏的数据快里的数据如何找回来,目前我也不清楚.

坏坏小丸子2011-12-30 23:07:02

损失的数据怎么办呢?

☆彼岸★花开2011-12-30 21:35:45

格式化之后的数据还能恢复么?