Enabling Flashback Database

The following are the 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.

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

Start SQL*Plus and ensure that the database is mounted, but not open. For example:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Optionally, set the DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes:

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

By default DB_FLASHBACK_RETENTION_TARGET is set to one day (1440 minutes).

Note: DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST is set.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=536870912;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oradata/flashback_area';

Enable the Flashback Database feature for the whole database:

SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON
——————
YES

-- Turn off flashback, this can be executed when the database is in mount/open state
SQL> ALTER DATABASE FLASHBACK OFF;

Database altered.

-- prints current usage and limit set for flashback recovery, amount of space that can be recalimed and number of files
SELECT * FROM V$recovery_file_dest

-- Amount of space used for different types of files in flashback
SELECT * FROM V$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
———— —————— ————————- —————
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s