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/，如需轉載，請註明出處，否則將追究法律責任。