Monday, April 15, 2013

Oracle Total Recall (Flashback Data Archive)

How hard and painful it used to be archiving historical data that required an Application intelligence or a dedicated database to keep history of tables, certainly a daunting task for DBAs and Application Developers. We needed to procure new Hardware or overuse of existing resources, it always used to happen at an additional unacceptable cost for IT Infrastructure and maintenance.

To overcome such issues, Oracle has introduced a feature "Total Recall" with version 11g, called Flashback Data Archive technically.


What is it and How it works?

Flashback Data Archive is an in-built archiving process that is taken care by Oracle background process called FBDA(FlashBack Data Archive). It keeps a track of all transactional changes occur on Flashabck Data Archive enabled tables over a specified period of time.
When a transaction is committed on a tracked table, FBDA snaps the before-image of the rows in the archive. FDBA maintains metadata on the current rows and tracks how much data has been archived. FBDA is also responsible for automatically managing the flashback data archive for space, organization (partitioning tablespaces), and retention. FBDA also keeps track of how far the archiving of tracked transactions has progressed.


What is required to create and control?

A Tablespace with Flashback Data Archive ability
FLASHBACK ARCHIVE object privilege
FLASHBACK ARCHIVE ADMINISTER privilege

What are its limitations so far?
It can not be enabled on nested, clustered, temporary, remote, or external tables
It can not be enabled on tables with LONG nor nested columns

How to enable it?

Step (1) : Create Flashback Data Archive
Login as SYSDBA or the user with "CREATE FLASHBACK ARCHIVE" privilege
CREATE FLASHBACK ARCHIVE DEFAULT FDA1 TABLESPACE USERS QUOTA 100M RETENTION 1 YEAR;
Flashback archive created.
We notice the below logging information in the alert log while the above command is executed,

Starting background process FBDA

Mon Apr 15 06:12:27 2013
FBDA started with pid=41, OS id=17443 
The above command creates a default flashback archive that gives any flashback enabled table the ability to push transactional changes automatically even though an archive is unspecified. 

Below command creates a simple Flashback Data Archive,


SQL> CREATE FLASHBACK ARCHIVE SCOTT_FDA1 TABLESPACE USERS QUOTA 100M RETENTION 1 YEAR;
Flashback archive created.

Step(2) : Enable Flashback Archive for a table

I am using SCOTT schema for demo purposes, and create a table with FLASHBACK ARCHIVE ability as below
SQL> GRANT FLASHBACK ARCHIVE ON SCOTT_FDA1 TO SCOTT;
Grant succeeded.
SQL> CREATE TABLE TEST_FDA(SNO NUMBER,NAME CHAR(20),OS VARCHAR(20)) FLASHBACK ARCHIVE SCOTT_FDA1;
Table created.

Following tables/views can always be queries to find the information aboie Flashback Archives created/enabled in the Database

USER_FLASHBACK_ARCHIVE_TABLES
USER_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TS
DBA_FLASHBACK_ARCHIVE_TABLES
DBA_FLASHBACK_ARCHIVE

Step(3) : Test If transactions being archived
SQL> INSERT INTO TEST_FDA VALUES(&SNO,'&NAME','&OS');
Enter value for sno: 1
Enter value for name: SCOTT
Enter value for os: OEL
old   1: INSERT INTO TEST_FDA VALUES(&SNO,'&NAME','&OS')
new   1: INSERT INTO TEST_FDA VALUES(1,'SCOTT','OEL')
1 row created.
SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM TEST_FDA;
       SNO NAME OS
---------- -------------------- --------------------
1 SCOTT OEL

SQL> DELETE TEST_FDA;
1 row deleted.
SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM TEST_FDA;
no rows selected

Let's check if the insert is still available that should be archived
SQL> SELECT * FROM TEST_FDA AS OF TIMESTAMP TO_TIMESTAMP ('2013-04-15 06:59:00', 'YYYY-MM-DD HH24:MI:SS');
       SNO NAME OS
---------- -------------------- --------------------
1 SCOTT OEL

How to recover Data using Flashback Data Archive?
Let's recover the record that we have deleted on demo
SQL> INSERT INTO TEST_FDA SELECT * FROM TEST_FDA AS OF TIMESTAMP TO_TIMESTAMP ('2013-04-15 06:59:00', 'YYYY-MM-DD HH24:MI:SS');
1 row created.

SQL> SELECT * FROM TEST_FDA;
       SNO NAME OS
---------- -------------------- --------------------
1 SCOTT OEL


Command Line Help
To enable Flashback Data Archive on an existing table
SQL> ALTER TABLE EMP FLASHBACK ARCHIVE SCOTT_FDA1;
Table altered.

To disable Flashback Data Archive

It requires "FLASHBACK ARCHIVE ADMINISTER" privilege in order to disable, grant it using SYSDBA
SQL> GRANT FLASHBACK ARCHIVE ADMINISTER TO SCOTT;
Grant succeeded.
Login as SCOTT

SQL> ALTER TABLE EMP NO FLASHBACK ARCHIVE;
Table altered.

To increase or decrease the retention time period of an archive
Login as archive owner (SYSDBA in our case)

SQL> ALTER FLASHBACK ARCHIVE SCOTT_FDA1 MODIFY RETENTION 2 YEAR;
Flashback archive altered.
Note: when we decrease the retention period, It by default purges the oldest records

To purge all historical data from a specific archive

Login as archive owner (SYSDBA in our case)

SQL> ALTER FLASHBACK ARCHIVE SCOTT_FDA1 PURGE ALL;
Flashback archive altered.

To drop a flashback archive

Login as archive owner (SYSDBA in our case)

SQL> DROP FLASHBACK ARCHIVE FDA1;
Flashback archive dropped.

To drop a table that has flashback archive enabled
When we attempt to drop the flashback archive table, we encounter the following error
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
To drop it:
First, disable archiving on this table
SQL> ALTER TABLE EMP NO FLASHBACK ARCHIVE;
Table altered.
Second, drop the table
SQL>DROP TABLE EMP PURGE;
Table dropped.


More to follow
Oracle has improved the feature in 11g Release 2 by giving the ability to control over DDL statements as mentioned below,

ALTER TABLE statement that does any of the following:


  • Adds, drops, renames, or modifies a column
  • Adds, drops, or renames a constraint
  • Drops or truncates a partition or subpartition operation
  • TRUNCATE TABLE statement
  • RENAME statement that renames a table 


I hope this helps you, comments or feedback most welcome!!