Flashback Data Archive enhancements in Oracle 12c

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.

Oracle 12c (12.1.0.1) has below enhancements in FDA

• Data Hardening
• User context tracking

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

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

SQL> Begin
DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION (

application_name=> 'ORACLERACEXPERT', 

table_name=> 'EMP' , 

schema_name -> 'USER1');
end;
/

SQL> Begin
DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION (

application_name=> 'ORACLERACEXPERT', 

table_name=> 'DEPT' , 

schema_name -> 'USER1');
end;
/

You can remove the tables using REMOVE_TABLE_FROM_APPLICATION procedure from

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

SQL> Begin
DBMS_FLASHBACK_ARCHIVE.enable_application(

application_name => 'ORACLERACEXPERT');
end;
/

To disable the application use disable_application procedure

SQL> 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*Plus

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.

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

SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 600
SQL> COLUMN owner FORMAT A10
SQL> COLUMN table_name FORMAT A25

SQL> SELECT owner, table_name FROM dba_tables WHERE table_name LIKE '%FBA%';

 
OWNER       TABLE_NAME
--------------  -------------------------
SYS              SYS_FBA_FA
SYS              SYS_FBA_TSFA
SYS              SYS_FBA_TRACKEDTABLES
SYS              SYS_FBA_PARTITIONS
SYS              SYS_FBA_USERS
SYS              SYS_FBA_BARRIERSCN
SYS              SYS_FBA_DL
SYS              SYS_FBA_CONTEXT
SYS              SYS_FBA_CONTEXT_AUD
SYS              SYS_FBA_CONTEXT_LIST
SYS              SYS_FBA_APP
SYS              SYS_FBA_APP_TABLES
SYS              SYS_FBA_COLS
SYS              SYS_FBA_PERIOD
SYS              SYS_MFBA_STAGE_RID
SYS              SYS_MFBA_TRACKED_TXN
SYS              SYS_MFBA_NROW
SYS              SYS_MFBA_NCHANGE
SYS              SYS_MFBA_NTCRV

You can refer below Oracle Doc for best practices

FDA - Flashback Data Archive Usage and Best Practices (Doc ID 2370465.1)

Flashback Data Archive provides many benefits for maintaining historic data against tracked tables. The FDA helps to perform undo-based flashback operations for an extended period and take advantage of this feature.