Skip to main content

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!!


Popular posts from this blog

Setting NLS_LANG on Windows for Oracle Database

Sometimes, it is difficult to deal with Windows Platform as it drains the hell out of us!! Recently, I have come across a situation where one of my client's requirement was to input Arabic language into Oracle Database [or] read/retrieve the output into Various client Applications such as PL/SQL Developer/SQL Developer/TOAD etc.. Inputting the non-english language into Database  has never been difficult as we are given plenty of language options within our beloved Oracle Database, but, the problem lies within the complex Windows OS when user wanted to view the data in his/her beloved language, in Applications such as PL/SQL Developer or SQL Developer etc.. This post is all about it.

Character Set, Character encoding, & Code point Yes, it is a group of characters that is recognised by the Hardware through the OS Interface. Every character is allocated a number, called a code point, these code points will be represented in the computer by one or more bytes. So, every character h…

Data Guard Configuration with Oracle DGMGRL

About Data GuardEfficient business operations, quality customer service, compliance with government regulations, and safeguarding corporate information assets all require high levels of data protection and data availability. Thus it is no surprise that data protection and data availability are among the top priorities for enterprises of all sizes and industries.
A set of questions one needs to ask him/herself is what If a disaster impacts ongoing business transactions, How soon can those systems be back to business again, How much Data is affordable to loose, is it acceptable? The only answer for all these questions to position a proper "Business Continuity Plan (BCP)" in place so that it helps business to grow inline with its consumer expectations.  Technically, this is where Oracle's Data Guard plays a vital role that ensures superior reliability, and rock solid performance. DataGuard is Oracle's complete Disaster Recovery Solution which can reliably deliver aggressi…

IPC Send timeout detected - The Story of a Database

Some errors make us panic, nervous, and beyond belief when we positively attempt to resolve a panic situation. Specific to case of Clusterware where it attempts to resolve a conflict by removing dead resources from the cluster but it suffers because of lack of additional resources to complete the task, and makes the whole situation even worst so everything that has in contact with the key resource also suffers. The key resource what we are talking is a RAC Database and the Clusterware under a panic situation is Oracle Clusterware.

Here's the environment
Oracle RDBMS 11.2.0.4.4Oracle GI 11.2.0.4.43 Node RAC Database with vault function enabledNetApp NFS Filer based Storage
What was the issue?
On Instance 3 I have noticed "IPC Send timeout". Fri Aug 14 04:46:05 2015 IPC Send timeout detected. Receiver ospid 20545 [ Fri Aug 14 04:49:55 2015 And then, Oracle has started suspending MMON actions one by one as we noticed below messages in the log Fri Aug 14 10:49:07 2015 Suspending M…