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.
Your database must be in Archive Log Mode. The Flash Recovery Area (FRA) must be enabled. 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. Adjust UNDO_RETENTION to a value that provides a good balance between your ability to use Flashback and your resources. If you create your database using the DBCA it will walk you through the required entries for setting up a FRA.
Manually Enabling the Flash Recovery Area (FRA)
To enable the FRA two parameters must be set:
It is the disk limit, which is the amount of space the flash recovery area is permitted to use. The minimum size of the Flash Recovery Area should be at least large enough to contain archive logs that have not been backed up.
This initialization parameter is a valid destination to create the Flash Recovery Area. The destination can be defined as a directory, file system, or ASM disk group. Oracle University training recommends the FBR area 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;
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 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;
--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' );