Oracle Database 12c: Flashback Moving Forward

0

Flashback started out as a feature in Oracle Database 9i. Although to be honest it is just the opening up of a mechanism that has been at the core of the Oracle Database from very early on: the ability to have concurrent sessions and transactions and allow transaction rollback and long running queries unaffected by transactions completed after the start of the query are all based on the same mechanism as flashback.

Flashback was great for demos and it was great for administrators. Developers however could not to very much with it. At least not until Oracle Database 11g when the Flashback Data Archive was introduced that provided fine grained control over which tables should have flashback data associated with it and which would not. At the time, the FDA was part of the Advanced Compression Database Option on top of the Enterprise Edition. So:great feature but for the happy few. In addition, there still were two main limitations with Flashback: history starts only at the day when the FDA is created. Nothing from before that day would be available. It’s a bit like the butterfly that does not have any of the memories of the caterpillar. The second limitation: Flashback did not record the context of the transaction in which the changes were applied. The identity of the perpetrator nor any other session characteristic were captured – so a straight out replacement for journaling tables capturing the audit trail was not offered by Flashback (it led me to an elaborate work around: http://technology.amis.nl/2011/02/06/database-transaction-recorder-adding-who-to-when-and-what-to-make-flashback-take-over-from-journalling-tables/).

Now for the good news on Flashback in Oracle Database 12c – good news that comes in three parts:

  1. As of 12c – Flashback will capture the session context of transactions. To set the user context level (determining how much user context is to be saved), use the DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL procedure. To access the context information, use the DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT function. (The DBMS_FLASHBACK_ARCHIVE package is described in Oracle Database PL/SQL Packages and Types Reference.)
  2. As of 12c – you can construct and manipulate the contents of the Flashback Data Archive. In other words: you can create your own history. Which means that a flashback query can travel back in time to way beyond the moment you turned on the FDA. In fact, it can go to before the introduction of the Flashback feature in the Oracle Database and even before the launch of the Oracle RDBMS product. It is in your hands! Import and export of history using DBMS_FLASHBACK_ARCHIVE procedures to create a temporary history table, and then later importing that table into the designated history table after loading that table with the desired history data. The temporary history table can be loaded using a variety of methods, including Data Pump. Support is also included for importing user-generated history. If you have been maintaining history using some other mechanism, such as triggers, you can import that history into Flashback Data Archive.
  3. As of 12c, Flashback Data Archive is available in every edition of the database (XE, SE, SE One, EE). See the Feature by Edition guide:

    image

    Note: Flashback Query and Flashback Versions query are also available in every edition of the database!

 

Resources

Oracle Documentation: http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_flashback.htm#ADFNS01011

Feature by Edition for Oracle Database: http://docs.oracle.com/cd/E16655_01/license.121/e17614/editions.htm#CJACGHEB

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Leave a Reply