Oracle Database 12c: Flashback Data Archive (FDA) – new features

Oracle Database 12c was published in June and among the new features of this release are multiple improvements of the Flashback Data Archive (FDA), an option also known as Oracle Total Recall. In this post, I will present the new features coming with Oracle 12c.

Overview

Before speaking about these improvements, let me point out the main concept of Oracle FDA.

Flashback Data Archive appeared with Oracle 11g and improved the flashback technology introduced with earlier releases of Oracle Database:

• Flashback technology offers a solution to recover from human errors and logical corruptions based on undo data or flashback logs.

• FDA adds the ability to store database information for a long time – for archiving or restoring purposes – based on a dedicated archive tablespace. Data is still accessed with flashback queries (SELECT … AS OF SCN …), but the undo retention is not significant any more, since undo data is immediatly archived in the FDA during the FDA retention period.

There are three scenarios where FDA can be useful:

1) Keeping historical data changes during a predefined retention

For example, a company that wants to keep important data during ten years for a legal reason can use FDA. Data exceeding the retention period will be automatically purged, without any administrative action

2) Navigating in time – like in a “time machine”

With FDA it is possible to go back into the past to get data for comparison purposes. For example, a company that wants to know what its stock was in the beginning of the year can use FDA to compare past data with the current values.

3) Recovering data after a bad manipulation

If some records have been removed or updated accidentally, FDA can be used in order to recover the values with a single query. However, reference constraints are not preserved by FDA.

The following picture, extracted from the Oracle documentation, shows how FDA works:

Each information written in the undo tablespace is also written in the FDA by the FBDA background process.

Setting up your environment

I enabled FDA on an Oracle 12.1.0.1 database in order to test the new features.
First, I created a dedicated tablespace for FDA:

SQL> create tablespace TBS_FDA datafile '/u01/oradata/DB12C/tbs_fda1.dbf' size 2G;

Then, I created a user to access the database, with correct roles and permissions to create tables and use the FDA.

SQL> create user msc identified by *** quota unlimited on USERS,TBS_FDA;
SQL> grant CREATE SESSION, CREATE TABLE to msc;
SQL> grant FLASHBACK ARCHIVE on FBA1 to msc;
SQL> grant FLASHBACK ARCHIVE ADMINISTRATOR to msc;

As “msc” user, I created a flashback archive file:

