Migrating to Oracle Flashback for Historization on Oracle 11g

Question

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.

Answer 1

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.

Answer 2

I do not think you will be able to mock up fake history with Flashback Data Archive - as Tom indicates in the link you provide. Other than that it really does sound purpose-built for your data.

Perhaps you could consider a migration that leaves the current history data in a frozen state and gives access to a 'union' of the two histories for your historical data (through a set returning function for example)?

--edit

Further; How to 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.

Ok, that changes everything - Flashback Data Archive is not for you. Instead consider moving to a 2-table solution: one for current data and one for history.

11.2 allows many more DDL operations such as drop column than 11.1 but AFAIK there is no way of splitting the table into two copies retaining the history.