Certain situations are suited for using point-in-time recovery or flashback features to return the database or database object to its state at a previous point in time.

Some typical situations include the following:

• A user error or corruption removes needed data or introduces corrupted data. For example, a user or DBA might erroneously delete or update the contents of one or more tables, drop database objects that are still needed during an update to an application, or run a large batch update that fails midway.

• A database upgrade fails or an upgrade script goes awry.

• A complete database recovery after a media failure cannot succeed because you do not have all of the needed redo logs or incremental backups.

Database point-in-time recovery (DBPITR) and Flashback features enable you to recover your database to a prior point in time.

DBPITR is the most basic solution to unwanted database changes. It is sometimes called incomplete recovery because it does not use all of the available redo or completely recover all changes to your database. In this case, you restore a whole database backup and then apply redo logs or incremental backups to re-create all changes up to a point in time before the unwanted change.

If unwanted database changes are extensive but confined to specific tablespaces, then you can use tablespace point-in-time recovery (TSPITR) to return these tablespaces to an earlier system change number while the unaffected tablespaces remain available.

If unwanted database changes are limited to specific tables or table partitions, then you can use a previously created RMAN backup to return only these objects to a point in time before the unwanted changes occurred.

Oracle Database also provides a set of features collectively known as Flashback Technology that supports viewing past states of data, and winding and rewinding data back and forth in time, without requiring the restore of the database from backup. Depending on the changes to your database, Flashback Technology can often reverse the unwanted changes more quickly and with less impact on database availability.

DBPITR works at the physical level to return the data files to their state at a target time in the past.

In an RMAN DBPITR operation, you specify a target SCN, log sequence, restore point, or time. RMAN restores the database from backups created before the target time, and then applies incremental backups and logs to recreate all changes between the time of the data file backups and the end point of recovery. When the end point is specified as an SCN, the database applies the redo logs and stops after each redo thread or the specified SCN, whichever occurs first. When the end point is specified as a time, the database internally determines a suitable SCN for the specified time and then recovers to this SCN.

If your backup strategy is properly designed and your database is running in ARCHIVELOG mode, then DBPITR is an option in nearly all circumstances. Given a target SCN, data files are restored from backup and recovered efficiently with no intervention from the user. Nevertheless, RMAN DBPITR has the following disadvantages:

• You cannot return selected objects to their earlier state, only the entire database.

• Your entire database is unavailable during the DBPITR.

• DBPITR can be time-consuming because RMAN must restore all data files. Also, RMAN may need to restore redo logs and incremental backups to recover the data files. If backups are on tape, then this process can take even longer.

To perform a Flashback Table operation, the table must be eligible to be flashed back and the user performing the operation must have the required privileges.

You must have the following privileges to use the Flashback Table feature:

• You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.

• You must have READ or SELECT, INSERT, DELETE, and ALTER privileges on the table.

• To flash back a table to a restore point, you must have the SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privilege or the SELECT_CATALOG_ROLE role.

For an object to be eligible to be flashed back, the following prerequisites must be met:

• The object must not be included the following categories: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.

• The structure of the table must not have been changed between the current time and the target flashback time.

  The following Data Definition Language (DDL) operations change the structure of a table: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (except adding a range partition).

• Row movement must be enabled on the table, which indicates that rowids change after the flashback occurs.

  This restriction exists because if rowids before the flashback were stored by the application, then there is no guarantee that the rowids correspond to the same rows after the flashback. If your application depends on rowids, then you cannot use Flashback Table.

• The undo data in the undo tablespace must extend far enough back in time to satisfy the flashback target time or SCN.

  The point to which you can perform Flashback Table is determined by the undo retention period, which is the minimal time for which undo data is kept before being recycled, and tablespace characteristics. The undo data contains information about data blocks before they were changed. The flashback operation uses undo to re-create the original data.

  To ensure that the undo information is retained for Flashback Table operations, Oracle suggests setting the UNDO_RETENTION parameter to 86400 seconds (24 hours) or greater for the undo tablespace.

