LATEST TOPICS

RMAN Block Media Recovery: Lets have a deeper look

As a DBA, we know there are situations where the database blocks get corrupted and we have different methods available to fix the corrupted blocks.
In today’s article, I would be discussing the internals involved in the recovery of database block corruption using RMAN (Recovery Manager) Block Media Recovery method.

Identifying Block Corruption

Lets say, we are seeing the following block corruption error in the database log file.

Tue Dec 30 00:26:51 2014
Hex dump of (file 6, block 146) in trace file /app/oracle/diag/rdbms/labdb/labdb/trace/labdb_ora_9332.trc

Corrupt block relative dba: 0x01800092 (file 6, block 146)
Completely zero block found during validation

Reread of blocknum=146, file=/workspace/data/labdb/users1.dbf. found same corrupt data
Reread of blocknum=146, file=/workspace/data/labdb/users1.dbf. found same corrupt data
Reread of blocknum=146, file=/workspace/data/labdb/users1.dbf. found same corrupt data
Reread of blocknum=146, file=/workspace/data/labdb/users1.dbf. found same corrupt data
Reread of blocknum=146, file=/workspace/data/labdb/users1.dbf. found same corrupt data
Hex dump of (file 6, block 146) in trace file /app/oracle/diag/rdbms/labdb/labdb/trace/labdb_ora_9332.trc

Corrupt block relative dba: 0x01800092 (file 6, block 146)
Completely zero block found during buffer read

Reading datafile '/workspace/data/labdb/users1.dbf' for corruption at rdba: 0x01800092 (file 6, block 146)
Reread (file 6, block 146) found same corrupt data (no logical check)
Mon Dec 29 13:56:51 2014
Corrupt Block Found
         CONT = 0, TSN = 4, TSNAME = USERS
         RFN = 6, BLK = 146, RDBA = 25165970
         OBJN = -1, OBJD = 73633, OBJECT = USERS, SUBOBJECT =
         SEGMENT OWNER = , SEGMENT TYPE = Temporary Segment
