Total Recall - Flashback Data Archive Feature of Oracle 11g

Contents- Total Recall (Introduced from oracle 11g Release 2), Practical

Concepts

In Database world, Oraganization would keep track of every change to every piece of data they manage.

The Oracle Total Recall option makes it to securely track and query historical data for any database table, without the cost or performance impact.    

Ways to maintain history of Data

Database Triggers

Database Trigger is used to maintain the history of data. But using triggers does not provide a centralized management interface and can have  a significant impact on application.

Problem:

Privileged users can modify the historical data, calling into question of integrity of data.

Redo Log Mining

Another approach is to mine redo logs to create and store data history.

But Log mining requires a separate set of tools process to configure, monitor and manage.

Problem:

As with application and database triggers, privileged users can modify the historical data stored by log mining the question of integrity of data.

Total Recall Advantage

Flashback Data Archive

Flashback Data Archive (FDA) feature of the oracle total recall option in 11g removes the cost, performance and complexity to maintain historical data.

A Flashback  data archive is a logical container for managing historical information for specified tables. It is new dictionary object in oracle database 11g that defines archive storage and data purging policies.

A Flashback data archive can span multiple tablespace.

Administrators can define the amount of space a flashback data archive can use in each tablespace using the quota parameter.

Multiple Flashback data archives can be created as needed to implement different archiving policies.

Each flashback data archive contain a RETENTION parameter that specifies the duration for retaining historical changes.

Flashback  data archive makes it possible to automatically and transparently track all the changes to any set of tables in oracle 11g database and to easily query data in history table as of any point in time or over any interval within the specified retention period with minimum performance impact.

Features

Minimum cost to maintain historical data.

Data Forensics:- Find and revert changes made by a disgruntled employee.

Automatically purge history older than 5 years.

Error Recovery :- Restore records that were erroneously removed or updated.

Flashback Data archive can be enabled on both existing and new tables and in a completely application transparent manner.

Application transparency:

Enabling flashback data archive on one or more tables can be done instantaneously with no application changes.

Security      

Access to internal history tables is restricted to read only. No DML Operations on historical data are allowed for any users, including administrators. 

Application need not query the internal history tables directly as seamless access is provided through the flashback query mechanism.

Storage Optimization :- Flashback data archive compresses and partitions the internal history tables to optimize storage and performance.

Architecture

In Flashback data archive history generation is implemented by a new process called FBDA.

After a base table has been enabled for history tracking with flashback data archive, all transactions on the table and the corresponding  undo records are marked for archival.

In order to gurantee that every such transactions is archived, the undo records are not recycled until the history is generated and stored in the database.

 

Process

 1)   Create a tablespace to store flashback data.

 2)   Create a Flashback Data Archive in new tablespace.

 3)   Enable the flashback data archive feature on the tables you want to track.

Practical

Requirement

Flashback data archive tablespace must be managed with automatic segment space management.

Automatic Undo Management Must Be enabled.

 1)   Create  a new tablespace to enable flashback data archive.

Sql> create tablespace demofda datafile '+data(datafile)' size 10m autoextend on next 10m extent management local segment space management auto;

 2)   Grant Flashback archive administrator privilege to create flashback data archive.

 3)   Create a Flashback Data Archive on a Created tablespace.

Sql> create flashback archive fda1 tablespace demofda retention 1 year;

 4)   Enable Flashback data archived on Desired Table.

Sql> alter table employees flashback archive fda1;

Or

Sql> grant flashback archive to hr;

 5)   Sql> conn hr/hr

Sql> create table testdb(id number,name varchar2(100),salary number) flashback archive;

Sql> insert into test values('&id','&nm','&sal');

Sql>update test set salary=100000 where name=’Mitesh’;

Now Query to see your historical data by using flashback version query or flashback query

Note:- Flashback archive object privileges is required to enable flashback data archive.

Data Dictionary Views

USER_FLASHBACK_ARCHIVE-

Displays information about flashback data archive.

USER_FLASHBACK_ARCHIVE_TS       

Displays tablespace and the mapping to flashback data archives.

USER_FLASHBACK_ARCHIVE_TABLES-  

Display information about tables that are enabled for flashback data archive.