Databases Reference
In-Depth Information
How It Works
Time-based restore and recovery is commonly used when you know approximately the date and time to which you
want to recover your database. For example, 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 where you have missing or damaged log
files. In such scenarios, you can recover only up to your last good archived redo log 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.
Tablespace point-in-time recovery is used in situations where you can restore and recover just a few tablespaces.
You can use RMAN to automate many of the tasks associated with this type of incomplete recovery.
Table point-in-time recovery may be appropriate if you accidentally dropped a table or erroneously deleted data.
See Chapter 13 for all options available with restoring a table back to a time in the past.
Flashing back your database works only if you have enabled the flashback database feature. DBAs often use this
feature in environments where the database needs to be rolled back to a baseline (like a test environment), or also in
production environments to capture the state of the database right before an application upgrade takes place. This
allows you to restore the database back to a point in time in the event the upgrade doesn't go as planned. Chapter 13
covers this topic in detail.
Performing Time-Based Recovery
Problem
You want to restore your database to a previous date and time.
Solution
You can restore your database to a previous time in one of two ways:
•
Specify the time as part of the restore and recover commands.
set until time
command, and then issue unqualified
restore
and
recove
r
commands.
The following example specifies a time when issuing the
restore
and
recover
commands:
•
Use the
$ rman target /
RMAN> startup mount;
RMAN> restore database until time
"to_date('28-aug-2012 08:10:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database until time
"to_date('28-aug-2012 08:10:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> alter database open resetlogs;
If everything went well, you should now see output similar to this:
Statement processed
