Migrating to Oracle Flashback for Historization on Oracle 11g

flashbackoracleoracle-11goracle-11g-r2

Our application built using Oracle 11g requires that we keep historic data.

The historization is legally mandated, and is mainly used for manual queries, when investigating problems, etc. Basically we need to save some stuff 10 years, and we dig for old data a few times each year. The historical data is also used frequently during development and testing for debugging purposes.

Or homemade solution is to add an extra sequence number to the primary key, inserting a copy with a new seq-no when an existing row is "modified". We are using the latest seq-no as the actual "active" data in all queries. Effectively we are doing a subselect or join on max(seq_no) + the other primkeys whenever we access a table.

id           varchar[8],  --pk
seq_no       number,      --pk
omg_ponies    varchar[42],

The performance is …almost adequate, but we are seeing bigger data volumes, and a lot of queries are getting unbelievably complex as our application grows. It also hinders us from using JPA or Hibernate efficiently as the model gets messed up. We are solving that with views, but it has it's complications.

So how to handle historization? Oracle seems to recommend using Flashback and it looks like a Good Thing.

But how do we migrate, keeping our (10 year long) history? Ideally, only one row should be left for each id, with the old variants (lower seq_no:s) available in the flashback logs. Preferably with an proper "old" timestamp.

Furthermore, how do you handle refactoring? We see a lot of new business requirements, and occasionally we need to do major changes, like splitting a table into two, or other larger modifications caused by new requirements and features. Until now, we've been doing larger changes once or twice each year. How do we handle changes, while retaining or repopulating the history, again preferably with the proper "older" dates.

Footnotes: We do not believe that we must leave historic entries "intact". It's OK to modify the logical model as long as we are not loosing track of changes and certain events.

Best Answer

It sounds like Oracle Total Recall may fit your needs:

Regulatory oversight such as Sarbanes-Oxley, HIPAA, and Basel-II, as well as internal audits, require companies to keep historical data available for long periods of time. Oracle Total Recall, part of Oracle's comprehensive portfolio of database security solutions, works with Oracle Database 11g, Enterprise Edition to help companies store their data in secure, tamper-proof databases while keeping it accessible to existing applications

The documentation states that it can support refactoring:

In Oracle Database 11g Release 1, the Add Column DDL operation is supported with Flashback Data Archive. With Oracle Database 11g Release 2, the following DDL operations are supported, with full support for Flashback queries across all associated changes:

• Add, Drop, Rename, Modify Column

• Drop, Truncate Partition

• Rename, Truncate, Drop Table

For more complex DDL – upgrades, split table, etc. – the Disassociate and Associate PL/SQL procedures can be used to temporarily disable Flashback Data Archive on specified tables.

Related Solutions

Flashback feature in Oracle 11g

Firstly, you must enable flashback. In SQL*Plus as SYSDBA:

shutdown immediate;
startup mount;
alter database flashback on;
shutdown immediate;
startup;

Next before your load operation, create a restore point:

create restore point before_load;

Now do you work, and when you're done:

shutdown immediate;
startup mount;
flashback database to restore point before_load;
shutdown immediate;
startup;

And your data is back as it was. You can repeat this as many times as you like, and when you are done, drop the restore point. HOWEVER the commenters are correct; you should leave the constraints enabled and just defer them.

How high can I set undo retention on Oracle 11gR2

Although I haven't tried it, I would tend to suspect that you'll run into problems when your UNDO_RETENTION exceeds the length of time that Oracle maintains its SCN to timestamp mapping. If memory serves, that is roughly 1 week (well, if memory serves, it was 1 week in the 10g days and I'm not aware of anything that would have changed that in 11.2). I would tend to suspect that Oracle would use that mapping to determine which UNDO segments are old enough that they should be purged and that you would start to encounter problems if you exceeded that limit.

On my 11.2 system, for example, I can convert 7 days ago to an SCN and then back to a timestamp but not 8 days ago

  1* select scn_to_timestamp( timestamp_to_scn( systimestamp - 7) ) from dual
SQL> /

SCN_TO_TIMESTAMP(TIMESTAMP_TO_SCN(SYSTIMESTAMP-7))
---------------------------------------------------------------------------
06-NOV-12 01.06.47.000000000 PM

SQL> ed
Wrote file afiedt.buf

  1* select scn_to_timestamp( timestamp_to_scn( systimestamp - 8) ) from dual
SQL> /
select scn_to_timestamp( timestamp_to_scn( systimestamp - 8) ) from dual
                         *
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1

I would tend to suspect on my system, therefore, that if I tried to retain 8 days of UNDO that Oracle would generally have a problem complying. It would take a week to verify one way or the other but if I had to wager, I would tend to wager that trying to hold more than a week of UNDO would fail.

Even if you could hold the UNDO that long, formulating the flashback query would be a major challenge since you couldn't specify a timestamp. You could potentially maintain your own (more long-term) mapping between SCN and timestamp by writing an automated job that would capture both every few seconds and hold them for weeks or months at a time and then specify AS OF SCN in your flashback queries. I'd tend to expect that the older UNDO would be gone but it might work.

Best Answer

Related Solutions

Flashback feature in Oracle 11g

How high can I set undo retention on Oracle 11gR2

Related Question