To use the Flashback Table feature on one or more tables, use the FLASHBACK TABLE SQL statement with a target time or SCN.

1. Ensure that the prerequisites that are described in "Prerequisites for Flashback Table" are met.
2. (Optional) To keep database triggers enabled during the flashback table operation, see "Keeping Triggers Enabled During Flashback Table".
3. Connect SQL*Plus to the PDB that contains the table, as a common user or local user with the SYSDBA or SYSBACKUP privilege. Identify the current database (CDB) SCN.

   You cannot roll back a FLASHBACK TABLE statement, but you can issue another FLASHBACK TABLE statement and specify a time just before the current time. Therefore, it is advisable to record the current SCN.

4. Identify the time, SCN, or restore point to which you want to return the table.

   If you have created restore points, then you can list available restore points by executing the following query:

   SELECT NAME, SCN, TIME FROM V$RESTORE_POINT;
   

5. Ensure that enough undo data exists to rewind the table to the specified target.

   If the UNDO_RETENTION initialization parameter is set, and the undo retention guarantee is on, then you can use the following query to determine how long undo data is being retained:

   SELECT NAME, VALUE/60 MINUTES_RETAINED
   FROM   V$PARAMETER
   WHERE  NAME = 'undo_retention';
   

6. Ensure that row movement is enabled for all objects that you are rewinding with Flashback Table.

   You can enable row movement for a table with the following SQL statement:

   ALTER TABLE hr.temp_employees ENABLE ROW MOVEMENT;
   

7. Determine whether the table that you intend to flash back has dependencies on other tables. If dependencies exist, then decide whether to flash back these tables as well.

   You can issue the following SQL query to determine the dependencies, where schema_name is the schema for the table to be flashed back and table_name is the name of the table:

   SELECT other.owner, other.table_name
   FROM   sys.all_constraints this, sys.all_constraints other
   WHERE  this.owner = schema_name
   AND    this.table_name = table_name
   AND    this.r_owner = other.owner
   AND    this.r_constraint_name = other.constraint_name
   AND    this.constraint_type='R';
   

8. Execute a FLASHBACK TABLE statement for the objects to flash back.

   The following SQL statement returns the hr.temp_employees table to the restore point named temp_employees_update:

   FLASHBACK TABLE hr.temp_employees
     TO RESTORE POINT temp_employees_update;
   

   The following SQL statement rewinds the hr.temp_employees table to its state when the database was at the time specified by the SCN:

   FLASHBACK TABLE hr.temp_employees
     TO SCN 123456;
   

   As shown in the following example, you can also specify the target point in time with TO_TIMESTAMP:

   FLASHBACK TABLE hr.temp_employees
     TO TIMESTAMP TO_TIMESTAMP('2013-10-17 09:30:00', 'YYYY-MM-DD HH:MI:SS');

   Note:

   The mapping of time stamps to SCNs is not always exact. When you use time stamps with the FLASHBACK TABLE statement, the time to which the table is flashed back can vary by up to approximately 3 seconds of the time specified for TO_TIMESTAMP. If an exact point in time is required, then use an SCN rather than a time.

9. Optionally, query the table to check the data.

Flashback Drop reverses the effects of a DROP TABLE operation. Flashback Drop is faster than other recovery mechanisms that can be used in this situation, such as point-in-time recovery, and does not lead to downtime or loss of recent transactions.

When you drop a table, the database does not immediately remove the space associated with the table. Instead, the table is renamed and, along with any associated objects, placed in the recycle bin. System-generated recycle bin object names are unique. You can query objects in the recycle bin, just as you can query other objects.

A flashback operation retrieves the table from the recycle bin. When retrieving dropped tables, you can specify either the original user-specified name of the table or the system-generated name.

