Flashback

Overview

Oracle's Flashback technologies protect a database against human error (accidental deletion of records etc.). When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a Recycle Bin.

The Flashback Table and Flashback Query options rely on the undo retention. As a result, using Flashback to remedy a data loss event is an option only for a finite period of time. Educate users about the Flashback features and this limitation. The default retention is for 24 hours. This can be adjusted via the DB_FLASHBACK_RETENTION_TARGET parameter.

Prerequisites

• Your database must be in Archive Log Mode.
• The Fast Recovery Area (FRA) must be enabled. If you create your database using the DBCA it will walk you through the required entries for setting up a FRA.
• You must be using the Enterprise Edition to configure the FRA. To confirm your version: SELECT * FROM v$version;
• You must be using Automatic Undo Management to use the Flashback Table feature.
• For any table to use Flashback Table also requires that Row Movement be enabled on it.
• All flashback options require that the Recycle Bin is enabled (RECYCLEBIN=ON). It is on by default.

Manually Enabling the Fast Recovery Area (FRA)

To enable the FRA two parameters must be set:

DB_RECOVERY_FILE_DEST_SIZE
It is the disk limit, which is the amount of space the FRA is permitted to use. The minimum size of the FRA should be at least large enough to contain archive logs that have not been backed up.

DB_RECOVERY_FILE_DEST
This initialization parameter is a valid destination to create the FRA. The destination can be defined as a directory, file system, or ASM disk group.

Oracle University training recommends for small databaes the FRA be located in a dedicated directory under the ORACLE_BASE.

SQL> ALTER SYSTEM SET db_recovery_file_dest_size=2147483648             SCOPE=SPFILE;
     ALTER SYSTEM SET db_recovery_file_dest='C:\app\oracle\oradata\fbr' SCOPE=SPFILE;

Enable Flashback

 ALTER DATABASE FLASHBACK ON;

 SELECT flashback_on FROM v$database;

Configuration Comments

The DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST. You cannot use the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters to specify redo log archive destinations. You must always use the LOG_ARCHIVE_DEST_n parameters in case you have configured flash recovery area. LOG_ARCHIVE_DEST_10 is implicitly set to USE_DB_RECOVERY_FILE_DEST if you create a recovery area and do not set any other local archiving destinations.

The Flash Recovery Area cannot be stored on a raw file system. For RAC the location of Flash Recovery Area must be on a cluster file system, ASM or a shared directory configured through NFS. The location and disk quota must be the same on all instances.

Flashback Dropped Table

FLASHBACK TABLE [schema.]TableName TO BEFORE DROP [RENAME TO TableName];

SQL> FLASHBACK TABLE emp.deptno TO BEFORE DROP;
SQL> FLASHBACK TABLE emp.deptno TO BEFORE DROP RENAME TO deptno_orig;

Flashback Table

FLASHBACK TABLE TO TIMESTAMP [ENABLE | DISABLE TRIGGERS];
FLASHBACK TABLE TO SCN [ENABLE | DISABLE TRIGGERS];

FLASHBACK TABLE hr.emp TO TIMESTAMP('01-OCT-11 9:00:00','DD-MON-YY HH24:MI:SS');
FLASHBACK TABLE hr.emp TO SCN 407545;

Flashback Query

--Display Past Data

SELECT * FROM emp
AS OF TIMESTAMP TIMESTAMP('01-OCT-13 9:00:00','DD-MON-YY HH24:MI:SS')
WHERE name = 'SCOTT';

--Get Past Data

INSERT INTO emp
   (SELECT * 
    FROM emp AS OF TIMESTAMP('01-OCT-13 9:00:00','DD-MON-YY HH24:MI:SS')
    WHERE name = 'SCOTT'
   );

<- SpecialTopics