Point in time recovery using RMAN

If you want to recover your database to the exact date/time in the past, use RMAN point in time recovery.

RMAN database point-in-time recovery (DBPITR) restores the db from RMAN backups.

RMAN will be consider all ( required ) backups (full, incremental, transectional) to restore or roll forward to the desire time.

Point in time recovery may be incomplete recovery because it does not use all the available archive logs files or completely recover all changes to your database.

In case of you are performing RMAN recovery and you forgotten to provide date/time then RMAN will apply all the archived logs available.

This recovery also called as time base recovery.

 

Pre-requisites for the DBPITR:

  • Target database SID & sys password.
  • Database must be running in archivelog mode.
  • Desire time to perform DBPITR.
  • You must have all the datafile backups available prior to target time to recover.
  • You must have all archivelog files available between previous backups & target time, in case of these files have been moved ( on usb disk or tape ) from default location then move it back to the same.

 

Consider following example, here we are performing DBPITR to the date time : 27-Jan-2014 15:00:00

RMAN> shutdown immediate;

RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 308981760 bytes
Fixed Size 2212896 bytes
Variable Size 192941024 bytes
Database Buffers 109051904 bytes
Redo Buffers 4775936 bytes

For DBPITR “set until time” clause used:
RMAN> RUN {
set until time “to_date(’27-JAN-2014 15:00:00′,’DD-MON-YYYY HH24:MI:SS’)”;
restore database;
recover database;
alter database open resetlogs; }

executing command: SET until clause

Starting restore at 28-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/app/oracle/oradata/
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/data/user1.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_reco
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_24/o1_mf_nnndf_TAG20140124T065819_9g43rd8w_.bkp tag=TAG20140124T065819
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:23
Finished restore at 28-JAN-14

Starting recover at 28-JAN-14
using channel ORA_DISK_1

starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /home/oracle/arch/1_9_837331111.dbf
archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/arch/1_1_837675750.dbf
archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/arch/1_2_837676382.dbf
archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/arch/1_3_837676382.dbf
archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/arch/1_4_837676382.dbf
archived log for thread 1 with sequence 5 is already on disk as file /home/oracle/arch/1_5_837676382.dbf
archived log for thread 1 with sequence 6 is already on disk as file /home/oracle/arch/1_6_837676382.dbf
archived log for thread 1 with sequence 7 is already on disk as file /home/oracle/arch/1_7_837676382.dbf
archived log for thread 1 with sequence 8 is already on disk as file /home/oracle/arch/1_8_837676382.dbf
archived log file name=/home/oracle/arch/1_9_837331111.dbf thread=1 sequence=9
archived log file name=/home/oracle/arch/1_1_837675750.dbf thread=1 sequence=1
archived log file name=/home/oracle/arch/1_1_837676382.dbf thread=1 sequence=1
archived log file name=/home/oracle/arch/1_2_837676382.dbf thread=1 sequence=2
archived log file name=/home/oracle/arch/1_3_837676382.dbf thread=1 sequence=3
archived log file name=/home/oracle/arch/1_4_837676382.dbf thread=1 sequence=4
archived log file name=/home/oracle/arch/1_5_837676382.dbf thread=1 sequence=5
archived log file name=/home/oracle/arch/1_6_837676382.dbf thread=1 sequence=6
archived log file name=/home/oracle/arch/1_7_837676382.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:01:07
Finished recover at 28-JAN-14

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

// DBPITR has been completed successfully.

 

Alternatively, if you are aware of exact SCN, log sequence number or restore point then you can also use following RMAN command to recover target database.

RUN { 
set until scn 1200;
… }

OR
RUN { 
set until sequence 65;
… }

OR
RUN {
set until restore point <Restore_point_name>;
… }

After this activity ( resetlogs ), please consider full database RMAN backup because this is incomplete recovery.

************************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

7 thoughts on “Point in time recovery using RMAN”

  1. Hi above posting is very use full , but i have a doubt how the rman know where is your database full database and your archive logs …, do we need to catalog before running that resotre & recover script…can u add those steps will be more clear.

    Thanks
    Satish

    • Very nice question Satish!
      I have already mentioned it in the Pre-requisites section, where all the required files needed for recovery should available on desired location.

    • Very nice question Satish!
      I have already mentioned it in the Pre-requisites section, where all the required files needed for recovery should available on the desired location.

  2. i used the SET UNTIL TIME “to_date(’27-JAN-2014 15:00:00′,’DD-MON-YYYY HH24:MI:SS’)”;

    but it recovered until 15:09:00

    what was the issue

  3. Hi

    Thanks for sharing this article for public users.
    I have a doubt. Database is up and running
    we had a backup full backup done at 10.00 p.m
    I am performing PITR till 9.45 p.m

    After performing PITR, if any archives in FRA location, Will Oracle apply or ignore. ?

    run
    {
    SET UNTIL TIME “to_date(‘2020-02-20 21:45:00′,’yyyy-mm-dd:hh24:mi:ss’)”;
    restore database;
    recover database;
    }

    Kindy update.

    Thanks

  4. is there a way of restoring the database to a point in time without deleting the datafilies and using just archivelogs and controlfile?

Leave a Reply to Satish KrishnaCancel reply