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.

In this series of articles I intend to outline 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 syntax 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;

Oracle Flashback Version Query:

While flashback query seems pretty much like magic, it actually comes with a cherry on it. Flashback Version Query has the power to not only resurrect bygone data, it can also give you important metadata about those rows. You can find out the exact chronology of the changes that have occurred to those rows – the whens and the whats. You do this using the SELECT … VERSIONS BETWEEN syntax referencing a start and end timestamp (you can also reference the System Change Number – SCN – if you prefer). Using this syntax you can query a number of pseudocolumns:

VERSIONS_STARTTIME The timestamp at which the row version was created.
VERSIONS_ENDTIME The timestamp at which the row version ended.
VERSIONS_OPERATION This pseudocolumn contains the operation that resulted in the row version. I(nsert), U(pdate) or D(elete).
VERSIONS_XID The identifier for the transaction that resulted in the row version.

Imagine that those rows from the previous example were not simply deleted, but were updated and changed numerously, and you are interested in finding out the details of each edition that occurred to a particular row. You might write a query such as the following:

SELECT versions_starttime, versions_endtime, versions_operation, versions_xid, ename, sal
FROM  emp BETWEEN TIMESTAMP (systimestamp - interval '6' minute) AND systimestamp;

Edit a row or two in one of your database tables and give the above query a spin. It will immediately strike you how important the flashback version query pseudocolumns could be. Pay attention to versions_xid, we will need it to add a further layer of functionality to our flashback cake.

Oracle Flashback Transaction Query:

The versions_xid value that we got from the previous query is the internal id that Oracle assigns each transaction. It’s a hexadecimal value, seemingly unremarkable in itself; but using it we can rummage around in the FLASHBACK_TRANSACTION_QUERY data dictionary view and obtain further information about our query. Amongst the columns in this view are the following:

XID
START_TIMESTAMP
COMMIT_TIMESTAMP
LOGON_USER
OPERATION
UNDO_SQL

There are a few other columns in the view; one identifying the table and a few identifying the SCN. Describe the view yourself to get the full picture. However, do pay LOGON_USER and UNDO_SQL especial attention. Going back to our previous example, we can use this view to not only discover who deleted the missing rows, but also to allow Oracle generate for us the code it would take to undo the change.

SELECT logon_user, operation, start_timestamp, undo_sql
FROM flashback_transaction_query
WHERE xid in (
SELECT  versions_xid
FROM  emp BETWEEN TIMESTAMP (systimestamp - interval '6' minute) AND systimestamp);

DBMS_FLASHBACK

You know how, when you go to a really fancy store, they package your shopping up for you in a really nice bag? Well, that’s what Oracle have done with the DBMS_FLASHBACK package. The flashback technology is nothing short of amazing, but with DBMS_FLASHBACK they have wrapped it up and put a bow on it.

Using the tag team of DBMS_FLASHBACK.ENABLE_AT_TIME and DBMS_FLASHBACK.DISABLE, you can dial back your session to a past time and carry out select queries without having to bother with the SELECT…AS OF or SELECT … VERSIONS BETWEEN syntaxes. Using this package, the above example can be rewritten as follows:

EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(systimestamp- interval '6' minute);

SELECT * FROM emp;

EXECUTE DBMS_FLASHBACK.DISABLE;

There are a few things you need to bear in mind, however. Only run select statements, no other DML and definitely no DDL statements. Also, you must follow each call to DBMS_FLASHBACK.ENABLE_AT_TIME with a call to DBMS_FLASHBACK.DISABLE before calling DBMS_FLASHBACK.ENABLE_AT_TIME again.

The DBMS_FLASHBACK package can also smooth out the hassle of restoring lost data for you. Using the TRANSACTION_BACKOUT procedure, Oracle will not only rollback any specified transactions, but also any dependent transactions that may have taken place as a result of them.

DBMS_FLASHBACK.BACKOUT_TRANSACTION

numberofxids NUMBER Number of transactions to be backed out of.
Xids XID_ARRAY Array of transaction ids.
Options NUMBER How do you want dependent transactions to be handled?

·         NOCASCADE: You expect no dependencies.

·         NOCASCADE_FORCE: Back out of specified transactions, ignoring any dependencies.

·         NONCONFLICT_ONLY: Only back out of non-conflicting transactions.

·         CASCADE: Remove the parameterized transactions and all dependencies.

The default value of the options parameter is NOCASCADE. If your transaction has dependencies, you will want to use CASCADE instead. Also bear in mind that you will need to commit the changes the procedure has made. However, you will probably want to examine the reports that the procedure has created in USER_FLASHBACK_TXT_STATE and USER_FLASHBACK_TXT_REPORT (or DBA_FLASHBACK_TXN_STATE and DBA_FLASHBACK_TXN_REPORT, if you have the access to them) before saving the changes.

Conclusion:

No man, Oscar Wilde once said, is rich enough to buy back his past. Perhaps so, but with Oracle Flashback Technology we can make the mistakes of the past a little less expensive. Using the functionality I have outlined above, developers can either deal with spilt milk on a transaction by transaction basis as needed, or perhaps build a simple overlying application that can reverse time at the press of a button.

“The past is not dead,” William Faulkner once said. “In fact, it’s not even past.”

Perhaps he was an Oracle developer too.

In the next instalment in this series I will discuss the tools in the Oracle Flashback Technology locker that database administrators will find invaluable.