Time-based restore and recovery are commonly used when you know the approximate date and time to which you want to recover your database.

For instance, you may know approximately the time you want to stop the recovery process, but not a particular SCN.

Log sequence-based and cancel-based recovery work well in situations in which you have missing or damaged log files. In such scenarios, you can recover only up to your last good archivelog file.

SCN-based recovery works well if you can pinpoint the SCN at which you want to stop the recovery process.

You can retrieve SCN information from views such as V$LOG and V$LOG_HISTORY.

You can also use tools such as LogMiner to retrieve the SCN of a particular SQL statement.

Restore point recoveries work only if you have established restore points. In these situations, you restore and recover up to the SCN associated with the specified restore point.

TSPITR is used in situations in which you need to restore and recover just a few tablespaces.

You can use RMAN to automate many of the tasks associated with this type of incomplete recovery.

Performing Time-Based Recovery

To restore and recover your database back to a point in time in the past, you can use either the UNTIL TIME clause of the RESTORE and RECOVER commands or the SET UNTIL TIME clause within a run{} block.

It is useful to have run{} blocks of code with the correct syntax available to replace a TIME to be able to perform the restores without having to search for the syntax.

Using these examples in the book and running test and practice restores will give you the blocks of code needed to have ready to use.

RMAN will restore and recover the database up to, but not including, the specified time. In other words, RMAN will restore any transactions committed prior to the time specified.

RMAN automatically stops the recovery process when it reaches the time you specified.

The default date format that RMAN expects is YYYY-MM-DD:HH24:MI:SS. However, it is recommended to use the TO_DATE function and specify a format mask.

This eliminates ambiguities with different national date formats and having to set the OS NLS_DATE_ FORMAT variable.

The following example specifies a time when issuing the restore and recover commands:

$ rman target / RMAN> startup mount;

RMAN> restore database until time

“to_date(’15-jun-2018 12:20:00′, ‘dd-mon-rrrr hh24:mi:ss’)”; RMAN> recover database until time

“to_date(’15-jun-2018 12:20:00′, ‘dd-mon-rrrr hh24:mi:ss’)”; RMAN> alter database open resetlogs;

If everything goes well, you should see output such as this:

Statement processed