Creating a simulation of block corruption and recovering it through RMAN in linux

1.Before proceeding with block corruption simulation take the full database backup along with full archive log through rman
[oracle@oralinux ~]$ rman target sys/sys
RMAN>backup database plus archive log ;

2.create a table named mytab for testing purpose
SQL> create table mytab tablespace users as select * from tab;
Table created.
SQL> select count(*) from mytab;
COUNT(*)
----------
3645

3.Generate script for corrupting the block
SQL> set heading off
SQL> set lines 113
SELECT 'dd of=' f.file_name ' bs=8192 conv=notrunc seek='
to_number(S.HEADER_BLOCK + 1) ' << EOF',
'CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt ',
'EOF'
FROM DBA_SEGMENTS S, dba_data_files f, dba_tables t
WHERE f.tablespace_name = t.tablespace_name
and S.SEGMENT_NAME = t.table_name
and t.table_name = 'MYTAB'
and S.OWNER = t.owner
and t.owner = 'SYS';

The output will be something like this

dd of=/oracle/oracle10/oradata/linx/users01.dbf bs=8192 conv=notrunc seek=420 << EOF CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt EOF SQL> exit

4.Copy the dd command generated and execute from shell prompt
[oracle@oralinux dbs]$ dd of=/oracle/oracle10/oradata/linx/users01.dbf bs=8192 conv=notrunc seek=420 << EOF > CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
> EOF
0+1 records in
0+1 records out
112 bytes (112 B) copied, 0.000115064 seconds, 973 kB/s
[oracle@oralinux dbs]$

SQL> select count(*) from mytab;
COUNT(*)
----------
3645
SQL> alter system flush buffer_cache;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select count(*) from mytab;
select count(*) from mytab
*ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 420)
ORA-01110: data file 4: '/oracle/oracle10/oradata/linx/users01.dbf'
SQL>

Now check the dynamic view $database_block_corruption for any logical block corruption
SQL> select * from v$database_block_corruption;
no rows selected
SQL>

From my experience i observed that this view will get populated during the backup validation
Through rman , now validate the backup by following command.

RMAN> backup validate check logical database archivelog all;
Starting backup at 03-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle/oracle10/oradata/linx/system01.dbf
input datafile fno=00003 name=/oracle/oracle10/oradata/linx/sysaux01.dbf
input datafile fno=00005 name=/oracle/oracle10/oradata/linx/example01.dbf
input datafile fno=00002 name=/oracle/oracle10/oradata/linx/undotbs01.dbf
input datafile fno=00004 name=/oracle/oracle10/oradata/linx/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=11 recid=1 stamp=774233518
input archive log thread=1 sequence=12 recid=2 stamp=774233578
input archive log thread=1 sequence=13 recid=3 stamp=774233871
input archive log thread=1 sequence=14 recid=4 stamp=774233952
input archive log thread=1 sequence=15 recid=5 stamp=774234059
input archive log thread=1 sequence=16 recid=6 stamp=774234160
input archive log thread=1 sequence=17 recid=7 stamp=774234573
input archive log thread=1 sequence=18 recid=8 stamp=774234575
input archive log thread=1 sequence=19 recid=9 stamp=774234730
input archive log thread=1 sequence=20 recid=10 stamp=774234811
input archive log thread=1 sequence=21 recid=11 stamp=774234842
input archive log thread=1 sequence=22 recid=12 stamp=774234939
input archive log thread=1 sequence=23 recid=13 stamp=774235147
input archive log thread=1 sequence=24 recid=14 stamp=774235192
input archive log thread=1 sequence=25 recid=15 stamp=774235215
input archive log thread=1 sequence=26 recid=16 stamp=774235261
input archive log thread=1 sequence=27 recid=17 stamp=774235294
input archive log thread=1 sequence=28 recid=18 stamp=774235334
input archive log thread=1 sequence=29 recid=19 stamp=774236122
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 03-FEB-12
RMAN>

SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
4 420 1 0 CORRUPT

Note:-Starting with Oracle 9i we can use RMANto check a database for both physically and logically corrupt blocks.

5.Now recover the corrupted block by the following command
Rman > blockrecover datafile 4 block 420;
you can also do
rman > blockrecover corruption list;  (all blocks from v$database_block_corruption)
I closed the window before copying the output of this command , however rman will
Recover the block corruption and finally it will goes through media recovery from the archive log files .
You can see the view v$database_block_corruption still contain the information about the corrupted block , and it will vanish when you will run the ‘backup validate check logical database archivelog all;’ command again .