Oracle 11g Total Recall Feature

Oracle 11g Total Recall Feature

The Flashback Data Archive (FDA) feature makes it possible to automatically and transparently track all the changes to any set of tables, and to easily query data in those tables as of any point in time over any interval within the specified period with minimal performance impact.

FDA can be used in

- data forensic: find and revert changes made by a disgruntled employee
- retention policy enforcement: automatically purge history older than  years.
- Historical reporting: analyze product changes over time
- Error recovery: restore records that were erroneously removed or updated
- Employee fraud detection: find assets tht were deleted but never sold
- Tamper-proof history: retain a SecureFile file even when the original is destroyed.

Benefits:

Flash Data Archive overcomes the limitations inheritent in current apporach as historical data management. It automatically tracks every change made to FDA enabled data stored in the database, amd maintains a secure, efficient and easily accessible archive of historical data. The historical data can be returned as long as needed, and is easily accessible with standard SQL statements. Flashback Data Archive can be enabled on both existing and new tables instantaneously and in a completely application transparent manner.

- Application transparency :

Enabling FDA on one or more tables can be done instanttaneously with no application changes.

- Seamless Access

Applications can seamlessly query the historical data as it existed at any point in time or during any times interval within the retention period.

- Security :
Access to internal historical tables is restricted to read only. No DML is allowed on historical data for any users including administrators.

- Minimal performance overhead:
FDA employes a lightweight mechanism to mark DML operations on FDA enabled tables. The actual history generation and archiving is done asynchronously with background processes.

- Storage mechanism

Both compression and partitioning in FDA are managed automatically and require no special administration

- Centralized management:

we can create group of tables and set a common retention policy for each group. when a new table is added to a FDA, it automatically inherits the retention parameter from archive. The Oracle database automatically purges aged out history data for all the tracked tables based on the specified retention policy.

Flashback Data Archive requirements :

1- FDA tablespaces must be managed with automatic segment space mamanegment (ASSM).
2- Automatic Undo Management must be enabled

Implementation:

The are 3 steps process to FDA
1- Either create a new tablespace or use an existing tablespace( ASSM)

2- create a flashback data archive:
CREATE FLASHBACK ARCHIVE fda1
TABLESPACE fdatbs
RETENTION 5 YEARS;

This will create a new FDA calls fda1 on tablespace tbs1 with a retention period of 5 years, with an unlimited quota on the tablespace.

--FLASHBACK_ARCHIVE ADMINISTRATOR system privilege is required for creating a new flashback data archive.

--  FDA dictionary views

DBA/USER_FLASHBACK_ARCHIVE - Displays information about flashback data archives

DBA/USER_FLASHBACK_ARCHIVE_TS - Displays tablespaces and the mapping to flashback data archive

3. Enable flashback data architect on the desired table

ALTER TABLE EMPLOYEE FLASHBACK ARCHIVE fda1;

The employee table is now enabled for flashback data archive, and its history data will be stored in the flashback data archive fda1. when the first DML operation occurs, the internal history table is created in tbs1 tablespace.

DBA/USER_FLASHBACK_TABLES - views displays information about tables that are enabled for flashback data archive.

Querying a FDA table:

SELECT LAST_NAME,FIRST_NAME,SALARY
FROM SEMP
WHERE TIMESTAMP TP_TIMESTAMP('2012-03-01 00:00:00','YYYY-MM-DD:HH24:MI:SS')
WHERE EMPLOYEE_ID=110;

Version query:

SELECT LAST_NAME,FIRST_NAME,SALARY
FROM SEMP
VERSION BETWEEN TIMESTAMP
TO_TIMESTAMP('2011-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND
TO_TIMESTAMP('2012-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND
WHERE EMPLOYEE_ID=110;