Mastering Data History : A Guide using Oracle's Flashback Data Archive Feature

The Historical data is key for business decisions and having reliable data is very important for organizations. There are frequent implications for both financial and legal data for organizations and having a secure and accurate history of change of data is crucial for business. Oracle Flashback technology offers significant benefits for database management and recovery to meet the organizational needs. 

In Oracle Flashback technology one of the key features is to view past status of Database objects without performing point-in-time recovery of complete database, Oracle database uses undo data to perform this activity.  Flashback feature uses Automatic Undo Management for historical and metadata transactions.

Flashback Data archive (FDA) feature introduced in Oracle 11g for undo-based flashback operations, and it is configured using retention time. Flashback data archive supported for multitenant (12.1.0.2 and above versions) as well using local or shared undo configuration. Using Flashback Data archive, you can track Database changes in a secure and efficient way. It provides centralized historical data store by efficiently using the resources. 

Using Flashback feature you can

• You can recover tables or objects to a point in time
• You can rollback a transaction
•  You can query that return history of metadata
• You can run queries that return past data

You can enable flashback data archive, but you must meet below conditions.

•  The table is not nested, remote, external, or temporary
•  The table has neither nested column or LONG column
•  The table does not any reserved words as column names such as START SCN, END SCN, OP, OPERATION, XID, RID
•  The user should have FLASHBACK ARCHIVE object privilege

Creating and Managing Flashback Data Archive (FDA) : Before you enable FDA, you need to consider following  

•   Allocate sufficient storage to handle retention period
•  For Faster access create indexes on the History tables
•  Ideal approach is to create FDA on tables with primary key
• Enable FDA on tables which are important for business not on all tables in a schema

Enable Flashback Data Archive: You can create Tablespace and enable Flashback Data Archive using below commands with 1 year retention using below command

SQL> CREATE TABLESPACE tbs1 DATAFILE ‘\oracle\data\ tbs1.dbf’ SIZE 1G AUTOEXTEND ON MAXSIZE 15G;

SQL> ALTER USER ORASCH QUOTA UNLIMITED ON tbs1;

SQL> CREATE FLASHBACK ARCHIVE DEFAULT fda1 TABLESPACE tbs1 QUOTA 5G RETENTION 1 YEAR;         

When you enable Flashback Data archive there is a Flashback Data Archiver Process (FBDA) process runs in background to track and store transactional changes.

You can query DBA_FLASHBACK_ARCHIVE and DBA_FLASHBACK_ARCHIVE_TS views to see the information about Flashback Data Archive and associated tablespaces.

SET PAGESIZE 100
SET LINESIZE 600
COLUMN owner_name FORMAT A10
COLUMN flashback_archive_name FORMAT A25
COLUMN flashback_archive# FORMAT 99999
COLUMN retention_in_days FORMAT 99999
COLUMN create_time FORMAT A20
COLUMN last_purge_time FORMAT A20
COLUMN status FORMAT A10
SELECT owner_name,
      flashback_archive_name,
      flashback_archive#,
      retention_in_days,
      TO_CHAR(create_time, 'MM-DD-YYYY HH24:MI:SS') AS create_time,
      TO_CHAR(last_purge_time, 'MM-DD-YYYY HH24:MI:SS') AS last_purge_time,
      status
FROM  dba_flashback_archive
ORDER BY owner_name, flashback_archive_name;
OWNER_NAME          FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME         LAST_PURGE_TIME      STATUS
------------------ ---------------------- ------------------ ----------------- -------------------- -------------------- -------
SYS                 FDA1                                  1                365  05-02-2023 13:10:32 05-02-2023 13:10:32  DEFAULT        

You can ALTER the use Flashback Data archive using below commands

To Add 5 GB from Tablespace tbs2 to Flashback Data archive fda1

SQL> ALTER FLASHBACK ARCHIVE fda1 ADD TABLESPACE tbs2 QUOTA 5G;        

To change Flashback Data archive fda1 quota on tbs1 to 10G

SQL> ALTER FLASHBACK ARCHIVE fda1 MODIFY TABLESPACE tbs1 QUOTA 10G;        

To Add tbs3 to Flashback Data archive fda1 without any specific quota

SQL> ALTER FLASHBACK ARCHIVE fda1 ADD TABLESPACE tbs3;        

To remove tbs3 from Flashback Data archive fda1

SQL> ALTER FLASHBACK ARCHIVE fda1 REMOVE TABLESPACE tbs3;        

Change Flashback Data archive fda1 retention to 2 years

SQL> ALTER FLASHBACK ARCHIVE fda1 MODIFY RETENTION 2 YEAR;        

To purge all historical data from Flashback Data archive fda1

SQL> ALTER FLASHBACK ARCHIVE fda1 PURGE ALL;        

To DROP flashback Data archive fda1

SQL> DROP FLASHBACK ARCHIVE fda1;        

Before you enable Flashback Data archive for a table you must enable Automatic undo Management (AUM) otherwise you will receive error ORA-55614.

You can get the Flashback Archive information by running queries against views

*_FLASHBACK_ARCHIVED      -  Info on Flashback Archive files

