Oracle RMAN修複邏輯壞塊

Oracle RMAN修複邏輯壞塊

RMAN 實現資料區塊恢複
試用Rman可以實現資料區塊級的資料恢複，在傳統恢複手段中即某個資料檔案的一個資料區塊被損壞，就造成整個資料檔案無法試用，此時必須通過備份恢複整個資料檔案。顯然這樣的方法會會時間較長，而RMAN實現塊級恢複，如果某個資料檔案的資料損毀，通過資料檔案的完整備份就可以恢複資料區塊。

案例：
資料庫是一個單一實例Oracle資料庫，該庫的總大小有700G。
存放裝置使用華為儲存，備份裝置使用希捷3T的移動硬碟。該資料庫無DG無OGG。備份策略為每周六0點全庫備份，周三0點1級差異備份其餘時間為每天0點做2級差異備份。全庫備份大小為500G左右。
2.2 故障情況
本次故障原因是INSPUROA使用者在查詢EDOC_BASE_WORKFLOW表出現報錯。提示故障壞塊為datafile 5。報錯資訊取至alter日誌如下：

Reading datafile '/oradata/datafiles/oadb/oa01.dbf' for corruption at rdba: 0x016d4dd5 (file 5, block 2969045)
Reread (file 5, block 2969045) found same corrupt data (no logical check)
Tue Aug 18 10:53:51 2015
Corrupt Block Found
        TSN = 6, TSNAME = OA
        RFN = 5, BLK = 2969045, RDBA = 23940565
        OBJN = 95690, OBJD = 95690, OBJECT = EDOC_BASE_WORKFLOW, SUBOBJECT =
        SEGMENT OWNER = INSPUROA, SEGMENT TYPE = Table Segment
Tue Aug 18 10:55:03 2015
Hex dump of (file 5, block 2969045) in trace file /u01/app/oracle/diag/rdbms/oadb/oadb/trace/oadb_ora_4565.trc
Corrupt block relative dba: 0x016d4dd5 (file 5, block 2969045)
Bad header found during buffer read
Data in bad block:
 type: 117 format: 0 rdba: 0x20206b73
 last change scn: 0x2020.20202020 seq: 0x20 flg: 0x20
 spare1: 0x64 spare2: 0x69 spare3: 0x0
 consistency value in tail: 0x4d240601
 check value in block header: 0x5f49
 block checksum disabled
Reading datafile '/oradata/datafiles/oadb/oa01.dbf' for corruption at rdba: 0x016d4dd5 (file 5, block 2969045)
Reread (file 5, block 2969045) found same corrupt data (no logical check)
Tue Aug 18 10:55:03 2015
Corrupt Block Found
        TSN = 6, TSNAME = OA
        RFN = 5, BLK = 2969045, RDBA = 23940565
        OBJN = 95690, OBJD = 95690, OBJECT = EDOC_BASE_WORKFLOW, SUBOBJECT =
        SEGMENT OWNER = INSPUROA, SEGMENT TYPE = Table Segment
Tue Aug 18 10:57:29 2015
Hex dump of (file 5, block 2969045) in trace file /u01/app/oracle/diag/rdbms/oadb/oadb/trace/oadb_ora_21708.trc
Corrupt block relative dba: 0x016d4dd5 (file 5, block 2969045)
Bad header found during buffer read
Data in bad block:
 type: 117 format: 0 rdba: 0x20206b73
 last change scn: 0x2020.20202020 seq: 0x20 flg: 0x20
 spare1: 0x64 spare2: 0x69 spare3: 0x0
 consistency value in tail: 0x4d240601
 check value in block header: 0x5f49
 block checksum disabled

分析原因
觀察儲存，無報錯警告，初步懷疑邏輯壞塊
執行修複
  根據報錯資訊
