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>　　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>　　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