7.5 Reversing Database Changes with Flashback Database

If your database was previously configured for flashback logging as described in "Setup and Maintenance for Oracle Flashback Database", then you can use the FLASHBACK DATABASE command to return your database contents to points in time within the flashback window. You can also use FLASHBACK DATABASE to return to any guaranteed restore point you previously defined using the commands in "Creating Normal and Guaranteed Restore Points".

This section explains the common scenarios for using Flashback Database to reverse unwanted changes to your database. It contains the following topics:

Performing Flashback Database: Scenario
Performing Flashback Database to a Guaranteed Restore Point
Performing Flashback Database to Undo an OPEN RESETLOGS

Note:

If you do not have a guaranteed restore point defined, verify that the target SCN is within the flashback window, the range of SCNs for which you can use FLASHBACK DATABASE. See "Determining the Current Window for Flashback Database" for details on determining the flashback window, and "Listing Restore Points" for details on determining whether there is a guaranteed restore point you can use in recovery.

If the flashback window does not extend far enough back into the past to reach the desired target time and you do not have a guaranteed restore point at the desired time, you can achieve similar results by using database point-in-time recovery, as described in "Performing Database Point-In-Time Recovery".

7.5.1 Performing Flashback Database: Scenario

This section presents a basic outline of the process for performing Flashback Database in almost all cases, specifying the desired target point in time using a time expression, the name of a normal or guaranteed restore point, or an SCN.

To perform a Flashback Database operation in RMAN, use the following steps:

Determine the desired SCN, restore point or point in time for the FLASHBACK DATABASE command.

Note:

If you plan to specify the target using an SCN, and you want to return the database to a state with changes that were abandoned using a previous point-in-time recovery or Flashback Database operation, then see "Flashback Database and Ambiguous SCNs Across Incarnations" for special considerations.
You can use the method in "Performing Flashback Database to a Guaranteed Restore Point"if your goal is to flash back to a guaranteed restore point.
If the goal is to return the database to the point in time immediately before the most recent OPEN RESETLOGS operation, use the steps in "Performing Flashback Database to Undo an OPEN RESETLOGS".

Start RMAN and connect to the target database. For example:
```
rman TARGET /
```
Shut down the database cleanly, and ensure that it is not opened by any instance. Then mount it:
```
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
```

Repeat the query from"Determining the Current Window for Flashback Database". Some flashback logging data is generated when the database is shut down. It is possible that your target SCN is no longer reachable, if some flashback database logs were deleted due to space pressure in the flash recovery area in response to that logging.

Note:

If you attempt FLASHBACK DATABASE and your target SCN is now outside the flashback window, then FLASHBACK DATABASE will fail with an ORA-38729 error. In such a case, however, your database will not be changed.

During Flashback Database, RMAN may need to restore some archived redo logs from backup. If the backups are on tape and you have not configured the necessary channels to access the SBT device, then use a RUN block around the FLASHBACK DATABASE command and issue ALLOCATE CHANNEL commands as needed to allow RMAN to retrieve these logs from disk or tape.

Run the RMAN FLASHBACK DATABASE command, specifying the target time using one of the forms of the command shown in the following examples:

RMAN> FLASHBACK DATABASE TO SCN 46963;

RMAN> FLASHBACK DATABASE 
  TO RESTORE POINT BEFORE_CHANGES;

RMAN> FLASHBACK DATABASE TO TIME   
     "TO_DATE('09/20/00','MM/DD/YY')";

When the FLASHBACK DATABASE command completes, the database is left mounted and recovered to the specified point in time.

You can verify that you have returned the database to the desired state, by opening the database read-only and performing some queries to inspect the database contents.

RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';

7.5.1.1 Options After a Successful Flashback Database Operation

If you are satisfied with the state of the database after the Flashback Database operation, you have two choices:

Make the database available for updates by performing an OPEN RESETLOGS operation:

RMAN> ALTER DATABASE OPEN RESETLOGS;

Note:

Once you perform this OPEN RESETLOGS operation, all changes to the database after the target SCN for FLASHBACK DATABASE are abandoned. However, you can use the method in "Flashback Database To The Right of Open Resetlogs: Example" to return the database to that range of SCNs while they remain in the flashback window.

Use Oracle export utilities (Original Export or Data Pump Export) to export the objects whose state was corrupted. Then, recover the database to the present time:
```
RMAN>  RECOVER DATABASE;
```
This step undoes the effect of the Flashback Database, by re-applying all changes in the redo logs to the database, returning it to the most recent SCN.

After re-opening the database read-write, you can import the exported objects using the import utility corresponding to the export utility used earlier (Original Import or Data Pump Import).

7.5.1.2 Options After Flashback Database to the Wrong Time

If, after investigating the state of your database, you find that you used the wrong restore point, time or SCN for Flashback Database, then you have several options:

If your chosen target time was not far enough in the past, then you can use another FLASHBACK DATABASE command to rewind the database further in time.

RMAN> FLASHBACK DATABASE TO SCN 42963;  #earlier than current SCN

If you chose a target SCN that is too far in the past, then you can mount the database and use RECOVER DATABASE UNTIL to wind the database forward in time to the desired SCN:

RMAN> RECOVER DATABASE UNTIL SCN 56963; #later than current SCN

