Example: Recovering an Entire Database to a Specified Point-in-Time

This example demonstrates how to perform point-in-time recovery (PITR) for a protected database. PITR may be required to revert the protected database to a prior date to recover from user errors (accidentally dropping tables or updating the wrong tables), media failure, or a failed database upgrade. You need to restore the control file only if there has been a structural change to the control file (such as creating or dropping tablespaces). Use the SET UNTIL clause to specify the time, SCN, or log sequence to which the protected database must be recovered.

If Flashback Database is enabled for the protected database, you can also use this feature to recover to a prior point-in-time.

See Also:

Oracle Database Backup and Recovery User's Guide
Oracle Database Backup and Recovery Reference

To restore and recover the entire protected database, including the control file, to a specific point-in-time:

Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
Use RMAN to connect to the protected database as TARGET and the Recovery Appliance catalog as CATALOG.

See Also:

"Connecting to the Protected Database and Recovery Appliance Using CLI"

Determine the point-in-time to which the protected database must be recovered. You can use an SCN, a time, or log sequence number to specify the point-in-time.

Use the following query to translate between timestamp and SCN:

SQL> set linesize 222
SQL> select name, current_scn, scn_to_timestamp(current_scn) "Time" 
     from v$database;

NAME        CURRENT_SCN   TIME
---------   -----------   ------------------------------------
ORA121      122019556     22-APR-14 12.30.15.000000000 PM

If the protected database is not available, you can query the Recovery Appliance catalog views to obtain the SCN number. You must provide the range date and time for your recovery window and the db_unique_name of the protected database. The following query (sample output included) is run when connected to the Recovery Appliance catalog:

SELECT a.db_key,
a.db_name,
a.sequence#,
a.first_change#,
a.next_change#,
a.completion_time
FROM rc_archived_log a, db b
WHERE b.reg_db_unique_name = 'PTDB2' AND a.db_key = db.db_key 
    AND to_date('16-Jul-2014 06:55:23','DD-Mon-YYYY HH24:MI:SS') BETWEEN       
          a.first_time AND a.next_time;

DB_KEY DB_NAME   SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#   COMPLETION_TIME
------ -------- ---------- ------------- ------------   ---------------
24201  PTDB2      9911      288402086    288430116     14/07/2014 5:27:49 PM

The FIRST_CHANGE# is the first SCN number in the archive redo log. The NEXT_CHANGE# is the first SCN of the next archive redo log in this thread.

Restore and recover the control file and the protected database.

STARTUP NOMOUNT;
RUN 
{
   SET UNTIL TIME "TO_DATE('2014-14-07:17:27:49','yyyy-dd-mm:hh24:mi:ss')";
   RESTORE CONTROLFILE;
   ALTER DATABASE MOUNT;
   RESTORE DATABASE;
   RECOVER DATABASE;
   ALTER DATABASE OPEN RESETLOGS;
}