Flashback Database provides the following advantages in a Data Guard configuration:
- Provides an alternative to delaying the application of redo to protect against user errors or logical corruptions. By using Flashback Database in this context, standby databases are more closely synchronized with the primary database, thereby reducing failover and switchover times.
- Eliminates the need to completely re-create the original primary database after a failover. The failed primary database can be flashed back to a point in time before the failover and converted to be a standby database for the new primary database.
Flashback Database is used in a Data Guard configuration for the following features:
- Fast-start failover: You must enable Flashback Database and set up a fast recovery area on the primary database and the target standby database before enabling fast-start failover. (See the lesson entitled “Enabling Fast-Start Failover” for additional information.)
- Snapshot standby: To convert a physical standby database to a snapshot standby database, you must configure the Fast Recovery Area and size. If Flashback Database is not enabled it will be enabled when the physical standby database is converted to a is not enabled, it will be enabled when the physical standby database is converted to a snapshot standby database. (See the lesson titled “Creating and Managing a Snapshot Standby Database” for additional information.)
Overview of Flashback Database
With Flashback Database, you can quickly bring your database to an earlier point in time by undoing all changes that took place since that time. This operation is fast because you do not need to restore backups. You can use this feature to undo changes that resulted in logical data corruptions.
When you use Flashback Database, Oracle Database uses past block images to back out changes to the database. During normal database operation, Oracle Database occasionally logs these block images in flashback logs, which are written sequentially and are not archived. Oracle Database automatically creates, deletes, and resizes flashback logs in the fast recovery area. You need to be aware of flashback logs only for monitoring performance and deciding how much disk space to allocate to the fast recovery area for flashback logs and deciding how much disk space to allocate to the fast recovery area for flashback logs.
The time it takes to rewind a database with Flashback Database is proportional to how far back in time you need to go and the amount of database activity after the target time. The time it would take to restore and recover the whole database could be much longer. The before images in the flashback logs are used only to restore the database to a point in the past, and forward recovery is used to bring the database to a consistent state at some time in the past. Oracle Database returns data files to the previous point in time—but not auxiliary files such as initialization parameter files.
Flashback Database can also be used to complement Data Guard and Recovery Advisor and to synchronize cloned databases.
Configuring Flashback Database
The database can be open when you enable flashback. To enable flashback:
1. Configure the fast recovery area.
2. Set the retention target with the DB_FLASHBACK_RETENTION_TARGET initialization parameter.
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;
You can specify an upper limit, in minutes, on how far back you want to be able to flash back the database. The example uses 2,880 minutes, which is equivalent to two days. This parameter is only a target and does not provide any guarantee. Your flashback time interval depends on how much flashback data was kept in the fast recovery area.
3. Enable Flashback Database with the following command:
SQL> ALTER DATABASE FLASHBACK ON;
Before you can issue the command to enable Flashback Database, the database must be configured for archiving.
Determine whether Flashback Database is enabled with the following query:
SQL> SELECT flashback on FROM v$database;
Disable Flashback Database with the ALTER DATABASE FLASHBACK OFF command. As a result, all existing Flashback Database logs are deleted automatically.
Configuring Flashback Database by Using Enterprise Manager
1. From the database home page, select Availability > Backup and Recovery > Recovery Settings from the menu.
2. Ensure that your database is in ARCHIVELOG mode. If not, select ARCHIVELOG mode.
3. Review the fast recovery area location. The fast recovery area is a unified storage location for all recovery-related files and activities in an Oracle database. All files that are needed to completely recover a database from a media failure are part of the fast recovery area. The recovery-related files that can be created in the fast recovery area include archived redo log files, control files, backups created by Recovery Manager (RMAN), flashback logs, and the change tracking file. By allocating a storage location and unifying recovery-related files in a specific area, the Oracle database server relieves the database administrator from having to manage the disk files created by these components. If you want it in a different location, specify the location in the Fast Recovery Area Location field.
4. Set the fast recovery size in the Fast Recovery Area Size field.
5. Select Enable Flashback Database. You can also set the flashback retention time, and you can view important information regarding your flashback database window.
6. Scroll down to the bottom of the Recovery Settings page, and click Apply.
Using Flashback Database Instead of Apply Delay
As an alternative to the Apply Delay configuration option, you can use Flashback Database to protect against the application of corrupted or erroneous data to the standby database. The Apply Delay configuration uses the DELAY attribute of the LOG_ARCHIVE_DEST_n parameter so that redo data from the primary database is received on the standby database, but redo apply delays its application. The provides a window of time so that logical errors, such as an accidental row deletion that is committed on the primary database, can have the data exported from the standby database before the SQL command executes on the standby database.
Flashback Database can quickly and easily flashback a standby database to an arbitrary time in the past You can configure one standby database with Flashback Database to achieve the in the past. You can configure one standby database with Flashback Database to achieve the same benefit as multiple standby databases with different delays.
Using Flashback Database and Real-Time Apply
The Oracle Data Guard real-time apply feature reduces failover time. Flashback Database protects a physical standby database from logical data corruption and user error. The Data Guard broker automatically enables real-time apply.
Using Flashback Database After RESETLOGS
Physical Standby Configuration
For a physical standby database, it is possible that the Redo Apply service might not halt when it encounters the OPEN RESETLOGS command in the redo information. If the physical standby database’s SCN (system change number) is far enough behind the primary database’s SCN, the Redo Apply service can interpret the OPEN RESETLOGS command without stopping. This feature is known as “recovery through RESETLOGS.”
Use the following procedure to avoid re-creating a standby database after you issue an OPEN RESETLOGS command on the primary database and the managed recovery process is halted on the physical standby database:
1. On the primary database, determine an SCN that is at least two SCNs prior to the SCN when the OPEN RESETLOGS command was issued. This is necessary to enable the standby to recover properly through OPEN RESETLOGS. Use the following query to find the “before RESETLOGS” SCN:
SQL> SELECT TO_CHAR(resetlogs_change# - 2) FROM v$database;
2. On the standby database, obtain the current SCN by using the following query:
SQL> SELECT TO_CHAR(current_scn) FROM v$database;
3. Flash back the standby database to the “before RESETLOGS” SCN that you queried in step 1:
SQL> FLASHBACK STANDBY DATABASE TO SCN ;
4. Restart managed recovery on the standby database. The standby database will be ready to receive and apply logs from the primary database.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Logical Standby Configuration
For a logical standby database, it is possible that the SQL Apply service might not halt when it encounters the OPEN RESETLOGS command in the redo information. If the logical standby database’s SCN is far enough behind the primary database’s SCN, the SQL Apply service will be able to interpret the OPEN RESETLOGS command without stopping.
Use the following procedure to avoid re-creating a standby database after you perform an OPEN RESETLOGS on the primary database and the SQL Apply process has halted on the logical standby database:
1. On the primary database, determine an SCN that is at least two SCNs prior to the SCN when the OPEN RESETLOGS command was issued. This is necessary to enable the standby to recover properly through OPEN RESETLOGS. Use the following query to find the “before RESETLOGS” SCN:
SQL> SELECT TO_CHAR(resetlogs_change# - 2) FROM v$database;
2. On the standby database, obtain the current SCN by using the following query:
SQL> SELECT TO_CHAR(current_scn) FROM v$database;
3. Flash back the standby database to the “before RESETLOGS” SCN that you queried in step 1:
SQL> FLASHBACK STANDBY DATABASE TO SCN ;
4. Restart SQL Apply on the standby database. The standby database will be ready to receive and apply logs from the primary database.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Flashback Through Standby Database Role Transitions
Use Flashback Database to revert a database to a point in time before a physical standby database switchover or failover. The database role does not change when flashing back through a physical standby switchover, failover, or activation.
In a logical standby database, the FLASHBACK DATABASE command reverts the database to a system change number (SCN) or time stamp before a switchover or failover. For a logical standby database, the database reverts to its original role at the time of the flashback SCN or flashback time. You can also use Flashback Database to undo a physical standby activation.
Using Flashback Database After Failover
You invoke a failover operation when a catastrophic failure occurs on the primary database and there is no possibility of recovering the primary database in a timely manner. After a failover operation, the old standby database becomes the new primary database, and the old primary database is placed in the “needs reinstatement” state.
Use the Flashback Database feature to convert the old primary database into a new standby database without re-creating the database. You can flash back the old primary database so that it contains only those changes that are already applied to the old standby database. This enables you to convert the old primary database into a new standby database without restoring the old primary database.
The STANDBY_BECAME_PRIMARY_SCN column in V$DATABASE can provide the SCN number corresponding to the failover. The old primary database should use the FLASHBACK DATABASE TO SCN … command to rewind to this point. It can then be converted into a standby database with the SQL command ALTER DATABASE CONVERT TO PHYSICAL STANDBY.