Block Recovery using RMAN – on Oracle 11g

The purpose of this article is to simulate a block level corruption using BBED utility (block browser and editor) and recover using RMAN.
In this situation the data file remains online throughout the recovery operation and hence other segments within the tablespace remain accessible.
Since BBED exists from Oracle7 to Oracle10g, we will have to copy some files from earlier version and compile it

Cp $ORA10g_HOME/rdbms/lib/ssbbded.o $ORA11g_HOME/rdbms/lib
Cp $ORA10g_HOME/rdbms/lib/sbbdpt.o $ORA11g_HOME/rdbms/lib

Message files (list may differ):

Cp $ORA10g_HOME/rdbms/mesg/bbedus.msb $ORA11g_HOME/rdbms/mesg
Cp $ORA10g_HOME/rdbms/mesg/bbedus.msg $ORA11g_HOME/rdbms/mesg
Cp $ORA10g_HOME/rdbms/mesg/bbedar.msb $ORA11g_HOME/rdbms/mesg

Issue the following command:

make -f $ORA11g_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed $ORA11g_HOME/bin/bbed
password: blockedit
SQL> Set pages 0
SQL> set feedback off
SQL> spool fileunix.log
SQL> select file#||' '||name||' '||bytes from v$datafile;
SQL> spool off
$ cat bbed.par
blocksize=8192
listfile=/tmp/bbed/fileunix.log
mode=edit

Corrupt a block
Let us take the EMP table and find out the segment header of the same.

SQL> select header_file,header_block from dba_segments where segment_name='EMP';
HEADER_FILE HEADER_BLOCK
----------- ------------
5 1472
SQL> exit

$ bbed parfile=bbed.par
Password: blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Sun Apr 3 14:38:48 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set dba 5,10
DBA 0x0140000a (20971530 5,10)

BBED> copy to block 1475
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /u01/data/app/oracle/oradata/orcl/EMP_TS2.DBF (5)

Block: 7011 Offsets: 0 to 511 Dba:0x01401b63
------------------------------------------------------------------------
1ea20000 0a004001 db690200 00000104 bfc10000 05000000 80806c00 00000000
00000000 00f80000 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 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> exit

SQL> select * from EMP;
select * from EMP
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 5681)
ORA-01110: data file 5: '/u01/data/app/oracle/oradata/orcl/EMP_TS2.DBF

Recover the block
We now run a validate database command which
- Checks datafiles for physical and logical corruption
- Confirms that all database files exist and are in the correct locations
- insert the list of corrupted blocks to v$database_block_corruption

RMAN> backup validate check logical database;
SQL> select * from v$database_block_corruption;

recovering with RMAN using the list in v$database_block_corruption

RMAN> blockrecover corruption list;

you can still do it manually:

RMAN> run {blockrecover datafile 4 block 24165;}