Oracle® Database SQL Reference 10g Release 1 (10.1) Part Number B10759-01 |
|
|
View PDF |
Use the FLASHBACK
DATABASE
statement to return the database to a past time or system change number (SCN). This statement provides a fast alternative to performing incomplete database recovery.
Following a FLASHBACK
DATABASE
operation, in order to have write access to the flashed back database, you must reopen it with an ALTER
DATABASE
OPEN
RESETLOGS
statement.
Note: You cannot Flashback the database beyond the last resetlogs operation (ALTER DATABASE OPEN RESETLOGS ). |
See Also: Oracle Database Backup and Recovery Advanced User's Guide for more information onFLASHBACK DATABASE |
Prerequisites
You must have the SYSDBA
system privilege. A flash recovery area must have been prepared for the database, and the database must have been put in FLASHBACK
mode with an ALTER
DATABASE
FLASHBACK
ON
statement. The database must be mounted in exclusive mode but not open.
See Also: Oracle Database Backup and Recovery Advanced User's Guide and theALTER DATABASE ... flashback_mode_clause for information on putting the database in FLASHBACK mode |
Syntax
Semantics
When you issue a FLASHBACK
DATABASE
statement, Oracle Database first verifies that all required archived and online redo logs are available. If they are available, then it reverts all currently online datafiles in the database to the SCN or time specified in this statement.
The amount of Flashback data retained in the database is controlled by the DB_FLASHBACK_RETENTION_TARGET
initialization parameter and the size of the flash recovery area. You can determine how far back you can Flashback the database by querying the V$FLASHBACK_DATABASE_LOG
view.
If insufficient data remains in the database to perform the Flashback, then you can use standard recovery procedures to recover the database to a past point in time.
If insufficient data remains for a set of datafiles, then the database returns an error. In this case, you can take those datafiles offline and reissue the statement to revert the remainder of the database. You can then attempt to recover the offline datafiles using standard recovery procedures.
See Also: Oracle Database Backup and Recovery Advanced User's Guide for more information on recovering datafiles |
Specify STANDBY
to revert the standby database to an earlier SCN or time. If the database is not a standby database, then the database returns an error. If you omit this clause, then database
can be either a primary or a standby database.
See Also: Oracle Data Guard Concepts and Administration for information on how you can useFLASHBACK DATABASE on a standby database to achieve different delays |
Specify a system change number (SCN):
TO SCN
reverts the database back to its state at the specified SCN.
TO BEFORE SCN
reverts the database back to its state at the system change number just preceding the specified SCN.
You can determine the current SCN by querying the CURRENT_SCN
column of the V$DATABASE
view. This in turn lets you save the SCN to a spool file, for example, before running a high-risk batch job.
Specify a valid datetime expression.
TO TIMESTAMP
reverts the database back to its state at the specified timestamp.
TO BEFORE TIMESTAMP
reverts the database back to its state one second before the specified timestamp.
You can represent the timestamp as an offset from a determinate value, such as SYSDATE
, or as an absolute system timestamp.
Examples
Assuming that you have prepared a flash recovery area for the database, enable database FLASHBACK
mode and open the database with the following statements:
STARTUP MOUNT EXCLUSIVE ALTER DATABASE FLASHBACK ON; ALTER DATABASE OPEN
With your database open for at least a day, you can flash back the database one day with the following statements:
SHUTDOWN DATABASE STARTUP MOUNT EXCLUSIVE FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1;