Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
|
|
View PDF |
Oracle's flashback features, which let you undo damage to your database after logical data corruption, include the following:
Oracle Flashback Database, which returns your entire database to a previous state without requiring you to restore files from backup;
Oracle Flashback Table, which returns one or more tables to their contents at a previous time;
Oracle Flashback Drop, which undoes the effects of the DROP TABLE
operation;
Oracle Flashback Query, which is used to query the contents of the database at a past time;
Oracle Flashback Version Query, which lets you view past states of data;
Oracle Flashback Transaction Query, which is used to review transactions affecting a table over time.
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.
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:
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;
Use other flashback features if necessary, to identify the SCN or time of the unwanted changes to your database.
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.