When you drop a table, the table and all of its dependent objects go into the recycle bin together. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together. When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. Oracle Database retrieves all indexes defined on the table except for bitmap join indexes, and all triggers and constraints defined on the table except for referential integrity constraints that reference other tables.

Some dependent objects such as indexes may possibly have been reclaimed because of space pressure. In such cases, the reclaimed dependent objects are not retrievable from the recycle bin.

Prerequisites must be met before you perform a Flashback Drop operation.

The user privileges required for the operations related to Flashback Drop and the recycle bin are as follows:

• DROP

  Any user with DROP privileges over an object can drop the object, placing it in the recycle bin.

• FLASHBACK TABLE ... TO BEFORE DROP

  Privileges for this statement are tied to the privileges for DROP. That is, any user who can drop an object can perform Flashback Drop to retrieve the dropped object from the recycle bin.

• PURGE

  Privileges for a purge of the recycle bin are tied to the DROP privileges. Any user having DROP TABLE, DROP ANY TABLE, or PURGE DBA_RECYCLE_BIN privileges can purge the objects from the recycle bin.

• READ or SELECT and FLASHBACK for objects in the Recycle Bin

  Users must have the READ or SELECT and FLASHBACK privileges over an object in the recycle bin to query the object in the recycle bin. Any users who had the READ or SELECT privilege over an object before it was dropped continue to have the READ or SELECT privilege over the object in the recycle bin. Users must have FLASHBACK privilege to query any object in the recycle bin, because these are objects from a past state of the database.

Objects must meet the following prerequisites to be eligible for retrieval from the recycle bin:

• The recycle bin is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments (objects) is in a dictionary-managed tablespace, then these objects are protected by the recycle bin.

• Tables that have fine-grained auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.

• Partitioned index-organized tables are not protected by the recycle bin.

• The table must not have been purged, either by a user or by Oracle Database during a space reclamation operation.

Use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover objects from the recycle bin. You can specify either the name of the table in the recycle bin or the original table name.

1. Ensure that the prerequisites described in "Prerequisites of Flashback Drop" are met.
2. Connect SQL*Plus to the PDB as a common user or local user with SYSDBA or SYSBACKUP privilege and obtain the name of the dropped table in the recycle bin.

   You can use the SQL*Plus command SHOW RECYCLEBIN as follows:

   SHOW RECYCLEBIN;
   
   ORIGINAL NAME    RECYCLEBIN NAME                   TYPE         DROP TIME
   ---------------- --------------------------------- ------------ -------------
   EMPLOYEE_DEMO    BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0  TABLE    2019-04-11:17:08:54
   

   The ORIGINAL NAME column shows the original name of the object, whereas the RECYCLEBIN NAME column shows the name of the object as it exists in the bin.

   Alternatively, you can query USER_RECYCLEBIN or DBA_RECYCLEBIN to obtain the table name. The following example queries the RECYCLEBIN view to determine the original names of dropped objects:

   SELECT object_name AS recycle_name, original_name, type 
   FROM   recyclebin;
   
   RECYCLE_NAME                      ORIGINAL_NAME          TYPE
   --------------------------------  ---------------------  ----------
   BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0  EMPLOYEE_DEMO          TABLE
   BIN$JKS983293M1dsab4gsz/I249==$0  I_EMP_DEMO             INDEX
   

   If you plan to manually restore original names for dependent objects, then ensure that you make note of each dependent object's system-generated recycle bin name before you restore the table.

   Note:

   Object views such as DBA_TABLES do not display the recycle bin objects.

3. Optionally, query the table in the recycle bin.

   You must use the recycle bin name of the object in your query rather than the object's original name. The following example queries the table with the recycle bin name of BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0:

   SELECT * FROM  "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0";
   

   Quotation marks are required because of the special characters in the recycle bin name.

   Note:

   If you have the necessary privileges, then you can also use Flashback Query on tables in the recycle bin, but only by using the recycle bin name rather than the original table name. You cannot use Data Manipulation Language (DML) or DDL statements on objects in the recycle bin.

