Introduction to Oracle Flashback Technology (Part 3: Flashback Database)

In this series of articles, we are discussing the collection of tools that make up the Oracle Flashback Technology suite. They are varied in their purpose and in their implementation, but together they act very much like a morning-after pill for your database, a means by which developers and administrators can protect their database from immediate past errors and indiscretions. In the first part of this series, I talked about the flashback tools that every developer should have in his arsenal, and in the second we rummaged through the pile looking for things that might be of interest to administrators.  However, I have saved the biggie for last: Flashback Database.

What is Flashback Database?

 For those times when minor surgery is not enough, Flashback Database can apply a defibrillator to the heart of your database, shout “Clear!” and proceed to shock it back to a happier time. This is because Flashback Database is a mechanism that allows administrators rewind an entire database to a past time or SCN.  Flashback Database has most of the advantages of a point in time recovery, with  only a fraction of the hassle and longwindedness. Flashback Database, unlike some of the lesser members of the Flashback family, is a physical-level recovery mechanism; it uses its flashback logs to access past versions of data blocks, finessing the final result with information from the archived redo log where necessary.

Setting Up Flashback Database

Flashback Database must be enabled before you can use it.  This simple query will tell you whether it is or not:

SELECT FLASHBACK_ON FROM V$DATABASE;

The result will either be YES or NO.  If Flashback Database is not enabled and you wish to enable it, you will need to ensure that the database is in ARCHIVELOG mode first.

There are two other prerequisites for enabling flashback database.  You will need to instruct the database on where to store the flashback logs that it will generate and you will need to tell it how much space it can use up doing so. These are expressed in two instance parameters – DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE.  Remember to set the size before the location.

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 9G SCOPE=BOTH;

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘/oracle/flash_recovery_area’;

When Flashback is up and running, your control over the Flashback Recovery Area, which is what the location you specify in the DB_RECOVERY_FILE_DEST parameter is called, will be limited; the database will create and age out the flashback logs as necessary. One way in which you can attempt to manage the Flashback Recovery Area is to specify a retention target, telling the database how long you would like it to retain its logs before they are overwritten by newer ones. Whatever retention target you specify, however, is not set in stone; in practice, the length of time that logs are retained will depend on the intersection of the retention target and the space made available by the DB_RECOVERY_FILE_DEST_SIZE parameter. If the database runs out of space, it will kill off old logs, irrespective of the retention target.

The retention target has a default of 1 day and is set in minutes, thusly:

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 2880; /* 2 Days */

This done, you are now ready to flick the switch on Flashback Database. To do so you will need to shut down cleanly and mount the database, and while in this mode run the following:

ALTER DATABASE FLASHBACK ON;

Continue reading...