Blog - comments

Hi Olivier, That's a good question. I think the reason is that data_object_id cannot identify a segm...
@toms and others: -- create your output: iozone -Rab output.xls /u01-- scp off to desptop, or to loc...
Scott
Hi Frank,Very interesting post ! Congratulations for starting an Oracle 7 ; did you virtualize an HP...
Olivier Berthommé
Belle démo, je crois que je vais te la piquer ! Mais as tu essayé un DBCC CHECKCONSTRAINTS ou un DBC...
SQLpro

Thanks Franck , you are a savior! Great article indeed.

Prasad
Blog Michael Schwalm Oracle Database 12c: Flashback Data Archive (FDA) - new features

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

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:

 concept

 

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.

Rate this blog entry:
3

Michael Schwalm is Consultant at dbi Services and has more than two years of experience in Oracle database administration. He has a broad knowledge in the realization of virtualization infrastructures such as vMware vSphere. He took his first steps in database administration as an integrator of a web applications on Unix, Oracle, and Websphere environments. Michael Schwalm is Oracle Certified Professional 11g and RAC Implementation Specialist 11g. Prior to joining dbi services, Michael Schwalm was application administrator at SOGETI Est (F) on behalf of PSA Peugeot Citroen and responsible for the realization and managing of Unix environments and Oracle databases in the context of migration projects. Michael Schwalm holds a BTS diploma in Information System Management from Belfort (F) and a TSAR diploma in advanced network administration from Strasbourg (F). His branch-related experience covers Automotive, Software industry, Financial Services / Banking, etc.

Comments

  • Guest
    Tom Friday, 05 September 2014

    If I were to import legacy data into my flashback how would it handle the fact that the scn to timestamp mapping will not exist. If my data is 2001 and this is a new DB certainly there is no mapping for 2001 in the scn to timestamp table.

Leave your comment

Guest Saturday, 01 November 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter