Before Oracle 10g, if the database suffered some logical data corruptions or user errors, your choice would be Point-in-time recovery. That means restoring datafiles from backup and after that using archived redo logs to applying changes. In short, it was a costly and time-consuming process.
Flashback Database is another flashback technique that can be used as an alternative to perform incomplete recovery. It enables you to return the entire database to a specified previous point in time in the recent past. This operation has the same result as the RMAN Point-in-time recovery, but it is much faster because it does not require restoring all databases’ datafiles from backup and requires applying fewer changes from the archived redo logs. So it is the size of the error that matters [number of changes you want to undo] not the size of database.
Flashback database uses Flashback logs stored in the Flash/Fast Recovery Area [FRA]. These logs are used to perform Flashback database operation. A FRA must be configured for database because database cannot create Flashback logs anywhere else. Flashback logs are generated by database at a regular interval and are not archived, multiplexed or backed up to the disk.
Flashback Database is only helps you to recover from logical corruption or user errors [dropped schemas, a table is truncated or a transaction is performed partially changes] that affected the entire database. In case of physical media corruption or loss of data files, tradition complete recovery is required.
Flashback database is not enabled by default, when enabled image of the modified blocks are copied from db buffer cache to new memory area within SGA called flashback buffer. RVWR – Recovery Writer process is used to copies modified blocks from the flashback buffer to flashback database logs. Unlike redo log which logs all the changes in the database, Flashback Database logs contain a complete “before” image of physical database blocks. But not every change is logged as this would be the overhead in terms of memory usage and the amount of extra disk I/O required, so only as many as blocks are copied to flashback buffer such that the performance of database is not impacted with this.
A user must have a SYSDBA privilege to perform flashback database operation and FRA must have been configured for the database.
Configuring the Flashback Database
First step to ensure that database is in ARCHIVELOG mode. If not then it must be switched to ARCHIVELOG mode. You can use one of the following SQL to check database log mode.
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination e:\app\Madhurendra\arch\O11R2 Oldest online log sequence 26 Next log sequence to archive 28 Current log sequence 28 OR SQL> SELECT log_mode FROM v$database; LOG_MODE ------------- ARCHIVELOG
If your database is in NOARCHIVE log mode, you can perform following steps to bring db to ARCHIVELOG mode.
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN;
Now configure Flash/Fast Recovery Area for database, if not already configured.
Use DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST initialization parameter in init.ora file to configure a flash recovery area.
You can also use ALTER SYSTEM statement to configure FRA if you do not want to specify in init.ora file
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=8G; SQL> ALTER SYSTEM SET db_recovery_file_dest = <specify location for the FRA>;
In my case
SQL> show parameter DB_RECOVERY_FILE_DEST NAME TYPE VALUE --------------------------- ----------- ------------------------------ db_recovery_file_dest string E:\app\Madhurendra\arch\O11R2 db_recovery_file_dest_size big integer 8G
Choose a Flashback Retention Time with specifying value for the DB_FLASHBACK_RETENTION_TARGET parameter.
SQL> ALTER SYSTEM SET db_flashback_retention_target=2880;
Keep in mind the duration we specify is in minute. In this case 2880 minutes [2 days]. This parameter specifies how far back you can rewind a database with Flashback Database.
Now shut down and restart the database in mount or mount exclusive mode, enable the Flashback Database feature and open the database with ALTER DATABASE OPEN command.
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE FLASHBACK ON; ALTER DATABASE OPEN;
Now check if Flashback Database feature is enabled or not by querying V$DATABASE view.
SQL> SELECT flashback_on FROM v$database; FLASHBACK_ON --------------- YES
If you do not want to perform these entire steps using SQL command line tool, use Oracle Enterprise Manager [OEM] Database Control to enable Flashback feature.
To place Database in ARCHIVELOG Mode using OEM 11g Database Control:
a. Click on the Availability tab on the OEM DB Control Home Page.
b. Click on Recovery Settings under the Setup which is under the Backup/Recovery section.
c. Now check the ARCHIVELOG Mode checkbox within the Media Recovery section to place the database in ARCHIVELOG mode.
d. Click the Apply button to save and Restart the database with specifying your Host and Database Credentials.
To Configure Fast/Flash Recovery Area:
a. Click on the Availability tab on Home page.
b. Click on Recovery Settings under the Setup which is under the Backup/Recovery section.
c. Enter a Flash Recovery Area Location (DB_RECOVERY_FILE_DEST) and Flash Recovery Area Size within the Flash Recovery section.
d. Check the Enable Flashback Database check-box to enable flashback logging and enter a value for Flashback Retention Time.
e. Click the Apply button to save and Restart the database with specifying your Host and Database Credentials.
USING FLASHBACK DATABASE
SQL> CREATE TABLE user_object_info AS SELECT object_id, object_name, object_type FROM user_objects; Table created. SQL> SELECT count(*) FROM user_object_info; COUNT(*) ---------- 39 SQL> SELECT current_scn FROM v$database; CURRENT_SCN -------------- 1362393 SQL> INSERT INTO user_object_info SELECT * FROM user_object_info; 39 rows created. . . . . . SQL> SELECT count(*) FROM user_object_info; COUNT(*) ---------- 4992 SQL> SELECT current_scn FROM v$database; CURRENT_SCN -------------- 1364231 SQL> TRUNCATE TABLE user_object_info; Table truncated. SQL> SELECT count(*) FROM user_object_info; COUNT(*) ---------- 0
After performing all these operations we want flashback our database to the SCN 1362393. This means flashing back to the time when user_object_info table held 39 rows.
First shutdown the database and restart in the mount mode. Database must be running in the mount mode in order to use FLASHBACK DATABASE.
SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP MOUNT; ORACLE instance started. Total System Global Area 644468736 bytes Fixed Size 1376520 bytes Variable Size 301993720 bytes Database Buffers 335544320 bytes Redo Buffers 5554176 bytes Database mounted. SQL> FLASHBACK DATABASE TO SCN 1362393; Flashback complete. SQL> ALTER DATABASE OPEN READ ONLY; Database altered. /*Open the Database in Read Only mode to ensure that you have flashed back the table to the correct SCN*/ SQL> SELECT count(*) FROM user_object_info; COUNT(*) ---------- 39
Once you are confirm that the corruption have been reversed, shutdown the database, start in Mount mode and open database with RESETLOGS option.
SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP MOUNT; ORACLE instance started. Total System Global Area 644468736 bytes Fixed Size 1376520 bytes Variable Size 301993720 bytes Database Buffers 335544320 bytes Redo Buffers 5554176 bytes Database mounted. SQL> ALTER DATABASE OPEN RESETLOGS; Database altered.
If you try open database with using RESETLOGS you will likely to encounter the following error message
SQL> ALTER DATABASE OPEN; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
You can disable FLASHBACK DATABASE feature by executing ALTER DATABASE FLASHBACK OFF. Shutdown the db, restart in mount or mount exclusive mode and enter the ALTER DATABASE FLASHBACK OFF command.
• FLASHBACK DATABASE feature requires database to running in ARCHIVE LOG mode.
• It cannot be used for physical media failure or in the case of data file deletion.
• It can be possible that you want to flashback database to a particular time and the nearest flashback logs were written at few minute before. In that case you have to apply changes from online redo files or archived redo logs to cover these time gaps.
• Not possible to flashback the database to an SCN prior to the earliest available SCN available in the flashback logs.
• FLASHBACK DATABASE operation can be performed on SQL* Plus as well as on RMAN with same syntax with minor changes.
• You can check the size of flashback buffer by querying the V$SGASTAT view:
SELECT * FROM v$sgastat WHERE name LIKE 'flashback%';
• Use V$FLASHBACK_DATABASE_LOG and V$FLASHBACK_DATABASE_STAT to monitor current flashback capability and estimate the space needed for and flashback logs the I/O overhead of logging flashback data respectively.
• This retention target specifies how far back you can rewind a database with Flashback Database. But Oracle does not guarantee that you can flash back your database to the time that set in the DB_FLASHBACK_RETENTION_TARGET parameter. It could be possible if FRA is running low on free space. In this case Oracle will remove some older flashback logs in order to make space for archived redo logs or backup related files.
You can find more information about Flashback Database in Oracle Database 11g Backup and Recovery guide.