Oracle Flash Back Technology

 
 
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 article in this series, I talked about the flashback tools that every developer should have in his arsenal, and in the second article 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;
How to Use Flashback Database

To use Flashback Database, you can either go to the Database Home page, navigate to the Perform Recovery link and follow the options, or get your hands dirty and execute it a little more manually. To do this you will need to shut down the database, mount it and then flashback to a time or SCN as follows:

FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('01/06/2012 15:00:00','DD/MM/YYYY HH24:MI:SS');
Once you have done this, you will want to open the database read-only and ensure that you are happy with the new state of the data, before opening it with the RESETLOGS option. If, however, you are a little dissatisfied with the state of the data, you have the option to flashback the database further into the past or, if required, roll the database a little further ahead in time using the RECOVER DATABASE UNTIL command. In this example, I am rolling my database ten minutes ahead of the time I had previously flashed back to.

RECOVER DATABASE UNTIL TIME '2012-06-01:15:10:00';
You may also want to note that if you run the RECOVER DATABASE command without the UNTIL clause it will completely undo the FLASHBACK DATABASE.

When to Use Flashback Database

Flashback Database is the big dog. If, say, a whole schema is accidentally dropped, for instance. However, Flashback Database does have less dramatic uses, too. You will want to use it as protection whilst carrying out high-risk operations, like application upgrades or large-scale database updates. In such cases, you will want to create something called a ‘restore point’, which is, basically, a named SCN bookmark to which you can later flashback if necessary. Restore points are useful because they save you the trouble of needing to find out the correct SCN or noting the exact timestamp. Instead you can do the following:

CREATE RESTORE POINT rp_before_upgrade;
That way, if everything goes wrong, you can simply say:

FLASHBACK DATABASE TO RESTORE POINT rp_before_upgrade;
Things to Note

Flashback Database is extremely powerful, but even Superman has his kryptonite; it does have limitations. It can only undo changes to a datafile made by an Oracle database. It cannot, for instance, rescue you from hard disk crashes, the accidental deletion of datafiles or any such physical media balls-ups. Also, flashback database cannot undo a shrink datafile operation.

It should also go without saying that Flashback Database cannot reach further into the past than the period covered by the flashback window. As I have said, flashback logs are managed automatically and are aged out by the database. Once the logs covering a certain period have grown geriatric and died, not even Flashback Database can bring them back to life.

Conclusion

Ships aren’t fitted with lifeboats because we hope they will sink; they are there in case they do. Oracle Flashback Technology exists for the same reason; not so we will all rush out and recklessly destroy our databases as if the police were at the door and we wanted to get rid of incriminating evidence, but as a buffer to protect our database for those rare occasions when we do make a mistake. We hope we never need it, but we should be glad that it exists.