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...

Introduction to Oracle Flashback Technology (Part 2: Database Administrators)

In the first in this series of articles on the tools in the Oracle Flashback Technology armoury, I discussed what is available to application developers, the various means by which they can rewind the clock and correct recently-past data errors.  However, if this was a world in which the worst thing that could happen was a small mix-up with data, what a happy world it would be. However, this is not Disneyland, and data errors are not the big, bad wolf at the door; it is errors that raze whole database objects that database administrators fear. But fortunately, Oracle Flashback can come to our rescue in those situations too.

First a quick recap: Oracle Flashback Technology is actually a clutch of different Oracle tools that enable developers and administrators reach into the immediate past of their database to recover from errors, without having to resort to point-in-time recovery options. For this reason, they are relatively fast and incur a relatively low logistical overhead (since the database does not need to suffer any downtime to implement them).

FLASHBACK TABLE:

Flashback table enables you revert a table to its state at a specified timestamp or SCN. Any rows of data created after the parameterized timestamp disappear and any deleted rows zombie back to existence; in addition, associated attributes such as triggers, indexes and constraints remain unviolated. And all this magic occurs without even the slightest blip of interference affecting the rest of the database; it stays up and available.

Flashback table is able to sidestep the unpleasantness of restoring from backups by using information in the undo tablespace to restore the table.

Imagine this scenario: some catastrophe has befallen the EMP table and the developers are unable to unpick the data. All eyes turn to you: Save us, they beseech, wave your wand and erase the past hour. This is where you would find Flashback Table useful:

FLASHBACK TABLE EMP TO SYSTIMESTAMP – INTERVAL '60' MINUTE;

And it’s done. Day saved. Simple.

To use flashback table, however, there are a few prerequisites that need to be in place:

• You must have the FLASHBACK privilege on the table. Or you must have the FLASHBACK ANY TABLE system privilege.
• You must have the SELECT, INSERT, DELETE and ALTER privileges on the table.
• Row movement must be enabled on the table.
• Finally - and crucially - the information in the undo tablespace must extend back far enough to cover your timestamp or SCN.

Continue reading...

Introduction to Flashback Technology (Part 1: Application Developers)

Imagine, if you will, that someone invented time travel. What would you do with it? Nip back into the past and fix that error you made? And maybe you’d want to do something about that silly, ill-advised haircut you had when you were 17. Well, the good news is that, in a manner of speaking, time travel has been invented. At least as far as Oracle databases go.

And the bad news?  There’s nothing it can do about your teenage haircut.

Oracle Flashback Technology is a suite of features that enables Oracle application developers and database administrators to wind the clock back and examine their database objects and data in a previous state without having to resort to backup media. Simply put, Oracle Flashback Technology is time travel for databases.

In this series of articles I intend to expound on the benefits of Oracle Flashback to application developers and to database administrators. This first installment will concentrate on the tools that will be of interest to application developers.

Oracle Flashback Technology is actually a collection of features that enables the developer to examine the database’s recent past in a number of ways:


Oracle Flashback Query:
Flashback query is a neat trick. It allows you run a query against your data as it was at past time. Imagine you accidentally delete a number of rows and commit your changes. With flashback query you can easily reach into the past and retrieve those lost rows.  Using the SELECT... AS OF clause you can examine the ghost of data past by referencing its timestamp.

SELECT *
FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2012-04-21 17:00:00','YYYY-MM-DD HH24:MI:SS');

And if you wanted to view those rows that you accidentally deleted 5 minutes ago?

SELECT *
FROM emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '6' MINUTE)
MINUS
SELECT *
FROM emp;

Continue reading...