Oracle flashback database enables you to wind your entire database backward in time, reversing the effects of unwanted database changes within a given time window. It does not require you to restore datafiles from backup.
- Flashback Database uses its own logging mechanism, called flashback logs which are stored in the flash recovery area.
- Oracle flashback is alternative and faster than Point-in-Time Recovery (DBPITR).
- Oracle Database also supports restore points. A restore point is an alias corresponding to a system change number (SCN).
- To enable Flashback Database, you set up a flash recovery area, and set a flashback retention target, to specify how far back into the past you want to be able to restore your database with Flashback Database
Flashback Database uses flashback logs to access past versions of data blocks, as well as some information from the archived redo log. You can configure to generate flashback logs to repair your database. Flashback logs cannot be backed up outside the flash recovery area.
Flashback Database Window
The range of SCNs for which there is currently enough flashback log data to support the FLASHBACK DATABASE command is called the flashback database window.
- Flashback Database can only undo changes to a datafile made by an Oracle database. It cannot be used to repair media failures, or to recover from accidential deletion of datafiles.
- If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.
- Database must be running in ARCHIVELOG mode
- You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
- For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.
Flashback Writer (RVWR)
The background process RVWR writes flashback data to flashback database logs in the flash recovery area. If RVWR encounters an I/O error, the following behavior is expected:
- If there are any guaranteed restore points defined, the instance will crash when RVWR encounters I/O errors.
- If no guaranteed restore points are defined, the instance will not crash when RVWR encounters I/O errors. There are 2 cases:
- On a primary database, Oracle automatically disables flashback database while the database is open. All existing transactions and queries will proceed unaffected. This behavior is expected for both single instance and RAC.
- On a physical or logical standby, RVWR will appear to be hung, retrying the I/O periodically. This may eventually hang the logical standby or the managed recovery of physical standby. (Oracle does not crash the standby instance because it does not want to crash the primary database in turn in max protection mode.) To resolve the hang, a DBA can either perform a SHUTDOWN ABORT or issue ALTER DATABASE FLASHBACK OFF.
Oracle Flashback Database Logs Operations
Before issuing oracle FLASHBACK DATABASE command you must enable Flashback logs. Following operation can be performed using flashback database logs.
- Enable Flashback Logs for Flashback Database
- Disable flashback logs for specific tablespaces
- Enable flashback logs for specific tablespaces
- Disable flashback logs for Entire Database
- Disk Requirements for Flashback Logs
1. Enable Flashback Logs for Flashback Database
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
-- Optionally, set the length of desired flashback window in minutes:
-- BY DEFAULT DB_FLASHBACK_RETENTION_TARGET IS SET TO one day (1440 minutes)
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; -- 3 days
-- Enable Flashback Database on whole database:
SQL> ALTER DATABASE FLASHBACK ON;
2. Disable flashback logs for specific tablespaces:
By default, flashback logs are generated for all permanent tablespaces.
--Disabling flashback logging for specific tablespaces:
SQL> ALTER TABLESPACE tbs_3 FLASHBACK OFF;
3. Enable flashback logs for specific tablespaces:
--re-enable flashback logging for a tablespace later:
SQL> ALTER TABLESPACE tbs_3 FLASHBACK ON;
Note that if you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK DATABASE.
4. Disable flashback logs for Entire Database:
--Disable flashback logging for entire database:
SQL> ALTER DATABASE FLASHBACK OFF;
You can also enable flashback logging on a standby database. Enabling Flashback Database on a standby database enables you to perform Flashback Database on the standby database.
5. Disk Requirements for Flashback Logs
After you have enabled logging for Flashback Database and set a flashback retention target, allow the database to run under a normal workload for a while, to generate a representative sample of flashback logs. Then run the following query:
SQL> SELECT ESTIMATED_FLASHBACK_SIZE
The result is an estimate of the disk space needed to meet the current flashback retention target, based on the database workload since Flashback Database was enabled. Add the amount of disk space specified in $FLASHBACK_DATABASE_LOG.ESTIMATED_FLASHBACK_SIZE to your flash recovery area size, to hold the expected database flashback logs.
- You cannot manage the flashback logs in the flash recovery area directly
- BACKUP RECOVERY AREA does not include the flashback logs when backing up flash recovery area contents to tape.
- If the flash recovery area is full, then an archived redo log may be automatically deleted by the flash recovery area to make space for other files. In such a case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.
Oracle Flashback Database Operations
You can use the FLASHBACK DATABASE command to return your database contents to points in time within the flashback window. You can also use FLASHBACK DATABASE to return to any guaranteed restore point you previously defined
Note: If you do not have a guaranteed restore point defined, verify that the target SCN is within the flashback window, the range of SCNs for which you can use FLASHBACK DATABASE.
If the flashback window does not extend far enough back into the past to reach the desired target time and you do not have a guaranteed restore point at the desired time
Following operations can be perform using Flashback Database.
- Flashback Database to desired target time
- Flashback Database to Guaranteed Restore Point
1. Flashback Database to desired target time
Setp -1 Determine desired target time.
Determine the desired SCN, restore point or point in time for the FLASHBACK DATABASE command.
Determine Current Window
When flashback logging is enabled, the earliest SCN in the flashback database window can be determined by querying.
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
If the results of this query regularly indicate that the flashback database window does not satisfy the flashback retention target, you may need to increase your flash recovery area to accomodate more flashback logs.
Determine current SCN
This view indicates how much flashback log data is available for your database. The most recent SCN that can be reached with Flashback Database is the current SCN of the database. This query returns the current SCN:
SQL>SELECT CURRENT_SCN FROM V$DATABASE;
Setp -2 Shut down and mount the database.
rman TARGET /
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
Step -3 Run RMAN FLASHBACK DATABASE command
#Specify the target time using one of the forms
RMAN> FLASHBACK DATABASE TO SCN 46963;
RMAN> FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;
RMAN> FLASHBACK DATABASE TO TIME "TO_DATE('09/20/00','MM/DD/YY')";
Step -4 Verify flashback database
Verify that you have returned the database to the desired state, by opening the database read-only and performing some queries to inspect the database contents.
RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';
Successful Flashback Database.
If you are satisfied with the state of the database after the Flashback Database operation, you have two choices:
Make the database available for updates by performing an OPEN RESETLOGS operation:
RMAN> ALTER DATABASE OPEN RESETLOGS;
Use Oracle export utilities to export the objects whose state was corrupted. Then, recover the database to the present time:
This step undoes the effect of the Flashback Database, by re-applying all changes in the redo logs to the database, returning it to the most recent SCN.
RMAN> RECOVER DATABASE;
After re-opening the database read-write, you can import the exported objects using the import utility
Flashback Database to the Wrong Time
If, after investigating the state of your database, you find that you used the wrong restore point, time or SCN for Flashback Database, then you have several options:
If your chosen target time was not far enough in the past, then you can use another FLASHBACK DATABASE command to rewind the database further in time.
RMAN> FLASHBACK DATABASE TO SCN 42963; #earlier than current SCN
If you chose a target SCN that is too far in the past, then you can mount the database and use RECOVER DATABASE UNTIL to wind the database forward in time to the desired SCN:
RMAN> RECOVER DATABASE UNTIL SCN 56963; #later than current SCN
If you want to completely undo the effect of the FLASBACK DATABASE command, you can perform complete recovery of the database by using the RECOVER DATABASE command without an UNTIL clause or SET UNTIL command:
RMAN> RECOVER DATABASE;
This re-applies all changes to the database, returning it to the most recent SCN.
2. Flashback Database to a Guaranteed Restore Point
You can list the available guaranteed restore points using the V$RESTORE_POINT view, as follows:
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
NAME SCN TIME DATABASE_INCARNATION# GUA
--------------- ---------- --------------------- --------------------- ---
BEFORE_CHANGES 5753126 04-MAR-05 12.39.45 AM 2 YES
Having identified the restore point to use, mount the database and run the FLASHBACK DATABASE command, using the restore point. For example:
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_CHANGES';
When the command completes, you may open the database read-only and inspect the effects of the operation, and if satisfied, open the database with the RESETLOGS option