Analysis of OCP questions _ 043: bbed damage of RMAN series, blockrecover recovery case demonstration

Environment:

sys@EMREP> select * from v$version where rownum=1;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prodsys@EMREP> !uname -aLinux localhost.localdomain 2.6.18-308.el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux

There was a bad block 63 before, so DBV verification has two bad blocks, so we need to set bad blocks for RMAN backup. Ha, it's lazy, so ....

① Test preparation

RMAN> run {2> set maxcorrupt for datafile 4 to 1;3> backup tablespace users;4> }executing command: SET MAX CORRUPTStarting backup at 17-DEC-12using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00004 name=/u01/app/oracle/oradata/emrep/users01.dbfchannel ORA_DISK_1: starting piece 1 at 17-DEC-12channel ORA_DISK_1: finished piece 1 at 17-DEC-12piece handle=/u01/app/oracle/flash_recovery_area/EMREP/backupset/2012_12_17/o1_mf_nnndf_TAG20121217T205237_8dy5dor3_.bkp tag=TAG20121217T205237 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 17-DEC-12u1@EMREP> select t2.*,dbms_rowid.rowid_block_number(rowid) from t2;         B DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)---------- ------------------------------------         1                                   71         3                                   71

 

② Simulate bad bbed Blocks

BBED> set file 4        FILE#           4BBED> modify 1000 file 4 block 71 File: /u01/app/oracle/oradata/emrep/users01.dbf (4) Block: 71               Offsets:    0 to  511           Dba:0x01000047------------------------------------------------------------------------ 03e80000 47000001 a63e0400 00000406 454c0000 01000000 3d290000 993e0400  00000000 02003200 41000001 09001400 7e000000 ba008000 48001a00 02200000  a63e0400 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00010200 ffff1600 8c1f701f 701f0000 0200921f 8c1f0000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  <32 bytes per line>BBED> verifyDBVERIFY - Verification startingFILE = /u01/app/oracle/oradata/emrep/users01.dbfBLOCK = 71Block 71 is corruptCorrupt block relative dba: 0x01000047 (file 0, block 71)Bad header found during verificationData in bad block: type: 3 format: 0 rdba: 0x01000047 last change scn: 0x0000.00043ea6 seq: 0x4 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x3ea60604 check value in block header: 0x4c45 computed block checksum: 0x4a05DBVERIFY - Verification completeTotal Blocks Examined         : 1Total Blocks Processed (Data) : 0Total Blocks Failing   (Data) : 0Total Blocks Processed (Index): 0Total Blocks Failing   (Index): 0Total Blocks Empty            : 0Total Blocks Marked Corrupt   : 1Total Blocks Influx           : 0

 

③ Sqlplus check

u1@EMREP> conn / as sysdbaConnected.sys@EMREP> startup force;    ORACLE instance started.Total System Global Area  524288000 bytesFixed Size                  1220384 bytesVariable Size             247464160 bytesDatabase Buffers          272629760 bytesRedo Buffers                2973696 bytesDatabase mounted.Database opened.sys@EMREP> conn u1/u1Connected.u1@EMREP> select * from t2;select * from t2              *ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 4, block # 71)ORA-01110: data file 4: '/u01/app/oracle/oradata/emrep/users01.dbf'

 

④ DBV confirm again

[oracle@localhost ~]$ dbv file=/u01/app/oracle/oradata/emrep/users01.dbfDBVERIFY: Release 10.2.0.1.0 - Production on Mon Dec 17 21:00:48 2012Copyright (c) 1982, 2005, Oracle.  All rights reserved.DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/emrep/users01.dbfPage 63 is marked corruptCorrupt block relative dba: 0x0100003f (file 4, block 63)Bad header found during dbv: Data in bad block: type: 3 format: 0 rdba: 0x0100003f last change scn: 0x0000.00050c11 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x0c110601 check value in block header: 0xd375 computed block checksum: 0x4a05Page 71 is marked corruptCorrupt block relative dba: 0x01000047 (file 4, block 71)Bad header found during dbv: Data in bad block: type: 3 format: 0 rdba: 0x01000047 last change scn: 0x0000.00043ea6 seq: 0x4 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x3ea60604 check value in block header: 0x4c45 computed block checksum: 0x4a05DBVERIFY - Verification completeTotal Pages Examined         : 640Total Pages Processed (Data) : 39Total Pages Failing   (Data) : 0Total Pages Processed (Index): 6Total Pages Failing   (Index): 0Total Pages Processed (Other): 45Total Pages Processed (Seg)  : 0Total Pages Failing   (Seg)  : 0Total Pages Empty            : 548Total Pages Marked Corrupt   : 2Total Pages Influx           : 0Highest block SCN            : 330790 (0.330790)

 

⑤ RMAN repair

RMAN> blockrecover datafile 4 block 71;Starting blockrecover at 17-DEC-12using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=538 devtype=DISKchannel ORA_DISK_1: restoring block(s)channel ORA_DISK_1: specifying block(s) to restore from backup setrestoring blocks of datafile 00004channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/EMREP/backupset/2012_12_17/o1_mf_nnndf_TAG20121217T205237_8dy5dor3_.bkpchannel ORA_DISK_1: restored block(s) from backup piece 1piece handle=/u01/app/oracle/flash_recovery_area/EMREP/backupset/2012_12_17/o1_mf_nnndf_TAG20121217T205237_8dy5dor3_.bkp tag=TAG20121217T205237channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02starting media recoverymedia recovery complete, elapsed time: 00:00:07Finished blockrecover at 17-DEC-12

 

6. Data Verification:

sys@EMREP> conn u1/u1Connected.u1@EMREP> select * from t2;         B----------         1         3

 

7. Notes:

Blockrecover datafile 9 Block 13 datafile 2 Block 19 restore until SCN 100;
The until keyword in blockrecover statement does not indicate Incomplete recovery.
Until refers to restoring a data block (Restore) from a backup generated on a specific date or before SCN)
After restoration, the recover log related to the data block will be applied)

OCP-043 questions attached