4. Retrieve the dropped table.

   Use the FLASHBACK TABLE ... TO BEFORE DROP statement. The following example restores the BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 table, changes its name back to hr.employee_demo, and purges its entry from the recycle bin:

   FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;
   

   The table name is enclosed in quotation marks because of the possibility of special characters appearing in the recycle bin object names.

   Alternatively, you can use the original name of the table:

   FLASHBACK TABLE HR.EMPLOYEE_DEMO TO BEFORE DROP;
   

   You can also assign a new name to the restored table by specifying the RENAME TO clause. For example:

   FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP 
     RENAME TO hr.emp_demo;
   

5. Optionally, verify that all dependent objects retained their system-generated recycle bin names.

   The following query determines the names of the indexes of the retrieved hr.employee_demo table:

   SELECT INDEX_NAME 
   FROM   USER_INDEXES 
   WHERE  TABLE_NAME = 'EMPLOYEE_DEMO';
   
   INDEX_NAME
   ------------------------------
   BIN$JKS983293M1dsab4gsz/I249==$0
   

6. Optionally, rename the retrieved indexes to their original names.

   The following statement renames the index to its original name of i_emp_demo:

   ALTER INDEX "BIN$JKS983293M1dsab4gsz/I249==$0" RENAME TO I_EMP_DEMO;
   

7. If the retrieved table had referential constraints before it was placed in the recycle bin, then re-create them.

   This step must be performed manually because the recycle bin does not preserve referential constraints on a table.

Flashback Database works by undoing changes to the data files that exist at the moment that you run the command. Prerequisites must be met to perform a Flashback Database operation.

To use the FLASHBACK DATABASE command to return your database contents to points in time within the flashback window, your database must have been previously configured for flashback logging. To return the database to a guaranteed restore point, you must have previously defined a guaranteed restore point.

Note the following important prerequisites:

• No current data files are lost or damaged. You can only use FLASHBACK DATABASE to rewind changes to a data file made by an Oracle database, not to repair media failures.

• You are not trying to recover from accidental deletion of data files or undo a change to the database name.

• You are not trying to use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file. If the database control file is restored from backup or re-created, then all accumulated flashback log information is discarded.

• You are not trying to use FLASHBACK DATABASE to undo a compatibility change.

Data dictionary views contain information that is used to monitor flashback database.

SQL> SELECT sofar, totalwork, units FROM v$session_longops WHERE opname = 'Flashback Database';
 
SOFAR  TOTALWORK  UNITS
-----  ---------- --------------------------------
   17          60 Megabytes

Certain prerequisites must be met to perform database point-in-time recovery (DBPITR).

This includes the following:

• Your database must be running in ARCHIVELOG mode.

• You must have backups of all data files from before the target SCN for DBPITR and archived logs for the period between the SCN of the backups and the target SCN.

• If the backups were created using transparent encryption, and if a password-protected software keystore was used, then the keystore password must be provided before the restore operation is performed. Use the SET command with the DECRYPTION WALLET OPEN IDENTIFIED BY option to specify the password that must be used to open the password-based keystore.

  If a user with the SYSBACKUP privilege is performing the recovery, and a password-protected software keystore is used, grant the SYSKM privilege to this user.

• If the backups were created using password-mode encryption, then you must provide the password used to decrypt backups before you run the RESTORE and RECOVER commands. Use the SET DECRYPTION IDENTIFIED BY command to specify the password used to decrypt the backups.

Use the RESTORE and RECOVER commands to perform point-in-time recovery for a whole CDB.

