FlashBack is a feature introduced in Oracle9i and improved (and hyped) in Oracle10g. Effectively, it's a "oh shit!" proctection mechanism for DBAs. FlashBack leverages Oracle undo segments (remember the undo tablespace undo01.dbf?). Undo segments used to be called rollback segments, and you might have heard that muttered before.
To be more plain, Flashback is similar in idea to the Undo feature of your word proccessor or GIMP/Photoshop. You work along happily and then suddenly realize you really don't like where things are going, so rather than having to fix it we can just Undo. Applications in the last several years actually allow you use an Undo history to undo things you did several changes ago. Flashback is the same concept but for the database. Once Flashback is enabled and you have been granted permission to use it you could do something as important as "flashbacking" a dropped table, or something as minor as undoing your last SQL statements chnages to a table. You can even flash back an entire database to get back to an earlier point in time across the board!
To understand Flashback you need to be clarified on two things: The "recycle bin" and Oracle SCNs. An System Change Number or SCN is an integer value associated with each change to the database. You might think of revision numbers in a source control system. Each time you do something, whether your adding or removing data, a unique number is associated with the change. Reverting to an earlier state is as easy as telling Flashback which SCN you want to revert to. Obviously the kink is that if you drop a table the SCN isn't going to help you, therefore Oracle puts dropped objects into a recycle bin rather than blowing them into the nether regions immediately. Because of this you won't reclaim space immediately when you drop an object, however you can forcably purge objects from the recyle bin using the "PURGE" SQL statement.
The componants needed for Flashback have actually been in database for awhile to facilitate OLTP. All OLTP changes need to be atomic (discussed later) so when a transaction is modifying the database and for some reason fails (or in dba speak "throws an exception") the transactions that were uncommited are rolled back. Rollback Segments, now called undo segments, provided the neccisary historical information to allow for this. All this is leveraged, repackaged and dubbed "Flashback".
Before you get started playing with flashback, there is one little catch you need to be aware of: it doesn't work on the system tablespaces. This means that if you connect to Oracle as sys (who uses the system tablespace by default) and create a table, drop it, and then try to flashback it, it will fail. Flashback works great on the non-system tablespace, but if you blow away a system table your going to take more extreme measures, not just flashback restore it.
The easiest way to enable flashback is to enable it during database creation with dbca. And, as usual, Enterprise Manager makes everything a snap. We'll discuss it's setup here in case you want to enable it on existing databases using the SQL*Plus interface.
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.
I want you to specically notice that our first attempt to flashback the table failed! This is because Oracle refers to the table (and thus the recycle bin does too) in all caps and is case sensative. The second attempt using the table name in all caps works just fine. If you forget this you'll find yourself repeatedly hitting yourself in the head to figure out what went wrong.
For more information about Flashback check out the book Oracle Database 10g High Availability with RAC, Flashback and Dataguard and the Oracle Database Backup and Recovery Advanced User's Guide manual:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10734/rcmflash.htm