FLASHBACK : SETUP & MAINTENANCE

Limitations of Flashback Database

1. 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 a deletion of datafiles.
2. You cannot use Flashback Database to undo a shrink datafile operation.
3. 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.
4. When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and datafiles affected by the NOLOGGING operation. For example, if you perform a direct-path INSERT operation in NOLOGGING mode, and that operation runs from 9:00 to 9:15 on April 3, 2005, and you later need to use Flashback Database to return to the target time 09:07 on that date, the objects and datafiles updated by the direct-path INSERT may be left with block corruption after the Flashback Database operation completes. If possible, avoid using Flashback Database with a target time or SCN that coincides with a NOLOGGING operation. Also, perform a full or incremental backup of the affected datafiles immediately after any NOLOGGING operation to ensure recoverability to points in time after the operation. If you expect to use Flashback Database to return to a point in time during an operation such as a direct-path INSERT, consider performing the operation in LOGGING mode.

Requirements for Enabling Flashback Database

Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation. 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.

Enabling Logging for Flashback Database

To enable logging for Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement.

1. Start SQL*Plus and ensure that the database is mounted, but not open.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

2. Optionally, set theDB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes:
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
# 4320 = 3 days (By default DB_FLASHBACK_RETENTION_TARGET is set to one day (1440 minutes)).

3. Enable the Flashback Database feature for the whole database:
SQL> ALTER DATABASE FLASHBACK ON;

By default, flashback logs are generated for all permanent tablespaces. If you wish, you can reduce overhead by disabling flashback logging for specific tablespaces:
SQL> ALTER TABLESPACE tbs_3 FLASHBACK OFF;

You can re-enable flashback logging for a tablespace later with this command: SQL> ALTER TABLESPACE tbs_3 FLASHBACK ON;

Note: if you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK DATABASE.

You can disable flashback logging for the entire database with this command: SQL> ALTER DATABASE FLASHBACK OFF;

Creating Normal and Guaranteed Restore Points

To create normal or guaranteed restore points, use the CREATE RESTORE POINT statement in SQL*Plus, providing a name for the restore point and specifying whether it is to be a guaranteed restore point or a normal one (the default). The database can be open or mounted when creating restore points. If it is mounted, then it must have been shut down cleanly.
SQL> CREATE RESTORE POINT before_upgrade;
Restore point created.
SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
Restore point created.

Listing Restore Points

To see a list of the currently defined restore points, use the V$RESTORE_POINT control file view, by means of the following query:

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

You can also use the following query to view only the guaranteed restore points:

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

For normal restore points, STORAGE_SIZE is zero. For guaranteed restore points, STORAGE_SIZE indicates the amount of disk space in the flash recovery area used to retain logs required to guarantee FLASHBACK DATABASE to that restore point.

Dropping Restore Points

When you are satisfied that you do not need an existing restore point, or when you want to create a new restore point with the name of an existing restore point, you can drop the restore point, using the DROP RESTORE POINT SQL*Plus statement.

SQL> DROP RESTORE POINT before_app_upgrade;
Restore point dropped.

The same statement is used to drop both normal and guaranteed restore points. Guaranteed restore points never age out of the control file. They remain until they are explicitly dropped.

Monitoring Space Usage For Guaranteed Restore Points

When guaranteed restore points are defined on your database, you should monitor the amount of space used in your flash recovery area for files required to meet the guarantee. Use the query for viewing guaranteed restore points in "Listing Restore Points" and refer to the STORAGE_SIZE column to determine the space required for files related to each guaranteed restore point. To see the total usage of your flash recovery area, use V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE".

Sizing the Flash Recovery Area to Include Flashback Logs

When using Flashback Database, you must add extra space to the flash recovery area to hold the flashback logs, along with the other files stored in the flash recovery area. The setting of the DB_FLASHBACK_RETENTION_TARGET initialization parameter determines, indirectly, how much flashback log data the database retains. The size of flashback logs generated by the database for a given time period can vary considerably, however, depending on the specific database workload. If more blocks are affected by database updates during a given interval, then more disk space is used by the flashback log data generated for that interval.


Estimating Disk Space Requirements for Flashback Database Logs

The V$FLASHBACK_DATABASE_LOG view can help you estimate how much space to add to your flash recovery area 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.
SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

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.

Managing Space For Flashback Logs in the Flash Recovery Area

You cannot manage the flashback logs in the flash recovery area directly, other than by setting the flashback retention target or using guaranteed restore points. You can, however, manage flash recovery area space as a whole, in order to maximize space available for retention of flashback database logs.



Determining the Current Window for Flashback Database

When flashback logging is enabled, the earliest SCN in the flashback database window can be determined by querying
V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN and V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_TIME.

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;