In-Oracle.com  -> Oracle DBA -> Oracle Database Backup and Recovery

-> Oracle MBR (Media Block Recovery in Oracle) using rman

 Oracle MBR (Media Block Recovery in Oracle)

Here are the main topics for this article:

1. Test that there are no data block corruption in scott.EMP table

2. Generate a data block corruption for EMP table

3. Recover the corrupted block with RMAN

4. Test that there are no data block corruption in scott.EMP table

1. Test that there are no data block corruption in scott.EMP table

alter system flush buffer_cache; force to get the data from the data file and not from the buffer cache.

2. Generate a data block corruption for EMP table

   >>  Find a block which keep EMP data :

    
SQL> col HEADER_BLOCK format 99999999
SQL> col SEGMENT_NAME format a12
SQL> col NAME format a35

 

SQL> select d.name, s.header_block, s.segment_name
           from dba_segments s,
                   v$datafile_header d
           where s.TABLESPACE_NAME = d.TABLESPACE_NAME
              and s.segment_name = 'EMP' 
              and owner='SCOTT';

 

   >>  Write something wrong to the block 28 (the next block after the header) to corrupt the block (at the OS level):

         $ dd if=/dev/zero of=/DB1/oradata/db1/users01.dbf bs=8192 conv=notrunc seek=28 count=1

           bs = block size

           conv = convert the file  ( In this case = do not truncate the output file)

           seek = which block will be corrupt

           count = how many blocks

     >>  Verify the data block corruption:

SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-08103: object no longer exists

NOTE: Select count(*) from emp; could be used also if there are no indexes on the emp table;

3. Recover the corrupted block with RMAN

RMAN> blockrecover datafile 4 block 28;

Starting blockrecover at 03-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /home/oracle/Desktop/Backup_rman/b ackup/bk_34_1_645659824
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/home/oracle/Desktop/Backup_rman/backup/bk_34_1_645659824 tag=HOT_D B_BK_LEVEL0
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /DB1/flash_recovery_a rea/DB1/archivelog/2008_02_02/o1_mf_1_3_3tb95vdp_.arc
archive log thread 1 sequence 4 is already on disk as file /DB1/flash_recovery_a rea/DB1/archivelog/2008_02_02/o1_mf_1_4_3tbbkzk3_.arc
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1
channel ORA_DISK_1: reading from backup piece /home/oracle/Desktop/Backup_rman/b ackup/al_38_1_645659875
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/Desktop/Backup_rman/backup/al_38_1_645659875 tag=TAG20 080202T215755
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/DB1/flash_recovery_area/DB1/archivelog/2008_02_03/o1_mf_1_ 1_3tbj3q9x_.arc recid=21 stamp=645668279
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: reading from backup piece /home/oracle/Desktop/Backup_rman/b ackup/al_40_1_645659877
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/Desktop/Backup_rman/backup/al_40_1_645659877 tag=TAG20 080202T215755
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/DB1/flash_recovery_area/DB1/archivelog/2008_02_03/o1_mf_1_ 2_3tbj3skx_.arc recid=22 stamp=645668281
media recovery complete, elapsed time: 00:00:01
Finished blockrecover at 03-FEB-08

4. Test that there are no data block corruption in scott.EMP table

     select EMPNO,ENAME,JOB,MGR, HIREDATE from emp; will returm the emp data:

In-Oracle.com  -> Oracle DBA -> Oracle Database Backup and Recovery

-> Oracle MBR (Media Block Recovery in Oracle) using rman