This is an overview of some of the flashback features of Oracle 11g: Flashback Query, Flashback Database, Flashback Drop and Flashback Table. These features are all similar in that they allow you to access data as it existed in the past, yet they are very different in their purpose and execution.

Flashback Query - this allows you to query a table as it was at a given time in the past. This can be helpful in various troubleshooting scenarios and also used to recover one or more rows rows to a past point in time. In order for this to work, the data must still be in the UNDO tablespace so you need to size it accordingly and have your undo_retention set appropriately.  Otherwise, you will receive “ORA-01555: snapshot too old”

Use the “AS OF TIMESTAMP” syntax to execute a Flashback Query

SELECT * FROM EMPLOYEE AS OF TIMESTAMP
TO_TIMESTAMP('2012-03-19 10:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE fname=’MIKE’ and lname=’DEAN’;

In case you need to restore the record, you combine the above with an insert as follows:

INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2012-03-19 10:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE fname=’MIKE’ and lname=’DEAN’);

Flashback Database - Flashback Database allows you to restore your database to a prior point in time by undoing all the changes that have taken place since that time. This is different than a Point-In-Time Recovery in that you don’t restore a backup and then roll forward, instead you “rewind” the database by applying flashback logs. This operation can be very fast, because you do not need to restore the backups. This in turn results in much less downtime following data corruption or human error. Keep in mind that this is NOT a replacement for regular backups (preferably rman hot backups) as it DOES NOT protect you in the case of media failure, filesystem corruption, accidental deletion or anything else that effects the physical data files. It is used to undo logical data corruption, such as from a failed application upgrade. It can also be very useful in Test databases where you want to test a process repeatedly, starting with an identical data set each time.  There is overhead associated with generating and managing the flashback logs but for many databases, this is well worth it.

In order to enable Flashback Database, put the database is mount mode and run “alter database flashback on”. The flashback logs will be written to your FRA as defined by the db_recovery_file_dest parameter. The db_flashback_retention_target parameter defines how long you wish to save your flashback logs.

Here is an example of enabling Flashback Database, creating a restore point and then flashing back to that restore point:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.

SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

SQL> create restore point flashback_test;
Restore point created.
SQL> select name,time from v$restore_point;
NAME                 TIME
-------------------- ---------------------------------------------------------------------------
FLASHBACK_TEST       17-APR-12 09.17.46.000000000 AM

SQL> create table flashback_test as select * from all_objects;
Table created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.
SQL> flashback database to restore point flashback_test;
Flashback complete.

NOTE: You can also flashback to a given time or SCN as well as to a restore point

SQL> alter database open resetlogs;
Database altered.
SQL> select * from flashback_test;
select * from flashback_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

The table I created after the restore point is no longer there; the database was “rewound” to a prior point in time.  Related to this is the guaranteed restore point, which can be created even if Flashback Database has not been enabled. 

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> create restore point restore_point_test guarantee flashback database;
Restore point created.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
SQL> shutdown immediate

Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.
SQL> flashback database to restore point restore_point_test;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> drop restore point restore_point_test;

Once you are confident that you won’t be rolling back, make sure to drop the restore point or you will continue to generate flashback logs. 

Flashback Drop - allows you to recover a non-SYS table that was dropped by making use of the Recycle Bin.  This is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before it was dropped. You can view your dropped tables by querying the new RECYCLEBIN view. Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. Any dependent sources such as procedures are invalidated; the triggers and indexes of the original table are instead placed on the renamed table.  You can bypass the Recycle Bin by dropping the table with the “purge” option.

SQL> drop table drop_test;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
DROP_TEST        BIN$veGmdD1Yy9/gQKjAYIkPIg==$0 TABLE        2012-04-17:10:21:10
SQL> flashback table drop_test to before drop;
Flashback complete.

SQL> drop table drop_test purge;
Table dropped.
SQL> show recyclebin;
SQL> flashback table drop_test to before drop;
flashback table drop_test to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

Flashback Table - This can be used to restore data in a table to past point in time, based upon time or SCN.  This is similar to the Flashback Query in that it relies on the data being present in the UNDO tablespace. 

SQL> create table flashback_test (col1 number);
Table created.
SQL> alter table flashback_test enable row movement;
Table altered.
--insert a bunch of rows
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1048161
SQL> select count(*) from flashback_test;
  COUNT(*)
----------
       192

SQL> delete from flashback_test;
192 rows deleted.
SQL> commit;
Commit complete.
SQL> flashback table flashback_test to scn 1048161;
Flashback complete.
SQL> select count(*) from flashback_test;
  COUNT(*)
----------
       192