SQL> connect msc/***
SQL> create flashback archive default FBA1 tablespace TBS_FDA retention 2 day;

Finally, I created a table in the msc schema and performed some DML:

SQL> create table tb_products(prod_id number, prod_desc varchar(50)) flashback archive;
SQL> insert into tb_products values (1,'Oracle 9i');
SQL> insert into tb_products values (2,'Oracle 10g');
SQL> insert into tb_products values (3,'Oracle 11g');
SQL> commit;

With the table DBA_FLASHBACK_ARCHIVE_TABLES, we can see if FDA is activated in the table tb_products:

SQL> select TABLE_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME, STATUS
     from DBA_FLASHBACK_ARCHIVE_TABLES;
 
TABLE_NAME     FLASHBACK_ARCHIVE_NAME      ARCHIVE_TABLE_NAME         STATUS
-------------- --------------------------- -------------------------- ----------
TB_PRODUCTS    FBA1                        SYS_FBA_HIST_91091         ENABLED

Oracle 12c new features

There are some FDA improvements in Oracle Database 12c:

1) User context tracking

This new feature allows tracking the user context and makes it easier to know which user made which changes in a table.
To track user-contexts, Oracle has created two new subprograms in the DBMS_FLASHBACK_ARCHIVE package as well as a new table.

• “set_sys_context” procedure

This procedure allows to set the level of user-context to return. It has only one parameter (LEVEL).
The possible values for the LEVEL parameter are:

• ALL: Obtain all information from sys_context
• TYPICAL: Provides the user id, the global user id and the hostname
• NONE: Nothing

To enable the database to capture user-context, the procedure must be run once with “ALL” or “TYPICAL” value. Example for ALL:

SQL> exec dbms_flashback_archive.set_context_level(level=> 'ALL');

• “get_sys_context” function

This function returns the user-context only if the procedure set_sys_context was previously run with a LEVEL different of NONE.
The function has 3 parameters:

• XID: Transaction identifier provided by the archive history table
• NAMESPACE: Provided by SYS_FBA_CONTEXT_LIST
• PARAMETER: Parameters of namespace

As an example, I have performed an update to generate an XID in the archive table SYS_FBA_HIST_91091:

SQL> update tb_products set prod_desc='Oracle 11g R2' where prod_id=3;

After a few minutes, the background process FBDA recorded undo data in the flashback data archive. The archive table SYS_FBA_HIST_91091 now contains a record for the row, as it was before the update.

SQL> select xid, operation, prod_id, prod_desc from SYS_FBA_HIST_91091;
 
XID              O PROD_ID    PROD_DESC
---------------- - ---------- --------------------------------------------------
05001100D6020000 I 3          Oracle 11g

To know who performed the insert of the product “Oracle 11g” in the table, I have used the get_sys_context function as follows:

declare
  v_xid RAW(8);
  output varchar2(32767);
    begin   
    --Set the variable v_xid with the XID value
    select xid into v_xid from msc.sys_fba_hist_91091 where xid='05001100D6020000';
   
    --Displays the username
    output:= dbms_flashback_archive.get_sys_context (v_xid, 'USERENV', 'SESSION_USER');
    dbms_output.put_line('User: '|| output);
   
    --Displays the hostname of the terminal from where the user is connected
    output:= dbms_flashback_archive.get_sys_context (v_xid,'USERENV','HOST');
    dbms_output.put_line('Hostname: '||output);
   
    --Displays the module from where the user is connected
    output:= dbms_flashback_archive.get_sys_context (v_xid,'USERENV','MODULE');
    dbms_output.put_line('Module: '||output);
  end;/
 
User: MSC
Hostname: vmtest12c1
Module: SQL*Plus

You can consult the Oracle documentation to get the complete overview on available user-context information.

• “sys_fba_context_aud” table

Information returned by the get_sys_context function can also be retrieved from the new table sys_fba_context_aud.
The table contains user-context information for each XID. Joining it to the archive table, we can get the user-context for all DML performed on the table:

SQL> select s.authenticated_identity, s.host, s.module, h.operation, h.prod_id, h.prod_desc
     from sys.sys_fba_context_aud s, msc.sys_fba_hist_91091 h
     where h.xid=s.xid;
 
AUTHENTICATED_IDENTITY   HOST           MODULE          O PROD_ID    PROD_DESC
------------------------ -------------- --------------- - ---------- ---------
msc                      vmtestora11g   SQL*Plus        I 3          Oracle 11g

Note that user-context tracking is a good alternative to the auditing based on database triggers.

2) Database hardening

With Oracle 11g, FDA had to be managed at table level. Starting with Oracle 12c, it is possible to create logical groups of tables for an application: Enabling or disabling of FDA is performed at application level and it prevents the listing of all application tables to enable or disable FDA for each one.

To make the best out of this new feature, the first step is to create and enable an application group. In my example, the application is named “PRODUCTS”:

SQL> exec dbms_flashback_archive.register_application(application_name=>'PRODUCTS',flashback_archive_name=>'FBA1');

‪‪Once the application group is created, the tables must be linked to the group.
In my example, FDA has been disabled on the previous table TB_PRODUCTS. I have created a second table TB_PROVIDERS in the schema msc without enabling the flashback archive.

SQL> create table msc.tb_providers (prov_id number, prov_desc varchar2(50));
SQL> insert into tb_providers values (1,’dbi services’);
SQL> insert into tb_providers values (2,’Oracle’);
SQL> commit;

The table DBA_FLASHBACK_ARCHIVE_TABLES returns no values, meaning that no flashback archive is enabled.

SQL> select table_name, flashback_archive_name, archive_table_name, status
     from DBA_FLASHBACK_ARCHIVE_TABLES;
 
no rows selected

Then, I linked both tb_products and tb_providers tables to the group PRODUCTS:

SQL> exec dbms_flashback_archive.add_table_to_application(application_name=>'PRODUCTS',table_name=>'TB_PRODUCTS',schema_name=>'MSC');
 
SQL> exec dbms_flashback_archive.add_table_to_application(application_name=>'PRODUCTS',table_name=>'TB_PROVIDERS',schema_name=>'MSC');

Groups and linked tables can be listed with SYS_FBA_APP (list of applications) and SYS_FBA_APP_TABLES (list of linked tables):

SQL> select a.appname, b.object_name, c.obj#
     from SYS_FBA_APP a, DBA_OBJECTS b, SYS_FBA_APP_TABLES c
     where a.app#=c.app# and b.object_id=c.obj#;
 
APPNAME                        OBJECT_NAME                    OBJ#
------------------------------ ------------------------------ ----------
PRODUCTS                       TB_PRODUCTS                    91091
PRODUCTS                       TB_PROVIDERS                   91102

It is now possible to enable or disable the FDA on all tables belonging to this application. This is how you enable the FDA on the application PRODUCTS:

SQL> exec dbms_flashback_archive.enable_application(application_name=>'PRODUCTS');

After running this procedure, we can see that the FDA has indeed been enabled for the TB_PRODUCTS and TB_PROVIDERS tables:

SQL> select table_name, flashback_archive_name, archive_table_name, status
     from DBA_FLASHBACK_ARCHIVE_TABLES;
 
TABLE_NAME    FLASHBACK_ARCHIVE_NAME     ARCHIVE_TABLE_NAME     STATUS
------------- -------------------------- ---------------------- ---------
TB_PRODUCTS   FBA1                       SYS_FBA_HIST_91091     ENABLED
TB_PROVIDER   FBA1                       SYS_FBA_HIST_91102     ENABLED

3) Import and export of history

This feature allows to import external history data (e. g. history generated by triggers) into FDA history tables. The external history data must correspond to an FDA enabled table in the database.

The first step is to create a temporary history table which will receive user generated history data. This temporary table can be loaded with datapump or with simple DML. It will later be imported in the FDA enabled table.

In my example, I used the MSC schema with the TB_PRODUCTS table. I chose the DBMS_FLASHBACK_ARCHIVE.CREATE_TEMP_HISTORY_TABLE procedure to create a temporary history table for TB_PRODUCTS:

SQL> exec dbms_flashback_archive.create_temp_history_table('MSC', 'TB_PRODUCTS');

We can see that a new TEMP_HISTORY table has been created in the schema MSC:

SQL> select table_name from dba_tables where owner='MSC';
 
TABLE_NAME
-----------------------------------------------------------------------------
TB_PRODUCTS
TB_PROVIDERS
SYS_FBA_DDL_COLMAP_91091
SYS_FBA_HIST_91091
SYS_FBA_TCRV_91091
TEMP_HISTORY

This temporary table contains TB_PRODUCTS columns and other metadata columns.

SQL> desc msc.temp_history;
 
Name                           Null?     Type
------------------------------ --------- -----------------
RID                                      VARCHAR2(4000)
STARTSCN                                 NUMBER
ENDSCN                                   NUMBER
XID                                      RAW(8)
OPERATION                                VARCHAR2(1)
PROD_ID                                  NUMBER
PROD_DESC                                VARCHAR2(50)

Then I performed an insert to populate the temporary table with external data.

SQL> insert into MSC.TEMP_HISTORY values ('1111111111', '709494', NULL, '03000200BC020000', 'I', 4, 'Oracle 12c');
SQL> commit;

The record is registered into the temporary table:

RID                STARTSCN   ENDSCN     XID              O PROD_ID     PROD_DESC
------------------ ---------- ---------- ---------------- - ----------- -----------
1111111111         709494                03000200BC020000 I 3           Oracle 12c

Finally, the temporary table can be imported into the FDA table with the procedure DBMS_FLASHBACK_ARCHIVE.IMPORT_HISTORY:

SQL> exec DBMS_FLASHBACK_ARCHIVE.IMPORT_HISTORY('MSC', 'TB_PRODUCTS', 'TEMP_HISTORY');

The new record has been added to the SYS_FBA_HIST_91091 table, meaning that FDA has now access to new history data:

SQL> select xid, operation, prod_id, prod_desc from MSC.SYS_FBA_HIST_91091;

 
XID              O PROD_ID    PROD_DESC
---------------- - ---------- --------------------------------------------------
05001100D6020000 I 3          Oracle 11g
03000200BC020000 I 3          Oracle 12c

Note that the TEMP_HISTORY table has been removed after the import.

4) Hybrid Columnar Compression (HCC)

FDA can now be used with HCC compressed tables on Exadata and other Oracle storages. Unfortunately, I was not able to test this technology yet.

Note that if we try to use HCC on a classical storage, the following error occurs:

SQL> ALTER table MSC.TB_PRODUCTS compress for archive high;
ALTER table MSC.TB_PRODUCTS compress for archive high
*
ERROR at line 1:
ORA-64307:  Exadata Hybrid Columnar Compression is not supported for
tablespaces on this storage type

Conclusion

Oracle Database 12c does not add anything revolutionaryto the Flashback Data Archive. But some small improvements make this tool more complete and easier to manage.