oraclerman检查坏块,OracleRMAN修复逻辑坏块

RMAN 实现数据块恢复

试用Rman可以实现数据块级的数据恢复&＃xff0c;在传统恢复手段中即某个数据文件的一个数据块被损坏&＃xff0c;就造成整个数据文件无法试用&＃xff0c;此时必须通过备份恢复整个数据文件。显然这样的方法会会时间较长&＃xff0c;而RMAN实现块级恢复&＃xff0c;如果某个数据文件的数据损坏&＃xff0c;通过数据文件的完整备份就可以恢复数据块。

案例&＃xff1a;

数据库是一个单实例Oracle数据库&＃xff0c;该库的总大小有700G。

存储设备使用华为存储&＃xff0c;备份设备使用希捷3T的移动硬盘。该数据库无DG无OGG。备份策略为每周六0点全库备份&＃xff0c;周三0点1级差异备份其余时间为每天0点做2级差异备份。全库备份大小为500G左右。

2.2 故障情况

本次故障原因是INSPUROA用户在查询EDOC_BASE_WORKFLOW表出现报错。提示故障坏块为datafile 5。报错信息取至alter日志如下&＃xff1a;

Reading datafile &＃39;/oradata/datafiles/oadb/oa01.dbf&＃39; 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 &＃61; 6, TSNAME &＃61; OA

RFN &＃61; 5, BLK &＃61; 2969045, RDBA &＃61; 23940565

OBJN &＃61; 95690, OBJD &＃61; 95690, OBJECT &＃61; EDOC_BASE_WORKFLOW, SUBOBJECT &＃61;

SEGMENT OWNER &＃61; INSPUROA, SEGMENT TYPE &＃61; 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 &＃39;/oradata/datafiles/oadb/oa01.dbf&＃39; 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 &＃61; 6, TSNAME &＃61; OA

RFN &＃61; 5, BLK &＃61; 2969045, RDBA &＃61; 23940565

OBJN &＃61; 95690, OBJD &＃61; 95690, OBJECT &＃61; EDOC_BASE_WORKFLOW, SUBOBJECT &＃61;

SEGMENT OWNER &＃61; INSPUROA, SEGMENT TYPE &＃61; 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

分析原因

观察存储&＃xff0c;无报错警告&＃xff0c;初步怀疑逻辑坏块

执行修复

根据报错信息

Reading datafile &＃39;/oradata/datafiles/oadb/oa01.dbf&＃39; 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 &＃61; 6, TSNAME &＃61; OA

RFN &＃61; 5, BLK &＃61; 2969045, RDBA &＃61; 23940565

OBJN &＃61; 95690, OBJD &＃61; 95690, OBJECT &＃61; EDOC_BASE_WORKFLOW, SUBOBJECT &＃61;

SEGMENT OWNER &＃61; INSPUROA, SEGMENT TYPE &＃61; Table Segment

确定数据文件 datafile 5&＃xff0c;oa01.dbf出现坏块现象

查看坏块信息&＃xff1a;

SQL> select * from v$database_block_corruption;

FILE#    BLOCK#    BLOCKS CORRUPTION_CHANGE# CORRUPTIO

---------- ---------- ---------- ------------------ ---------

5    2969045          1                  0 CORRUPT

确定坏块为2969045号

检查备份日志(增量&＃xff0c;全量)是否完整备份

检查备份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&＃61;982 device type&＃61;DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number&＃61;00005 name&＃61;/oradata/datafiles/oadb/oa01.dbf

channel ORA_DISK_1: backup set complete, elapsed time: 00:05:35

List of Datafiles

&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;

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&＃61;/orabak_m/oadb/20150815_fullbkdb_ssqek9d1_1_1.bak tag&＃61;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&＃61;/orabak_m/oadb/20150815_fullbkdb_ssqek9d1_2_1.bak tag&＃61;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&＃61;/orabak_m/oadb/20150815_fullbkdb_ssqek9d1_3_1.bak tag&＃61;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&＃61;/orabak_m/oadb/20150815_fullbkdb_ssqek9d1_4_1.bak tag&＃61;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&＃61;1 sequence&＃61;8318

channel ORA_DISK_1: reading from backup piece /orabak_m/oadb/20150815_fullbkarc_t2qekhpb_1_1.bak

channel ORA_DISK_1: piece handle&＃61;/orabak_m/oadb/20150815_fullbkarc_t2qekhpb_1_1.bak tag&＃61;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&＃61;1 sequence&＃61;8319

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;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&＃61;/orabak_m/oadb/20150816_inc_2bkair_t9qemurm_1_1.bak tag&＃61;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&＃61;1 sequence&＃61;8321

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;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&＃61;/orabak_m/oadb/20150816_inc_2bkair_taqemurm_1_1.bak tag&＃61;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&＃61;1 sequence&＃61;8323

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;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&＃61;/orabak_m/oadb/20150817_inc_2bkair_thqepj8n_1_1.bak tag&＃61;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&＃61;1 sequence&＃61;8325

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;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&＃61;/orabak_m/oadb/20150817_inc_2bkair_tiqepj8n_1_1.bak tag&＃61;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&＃61;1 sequence&＃61;8327

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;8328

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;8329

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;8330

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;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&＃61;/orabak_m/oadb/20150818_inc_2bkair_tpqes7ml_1_1.bak tag&＃61;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&＃61;1 sequence&＃61;8332

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;8333

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;8334

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;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&＃61;/orabak_m/oadb/20150818_inc_2bkair_tqqes7ml_1_1.bak tag&＃61;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&＃61;1 sequence&＃61;8336

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;8337

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;8338

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;8339

channel ORA_DISK_1: restoring archived log

archived log thread&＃61;1 sequence&＃61;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&＃61;/orabak_m/oadb/20150818_inc_2bkair_trqes7ml_1_1.bak tag&＃61;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

查询故障块信息&＃xff1a;

SQL> select * from v$database_block_corruption;

no rows selected

查询故障表信息&＃xff1a;

SQL> select count(1) from INSPUROA.EDOC_BASE_WORKFLOW;

COUNT(1)

----------

1393635

--------------------------------------推荐阅读 --------------------------------------

--------------------------------------分割线 --------------------------------------