Oracle Total Recall

Oracle Total Recall is an option of the Oracle 11g database (separately licensed).

Total Recall is based on Oracle's Flashback feature and will allow users to query data "AS OF" an earlier time in the past. This will allow companies to "archive" data for auditing and regulatory compliance.

The main difference between flashback and Total Recall is that with Total Recall data will be permanently stored in an archive tablespace and will only age out after a user defined retention time.

Setup

Create a flashback archive:

CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE flasharch1
QUOTA 20G
RETENTION 1 YEAR;
Flashback archive created.

Set your tables to flashback archive mode:

ALTER TABLE hr.employees FLASHBACK ARCHIVE fla1;
Table altered.

Example

UPDATE hr.employees 
SET salary = 6000 WHERE employee_id = 200;
1 row updated.

Now, let's see if we can get the data back from the archive:

SELECT salary
FROM hr.employees AS OF TIMESTAMP TO_TIMESTAMP('2007-07-13 02:19:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE employee_id = 200;