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?
Best Answer
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