*_FLASHBACK_ARCHIVE_TS - Info on Tablespaces of Flashback Archive files

*_FLASHBACK_ARCHIVE_TABLES  -  Info on Tables that are enabled for Flashback Archive

Grant permission: To enable Flashback Data Archive on a table the user must have “FLASHBACK ARCHIVE” permission  

SQL> GRANT FLASHBACK ARCHIVE ON fda1 TO orasch;        

Now the ORASCH schema has permission to write historical data into Flashback Data Archive “FDA1”.

Note: If you need to disable Flashback Data Archive then you should have SYSDBA, or FLASHBACK ARCHIVE ADMINISTER privilege.

Track Data Changes:  The Flashback Data Archive is disable for any Table by default. When you enable Flashback Data archive on a table all changes to rows are tracked and recorded in Flashback Data archive for the retention period specified during the creation.

The history data is read-only and cannot be altered by schema owner or the administrator.

For demonstration creating EMP, DEPT tables and will insert some test data.

Create a table DEPT without associating with Flashback Data archive.

SQL> CREATE TABLE dept (
Deptno                NUMBER,
deptname              VARCHAR2 (15),
loc                   VARCHAR2 (20),
constraint pkey_dept primary key (deptno)
);        

You can alter an existing table DEPT to use Flashback Data archive using below command.

SQL>   ALTER TABLE dept FLASHBACK ARCHIVE fda1;        

To disable Flashback Data archive fda1 for table DEPT

SQL> ALTER TABLE dept NO FLASHBACK ARCHIVE fda1;        

Create table EMP in the ORASCH schema associated with Flashback Data archive FDA1

SQL> CREATE TABLE emp (
 empno               NUMBER,
 empname             VARCHAR2(30),
 salary              NUMBER, 
 hiredata            DATE,
 dept no             NUMBER, 
 constraint pkey_emp primary key (empno),  
 constraint fkey_deptno foreign key (deptno) references dept (deptno)  
 ) FLASHBACK ARCHIVE fda1;        

You can query DBA_FLASHBACK_ARCHIVE_TABLES views to see the information about Flashback Data Archive associated tables

SET PAGESIZE 100
SET LINESIZE 600
COLUMN owner_name FORMAT A10
COLUMN table_name FORMAT A15
COLUMN flashback_archive_name FORMAT A25
COLUMN archive_table_name FORMAT A25
COLUMN status FORMAT A10
 
SELECT owner_name,
      table_name,
      flashback_archive_name,
      archive_table_name,
      status
FROM  dba_flashback_archive_tables
Where table_name= ‘EMP’;

OWNER_NAME  TABLE_NAME  FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME       STATUS
----------- ----------- ---------------------- ------------------------ ------------------
ORASCH       EMP         FDA1                   SYS_FBA_HIST_93222       ENABLED        

SYS_FBA_HIST_*” are the internal History Tables

Flashback Data Archive supports below DDL statements only

•  ALTER TABLE statement

o  Add, rename, drop or modify a columns

o  Add, rename, or drop a constraint

o  drop or truncate a partition or sub partition

• RENAME a table
• TRUNCATE table

When working on Flashback Data archive adding or enabling constraint on a table where FDA enabled will result ORA-55610 error

Flashback Data Archive does not support move, split, merge, or coalesce partitions or sub partitions, or convert LONG to LOB columns. If user try to run unsupported DDL they may encounter ORA-55610 error

You can take advantage of OPTIMIZE DATA clause to enable advance compression for the specified Flashback Data Archive.

SQL> ALTER FLASHBACK ARCHIVE fda1 TABLESPACE tbs1 QUOTA 15g RETENTION 3 YEAR OPTIMIZE DATA;        

Oracle partitions history table using range partition and data compression when you enable OPTIMIZE DATA. Oracle automatically takes care of partition and compression of history data without any Database administrator involvement.

Note: Oracle Advance compression and partition are NOT free, and user must have or purchase license. 

Oracle 12c (12.1.0.1) has below enhancements in FDA

• Data Hardening – This feature helps to associate set of tables for a specific application, so that you can enable Flashback Data archive for all those tables in a single command. Use DMBS_FLASHBACK_ARCHIVE.REGISTER_APPLICATION to register an application

You can create new application using REGISTER_APPLICATION 

BEGIN
 DBMS_FLASHBACK_ARCHIVE.register_application(
   application_name      => 'ORACLERACEXPERT',
   flashback_archive_name => 'FDA1');
END;
/        

You can add tables to the application using ADD_TABLE_TO_APPLICATION procedure

BEGIN
DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION (application_name=> 'ORACLERACEXPERT', table_name=> 'EMP' , schema_name => 'ORASCH');
END;
/

BEGIN
DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION (application_name=> 'ORACLERACEXPERT', table_name=> 'DEPT' , schema_name ==> 'ORASCH');
END;
/        

You can remove the tables using REMOVE_TABLE_FROM_APPLICATION procedure from

Begin
DBMS_FLASHBACK_ARCHIVE.REMOVE_TABLE_FROM_APPLICATION (application_name=> 'ORACLERACEXPERT', table_name=> 'EMP' , schema_name => 'ORASCH');
END;
/

