Flashback Database

One of the new features of 10g that we really like is Flashback Database. We had just enabled this feature for a client on a development database and a few days later one of their developers reported that a table had been dropped accidentally. Wow, in the past this would have been a lot of work to recover the dropped table. With flashback database we were able to flashback the tablespace in three minutes (from request to completion). Now that is a really cool time saver.

You will still want to encourage your users to use common sense but flashback will make all our lives easier.

Here is an example implementation step to enable and use flashback.

shutdown immediate;
startup mount;
alter database flashback on;
select flashback_on from v$database -- verify it
alter database open;
select flashback_on from v$database -- verify it
create restore point before_upgrade;

If you need to restore:

shutdown immediate;
startup mount;
flashback database to restore point before_upgrade;

You can check the restore point with v$restore_point.

SQL> select oldest_flashback_scn, oldest_flashback_time, retention_target, estimated_flashback_size from v$flashback_database_log;

We will be writing more about flashback database and other oracle technologies in the future.