Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 30 April 2012

RMAN-Time-Based Recovery

Time-based recovery allows the DBA to recover to a desired point of time. This situation is most likely to occur if archive logfiles or redo logfiles needed for recovery are lost or damaged and cannot be restored. In this situation you would apply all logs until a point in time specified by the UNTIL TIME clause of the RECOVER command.
- If the database is still open, shut down the database using the SHUTDOWN command with the   ABORT option.
- Make a full backup of the database including all datafiles, a control file, and the parameter files in case an error is made during the recovery.
- Restore backups of all datafiles. Make sure the backups were taken before the point in time you are going to recover to. Any datafiles added after the point in time you are recovering to should not be restored. They will not be used in the recovery and will have to be recreated after recovery is complete. Any data in the datafiles created after the point of recovery will be lost.
Make sure read-only tablespace are offline before you start recovery so recovery does not try to update the datafile headers.
- Make sure all the datafiles in the database are online. You can check the status of datafiles by querying the V$DATAFILE view.

Now Connect the rman with target database and run the following command:

RMAN> shutdown Abort; 
RMAN> startup mount; 
RMAN> sql "alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''"; 
RMAN> set until time '15-Mar-2012 14:11:00'; 
RMAN> restore database; 
RMAN> recover database; 
RMAN> alter database open resetlogs;

Oracle will automatically stop recovery when the time specified in the RECOVER command has been reached. Oracle will respond with a recovery successful message.
- You should use the RESETLOGS option if you used a backup of the control file in recovery or the recovery was incomplete. Use the NORESETLOGS option if the recovery was complete. If you are using a standby database and must reset the logs, the standby database will need to be re-created. You can check the ALERT file to see if your incomplete recovery was actually a complete recovery. If the recovery was a complete recovery, the message in the ALERT file is as follows: “RESETLOGS after complete recovery through change scn” and If the recovery was incomplete, the following message is recorded: “RESETLOGS after incomplete recovery UNTIL CHANGE scn”
- After opening the database using the RESETLOGS option, perform a normal shutdown and a full database backup. If you do not do this, any changes made after the recovery and before the next full backup are unrecoverable. If you did not reset the logs, the database is still recoverable.


  1. Hi Shahid,

    I have read your blogs and its too good..
    Here i would like to ask one question about my RMAN recovery.
    I am preparing script for daily RMAN Recovery for "To be DR server" with following command in "rman_restore.rcv"
    2> run {
    3> alter database mount;
    4> catalog start with '/u11/rman/lawprod/database' noprompt; <====Here /u11 is a common NFS mount where backups are stored)
    5> #set until sequence 409;
    6> set until TIME "TO_DATE('2014-09-02:09:30:00','yyyy-dd-mm:hh24:mi:ss')";
    9> ALTER DATABASE OPEN resetlogs;
    10> }
    If I get sequence no. from production (list backup) and use it as (squence+1) in the above script it works but
    for point in time it gives error "RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time".
    I guess the reset logs may not be present.

    Please advice on how to avoid this situation and also let me how to get the script going with point in time recovery with generalised parameter
    in ".rcv"