RMAN

RMAN – Block Recovery using Flashback Log

In this post, I will explain how RMAN can recover a corrupted block with out a backup but from Oracle Flashback Logs.  This is a new feature in 11g. 

To start with I have enabled the flashback in the database level, which will capture all changes to the database.

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;

System altered.
SQL> alter system set db_recovery_file_dest_size=1024M;

System altered.
SQL>  alter system set db_recovery_file_dest = ‘/oradata/bmk/flash/’;

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount ;
ORACLE instance started.

Total System Global Area 1720844288 bytes
Fixed Size                  2148792 bytes
Variable Size            1124079176 bytes
Database Buffers          587202560 bytes
Redo Buffers                7413760 bytes
Database mounted.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL> alter database open ;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES

Now the Flashback is enabled for the database. 

Checked for any exisitng old backups – both RMAN backup and copy.

RMAN> list copy of database;

specification does not match any datafile copy in the repository
RMAN> list backup of database;

specification does not match any backup in the repository

 Both copy and RMAN backup reported no available backup.  Like in the previous posts, collect the information require to corrupt the block and using ‘dd’ command mark the block as corrupted.  

SQL> select distinct dbms_rowid.rowid_block_number(rowid) from test;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
————————————
                                 132
SQL> select distinct dbms_rowid.ROWID_RELATIVE_FNO(rowid) from test;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
————————————
                                   8

SQL> conn / as sysdba
Connected.
SQL> select file_name from dba_data_files where file_id=8;

FILE_NAME
——————————————————————————–
/oradata/bmk/bmk/data01.dbf
$ dd of=/oradata/bmk/bmk/data01.dbf bs=8192 seek=132 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out

 

Flush the buffer cache to force a disk read.

SQL> alter system flush buffer_cache;

System altered.
SQL> conn thomas/thomas
Connected.

SQL> select count(*) from test;
select count(*) from test
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 132)
ORA-01110: data file 8: ‘/oradata/bmk/bmk/data01.dbf’

The block is corrupted and no backup!   Let us try to recover the block using RMAN.

$ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Fri Nov 19 20:48:41 2010

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

connected to target database: BMK (DBID=4095539480)

RMAN> blockrecover datafile 8 block 132;

Starting recover at 19-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 device type=DISK
searching flashback logs for block images
finished flashback log search, restored 1 blocks

starting media recovery
media recovery complete, elapsed time: 00:00:07

Finished recover at 19-NOV-10

Excellent, new 11g feature saved us and recovered the block using the Flashback  log. 

SQL> conn thomas/thomas
Connected.
SQL> select count(*) from test;

  COUNT(*)
———-
         1

  So, one more new way to recover the corrupt block in 11g!

  1. Syed Rafi Ullah
    June 24, 2015 at 4:21 pm

    I do not understand the below step. What exactly you have done here?
    $ dd of=/oradata/bmk/bmk/data01.dbf bs=8192 seek=132 conv=notrunc count=1 if=/dev/zero
    1+0 records in
    1+0 records out

    • June 24, 2015 at 4:31 pm

      wiping out a block in OS level, so that it will be corrupt in Oracle level.

  2. Syed Rafi Ullah
    June 29, 2015 at 10:14 am

    Ok, got it. Thanks for the response.

  1. December 11, 2010 at 1:23 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: