Oracle 12c (12.1.0.1) has below enhancements in FDA
- Data Hardening
- User context tracking
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 (
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
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(
SQL> Begin
DBMS_FLASHBACK_ARCHIVE.enable_application(
application_name => 'ORACLERACEXPERT');
end;
/
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.
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> 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
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%';
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.
