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.

Technorati Tags: Oracle, Flashback, SQL*Plus

powered by performancing firefox