Migrating to Oracle Flashback for Historization on Oracle 11g 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.
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.