ORACLE中修复数据块损坏

1、alert日志报错信息：

Corrupt block relative dba: 0x08f3ff01 (file 35, block 3407617)
Bad check value found during buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x08f3ff01
last change scn: 0x0000.9ec80357 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x03570601
check value in block header: 0xbda9
computed block checksum: 0x66e2
Reread of rdba: 0x08f3ff01 (file 35, block 3407617) found same corrupted data
Mon Apr 15 11:39:05 2013
Corrupt Block Found
         TSN = 37, TSNAME = 0000050346
         RFN = 35, BLK = 3407617, RDBA = 150208257
         OBJN = 142282, OBJD = 142282, OBJECT = R_AGS_E, SUBOBJECT =
         SEGMENT OWNER = 0000050346, SEGMENT TYPE = Table Segment

说明0000050346用户下表R_AGS_E存在坏块。

2、处理方法：
创建一张与R_AGS_E表结构完全一致的表R_AGS_E_N；
重命名R_AGS_E表为R_AGS_E_OLD；
ALTER TABLE R_AGS_E RENAME TO R_AGS_E_OLD;
重命名R_AGS_E_N为R_AGS_E；
ALTER TABLE R_AGS_E_N RENAME TO R_AGS_E;

3、怎样挽救R_AGS_E_OLD中的数据： 主要参考盖国强的《 Oracle中模拟及修复数据块损坏 》
采用exp导出，后imp导入的办法恢复
[oracle@SHLT-PA4-ORACLE ~]$ exp 0000050346/0000050346 file=r_ags_e_old.dmp tables=r_ags_e_old
Export: Release 10.2.0.1.0 - Production on Tue Apr 16 09:12:11 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                    R_AGS_E_OLD
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 35, block # 3407617)
ORA-01110: data file 35: '/home/oracle/oracle10g/oradata/0000050346.dbf'
Export terminated successfully with warnings.
直接exp导出报错；

我们可以设置内部事件，使exp跳过这些损坏的block
SQL> ALTER SYSTEM SET EVENTS='10231 trace name context forever,level 10';
系统已更改。

更改完成后，执行exp导出操作
[oracle@SHLT-PA4-ORACLE ~]$ exp 0000050346/0000050346 file=r_ags_e_old.dmp tables=r_ags_e_old
Export: Release 10.2.0.1.0 - Production on Tue Apr 16 09:30:01 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                    R_AGS_E_OLD    2757906 rows exported
Export terminated successfully without warnings.
运气不错，已挽救这么多数据；

下一步在数据库中删除R_AGS_E_OLD，重启imp导入即可。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20553601/viewspace-1065202/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20553601/viewspace-1065202/