Skip Headers
Oracle® Database Backup and Recovery Advanced User's Guide
10g Release 2 (10.2)

Part Number B14191-01
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
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

User-Managed Flashback Features of Oracle

Oracle's flashback features, which let you undo damage to your database after logical data corruption, include the following:

All of these operations are available within SQL*Plus, and none of them require the use of Recovery Manager. More details about using the flashback features of Oracle in data recovery situations are provided in Oracle Database Backup and Recovery Basics.

Performing Flashback Database with SQL*Plus

The SQL*Plus FLASHBACK DATABASE command performs the same function as the RMAN FLASHBACK DATABASE command: it returns the database to a prior state.

Note that using Flashback Database requires that you create a flash recovery area for your database and enable the collection of flashback logs. See Oracle Database Backup and Recovery Basics for more details about how the Flashback Database feature works, requirements for using Flashback Database , and how to enable collection of flashback logs required for Flashback Database. The requirements and preparations are the same whether you use RMAN or user-managed backup and recovery.

To perform the FLASHBACK DATABASE operation in SQL*Plus:

  1. Query the target database to determine the range of possible flashback SCNs. The following SQL*Plus queries show you the the latest and earliest SCN in the flashback window:

    SQL> SELECT CURRENT_SCN FROM V$DATABASE;
    
    SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME 
         FROM V$FLASHBACK_DATABASE_LOG;
    
    
  2. Use other flashback features if necessary, to identify the SCN or time of the unwanted changes to your database.

  3. Start SQL*Plus with administrator privileges, and run the FLASHBACK DATABASE statement to return the database to a prior TIMESTAMP or SCN. For example:

    FLASHBACK DATABASE TO SCN 46963;
    FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);
    FLASHBACK DATABASE TO TIMESTAMP timestamp'2002-11-05 14:00:00';
    FLASHBACK DATABASE 
      TO TIMESTAMP to_timestamp('2002-11-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');
    
    

    Open the database read-only to examine the results of the Flashback Database operation. When the operation completes, you can open the database read-only and perform some queries to make sure you have recovered the data you need. If you find that you need to perform Flashback Database again to a different target time, then use RECOVER DATABASE to return the database back to the present time, and then try another FLASHBACK DATABASE statement.

    If you are satisfied with the results of Flashback Database, then you can re-open your database with the RESETLOGS option. If appropriate, you can also use an Oracle export utililty like Data Pump Export to save lost data, use RECOVER DATABASE to return the database to the present, and re-import the lost object.