Blog 
Flashback Query

It was in 2001 when the Oracle Open World came to Germany and a well known representative from Oracle announced Oracle9i with the exciting new features. One of those was named “Flashback Query” and in his speech he said something similar to: “with our new Flashback technology you are able to query past data no matter if it is days, weeks or even months old”. Wow that’s ambitious and it will probably open the arena for new applications. Unfortunately the reality was slightly different to the announcement: Flashback Query is relying on the undo information stored in the Undo-Segments. Let’s assume that those days you would like to retrieve data that was valid one month ago, you had to have an Undo-Retention of 2678400 (seconds) and hopefully tons of EMC² or other storage to retrieve those data. In reality that limits the flashback period to a few hours.

With Oracle 11g the statement from 2001 became reality: Oracle proudly announced “Total Recall” as an option for the Oracle Enterprise Edition. This feature now allows to store previous values for specific tables in a separate flashback archive instead of the Undo-Tablespace. There were some limitations in the first versions (like the inability to backup or export those data) but in principle the feature worked as expected. No longer was the Undo-Tablespace the limitation but individual transactions could be monitored or blacked out even if they were several weeks or months old. But as this feature was an expensive option to the Enterprise Edition it hasn’t  been used commonly – until Oracle 12c came out.

Oracle 12c not only brought some nice enhancements to the now renamed “Flashback Data Archive” but Oracle announced that this feature is now available in all Editions free of charge – not only for Oracle 12c but for 11.2.0.4 as well.

Now it makes sense to take a closer look at the functionality.

Flashback Archive

Flashback Data Archive is a name space within an existing tablespace. While defining this archive you only specify the tablespace and the retention period for all data within this archive.

CREATE FLASHBACK ARCHIVE fda1
TABLESPACE fda_tablespace
RETENTION 2 YEAR;

You can have as many archives as you like and for the definition no space is allocated. Let’s have a look at the definition:

SELECT owner_name AS owner, flashback_archive_name, retention_in_days
FROM dba_flashback_archive;

OWNER   FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS
------- ---------------------- -----------------
SYSTEM  FDA1                                 730

But now you can specify tables for which all changes should be captured and stored in the flashback data archive:

ALTER TABLE demo.personen FLASHBACK ARCHIVE fda1;
ALTER TABLE demo.auftraege FLASHBACK ARCHIVE fda1;
ALTER TABLE demo.positionen FLASHBACK ARCHIVE fda1;
ALTER TABLE demo.adressen FLASHBACK ARCHIVE fda1;
ALTER TABLE demo.telefone FLASHBACK ARCHIVE fda1;

That’s all. But keep in mind that the schema owner of the tables must have quota on the tablespace with the flashback archive as well (in this case fba_tablespace).

For each individual table a corresponding one is created in the tablespace you specified.

SELECT * FROM dba_flashback_archive_tables;

TABLE_NAME   OWNER_NAME   FLASHBAC    ARCHIVE_TABLE_NAME   STATUS
------------ ------------ ----------  -------------------- --------
PERSONEN     DEMO         FDA1        SYS_FBA_HIST_92011   ENABLED
AUFTRAEGE    DEMO         FDA1        SYS_FBA_HIST_92007   ENABLED
POSITIONEN   DEMO         FDA1        SYS_FBA_HIST_92015   ENABLED
ADRESSEN     DEMO         FDA1        SYS_FBA_HIST_92005   ENABLED
TELEFONE     DEMO         FDA1        SYS_FBA_HIST_92023   ENABLED

Interesting to know that the archive tables are partitioned even with the Oracle Standard Edition. 

Now all flashback queries are automatically redirected to the flashback archive if the “old” data is not available in the Undo Segments. But you can, of course, also directly query the content of the archive tables if you like.

SELECT * FROM personen
AS OF TIMESTAMP to_date('2013-12-19 15:00:00','YYYY-MM-DD HH24:MI:SS');

Transaction Context

Sometimes it might be nice to know who made the change or from which application context the change was made. This information can be stored in addition to the flashback data since Oracle 12c.

The procedure dbms_flashback.set_context_level is used to enable the storage of the context for transactions on a specific table:

dbms_flashback_archive.set_context_level ('ALL' | 'TYPICAL' | 'NONE');

Unfortunately it looks like there is no chance to set this parameter individually for table or a flashback archive but for the entire database. For each statement a transaction ID (XID) is now stored in addition to the changed content:

SELECT xid, persid, strasse, plz, ort
FROM demo.sys_fba_hist_92005;

XID                  PERSID STRASSE         PLZ ORT
---------------- ---------- ---------- -------- ----------
0500080028070000     100101 Behnitz       10115 Berlin

SELECT dbms_flashback_archive.get_sys_context('0500080028070000','USERENV','MODULE') AS CONTEXT
FROM DUAL;

CONTEXT
--------------
SQL*Plus

SELECT xid, authenticated_identity, host, module, os_user, session_user, terminal
FROM sys.sys_fba_context_aud

XID              AUTHENTIC HOST                      MODULE          OS_USER           SESSION_US TERMINAL
---------------- --------- ------------------------- --------------- ----------------- ---------- ----------
0500080028070000 demo      albinoni.carajandb.intra  SQL*Plus oracle DEMO              pts/1
090016003F080000 SYSTEM    CARAJANDB                 TOAD 12.1.0.22  Johannes Ahrends  SYSTEM     xxx

Conclusion

The ability to use flashback data archive free of charge and in all editions will probably change the way how “old” data is being stored tremendously. The setup couldn’t be simpler and there are numerous use cases for this feature.

No comments on “Flashback Query

Leave a Reply

Your email address will not be published.

What can CarajanDB do for you?