Begin
DBMS_FLASHBACK_ARCHIVE.REMOVE_TABLE_FROM_APPLICATION (application_name=> 'ORACLERACEXPERT', table_name=> 'DEPT' , schema_name => 'ORASCH');
END;
/        

The application will not enable automatically, use ENABLE_APPLICATION procedure to enable Flashback Data Archive for all tables in the specified application.

BEGIN
 DBMS_FLASHBACK_ARCHIVE.enable_application(
 application_name => 'ORACLERACEXPERT');
END;
/        

To disable the application use disable_application procedure

BEGIN
 DBMS_FLASHBACK_ARCHIVE.disable_application(
 application_name => 'ORACLERACEXPERT');
END;
/        

• User context tracking - By enabling this feature it is easy to track which user made what changes to the table.

Use DMBS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL procedure to Set the user content level and procedure DMBS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT procedure To Access the context

There are 3 options depending upon how much user context needs to save

• ALL – The entire SYS_CONTEXT is stored
• TYPICAL – The user context is stored
• NONE- Nothing

For ex: - to set context level to ALL

SQL> DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL ( level=>‘ALL’);

You can get the XID from the archive table

SQL> select XID from SYS_FBA_HIST_93222
XID
----------------
05000A0B7040000;        

Now with XID you can get the context information using dbms_flashback_archive.get_sys_context procedure

SQL> BEGIN
      dbms_output.put_line(dbms_flashback_archive.get_sys_context ('05000A0B7040000', 'USERENV', 'SESSION_USER'));
      dbms_output.put_line(dbms_flashback_archive.get_sys_context ('05000A0B7040000', 'USERENV', 'HOST'));
      dbms_output.put_line(dbms_flashback_archive.get_sys_context ('05000A0B7040000', 'USERENV', 'MODULE'));
     END;
 /
 USER1
 SRVHOST
 SQL*Plusn        

You can get all Transactions ID using below query

SQL> select empno, empname, VERSIONS_XID 
     from EMP
     order by empno;

   EMPNO EMPNAME     VERSIONS_XID
-------- ----------- ----------------
       1 ORARAC      05000A0B7040000           

You can use SYS_FBA_CONTEXT_AUD to get context information for each transaction.

In Oracle 19c, they rebranded the feature called “Flashback Time Travel”. The Flashback Time travel is a nothing but a combination Flashback Data archive and Flashback Query AS OF and Versions.

Flashback time travel feature also automatically track transactional changes and store over its lifetime and helpful accessing historical data, compliance, audit, selective data recovery and digital shredding by reducing the cost.  It is a tamper proof feature and not even privileged user can modify the time travel data.  

Scenario 1:  Use Flashback Time travel for retrieving historical data

For example, to retrieve historical data for all employees joined at the beginning of June 2010 use below query.  

SQL> SELECT empno, empname FROM EMP AS OF TIMESTAMP TO_TIMESTAMP (06-01-2010 00:00:00', 'MM-DD-YYYY HH24:MI:SS');        

Scenario 2: Use Flashback Time Travel for Audit purpose

For example, to retrieve employee’s data who salary increments not updated correctly on July 1st, 2015, use below query to verify data. 

SQL> SELECT empno, empname FROM emp AS OF TIMESTAMP TO_TIMESTAMP (07-01-2015 00:00:00', 'MM-DD-YYYY HH24:MI:SS');        

Scenario 3: Use Flashback Time travel to recover data

For example, to recover an employee (‘Scott Ryan’ ) data removed accidently 3 days back use below queries.  

First retrieve employee (‘Scott Ryan’) data as of 3 days back and verify any other transactions made with respect to the employee.

SQL> SELECT * FROM emp AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘3’ DAY) where EMPNAME=‘SCOTT RYAN’;        

Once verified recover data by retrieving employee (‘Scott Ryan’) data as of 3 days back and insert into EMP table.

SQL> INSERT INTO emp SELECT * FROM emp AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘3’ DAY) WHERE empname=‘SCOTT RYAN’;

Scenario 4: Use Flashback Time travel to enforce Digital Shredding

When you create Flashback Data Archive you specify a retention. The data will be purged when it exceeds the retention period.

For example, Flashback Data archive FDA3 created with 5 year retention and any data exceeds 5 year retention period will be deleted.  

SQL> CREATE FLASHBACK ARCHIVE fda3 TABLESPACE tbs3 RETENTION 5 YEAR;

There are many Flashback data archive views available and to get the list of all views run below query.

SET PAGESIZE 100
SET LINESIZE 600
COLUMN owner FORMAT A10
COLUMN table_name FORMAT A25
SELECT owner, table_name FROM dba_tables WHERE table_name LIKE '%FBA%';        

For more information you can refer "Flashback Data Archive Usage and Best Practices (Doc ID 2370465.1)"

Conclusion

The historical data is key for business decisions and having reliable data is very important for organizations. Flashback Data Archive provides centralized historical data store by tracking changes in a secure and efficient way, which helps organization with compliance, audit, historical reporting, selective data recovery and digital shredding.