Oracle RMAN repairs logical Bad blocks
RMAN implements block recovery
The trial of Rman can achieve block-level data recovery. In the traditional restoration method, that is, a data block of a data file is damaged, the entire data file cannot be used for trial, in this case, the entire data file must be restored through backup. Obviously, this method takes a long time, And RMAN implements block-level recovery. If the data in a data file is corrupted, the data block can be restored through the complete backup of the data file.
Case:
A database is a single-instance Oracle database with a total size of 700 GB.
The storage device uses Huawei storage, and the backup device uses Seagate 3 T mobile hard drive. This database has no DG and no OGG. The backup policy is to back up the entire database at every Saturday. The remaining time of level 1 differential backup on Wednesday is every day for Level 2 Differential backup. The full-Database Backup size is about GB.
2.2 fault situation
The cause of this failure is that the INSPUROA user reports an error in querying the EDOC_BASE_WORKFLOW table. Indicates that the fault block is datafile 5. The error message is as follows in the alter log:
Reading datafile '/oradata/datafiles/oadb/oa01.dbf' for upload uption at rdba: 0x016d4dd5 (file 5, block 2969045)
Reread (file 5, block 2969045) found same upload upt 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/trace/oadb_ora_4565.trc
Unzip upt 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 upload uption at rdba: 0x016d4dd5 (file 5, block 2969045)
Reread (file 5, block 2969045) found same upload upt 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/trace/oadb_ora_21708.trc
Unzip upt 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
Cause Analysis
Observe storage, no error warning, preliminary suspicion of bad logical Blocks
Execute repair
Based on the error message
Reading datafile '/oradata/datafiles/oadb/oa01.dbf' for upload uption at rdba: 0x016d4dd5 (file 5, block 2969045)
Reread (file 5, block 2969045) found same upload upt 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
Determine the data file datafile 5 and oa01.dbf with bad Blocks
View bad block information:
SQL> select * from v $ database_block_corruption;
FILE # BLOCK # BLOCKS upload uption_change # upload uptio
---------------------------------------------------------
5 2969045 1 0 slave upt
Determine that the bad block is 2969045
Check whether the backup log (incremental, full) is completely backed up
Check whether the 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 upt Empty Blocks Examined High SCN
-------------------------------------------------------------
5 failures 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 than upt blocks
See trace file/u01/app/oracle/diag/rdbms/oadb/trace/oadb_ora_13513.trc for details
Finished backup at 18-AUG-15
Execute repair
Use RMAN tools
RMAN> blockrecover datafile 5 blocks 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_ssqek9d0000000000001.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_ssqek9d4244_1.bak
Channel ORA_DISK_1: piece handle =/orabak_m/oadb/20150815_fullbkdb_ssqek9dda-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/415834213802601679.dbf
Archived log for thread 1 with sequence 8342 is already on disk as file/orabak/arch/4158342_802601679.dbf
Archived log for thread 1 with sequence 8343 is already on disk as file/orabak/arch/4158343_802601679.dbf
Archived log for thread 1 with sequence 8344 is already on disk as file/orabak/arch/4158344_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_00001.bak
Channel ORA_DISK_1: piece handle =/orabak_m/oadb/20150815_fullbkarc_t2qekhpb_00001.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_00001.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_1_emurm_1_1.bak
Channel ORA_DISK_1: piece handle =/orabak_m/oadb/20150816_inc_2bkair_1_emurm_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_00001.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_00001.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_00001.bak
Channel ORA_DISK_1: piece handle =/orabak_m/oadb/20150818_inc_2bkair_tpqes7ml_00001.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_00001.bak
Channel ORA_DISK_1: piece handle =/orabak_m/oadb/20150818_inc_2bkair_tqqes7ml_00001.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_00001.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
Fixed the problem of viewing the alter log.
Media Recovery Log/orabak/arch/limit 8336_802601679.dbf
Tue Aug 18 12:04:29 2015
Media Recovery Log/orabak/arch/logs 8336_802601679.dbf (restored)
Media Recovery Log/orabak/arch/limit 8337_802601679.dbf
Media Recovery Log/orabak/arch/limit 8338_802601679.dbf
Media Recovery Log/orabak/arch/limit 8339_802601679.dbf
Media Recovery Log/orabak/arch/limit 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 the fault table information:
SQL> select count (1) from INSPUROA. EDOC_BASE_WORKFLOW;
COUNT (1)
----------
1393635
-------------------------------------- Recommended reading --------------------------------------
ORA-19571 for RMAN backup
RMAN: Configure an archive log deletion policy
Basic Oracle tutorial-copying a database through RMAN
Reference for RMAN backup policy formulation
RMAN backup learning notes
Oracle Database Backup encryption RMAN Encryption
ORA-19588 encountered during RMAN backup
-------------------------------------- Split line --------------------------------------