1. Ensure that the prerequisites described in "Prerequisites of Database Point-in-Time Recovery" are met.
2. Determine the time, SCN, restore point, or log sequence that ends recovery.

   You can use the Flashback Query features to help you identify when the logical corruption occurred. If you have a flashback data archive enabled for a table, then you can query data that existed far in the past.

   You can also use the alert log to try to determine the time of the event from which you must recover.

   Alternatively, you can use a SQL query to determine the log sequence number that contains the target SCN and then recover through this log. For example, run the following query to list the logs in the current database incarnation (sample output included):

   SELECT RECID, STAMP, THREAD#, SEQUENCE#, FIRST_CHANGE#
          FIRST_TIME, NEXT_CHANGE#
   FROM   V$ARCHIVED_LOG
   WHERE  RESETLOGS_CHANGE# =
          ( SELECT RESETLOGS_CHANGE#
            FROM   V$DATABASE_INCARNATION
            WHERE  STATUS = 'CURRENT');
   
   RECID      STAMP      THREAD#    SEQUENCE#  FIRST_CHAN FIRST_TIM NEXT_CHANG
   ---------- ---------- ---------- ---------- ---------- --------- ----------
            1  344890611          1          1      20037 24-SEP-13      20043
            2  344890615          1          2      20043 24-SEP-13      20045
            3  344890618          1          3      20045 24-SEP-13      20046
   

   For example, if you discover that a user accidentally dropped a tablespace at 9:02 a.m., then you can recover to 9 a.m., just before the drop occurred. You lose all changes to the database made after this time.

3. If you are using a target time expression instead of a target SCN, then ensure that the time format environment variables are appropriate before invoking RMAN.

   The following are sample Globalization Support settings:

   NLS_LANG = american_america.us7ascii
   NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"
   

4. Connect RMAN to the root as a common user with the SYSBACKUP or SYSDBA privilege, as described in "Connecting as Target to the Root". If applicable, connect to a recovery catalog.
5. Bring the CDB to a mounted state.

   SHUTDOWN IMMEDIATE;
   STARTUP MOUNT;
   

6. Perform the following operations within a RUN block:
   1. For DBPITR, use SET UNTIL to specify the target time, SCN, or log sequence number, or use SET TO to specify a restore point. If specifying a time, then use the date format specified in the NLS_LANG and NLS_DATE_FORMAT environment variables.
   2. If automatic channels are not configured, then manually allocate disk and tape channels as needed.
   3. Restore and recover the CDB.

   The following example performs DBPITR on the target CDB until SCN 1000:

   RUN
   { 
     SET UNTIL SCN 1000;    
     RESTORE DATABASE;
     RECOVER DATABASE;
   }
   

   As shown in the following examples, you can also use time expressions, restore points, or log sequence numbers to specify the SET UNTIL time:

   SET UNTIL TIME 'Nov 15 2013 09:00:00';
   SET UNTIL SEQUENCE 9923;  
   SET TO RESTORE POINT before_update;
   

   If the operation completes without errors, then DBPITR has succeeded.

7. Perform either of the following mutually exclusive actions:

   • Open your CDB for read/write, abandoning all changes after the target SCN. In this case, you must shut down the CDB, mount it, and then execute the following command:

     ALTER DATABASE OPEN RESETLOGS;
     

     The OPEN RESETLOGS operation fails if a data file is offline unless the data file went offline normally or is read-only. You can bring files in read-only or offline normal tablespaces online after the RESETLOGS because they do not need any redo.

   • Export one or more objects from your CDB with Data Pump Export. You can then recover the CDB to the current point in time and reimport the exported objects, thus returning these objects to their state before the unwanted change without abandoning all other changes.

8. Open all the PDBs.

   PDBs are not opened when the CDB is opened. The following command, when connected to the root, opens all the PDBs.

   ALTER PLUGGABLE DATABASE ALL OPEN;
   

   You can open each PDB separately.

When you recover one or more PDBs to a specified point-in-time, the remaining PDBs in the CDB are not affected and they can be open and operational.

