Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E26088-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

FLASHBACK DATABASE

Purpose

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.

See Also:

Oracle Database Backup and Recovery User's Guide for more information on FLASHBACK DATABASE

Prerequisites

You must have the SYSDBA system privilege. A fast recovery area must have been prepared for the database. The database must have been put in FLASHBACK mode with an ALTER DATABASE FLASHBACK ON statement unless you are flashing the database back to a guaranteed restore point. The database must be mounted but not open. In addition:

See Also:

Syntax

flashback_database::=

Description of flashback_database.gif follows
Description of the illustration flashback_database.gif

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 data files in the database to the SCN or time specified in this statement.

See Also:

Oracle Database Backup and Recovery User's Guide for more information on recovering data files

STANDBY

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 use FLASHBACK DATABASE on a standby database to achieve different delays

TO SCN Clause

Specify a system change number (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.

TO TIMESTAMP Clause

Specify a valid datetime expression.

You can represent the timestamp as an offset from a determinate value, such as SYSDATE, or as an absolute system timestamp.

TO RESTORE POINT Clause

Specify this clause to flash back the database to the specified restore point. If you have not enabled flashback database, then this is the only clause you can specify in this FLASHBACK DATABASE statement. If the database is not in FLASHBACK mode, as described in the "Prerequisites" section above, then this is the only clause you can specify for this statement.

RESETLOGS

Specify TO BEFORE RESETLOGS to flash the database back to just before the last resetlogs operation (ALTER DATABASE OPEN RESETLOGS).

See Also:

Oracle Database Backup and Recovery User's Guide for more information about this clause

Examples

Assuming that you have prepared a fast recovery area for the database and enabled media recovery, enable database FLASHBACK mode and open the database with the following statements:

STARTUP MOUNT 
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 
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1;