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:
C:\RMAN TARGET SYS/[email protected] CATALOG RMAN/[email protected]

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.