1. Ensure that the prerequisites described in "Prerequisites of Database Point-in-Time Recovery" are met.
2. Determine the time, SCN, restore point, or log sequence that ends recovery.
   Use Flashback Query or the alert log to determine when the logical corruption occurred. Or, use a SQL query to determine the log sequence number that contains the target SCN and then recover through this log.
3. If you are using a target time expression instead of a target SCN, then ensure that the time format environment variables are appropriate before invoking RMAN.

   The following are sample Globalization Support settings:

   NLS_LANG = american_america.us7ascii
   NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"
   

4. Connect RMAN to the root as a common user with the SYSDBA or SYSBACKUP privilege, as described in "Making Database Connections with RMAN". If applicable, connect to a recovery catalog.
5. Close the PDB that is being recovered. The other PDBs and the CDB can remain open.

   ALTER PLUGGABLE DATABASE pdb1 CLOSE;
   

6. Perform the following operations within a RUN block:
   1. For DBPITR, use SET UNTIL to specify the target time, SCN, or log sequence number, or use SET TO to specify a restore point. If specifying a time, then use the date format specified in the NLS_LANG and NLS_DATE_FORMAT environment variables.
   2. If automatic channels are not configured, then manually allocate disk and tape channels as needed.
   3. Restore and recover the CDB.

   The following example performs DBPITR on the PDB my_pdb until SCN 1000:

   RUN
   { 
     SET UNTIL SCN 1000;    
     RESTORE PLUGGABLE DATABASE my_pdb;
     RECOVER PLUGGABLE DATABASE my_pdb;
   }
   

   If the operation completes without errors, then DBPITR has succeeded.

7. Open the PDB abandoning all changes after the target SCN.

   The following example opens the PDB named my_pdb.

   ALTER PLUGGABLE DATABASE my_pdb OPEN RESETLOGS;
   

ALTER PLUGGABLE DATABASE pdb5 CLOSE;
run 
{
   SET UNTIL SCN 1066;
   RESTORE PLUGGABLE DATABASE pdb5;
   RECOVER PLUGGABLE DATABASE pdb5;
}
ALTER PLUGGABLE DATABASE pdb5 OPEN RESETLOGS;

Flashback Database can be used to undo an OPEN RESETLOGS operation.

1. Connect SQL*Plus to the target database and verify that the beginning of the flashback window is earlier than the time of the most recent OPEN RESETLOGS.

   Run the following queries:

   SELECT RESETLOGS_CHANGE# FROM V$DATABASE;
   SELECT OLDEST_FLASHBACK_SCN FROM V$FLASHBACK_DATABASE_LOG;
   

   If V$DATABASE.RESETLOGS_CHANGE# is greater than V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN, then you can use Flashback Database to reverse the OPEN RESETLOGS operation.

2. Shut down the database, mount it, and recheck the flashback window. If the resetlogs SCN is still within the flashback window, then proceed to the next step.
3. Connect RMAN to the target database, as described in "Making Database Connections with RMAN".
4. Perform a flashback to the SCN immediately before the RESETLOGS.

   Use the following form of the FLASHBACK DATABASE command:

   FLASHBACK DATABASE TO BEFORE RESETLOGS;
   

   As with other uses of FLASHBACK DATABASE, if the target SCN is before the beginning of the flashback database window, an error is returned and the database is not modified. If the command completes successfully, then the database is left mounted and recovered to the most recent SCN before the OPEN RESETLOGS operation in the previous incarnation.

5. Open the database read-only in SQL*Plus and perform queries as needed to ensure that the effects of the logical corruption have been reversed.

   Open the database read-only as follows:

   ALTER DATABASE OPEN READ ONLY;
   

6. To make the database available for updates again, shut down the database, mount it, and then execute the following command:

   ALTER DATABASE OPEN RESETLOGS;

You can use Flashback Database to rewind a database to an abandoned database incarnation.

