Oracle 10g New Features -  Flashback Database

One of the more interesting Oracle 10g new features in the area of backup and recovery (more specifically human error correction) is the flashback database feature.

This is not to be confused with the flashback query technology introduced with Oracle 9i although the concept is similar.

Rather than just enabling you to query a table as it was in the past, this new feature allows you to rewind the whole database.

The situations for which flashback database is ideal are those which would otherwise call for a tablespace point-in-time recovery such as accidentally dropping a whole schema or tablespace or logical database corruption due to loading the wrong data. However it can't be used to recover from physical data corruption or media failure.

One of the main benefits of this new feature is that the whole process is automated so you no longer need to worry about taking ad-hoc backups before a major update or data load or where you stored the files or which versions to use. All this is taken care of by Oracle.

You might be thinking "This sounds great ... but where's the catch?" You're right, there is a catch which could be big or small depending on your situation.

The catch is that there is a new background process Recovery Writer (RVWR) which periodically writes changed blocks to flashback logs on disk. This will obviously take cpu, memory,  i/o and disk resources as these new logs are additional to the normal redo logs.  Fortunately, not every change to every block results in the block being saved to a flashback log, so the performance hit is minimised.

Flashback Database Architecture

Let's have a look at how this works.

First the prerequisites:-

• the flashback area must have been defined using db_recovery_file_dest and db_recovery_file_dest_size
• the flashback area must have been created on the disk
• the database must be running in archivelog mode
• flashback database must be enabled

When flashback database is enabled, the before images of changed blocks are written to the flashback buffers in the SGA before being written to the flashback logs on disk by RVWR.

Recovery Using Flashback Database

To recover from the loss of data, you can either use RMAN or Database Control or issue SQL commands. In SQL it's as easy as issuing the command

SQL> flashback database to timestamp (sysdate-1/12);

which will rewind the whole database to its state 2 hours previously. N.B. the database must be mounted but not open when this command is issued.

You can then open the database read only and check that your data is there. If not you can close the database and flashback to an earlier time or if it is there you can flashback to a later time to try to recover more data. Once you're happy with the state of the database you can open it for normal use with the RESETLOGS option.

More information on this aspect of Oracle 10g new features can be found in the Oracle 10g backup and recovery guide.