Reading datafile '/oradata/datafiles/oadb/oa01.dbf' for corruption at rdba: 0x016d4dd5 (file 5, block 2969045)
Reread (file 5, block 2969045) found same corrupt data (no logical check)
Corrupt Block Found
        TSN = 6, TSNAME = OA
        RFN = 5, BLK = 2969045, RDBA = 23940565
        OBJN = 95690, OBJD = 95690, OBJECT = EDOC_BASE_WORKFLOW, SUBOBJECT =
        SEGMENT OWNER = INSPUROA, SEGMENT TYPE = Table Segment

確定資料檔案 datafile 5，oa01.dbf出現壞塊現象
查看壞塊資訊：
SQL> select * from v$database_block_corruption;

    FILE#    BLOCK#    BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
        5    2969045          1                  0 CORRUPT

確定壞塊為2969045號
檢查備份日誌（增量，全量）是否完整備份
檢查備份datafile 5 是否完整

RMAN> backup validate datafile 5;

Starting backup at 18-AUG-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=982 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oradata/datafiles/oadb/oa01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1840        4190720        9484751217293
  File Name: /oradata/datafiles/oadb/oa01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data      0              2842014       
  Index      0              182983         
  Other      1              1163883       

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/oadb/oadb/trace/oadb_ora_13513.trc for details
Finished backup at 18-AUG-15

執行修複
使用RMAN工具
RMAN> blockrecover datafile 5 block 2969045;

Starting recover at 18-AUG-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /orabak_m/oadb/20150815_fullbkdb_ssqek9d1_1_1.bak
channel ORA_DISK_1: piece handle=/orabak_m/oadb/20150815_fullbkdb_ssqek9d1_1_1.bak tag=TAG20150815T000448
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: reading from backup piece /orabak_m/oadb/20150815_fullbkdb_ssqek9d1_2_1.bak
channel ORA_DISK_1: piece handle=/orabak_m/oadb/20150815_fullbkdb_ssqek9d1_2_1.bak tag=TAG20150815T000448
channel ORA_DISK_1: restored block(s) from backup piece 2
channel ORA_DISK_1: reading from backup piece /orabak_m/oadb/20150815_fullbkdb_ssqek9d1_3_1.bak
channel ORA_DISK_1: piece handle=/orabak_m/oadb/20150815_fullbkdb_ssqek9d1_3_1.bak tag=TAG20150815T000448
channel ORA_DISK_1: restored block(s) from backup piece 3
channel ORA_DISK_1: reading from backup piece /orabak_m/oadb/20150815_fullbkdb_ssqek9d1_4_1.bak
channel ORA_DISK_1: piece handle=/orabak_m/oadb/20150815_fullbkdb_ssqek9d1_4_1.bak tag=TAG20150815T000448
channel ORA_DISK_1: restored block(s) from backup piece 4
channel ORA_DISK_1: block restore complete, elapsed time: 00:29:50

starting media recovery

