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 [inlinecode]dbms_flashback.set_context_level[/inlinecode] 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.