Oracle 10g FlashBack Database

Posted by David Yahalom under Oracle

A few days ago I ran into a problem with one of our development DB2 servers. A programmer in my team accidentally activated a Java Hibernate class with an option that truncates tables before loading them into memory. This resulted in most of our tables becoming empty (and I’m taking about something like 300 tables here).

I had to do a tape restore in order for us to get our database running again. This wasn’t hard, but was a hassle nonetheless since I had to mount the tape, restore the image with the backup software and then do a restore and rollforward of the database. This is a manual process that must be done by the DBA.

During my late night restore session I wished it was an Oracle server I was working on. I remember a few months ago I had a smiler situation with an Oracle 10g server we had but that time I was able to use the “Flashback database” feature to restore my db to a working state in less time with allot fewer hassle.

Not many DBAs utilize Oracle’s 10g new “Flashback Database” feature which is a shame. Maybe its because we were all brought up on Oracle during simpler days when Flashbacking an entire database was only a dream.

Since Oracle 9i we have a “flashback query” option which allows us to restore the state of tables to an X minutes ago timestamp. This ability is based on the UNDO Tablespace data retention mechanism. When you modify a table, given a large enough UNDO tablespace, Oracle keeps copies of the old table blocks in its UNDO tablespace.

Since Oracle 10g we also have something new called “Flashback Database”. This allows us to rollback the database, either in full or in parts, back to a specific point in time. This is most useful when you run into a problem and want a quick way to restore your database to a previous “snapshot” without the hassle and time of doing a tape restore.

This feature utilizes 10Gs new “Flashback Area” which stores changes to the database in files dedicated solely for this purpose.

Doing a database flashback is actually simpler than most DBAs believe.

Let me give you a quick example:

1) First, lets put the database in Flashback mode:

SQL> shutdown immediate;

Database closed.
Database dismounted.
Oracle Instance shut down.

SQL> startup mount;

ORACLE instance started.

SQL> alter database archivelog;

Database altered.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=9600;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=1073741824;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST='/storage/flashback/';

System altered.

SQL> alter database open;

Database altered.

Note that this will only allow you to flashback your database to a point-in-time which is from now and forward.

To test flashbacking the db, let’s create a small test table:

SQL> create table hello_world (emp_id number(4), emp_name varchar2(30));

Table created.

SQL> insert into hello_world values (1, 'Bill Gates');

1 row created.

SQL> insert into hello_world values (2, 'Steve Jobs');

1 row created.

SQL> insert into hello_world values (3, 'Chris Eaton');

1 row created.

SQL> commit;

Commit complete;

Now lets get to the interesting stuff. Lets DROP THE TABLE and restore it from the flashback area:

SQL> drop table hello_world;

Table dropped.

SQL> flashback table "HELLO_WORLD" to before drop;

Flashback complete;

Pretty cool, right?
But this isn’t everything… Lets try to push the “Flashback Database” feature to its limits and try restoring the ENTIRE DATABASE to an earlier point in time.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount exclusive;

ORACLE instance started.

Database mounted.

SQL> FLASHBACK DATABASE to timestamp to_timestamp('01-01-2007 10:00', 'DD-MM-YYYY HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;