Flashback Data Archive

Hello, everyone!
Today I will make a brief post about flashback data archive.

Flashback data archives it is like a repository composty by one or more areas, one of which can be the default, assign a default retention period for objects in the repository, and then mark the appropriate tables for tracking.
It works like a undo tablespace. The undo data normally hold’s for a period of hours or days for all objects.

In flashback data archive rows can span years or decades, the focus in this feature is recording only historical
changes to table rows.
The flashback data archive records only UPDATE and DELETE statements but not INSERT statements.

Creating an Archive
You can create more than one Flashback Data Archive. For every archive you must have a default
retention period using the RETENTION clause and can optionally be identified as the
default archive using the DEFAULT keyword.

SQL> CREATE TABLESPACE FBDA1 DATAFILE ‘/u01/flashback/fdba1.dbf’ size 15g;
Tablespace created.
SQL>

Now, let’s create Flashback Data Archives one for the HR department
with no quota limit and a five years retention period:

SQL> create flashback archive fb_hr tablespace fbda1 retention 5 year;
Flashback archive created.

Another for finance department with a 300MB limit and a four years retention period.

SQL> create flashback archive fb_fi tablespace fbda1 quota 300m retention 4 year;
Flashback archive created.

And another for advertising department for all other users in the PROAPP tablespace as the default with a 100MB limit and a one year retention period.

SQL> create flashback archive default fb_dflt tablespace users4 quota 250m retention 1 year;

Flashback archive created.

SQL>

Sometime depending on your necessity you can enable and disable Flashback Data Archive on a table.
You cannot specify more than one tablespace in the CREATE FLASHBACK ARCHIVE command. You must use the ALTER FLASHBACK ARCHIVE command to add a tablespace

Permissions:

To create or modify a Flashback Data Archive the user must have the permission FLASHBACK ARCHIVE ADMINISTER system privilege
This permission also includes adding and removing tablespaces from an archive, dropping an archive, and performing an ad hoc purge of history data.

Management:

To alter the Flashback Data Archive use the command ALTER FLASHBACK ARCHIVE:

SQL> alter flashback archive fb_dflt add tablespace PRDPMP quota 150m;
Flashback archive altered.
SQL>

You can purge archive data with the PURGE clause. In this example, you want to purge all rows in the FB_DFLT archive before May 17, 2014:

SQL> alter flashback archive fb_dflt purge before timestamp to_timestamp(‘2014-05-17 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);

Including a table into a Flashback Data Archive:

You can include a table to an archive at level creation using standard CREATE TABLE adding of the FLASHBACK ARCHIVE clause,
or with the ALTER TABLE command:

SQL> alter table app.payment flashback archive fb_hr;
Table altered.

You can review the tables that use Flashback Data Archive by querying the data dictionary view DBA_FLASHBACK_ARCHIVE_TABLES:

SQL> select * from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME FLASHBACK_AR ARCHIVE_TABLE_NAME
————– ———- ————- ——————
PAYMENT APP FB_HR SYS_FBA_HIST_32198
Querying Flashback Data Archives

Query historical data for a table in a Flashback Data Archive with the AS OF clause in the table when you are using DML activity stored in an
undo tablespace.

In this case an employee deleted some important rows in the PAYMENT table.

SQL> delete from payment where salary = 10500;
1 row deleted.
SQL> commit;
Commit complete.
SQL>

The HR employee needs to find the deleted salary of 10500
Retrieving the historical information from the PAYMENT table with the AS OF clause specifying a time four weeks ago:

SQL> select name, pay_date, salary
from payment
as of timestamp (systimestamp – interval ’28’ day)
where salary = 10500;

NAME PAY_DATE   SALARY
—– —————–   ——
TONY 25/03/2015   10500

This feature it is a very useful depending of your business requirement.
It is completely transparent to the user whether Oracle is using an undo tablespace or a Flashback Data Archive for a query containing AS OF.

Hope that you enjoy

Cheers
Felipe

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s