archived log for thread 1 with sequence 8341 is already on disk as file /orabak/arch/1_8341_802601679.dbf
archived log for thread 1 with sequence 8342 is already on disk as file /orabak/arch/1_8342_802601679.dbf
archived log for thread 1 with sequence 8343 is already on disk as file /orabak/arch/1_8343_802601679.dbf
archived log for thread 1 with sequence 8344 is already on disk as file /orabak/arch/1_8344_802601679.dbf
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8318
channel ORA_DISK_1: reading from backup piece /orabak_m/oadb/20150815_fullbkarc_t2qekhpb_1_1.bak
channel ORA_DISK_1: piece handle=/orabak_m/oadb/20150815_fullbkarc_t2qekhpb_1_1.bak tag=TAG20150815T022754
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8319
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8320
channel ORA_DISK_1: reading from backup piece /orabak_m/oadb/20150816_inc_2bkair_t9qemurm_1_1.bak
channel ORA_DISK_1: piece handle=/orabak_m/oadb/20150816_inc_2bkair_t9qemurm_1_1.bak tag=TAG20150816T002318
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8321
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8322
channel ORA_DISK_1: reading from backup piece /orabak_m/oadb/20150816_inc_2bkair_taqemurm_1_1.bak
channel ORA_DISK_1: piece handle=/orabak_m/oadb/20150816_inc_2bkair_taqemurm_1_1.bak tag=TAG20150816T002318
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8323
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8324
channel ORA_DISK_1: reading from backup piece /orabak_m/oadb/20150817_inc_2bkair_thqepj8n_1_1.bak
channel ORA_DISK_1: piece handle=/orabak_m/oadb/20150817_inc_2bkair_thqepj8n_1_1.bak tag=TAG20150817T002350
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8325
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8326
channel ORA_DISK_1: reading from backup piece /orabak_m/oadb/20150817_inc_2bkair_tiqepj8n_1_1.bak
channel ORA_DISK_1: piece handle=/orabak_m/oadb/20150817_inc_2bkair_tiqepj8n_1_1.bak tag=TAG20150817T002350
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8327
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8328
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8329
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8330
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8331
channel ORA_DISK_1: reading from backup piece /orabak_m/oadb/20150818_inc_2bkair_tpqes7ml_1_1.bak
channel ORA_DISK_1: piece handle=/orabak_m/oadb/20150818_inc_2bkair_tpqes7ml_1_1.bak tag=TAG20150818T002453
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8332
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8333
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8334
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8335
channel ORA_DISK_1: reading from backup piece /orabak_m/oadb/20150818_inc_2bkair_tqqes7ml_1_1.bak
channel ORA_DISK_1: piece handle=/orabak_m/oadb/20150818_inc_2bkair_tqqes7ml_1_1.bak tag=TAG20150818T002453
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8336
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8337
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8338
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8339
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8340
channel ORA_DISK_1: reading from backup piece /orabak_m/oadb/20150818_inc_2bkair_trqes7ml_1_1.bak
channel ORA_DISK_1: piece handle=/orabak_m/oadb/20150818_inc_2bkair_trqes7ml_1_1.bak tag=TAG20150818T002453
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
media recovery complete, elapsed time: 00:00:16
Finished recover at 18-AUG-15

修複完成查看alter日誌情況
Media Recovery Log /orabak/arch/1_8336_802601679.dbf
Tue Aug 18 12:04:29 2015
Media Recovery Log /orabak/arch/1_8336_802601679.dbf (restored)
Media Recovery Log /orabak/arch/1_8337_802601679.dbf
Media Recovery Log /orabak/arch/1_8338_802601679.dbf
Media Recovery Log /orabak/arch/1_8339_802601679.dbf
Media Recovery Log /orabak/arch/1_8340_802601679.dbf
Recovery of Online Redo Log: Thread 1 Group 1 Seq 8341 Reading mem 0
  Mem# 0: /orabak/logfile/redo01a.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 8342 Reading mem 0
  Mem# 0: /orabak/logfile/redo02a.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 8343 Reading mem 0
  Mem# 0: /orabak/logfile/redo03a.log
Recovery of Online Redo Log: Thread 1 Group 4 Seq 8344 Reading mem 0
  Mem# 0: /orabak/logfile/redo04a.log
Recovery of Online Redo Log: Thread 1 Group 5 Seq 8345 Reading mem 0
  Mem# 0: /orabak/logfile/redo05a.log
Completed Block Media Recovery

查詢故障塊資訊：
SQL> select * from v$database_block_corruption;

no rows selected
查詢故障表資訊：
SQL> select count(1) from INSPUROA.EDOC_BASE_WORKFLOW;

  COUNT(1)
----------
  1393635

--------------------------------------推薦閱讀 --------------------------------------

RMAN備份時遭遇ORA-19571 

RMAN 配置歸檔日誌刪除策略

Oracle基礎教程之通過RMAN複製資料庫

RMAN備份策略制定參考內容

RMAN備份學習筆記

OracleDatabase Backup加密 RMAN加密

RMAN備份時遇到ORA-19588 

--------------------------------------分割線 --------------------------------------