1. Use SQL*Plus to connect to the target database and verify that the flashback logs contain enough information to flash back to the SCN.

   For example, execute the following query:

   SELECT OLDEST_FLASHBACK_SCN FROM V$FLASHBACK_DATABASE_LOG;
   

2. Determine the target incarnation number for the Flashback Database operation, that is, the incarnation key for the parent incarnation.

   For example, execute the following query:

   SELECT PRIOR_INCARNATION# 
   FROM   V$DATABASE_INCARNATION 
   WHERE  STATUS = 'CURRENT';
   

3. Start RMAN and connect to the target database, as described in "Making Database Connections with RMAN".
4. Shut down the database, and then mount it as follows:

   SHUTDOWN IMMEDIATE;
   STARTUP MOUNT;
   

5. Set the database incarnation to the parent incarnation.

   For example, use the following command to return to incarnation 1:

   RESET DATABASE TO INCARNATION 1;
   

6. Run the FLASHBACK DATABASE command, specifying the target SCN.

   For example, use the following command to rewind the database to SCN 1500:

   FLASHBACK DATABASE TO SCN 1500;
   

7. Open the database read-only in SQL*Plus and perform queries as needed to ensure that the effects of the logical corruption have been reversed.

   Open the database read-only as follows:

   ALTER DATABASE OPEN READ ONLY;
   

8. To make the database available for updates again, shut down the database, mount it, and then execute the following command:

   ALTER DATABASE OPEN RESETLOGS;

To perform DPITR to an noncurrent database incarnation, you must explicitly execute the RESET DATABASE to reset the database to the incarnation that was current at the target SCN. You must also restore a control file from the database incarnation containing the target SCN.

1. Ensure that the prerequisites described in "Prerequisites of Database Point-in-Time Recovery" are met.
2. Start RMAN and connect to a target database and recovery catalog, if required, as described in "Making Database Connections with RMAN".
3. Determine which database incarnation was current at the time of the backup.

   Use LIST INCARNATION to find the primary key of the incarnation that was current at the target time:

   LIST INCARNATION OF DATABASE trgt;
   
   List of Database Incarnations
   DB Key  Inc Key   DB Name   DB ID       STATUS     Reset SCN    Reset Time
   ------- -------   -------   ------      -------    ----------   ----------
   1       2         TRGT      1224038686  PARENT     1            02-OCT-13
   1       582       TRGT      1224038686  CURRENT    59727        10-OCT-13
   

   Look at the Reset SCN and Reset Time columns to identify the correct incarnation, and note the incarnation key in the Inc Key column. In this example, the backup was made 2 October 2013. In this case, the incarnation key value is 2.

4. Ensure that the database is started but not mounted.

   STARTUP FORCE NOMOUNT;
   

5. Reset the target database to the incarnation obtained in Step 2.

   In this example, specify the incarnation current at the time of the backup of 2 October. Use the value from the Inc Key column to identify the incarnation.

   RESET DATABASE TO INCARNATION 2;
   

6. Restore and recover the database, performing the following actions in the RUN command:

   • Set the end time for recovery to the time just before the loss of the data.

   • Allocate any channels required that are not configured.

   • Restore the control file from the October 2 backup and mount it.

   • Restore the data files and recover the database. Use the RECOVER DATABASE ... UNTIL command to perform DBPITR, bringing the database to the target time of 7:55 a.m. on October 8, just before the data was lost.

   The following example shows all of the steps required in this case:

   RUN
   {
     SET UNTIL TIME 'Oct 8 2013 07:55:00'; 
     RESTORE CONTROLFILE;
     # without recovery catalog, use RESTORE CONTROLFILE FROM AUTOBACKUP
     ALTER DATABASE MOUNT; 
     RESTORE DATABASE;
     RECOVER DATABASE;
   }
   ALTER DATABASE OPEN RESETLOGS;

   See Also:

   Oracle Database Backup and Recovery Reference for details about the RESET DATABASE command