If you want to completely undo the effect of the FLASBACK DATABASE command, you can perform complete recovery of the database by using the RECOVER DATABASE command without an UNTIL clause or SET UNTIL command:
```
RMAN>  RECOVER DATABASE;
```
This re-applies all changes to the database, returning it to the most recent SCN.

7.5.1.3 Flashback Database and Ambiguous SCNs Across Incarnations

In databases that have previously undergone point-in-time recovery or Flashback Database, an SCN can be an ambiguous method of designating a point in time.

The effect of Flashback Database or DBPITR followed by an OPEN RESETLOGS is to return the database to a previous SCN, and to abandon changes after that point. Therefore, some SCNs after that point can refer either to changes that were abandoned or changes in the current history of the database.

By default, an SCN used as an argument for the FLASHBACK DATABASE command is assumed to refer to a point in time in the current incarnation path, that is, to an incarnation that was not abandoned after some previous DBPITR or Flashback Database. If this is the goal, then you can use the procedure in this section with an SCN to specify the target for Flashback Database.

In cases when you want to use Flashback Database to reach a point in time corresponding to changes that were abandoned after a previous Flashback Database or DBPITR, you can use the method described in "Flashback Database To The Right of Open Resetlogs: Example".

7.5.2 Performing Flashback Database to a Guaranteed Restore Point

You can list the available guaranteed restore points using the V$RESTORE_POINT view, as follows:

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
        GUARANTEE_FLASHBACK_DATABASE
        FROM V$RESTORE_POINT
      WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
 
NAME                   SCN TIME                  DATABASE_INCARNATION# GUA
--------------- ---------- --------------------- --------------------- ---
BEFORE_CHANGES     5753126 04-MAR-05 12.39.45 AM                     2 YES

Having identified the restore point to use, mount the database and run the FLASHBACK DATABASE command, using the restore point. For example:

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_CHANGES';

When the command completes, you may open the database read-only and inspect the effects of the operation, and if satisfied, open the database with the RESETLOGS option.

7.5.3 Performing Flashback Database to Undo an OPEN RESETLOGS

The basic procedure for using Flashback Database to reverse an unwanted OPEN RESETLOGS is very similar to the general case described in "Performing Flashback Database: Scenario".

However, rather than specifying a particular SCN or point in time for the FLASHBACK DATABASE command, use FLASHBACK DATABASE TO BEFORE RESETLOGS, as in the following example.

Before performing the flashback, verify that the beginning of the flashback window is earlier than the time of the most recent OPEN RESETLOGS.

sql> select resetlogs_change# from v$database;
sql> 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.

Shut down the database, mount it, and re-check the flashback window. If the resetlogs SCN is still within the flashback window, then use this form of the FLASHBACK DATABASE command:

RMAN> FLASHBACK DATABASE TO BEFORE RESETLOGS;

As with other uses of FLASHBACK DATABASE, if the target SCN is prior to 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 last SCN before the OPEN RESETLOGS in the previous incarnation.

You can open the database read-only and perform queries to make sure the data is in the desired state. To make the database available for updates again, use ALTER DATABASE OPEN RESETLOGS command.

7.5.3.1 Flashback Database Across OPEN RESETLOGS With Standby Databases

Support for Flashback Database across OPEN RESETLOGS enables several applications of Flashback Database with standby databases. These include:

Flashback to undo logical standby switchovers, in which the database reverts to its role (primary or standby) at the target time for the Flashback Database operation
Undo of a physical standby activation, so that you can temporarily activate a physical standby database, use it for testing or reporting purposes, then use Flashback Database to return it to its role as a physical standby
Ongoing use of a clone or standby database for testing, without requiring the use of storage snapshots.

See Oracle Data Guard Concepts and Administration for details on these advanced applications of Flashback Database with Data Guard.

7.5.4 Flashback Database To The Right of Open Resetlogs: Example

In some cases, you may need to return the database to a point in time in the parent incarnation, later than the SCN of the OPEN RESETLOGS at which the current incarnation path branched from the old incarnation. These points, which correspond to abandoned changes in the parent incarnation, can be described as being "to the right" of the last OPEN RESETLOGS, with reference to an incarnation diagram such as Figure 7-1, "Database Incarnation History With Multiple Resetlogs". For example, in the diagram, the database might be in incarnation 3, and you might need to return to the abandoned SCN 1500 in incarnation 1.

You can use the RMAN RESET DATABASE TO INCARNATION command to specify the current incarnation referred to by the SCN to use with Flashback Datbase.

The process is as follows:

Verify that the flashback logs contain enough information to flash back to that SCN:
```
sql> select oldest_flashback_scn from v$flashback_database_log;
```
Determine the target incarnation number for the flashback, that is, the incarnation key for the parent incarnation:
```
SQL> select prior_incarnation# from v$database_incarnation where status = 'CURRENT';
```

In RMAN, shut down the database, then mount it:

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;

Set the database incarnation to the parent incarnation:
```
RMAN> RESET DATABASE TO INCARNATION 1;
```
Run the FLASHBACK DATABASE command:
```
RMAN> FLASHBACK DATABASE TO SCN 1500;
```

Once the flashback is complete, you can verify the results, and if successful, open the database with RESETLOGS.