7.5 Reversing Database Changes with Flashback Database

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:

1. 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".

2. Start RMAN and connect to the target database. For example:

   rman TARGET /
   
   

3. Shut down the database cleanly, and ensure that it is not opened by any instance. Then mount it:

   RMAN> SHUTDOWN IMMEDIATE;
   RMAN> STARTUP MOUNT;
   
   

4. 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.

5. 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.

6. 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';

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

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

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.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.