Oracle Flashback

In order to utilize Flashback you'll need to put your database in ARCHIVELOG mode. Then you can set the DB_FLASHBACK_RETENTION_TARGET parameter that defines the period of time which we want to retain flashback logs, and finally turn Flashback on with an ALTER DATABASE statement. Lets look at the setup.


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

Total System Global Area  184549376 bytes
Fixed Size                  1300928 bytes
Variable Size             157820480 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320;
System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=536870912;
System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST='/u02/fra';
System altered.

SQL> alter database flashback on;
Database altered.

SQL> alter database open;
Database altered.

Okey, Flashback is now enabled for this database. We've defined a flasback retension of 4320 minutes (or 72 hours), a recovery file size of 512MB and defined the location for the file recovery area (FRA) as /u02/fra.

Lets see Flashback in action now. You can look at the contents of the recyle bin by querying select DBA_RECYCLEBIN the table.


oracle@nexus6 ~$ sqlplus ben/passwd
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Nov 4 00:41:36 2004
Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table test_table(
  2  id number(2),
  3  name varchar2(30)
  4  );

Table created.

SQL> insert into test_table values (1, 'Ben Rockwood');
1 row created.

SQL> insert into test_table values (2, 'Tamarah Rockwood');
1 row created.

SQL> insert into test_table values (3, 'Nova Rockwood');
1 row created.

SQL>  insert into test_table values (4, 'Hunter Rockwood');
1 row created.

SQL> select * from test_table;
        ID NAME
---------- ------------------------------
         1 Ben Rockwood
         2 Tamarah Rockwood
         3 Nova Rockwood
         4 Hunter Rockwood

SQL> drop table test_table;
Table dropped.

SQL>  select * from test_table;
select * from test_table
               *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> flashback table "test_table" to before drop;
flashback table "test_table" to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

SQL> flashback table "TEST_TABLE" to before drop;

Flashback complete.

SQL> select * from test_table;

        ID NAME
---------- ------------------------------
         1 Ben Rockwood
         2 Tamarah Rockwood
         3 Nova Rockwood
         4 Hunter Rockwood

SQL>

In this example we logged in as a user (ben) that by default writes to the users tablespace. Alternately, we could have specified the tablespace explicitly and used the sys user for testing (ie: users.table_test instead of table_test). I've created a simple table and populated it with some data. Then we drop the table and verify that it's really gone. To restore the table we simply use the flashback "to before drop" statement. Another query verifies that is was properly restored.