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:
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 useFLASHBACK
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".
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 attemptFLASHBACK
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';
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 thisOPEN
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).
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.
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".
See Also:
"Point-in-Time Recovery and Database Incarnations: Concepts" for useful background information about database incarnations, abandoned changes, and the effects ofOPEN
RESETLOGS
.Unlike SCNs, time expressions and restore points are not ambiguous. A time expression is always associated with the incarnation that was current at that time. A restore point is always associated with the current incarnation when it was created. This is true even for times and restore points that correspond to abandoned database incarnations. The database incarnation is automatically reset to the incarnation that was current at the specified time or when the restore point was created.
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.
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.
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.
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
.