Is an alternative there for Oracle Total Recall?

Question

Oracle Total Recall is an option for Oracle Enterprise Edition (11g) that provides secure retention and access of historical data.

From the white paper:

Flashback Data Archive creates an internal history table for every tracked table. The internal history table is initially a replica of the FDA-enabled table with additional metadata columns. When one or more columns in the tracked table are updated, a new row is inserted into the history table that is the before-image of the row before the transaction. UPDATE and DELETE operations generate a new record in the history table, but INSERT operations do not – inserted rows appear in the base table. The internal history table is partitioned for better performance, and compressed to reduce disk space requirements. No modifications are allowed to internal history tables. Applications and users can use the ‘AS OF’ and ‘VERSIONS BETWEEN’ SQL constructs to seamlessly query the historical data.

Is an (open-source) alternative for history tracking there?

Answer 1

You could use log recovery in most RDBMS, but it is not as "easy" as Oracle's. Basically, all RDBMS can keep track of all executed queries.

So you could use this on a separate server to replay executed queries from a full backup and restore the state of the database at any given point in time (i.e. you could see the state of the database at December 3rd, 13:53 after transaction xxx). You could also analyze the logs and see what happened.

This is very limited and not very practical.

Check PostgreSQL docs here: http://www.postgresql.org/docs/8.4/static/continuous-archiving.html

Answer 2

This is generally done with triggers (or in ORM-land, with equivalent signals). I don't know of a generic and in-database solution, although the replication queues like PGQ are close.

Answer 3

You could look at Workspace Manager which is part of the standard database.

An overview of the functionality is here: http://www.oracle-base.com/articles/9i/WorkspaceManagement9i.php