Oracle Database 12c: Flashback Moving Forward
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:
- 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_LEVELprocedure. To access the context information, use the
DBMS_FLASHBACK_ARCHIVEpackage is described in Oracle Database PL/SQL Packages and Types Reference.)
- 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_ARCHIVEprocedures 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.
- 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:
Note: Flashback Query and Flashback Versions query are also available in every edition of the database!
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
- Tracking the moving history of averages and other aggregates – Flashback Aggregates in Oracle SQL
- Oracle 11g Total Recall – Flashback in the hands of Database Designers and Application Developers, at last. And: the end of Journalling Tables
- Database Transaction Recorder – Adding Who to When and What to make Flashback take over from Journalling tables
- Unspoiling the demo – Flashback Table and Flashback Query
- Oracle 11g Partial Table Flashback (flashback only selected columns or a subset of records)
- Steven Feuerstein Master Class. Anti-Pattern PL/SQL Programming + 12c New PL/SQL Features, December 12 and 13 2013
- The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper
- WebLogic & FMW Provisioning update
- The road ahead for WebLogic 12c
- OOW13: summarizing one week and 2000 sessions in 3 hours and a bit – the yearly AMIS OOW Review session – 10th October
- OOW13 and JavaOne 13: Notes from a Conference – Part Two
- OOW13: First glimpses of the new SOA Suite 12c
- You consolidated your applications in one database, but now one application needs recovery…
- OOW13: Sneak Preview of the Major Announcements?! In-Memory Database, PaaS (Database and Java) and M6 big memory machine
- Het Oracle OpenWorld Preview Evenement (5 september 2013) – 15 sprekers & sessies