Shulou(Shulou.com)06/01 Report--

Data Block recovery based on RMAN

Trial Rman can achieve block-level data recovery, in the traditional recovery methods, that is, a data block of a data file is damaged, resulting in the whole data file can not be tried.

At this point, the entire data file must be restored by backup. Obviously, this method will take a long time, but RMAN can achieve block-level recovery. If the data of a data file is corrupted, it can be done through a full backup of the data file.

Recover the data block.

Case study:

The database is a single instance ORACLE database with a total size of 700G.

The storage device uses Huawei storage, and the backup device uses Seagate 3T's mobile hard drive. The database has no DG, no OGG. The backup strategy is to make a full library backup at 0: 00 every Saturday, and a level 2 differential backup at 0: 00 every day for the rest of the 0: 00 differential backup on Wednesday. The backup size of the whole library is about 500g.

2.2 failure situation

The cause of this failure is that the INSPUROA user reported an error when querying the EDOC_BASE_ world table. It is suggested that the fault bad block is datafile 5. The error message is fetched to the alter log as follows:

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

Analyze the reasons

Observation storage, no error warning, preliminary suspicion of logic bad block

Perform repair

According to the error message

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

Make sure that the data file datafile 5reoa01.dbf has bad block phenomenon.

View bad block information:

SQL > select * from v$database_block_corruption

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

--

5 2969045 1 0 CORRUPT

Determine that the bad block is 2969045.

Check whether the backup log (incremental, full) is a full backup

Check that backup datafile 5 is complete

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

Perform repair

Use the RMAN tool

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

After repair, check the alter log.

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

Query fault block information:

SQL > select * from v$database_block_corruption

No rows selected

Query fault table information:

SQL > select count (1) from INSPUROA.EDOC_BASE_WORKFLOW

COUNT (1)

-

1393635

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.