Errors in file /app/oracle/diag/rdbms/labdb/labdb/trace/labdb_ora_9332.trc  (incident=108441):
ORA-01578: ORACLE data block corrupted (file # 6, block # 146)
ORA-01110: data file 6: '/workspace/data/labdb/users1.dbf'
Incident details in: /app/oracle/diag/rdbms/labdb/labdb/incident/incdir_108441/labdb_ora_9332_i108441.trc

Lets get the list of all corrupted database blocks from the alert log file.

[oracle@labserver trace]$ grep -i 'Corrupt block relative dba' alert_labdb.log | sort | uniq
Corrupt block relative dba: 0x01800092 (file 6, block 146)
Corrupt block relative dba: 0x01800093 (file 6, block 147)
Corrupt block relative dba: 0x01800094 (file 6, block 148)

As per the alert log, blocks# 146, 147 and 148 are corrupted in datafile# 6 (/workspace/data/labdb/users1.dbf)

Lets check, if we can see the corruption summary within the database.

sys@LABDB> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID
---------- ---------- ---------- ------------------ --------- ----------
         6        146          3                  0 ALL ZERO           0

Database view also confirmed the block corruption that is reported in the alert log file. As per the report, somehow the blocks (146, 147 and 148) became zero byte and that is the reason for corruption.

Lets verify the datafile# 6 with DBV (DBVERIFY) utility

[oracle@labserver labdb]$ dbv file=/workspace/data/labdb/users1.dbf

DBVERIFY: Release 12.1.0.1.0 - Production on Tue Dec 30 00:29:41 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /workspace/data/labdb/users1.dbf
Page 146 is marked corrupt
Corrupt block relative dba: 0x01800092 (file 6, block 146)
Completely zero block found during dbv:

Page 147 is marked corrupt
Corrupt block relative dba: 0x01800093 (file 6, block 147)
Completely zero block found during dbv:

Page 148 is marked corrupt
Corrupt block relative dba: 0x01800094 (file 6, block 148)
Completely zero block found during dbv:



DBVERIFY - Verification complete

Total Pages Examined         : 640
Total Pages Processed (Data) : 33
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 5
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 582
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 17
Total Pages Marked Corrupt   : 3
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2506706 (0.2506706)

DB Verify also confirmed the blocks being in zero byte.

At this point, we have identified the datafile blocks which are corrupted.

Our next task would be to check, if a valid backup is available to restore and recover the corrupted blocks.

Determining Valid backup to restore/recover corrupted blocks

We need a VALID backup of the datafile in order to be able to perform a BLOCK recovery. We can use, RMAN RESTORE VALIDATE command to validate if a suitable backup is available to restore the corrupted blocks.

Since the corrupted blocks belong to datafile 6 (/workspace/data/labdb/users1.dbf), I will run RESTORE VALIDATE for datafile 6 to check if the blocks can be restored.

RMAN> restore validate datafile '/workspace/data/labdb/users1.dbf';

Starting restore at 30-DEC-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /backup/labdb/labdb_df_0ipr1uor_1_1.bkp
channel ORA_DISK_1: piece handle=/backup/labdb/labdb_df_0ipr1uor_1_1.bkp tag=TAG20141225T110354
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:45
Finished restore at 30-DEC-14

As we can see, RMAN has successfully identified the backup piece required to perform the restoration of the corrupted blocks.

Recovering the Corrupted blocks using CORRUPTION LIST

We can use the RMAN BLOCKRECOVER command to recover the corrupted blocks (provided a VALID backup is available for the block restoration)

One of the noted advantage of the Block Media Recovery using RMAN is that, the affected datafiles can remain ONLINE during the course of recovery process.

The syntax of the BLOCKRECOVER command is as follows

BLOCKRECOVER [DEVICE TYPE [=] deviceSpecifier [, deviceSpecifier]...] 
CORRUPTION LIST | DATAFILE datafileSpec BLOCK integer [, integer]... | TABLESPACE tablespace_name DBA integer [, integer]...
[FROM {BACKUPSET | DATAFILECOPY} | FROM TAG [=] ['] tag_name ['] | RESTORE untilClause]

We can either recover the blocks by specifying the datafile and the respective block numbers to be recovered or simply we can recover all the corrupted blocks as reported in the CORRUPTION LIST by the database.

Lets say, we are recovering the blocks reported in the corruption list.

Recovery Manager: Release 12.1.0.1.0 - Production on Tue Dec 30 00:34:35 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

RMAN-06005: connected to target database: LABDB (DBID=1878775056)

RMAN> blockrecover corruption list;

RMAN-03090: Starting recover at 30-DEC-14
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=63 device type=DISK

RMAN-08106: channel ORA_DISK_1: restoring block(s)
RMAN-08108: channel ORA_DISK_1: specifying block(s) to restore from backup set
RMAN-08533: restoring blocks of datafile 00006
RMAN-08003: channel ORA_DISK_1: reading from backup piece /backup/labdb/labdb_df_0ipr1uor_1_1.bkp
RMAN-08611: channel ORA_DISK_1: piece handle=/backup/labdb/labdb_df_0ipr1uor_1_1.bkp tag=TAG20141225T110354
RMAN-08109: channel ORA_DISK_1: restored block(s) from backup piece 1
RMAN-08183: channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

RMAN-08054: starting media recovery

RMAN-06050: archived log for thread 1 with sequence 16 is already on disk as file +FRA/LABDB/ARCHIVELOG/2014_12_25/thread_1_seq_16.320.867242603
RMAN-06050: archived log for thread 1 with sequence 17 is already on disk as file +FRA/LABDB/ARCHIVELOG/2014_12_25/thread_1_seq_17.333.867282187
RMAN-06050: archived log for thread 1 with sequence 18 is already on disk as file +FRA/LABDB/ARCHIVELOG/2014_12_25/thread_1_seq_18.331.867282593
RMAN-06050: archived log for thread 1 with sequence 19 is already on disk as file +FRA/LABDB/ARCHIVELOG/2014_12_29/thread_1_seq_19.377.867586163
RMAN-06050: archived log for thread 1 with sequence 20 is already on disk as file +FRA/LABDB/ARCHIVELOG/2014_12_29/thread_1_seq_20.378.867586343
RMAN-06050: archived log for thread 1 with sequence 21 is already on disk as file +FRA/LABDB/ARCHIVELOG/2014_12_29/thread_1_seq_21.385.867587493
RMAN-08181: media recovery complete, elapsed time: 00:00:05
RMAN-03091: Finished recover at 30-DEC-14

Here, RMAN has identified the backup piece to be used for restoring the blocks. Then, it has restored all the corrupted blocks and finally recovered the datafile to be in the database consistent state.

RMAN has basically performed the following set of tasks to perform the recovery of the corrupted blocks.

When we mention to recover the corruption list, RMAN first translates the block corruption list to identify the datafile (s) to which the corrupted blocks belong to.

 
DBGSQL:          TARGET> begin dbms_rcvman.translateCorruptList; end;
DBGSQL:             sqlcode = 0
DBGMISC:         ENTERED krmkgdf [00:35:00.562]
DBGMISC:          Retrieved file 6, created: 26029, stopscn: 0, blocks: 640 (rfno: 6, ts: USERS [4]): [00:35:00.605] (krmkgdf)
DBGMISC:             Name: /workspace/data/labdb/users1.dbf (krmkgdf)
DBGMISC:             Auxname:  (krmkgdf)
DBGMISC:             Creation Thread: 0; Creation Size : 0 [00:35:00.606] (krmkgdf)
DBGMISC:          File 6 is online [00:35:00.606] (krmkgdf)
DBGMISC:          -- No more datafiles -- [00:35:00.607] (krmkgdf)
DBGMISC:          ENTERED krmkgbh [00:35:00.607]
DBGMISC:          EXITED krmkgbh with status No backup history required - no flags set [00:35:00.607] elapsed time [00:00:00:00.000]
DBGMISC:         EXITED krmkgdf [00:35:00.607] elapsed time [00:00:00:00.044]

As we can see RMAN has identified the datafile 6 as a member of the block corruption list.

Once the datafiles are identified, RMAN generates the list of corrupted blocks, mapping them along with the respective datafiles to prepare for block media recovery.

 
DBGSQL:          TARGET> declare first boolean := FALSE; begin if (:first > 0) then first := TRUE; end if; dbms_rcvman.bmrAddCorruptTable( dfnumber   => :indfno, blknumber  => :blkno,  range      => :count,  first      =>  first); end;
DBGSQL:             sqlcode = 0
DBGSQL:              B :indfno = 6
DBGSQL:              B :blkno = 146
DBGSQL:              B :count = 3
DBGSQL:              B :first = 1

As we can see, RMAN has generated the list of corrupted blocks (starting at block#146 and ranging to a count of 3 i.e. block# 146, 147 and 148) by mapping them to respective datafile (datafile# 6) to prepare for block media recovery.

Once the corruption list is generated, RMAN builds a COMMAND parse tree that needs to be executed to restore the corrupted blocks.

 
DBGMISC:       krmknmtr:  the parse tree after name translation is: [00:35:00.621] (krmknmtr)
DBGMISC:         1 RSLIST
DBGMISC:             1 RSPEC
DBGMISC:                 1 DFILE
DBGMISC:                     1 DF fno=6 pdbid=0 pdbname=  crescn=26029
DBGMISC:                        blksize=8192 blocks=640 rfno=6
DBGMISC:                       fn=/workspace/data/labdb/users1.dbf
DBGMISC:                       ts=USERS, flags=KRMKDF_INBACKUP
DBGMISC:                       fedata: sta=0x0e crescn=26029
DBGMISC:                       blkid=146, count=3
DBGMISC:      EXITED krmknmtr with status RSLIST [00:35:00.621] elapsed time [00:00:00:00.117]
DBGMISC:      krmrbmr: bmr parse tree is: [00:35:00.621]
DBGRCV:         1 RSLIST
DBGRCV:             1 RSPEC
DBGRCV:                 1 DFILE
DBGRCV:                     1 DF fno=6 pdbid=0 pdbname=  crescn=26029
DBGRCV:                        blksize=8192 blocks=640 rfno=6
DBGRCV:                       fn=/workspace/data/labdb/users1.dbf
DBGRCV:                       ts=USERS, flags=KRMKDF_INBACKUP
DBGRCV:                       fedata: sta=0x0e crescn=26029
DBGRCV:                       blkid=146, count=3

Once the parse tree is built and the respective RMAN commands are prepared, RMAN initiates the block media recovery process

 
DBGSQL:           CHANNEL> declare save_all_blocks    boolean; save_final_blocks  boolean; nofileupdate       boolean; doclear            boolean; begin if (:save     _all_blocks > 0) then save_all_blocks := TRUE; else save_all_blocks := FALSE; end if; if (:save_final_blocks > 0) then save_final_blocks := TRUE; else save_final_     blocks := FALSE; end if; if (:nofileupdate > 0) then nofileupdate := TRUE; else nofileupdate := FALSE; end if; if (:doclear > 0) then doclear := TRUE; else doclea     r := FALSE; end if; begin sys.dbms_backup_restore.bmrCancel; exception when others then null; end; sys.dbms_backup_restore.bmrStart( save_all_blocks   => save_all     _blocks, save_final_blocks => save_final_blocks, nofileupdate      => nofileupdate, doclear           => doclear, flags_clear       => :clrflgs ); end;
DBGSQL:              sqlcode = 0
DBGSQL:               B :save_all_blocks = 0
DBGSQL:               B :save_final_blocks = 0
DBGSQL:               B :nofileupdate = 0
DBGSQL:               B :doclear = 0
DBGSQL:               B :clrflgs = 0
DBGMISC:          ENTERED krmkrfh [00:35:00.681]
DBGRCV:            ENTERED krmklknn
DBGRCV:             Looking for newname for datafile: 6, Translate: 1, dosearch=1 (krmklknn)
DBGRCV:             Looking up in unprocessed newname list, need_dfinfo=0 (krmklknn)
DBGRCV:             ENTERED krmksearchnewname
DBGRCV:             EXITED krmksearchnewname with address 0
DBGRCV:             No newname found for datafile 6 (krmklknn)
DBGRCV:            EXITED krmklknn with address 0

DBGSQL:            TARGET> select fhscn, to_date(fhtim,'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), fhcrs, fhrls, to_date(fhrlc,'MM/DD/RR HH24:MI:SS', 'NLS_C
ALENDAR=Gregorian'), fhafs, fhrfs, fhrft, hxerr, fhfsz, fhsta, fhdbi, fhfdbi, fhplus, fhtyp into :ckpscn, :ckptime, :crescn, :rlgscn, :rlgtime, :afzscn, :rfzscn, 
:rfztime, :hxerr, :blocks, :fhsta, :fhdbi, :fhfdbi, :fhplus, :fhtyp from x$kcvfhall where  hxfil = :fno
DBGSQL:               sqlcode = 0
DBGSQL:                D :ckpscn = 2560775
DBGSQL:                D :ckptime = "29-DEC-14"
DBGSQL:                D :crescn = 26029
DBGSQL:                D :rlgscn = 2165909
DBGSQL:                D :rlgtime = "18-DEC-14"
DBGSQL:                D :afzscn = 0
DBGSQL:                D :rfzscn = 0
DBGSQL:                D :rfztime = "25-DEC-14"
DBGSQL:                D :hxerr = 0
DBGSQL:                D :blocks = 640
DBGSQL:                D :fhsta = 4
DBGSQL:                D :fhdbi = 1878775056
DBGSQL:                D :fhfdbi = 0
DBGSQL:                D :fhplus = 0
DBGSQL:                D :fhtyp = 3
DBGSQL:                B :fno = 6
DBGMISC:           krmkrfh: [00:35:00.689]
DBGMISC:           DF fno=6 pdbid=0 pdbname=  crescn=26029
DBGMISC:                blksize=8192 blocks=640 rfno=6
DBGMISC:               fn=/workspace/data/labdb/users1.dbf
DBGMISC:               ts=USERS, flags=KRMKDF_INBACKUP
DBGMISC:               fedata: sta=0x0e crescn=26029
DBGMISC:               fhdata: ckpscn=2560775 rlgscn=2165909
DBGMISC:               blkid=146, count=3
DBGMISC:          EXITED krmkrfh [00:35:00.689] elapsed time [00:00:00:00.008]

DBGSQL:           CHANNEL> begin sys.dbms_backup_restore.bmrAddBlock( dfnumber  => :indfno, blknumber => :blkno, range     => :range); end;
DBGSQL:              sqlcode = 0
DBGSQL:               B :indfno = 6
DBGSQL:               B :blkno = 146
DBGSQL:               B :range = 3

During the process of block media recovery, RMAN identifies the VALID backup piece to be used for block restoration.

 
DBGRCV:                valid backup set list is
DBGRCV:                  1 VBS copy#=1 tag=TAG20141225T110354 deviceType=DISK status=A
DBGRCV:                      1 BPIECEX key=12 recid=12 stamp=867236636
DBGRCV:                                      bskey=12 vbkey=0 set_stamp=867236635 set_count=18                      site_key=0
DBGRCV:                             pieceno=1 handle=/backup/labdb/labdb_df_0ipr1uor_1_1.bkp am_access=U
DBGRCV:                                device=DISK krmkch { count=0 found=FALSE }
DBGRCV:                restore target list is
DBGRCV:                  1 ACT type=full fromSCN=0 toSCN=2408666 fno=6
DBGRCV:                    DF fno=6 pdbid=0 pdbname=  crescn=26029
DBGRCV:                         blksize=8192 blocks=640 rfno=6
DBGRCV:                        fn=/workspace/data/labdb/users1.dbf
DBGRCV:                        ts=USERS, flags=KRMKDF_INBACKUP
DBGRCV:                        fedata: sta=0x0e crescn=26029
DBGRCV:                        fhdata: ckpscn=2408666 rlgscn=2165909
DBGRCV:                        blkid=146, count=3

Once the backup piece is identified, RMAN starts restoring the blocks.

 
DBGMISC:         ENTERED krmzlog [00:35:01.272]
RMAN-08106: channel ORA_DISK_1: restoring block(s)
DBGMISC:         EXITED krmzlog [00:35:01.273] elapsed time [00:00:00:00.000]
DBGMISC:         ENTERED krmzgparms [00:35:01.274]
DBGMISC:          Step id = 1; Code = 2 [00:35:01.274] (krmzgparms)
DBGMISC:         EXITED krmzgparms with status 0 (FALSE) [00:35:01.274] elapsed time [00:00:00:00.000]
DBGIO:           channel ORA_DISK_1: set_stamp=867236635 set_count=18 [00:35:01.274] (rsdf_start)
DBGRPC:          krmxrpc - channel ORA_DISK_1 kpurpc2 err=0 db=target proc=SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO excl: 0
DBGMISC:         ENTERED krmzlog [00:35:01.284]
RMAN-08108: channel ORA_DISK_1: specifying block(s) to restore from backup set
DBGMISC:         EXITED krmzlog [00:35:01.286] elapsed time [00:00:00:00.001]
DBGMISC:         ENTERED krmzlog [00:35:01.286]
RMAN-08533: restoring blocks of datafile 00006
DBGMISC:         EXITED krmzlog [00:35:01.287] elapsed time [00:00:00:00.001]
DBGRPC:          krmxrpc - channel ORA_DISK_1 kpurpc2 err=0 db=target proc=SYS.DBMS_BACKUP_RESTORE.RESTORESETPIECE excl: 0
DBGMISC:         ENTERED krmzlog [00:35:01.289]
RMAN-08003: channel ORA_DISK_1: reading from backup piece /backup/labdb/labdb_df_0ipr1uor_1_1.bkp
DBGMISC:         EXITED krmzlog [00:35:01.290] elapsed time [00:00:00:00.001]
DBGRPC:          krmxrpc - channel ORA_DISK_1 kpurpc2 err=3123 db=target proc=SYS.DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE excl: 0
DBGRPC:          krmxr - channel ORA_DISK_1 returned from peicnt
DBGRPC:          krmxpoq - returning rpc_number: 20 with status: STARTED40 for channel ORA_DISK_1
DBGRPC:          ENTERED krmqgns
DBGRPC:           krmqgns: looking for work for channel default (krmqgns)
DBGRPC:           krmqgns: commands remaining to be executed: (krmqgns)
DBGRPC:           CMD type=recover(2) cmdid=1 status=STARTED
DBGRPC:                 1 STEPstepid=1 cmdid=1 status=STARTED chid=ORA_DISK_1 bs.stamp=867236718 step_size=0 Bytes
DBGRPC:           krmqgns: no work found for channel default (krmqgns)
DBGRPC:            (krmqgns)
DBGRPC:          EXITED krmqgns with status 1
DBGRPC:          krmxpoq - returning rpc_number: 20 with status: STARTED40 for channel ORA_DISK_1
DBGRPC:          krmxr - sleeping for 1 seconds
DBGRPC:          ENTERED krmqgns
DBGRPC:           krmqgns: looking for work for channel default (krmqgns)
DBGRPC:           krmqgns: commands remaining to be executed: (krmqgns)
DBGRPC:           CMD type=recover(2) cmdid=1 status=STARTED
DBGRPC:                 1 STEPstepid=1 cmdid=1 status=STARTED chid=ORA_DISK_1 bs.stamp=867236718 step_size=0 Bytes
DBGRPC:           krmqgns: no work found for channel default (krmqgns)
DBGRPC:            (krmqgns)
DBGRPC:          EXITED krmqgns with status 1
DBGRPC:          krmxpoq - returning rpc_number: 20 with status: FINISHED40 for channel ORA_DISK_1
DBGRPC:          krmxr - channel ORA_DISK_1 calling peicnt
DBGRPC:          krmxrpc - channel ORA_DISK_1 kpurpc2 err=0 db=target proc=SYS.DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE excl: 0
DBGMISC:         ENTERED krmzlog [00:35:02.429]
RMAN-08611: channel ORA_DISK_1: piece handle=/backup/labdb/labdb_df_0ipr1uor_1_1.bkp tag=TAG20141225T110354
DBGMISC:         EXITED krmzlog [00:35:02.430] elapsed time [00:00:00:00.001]
DBGMISC:         ENTERED krmzlog [00:35:02.430]
RMAN-08109: channel ORA_DISK_1: restored block(s) from backup piece 1
DBGMISC:         EXITED krmzlog [00:35:02.431] elapsed time [00:00:00:00.000]
DBGMISC:         ENTERED krmzlog [00:35:02.432]
RMAN-08183: channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
DBGMISC:         EXITED krmzlog [00:35:02.432] elapsed time [00:00:00:00.000]

Once the blocks are restored from the backups, RMAN performs recovery of the respective blocks by applying the online logs or archive logs as necessary to make them consistent.

For the block media recovery to work using CORRUPTION LIST, the corrupted blocks must be identified using v$database_block_corruption dictionary view. If the corrupted blocks are not showing in the corruption list, we can use RMAN validate command to sync the corruption list

 
Recovery Manager: Release 12.1.0.1.0 - Production on Tue Dec 30 23:31:36 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: LABDB (DBID=1878775056)

RMAN> validate database;

Starting validate at 30-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=86 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00003 name=/workspace/data/labdb/sysaux1.dbf
input datafile file number=00001 name=/workspace/data/labdb/system1.dbf
input datafile file number=00002 name=/workspace/data/labdb/undotbs1.dbf
input datafile file number=00006 name=/workspace/data/labdb/users1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:45
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              17490        101124          2627232
  File Name: /workspace/data/labdb/system1.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              65799
  Index      0              13720
  Other      0              4111

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              1            12803           2627245
  File Name: /workspace/data/labdb/undotbs1.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              12799

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              26944        103685          2627245
  File Name: /workspace/data/labdb/sysaux1.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              17821
  Index      0              9650
  Other      0              49265

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    FAILED 0              17           642             2506706
  File Name: /workspace/data/labdb/users1.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              33
  Index      0              5
  Other      2              585

validate found one or more corrupt blocks
See trace file /app/oracle/diag/rdbms/labdb/labdb/trace/labdb_ora_7846.trc for details
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              616
Finished validate at 30-DEC-14

Recovering the Corrupted blocks by specifying block#

We can also perform Block Media Recovery of the corrupted blocks by specifying the datafile name/no along with the corrupted block# as show below

 
RMAN> blockrecover datafile 6 block 146;

RMAN-03090: Starting recover at 30-DEC-14
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=71 device type=DISK

RMAN-08106: channel ORA_DISK_1: restoring block(s)
RMAN-08108: channel ORA_DISK_1: specifying block(s) to restore from backup set
RMAN-08533: restoring blocks of datafile 00006
RMAN-08003: channel ORA_DISK_1: reading from backup piece /backup/labdb/labdb_df_0ipr1uor_1_1.bkp
RMAN-08611: channel ORA_DISK_1: piece handle=/backup/labdb/labdb_df_0ipr1uor_1_1.bkp tag=TAG20141225T110354
RMAN-08109: channel ORA_DISK_1: restored block(s) from backup piece 1
RMAN-08183: channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

RMAN-08054: starting media recovery

RMAN-06050: archived log for thread 1 with sequence 16 is already on disk as file +FRA/LABDB/ARCHIVELOG/2014_12_25/thread_1_seq_16.320.867242603
RMAN-06050: archived log for thread 1 with sequence 17 is already on disk as file +FRA/LABDB/ARCHIVELOG/2014_12_25/thread_1_seq_17.333.867282187
RMAN-06050: archived log for thread 1 with sequence 18 is already on disk as file +FRA/LABDB/ARCHIVELOG/2014_12_25/thread_1_seq_18.331.867282593
RMAN-06050: archived log for thread 1 with sequence 19 is already on disk as file +FRA/LABDB/ARCHIVELOG/2014_12_29/thread_1_seq_19.377.867586163
RMAN-06050: archived log for thread 1 with sequence 20 is already on disk as file +FRA/LABDB/ARCHIVELOG/2014_12_29/thread_1_seq_20.378.867586343
RMAN-06050: archived log for thread 1 with sequence 21 is already on disk as file +FRA/LABDB/ARCHIVELOG/2014_12_29/thread_1_seq_21.385.867587493
RMAN-08181: media recovery complete, elapsed time: 00:00:05
RMAN-03091: Finished recover at 30-DEC-14

In this case, RMAN doesn’t lookup in the corruption list and instead directly performs the block media recovery of the mentioned block provided it needs recovery.

Here RMAN directly builds the command parse tree without looking up for the corrupted block in the CORRUPTION LIST. The rest of the recovery tasks are similar to what we have seen in the earlier section.

 
RMAN-06005: connected to target database: LABDB (DBID=1878775056)

RMAN>

DBGMISC:    Node # 1 [02:02:12.822]
DBGMISC:    BRECOVER
DBGMISC:      1 RSLIST
DBGMISC:          1 RSPEC
DBGMISC:              1 DFILE
DBGMISC:                  1 DFNO = 6
DBGMISC:                  2 BLKNO = 146
DBGMISC:    ENTERED krmice [02:02:12.823]
DBGMISC:     command to be compiled and executed is: recover  [02:02:12.823] (krmice)
DBGMISC:     command after this command is: NONE  [02:02:12.823] (krmice)
DBGMISC:     current incarnation must match for recover  [02:02:12.823] (krmice)
DBGMISC:     ENTERED krmkcrsr [02:02:12.823]

Validating the datafiles after Block Media Recovery

We can use a number of methods to validate that there are no corruptions in the database blocks.

For example:

We can utilize DBVERIFY utility to analyze individual datafiles for corruption.

 
[oracle@labserver labdb]$ dbv file=/workspace/data/labdb/users1.dbf

DBVERIFY: Release 12.1.0.1.0 - Production on Tue Dec 30 00:35:36 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /workspace/data/labdb/users1.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 640
Total Pages Processed (Data) : 35
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 5
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 583
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 17
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2506706 (0.2506706)

We can also utilize RMAN VALIDATE command to check for any database corruption.

 

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Dec 31 00:02:46 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: LABDB (DBID=1878775056)

RMAN> validate database;

Starting validate at 31-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=87 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00003 name=/workspace/data/labdb/sysaux1.dbf
input datafile file number=00001 name=/workspace/data/labdb/system1.dbf
input datafile file number=00002 name=/workspace/data/labdb/undotbs1.dbf
input datafile file number=00006 name=/workspace/data/labdb/users1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              17490        101124          2632132
  File Name: /workspace/data/labdb/system1.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              65799
  Index      0              13720
  Other      0              4111

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              1            12803           2632149
  File Name: /workspace/data/labdb/undotbs1.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              12799

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              26940        103685          2632149
  File Name: /workspace/data/labdb/sysaux1.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              17821
  Index      0              9650
  Other      0              49269

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              17           642             2506706
  File Name: /workspace/data/labdb/users1.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              35
  Index      0              5
  Other      0              583

channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              616
Finished validate at 31-DEC-14


Finally, we can query the dictionary view v$database_block_corruption to check for any corruption.

 
sys@LABDB> select * from v$database_block_corruption;

no rows selected

Further Readings

Performing Block Media Recovery

5 Comments
  1. Nassyam Basha
  2. satya
    • Abu Fazal Abbas
      • satya
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed