8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Flashback Data Archive (FDA) Enhancements in Oracle Database 12c Release 1 (12.1)
Flashback Data Archive (FDA), also known as Flashback Archive (FBA), was introduced in Oracle 11g to provide long term storage of undo data, allowing undo-based flashback operations to be performed over an extended period of time. Oracle database 12c includes a number of changes that will allow FDA to reach a wider audience.
Thanks to Bjoern Rost and Connor McDonald for helping me when I couldn't see the wood for the trees. :)
- Licensing Changes (All Database Versions for Free)
- User-Context Tracking
- Export/Import Table History
- Database Hardening
- FDA Optimization (Compression)
- Hybrid Columnar Compression (HCC) Support
- Multitenant Support (CDB/PDB) (12.1.0.2 Onward)
- Relevant SYS Tables
- Flashback Time Travel (19c)
Related articles.
- Flashback Data Archive (Oracle Total Recall)
- Flashback New Features and Enhancements in Oracle Database 10g
Licensing Changes (All Database Versions for Free)
In previous releases, flashback data archive used compression features from the advanced compression option, so it could only be used with enterprise edition and the advanced compression option. In Oracle 12c, the default when creating flashback data archives is no compression, so it is available for free in all editions of the database. This new default setting has also been back-ported to 11.2.0.4, making it free in all editions that can be patched to that level.
User-Context Tracking
Although FDA has always been able to track the changes to data, it was not able to provide a complete audit of the changes unless columns were present in the table to indicate who made those changes. In Oracle 12c, the contents of the contexts available from SYS_CONTEXT
calls, including USERENV
, can be stored along with the data changes, allowing for a complete audit of both the data changes and the user environment setting. This allows FDA to be used in place of custom audit tables within applications.
The following code creates a new tablespace and an FDA with a 1 year retention period. The FLASHBACK ARCHIVE
privilege granted on the FDA to the TEST
user.
CONN sys@pdb1 AS SYSDBA CREATE TABLESPACE fda_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M; ALTER USER test QUOTA UNLIMITED ON fda_ts; CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_ts QUOTA 10G RETENTION 1 YEAR; GRANT FLASHBACK ARCHIVE ON fda_1year TO test; GRANT FLASHBACK ARCHIVE ADMINISTER TO test; GRANT EXECUTE ON DBMS_FLASHBACK_ARCHIVE TO test; GRANT CREATE ANY CONTEXT TO test;
Information about the FDA is displayed using the DBA_FLASHBACK_ARCHIVE
and DBA_FLASHBACK_ARCHIVE_TS
views. The following queries are available as fda.sql and fda_ts.sql.
SET LINESIZE 150 COLUMN owner_name FORMAT A20 COLUMN flashback_archive_name FORMAT A22 COLUMN create_time FORMAT A20 COLUMN last_purge_time FORMAT A20 SELECT owner_name, flashback_archive_name, flashback_archive#, retention_in_days, TO_CHAR(create_time, 'DD-MON-YYYY HH24:MI:SS') AS create_time, TO_CHAR(last_purge_time, 'DD-MON-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 FDA_1YEAR 1 365 06-JAN-2015 19:30:57 06-JAN-2015 19:30:57 DEFAULT SQL> SET LINESIZE 150 COLUMN flashback_archive_name FORMAT A22 COLUMN tablespace_name FORMAT A20 COLUMN quota_in_mb FORMAT A11 SELECT flashback_archive_name, flashback_archive#, tablespace_name, quota_in_mb FROM dba_flashback_archive_ts ORDER BY flashback_archive_name; FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB ---------------------- ------------------ -------------------- ----------- FDA_1YEAR 1 FDA_TS 10240 SQL>
Create a table in the test user schema and associate it with the FDA.
CONN test/test@pdb1 DROP TABLE tab1 PURGE; CREATE TABLE tab1 ( id NUMBER, description VARCHAR2(50), CONSTRAINT tab_1_pk PRIMARY KEY (id) ) FLASHBACK ARCHIVE fda_1year; -- Use ALTER TABLE to associate an existing table. -- ALTER TABLE tab1 FLASHBACK ARCHIVE fda_1year;
Information about the FDA and the associated table is displayed using the DBA_FLASHBACK_ARCHIVE_TABLES
view. The following query is available as fda_tables.sql.
SET LINESIZE 150 COLUMN owner_name FORMAT A20 COLUMN table_name FORMAT A20 COLUMN flashback_archive_name FORMAT A22 COLUMN archive_table_name FORMAT A20 SELECT owner_name, table_name, flashback_archive_name, archive_table_name, status FROM dba_flashback_archive_tables ORDER BY owner_name, table_name; OWNER_NAME TABLE_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS -------------------- -------------------- ---------------------- -------------------- ------------- TEST TAB1 FDA_1YEAR SYS_FBA_HIST_95999 ENABLED SQL>
To make sure the context information is stored with the transaction data, we need to use the DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL
procedure, passing one of the following parameter values.
TYPICAL
: Only basic auditing attributes from theUSERENV
context are stored.ALL
: All contexts available to the user via theSYS_CONTEXT
function are stored.NONE
: No context information is stored.
In this case use ALL
, so we capture the USERENV
and custom context values.
CONN sys@pdb1 AS SYSDBA EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('ALL');
Remember to wait at least 15 seconds between creating the FDA associated table and committing any DML or information may be lost.
Create a custom context and management package for use in the test.
CONN test/test@pdb1 CREATE OR REPLACE CONTEXT test_context USING test_ctx_api; CREATE OR REPLACE PACKAGE test_ctx_api AS PROCEDURE set_value (p_name IN VARCHAR2, p_value IN VARCHAR2); END test_ctx_api; / CREATE OR REPLACE PACKAGE BODY test_ctx_api AS PROCEDURE set_value (p_name IN VARCHAR2, p_value IN VARCHAR2) AS BEGIN DBMS_SESSION.set_context('test_context', LOWER(p_name), p_value); END; END test_ctx_api; /
Insert and amend some data. Between each action we alter the V$SESSION.CLIENT_IDENTIFIER
value and alter the value in our custom context.
CONN test/test@pdb1 EXEC DBMS_SESSION.set_identifier('Peter Pan'); EXEC test.test_ctx_api.set_value('my_attribute','First Action'); INSERT INTO tab1 VALUES (1, 'ONE'); COMMIT; EXEC DBMS_SESSION.set_identifier('Peter Parker'); EXEC test_ctx_api.set_value('my_attribute','Second Action'); UPDATE tab1 SET description = 'TWO' WHERE id = 1; COMMIT; EXEC DBMS_SESSION.set_identifier('Peter Rabbit'); EXEC test_ctx_api.set_value('my_attribute','Third Action'); UPDATE tab1 SET description = 'THREE' WHERE id = 1; COMMIT;
Check the context information is working as expected. The following flashback version query shows the history of the data changes in the table, including the SESSION_USER
value from the stored USERENV
information. Stored context information is retrieved using the DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT
function, which requires the transaction ID (XID), context namespace and parameter name. Some of the flashback version query pseudocolumns have been commented out to reduce the size of the output.
CONN test/test@pdb1 COLUMN versions_startscn FORMAT 99999999999999999 COLUMN versions_starttime FORMAT A24 COLUMN versions_endscn FORMAT 99999999999999999 COLUMN versions_endtime FORMAT A24 COLUMN versions_xid FORMAT A16 COLUMN versions_operation FORMAT A1 COLUMN description FORMAT A11 COLUMN session_user FORMAT A20 COLUMN client_identifier FORMAT A20 COLUMN my_attribute FORMAT A20 SET LINESIZE 200 SELECT versions_startscn, --versions_starttime, versions_endscn, --versions_endtime, versions_xid, versions_operation, description, DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS session_user, DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','CLIENT_IDENTIFIER') AS client_identifier, DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'test_context','my_attribute') AS my_attribute FROM tab1 VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP-(1/24/60) AND SYSTIMESTAMP WHERE id = 1 ORDER BY versions_startscn; VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V DESCRIPTION SESSION_USER CLIENT_IDENTIFIER MY_ATTRIBUTE ------------------ ------------------ ---------------- - ----------- -------------------- -------------------- -------------------- 2536699 2536792 060010000B0A0000 I ONE TEST Peter Pan First Action 2536792 2536826 0A00110076060000 U TWO TEST Peter Parker Second Action 2536826 020003005B080000 U THREE TEST Peter Rabbit Third Action SQL>
Remember, if you want to drop the table, you must first remove it from the FDA. Some of the following examples make use of this table, so delay dropping it until you have worked through them.
CONN sys@pdb1 AS SYSDBA ALTER TABLE test.tab1 NO FLASHBACK ARCHIVE; DROP TABLE test.tab1 PURGE;
Export/Import Table History
The DBMS_FLASHBACK_ARCHIVE
package contains routines to allow the history of a specified table to be exported and imported.
Check the name of the archive associated with the table of interest.
SET LINESIZE 150 COLUMN owner_name FORMAT A20 COLUMN table_name FORMAT A20 COLUMN flashback_archive_name FORMAT A22 COLUMN archive_table_name FORMAT A20 SELECT owner_name, table_name, flashback_archive_name, archive_table_name, status FROM dba_flashback_archive_tables ORDER BY owner_name, table_name; OWNER_NAME TABLE_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS -------------------- -------------------- ---------------------- -------------------- ------------- TEST TAB1 FDA_1YEAR SYS_FBA_HIST_77672 ENABLED SQL>
To export the history data, call the CREATE_TEMP_HISTORY_TABLE
procedure, passing the owner and name of the FDA backed table whose history you want to export. A table called TEMP_HISTORY
is created.
CONN test/test@pdb1 BEGIN DBMS_FLASHBACK_ARCHIVE.create_temp_history_table( owner_name1 => 'TEST', table_name1 => 'TAB1'); END; / DESC temp_history Name Null? Type ----------------------------------------------------- -------- ------------------------------------ RID VARCHAR2(4000) STARTSCN NUMBER ENDSCN NUMBER XID RAW(8) OPERATION VARCHAR2(1) ID NUMBER DESCRIPTION VARCHAR2(50) SQL>
Populate the TEMP_HISTORY
table with data from the archive you identified previously.
INSERT /*+ APPEND */ INTO temp_history SELECT * FROM SYS_FBA_HIST_77672; COMMIT;
Once exported, the table can be renamed if you wish. If the data needs to be loaded into another schema, it can be transferred using any of the usual methods, for example data pump. You can also manually amend the contents to load information from alternative audit sources into the FDA.
The contents of a history table can be loaded into the FDA using the IMPORT_HISTORY
procedure. The owner and name of the FDA-backed table are specified, along with the name of the history table and options for how any existing history data is handled.
BEGIN DBMS_FLASHBACK_ARCHIVE.import_history ( owner_name1 => 'TEST', table_name1 => 'TAB1', temp_history_name => 'TEMP_HISTORY', -- Default Setting. options => DBMS_FLASHBACK_ARCHIVE.NODELETE); -- Allowable values: NODROP, NOCOMMIT, NODELETE END; /
Database Hardening
Oracle database 12c includes a new feature called database hardening, which simplifies the management of flashback data archive for groups of tables, collectively known as applications. The management of applications is done using the DBMS_FLASHBACK_ARCHIVE
package.
Create the following tables, which will represent the application tables.
CONN test/test@pdb1 CREATE TABLE app_tab1 AS SELECT level AS id, 'Description for ' || level AS description FROM dual CONNECT BY level <= 5; ALTER TABLE app_tab1 ADD CONSTRAINT app_tab1_pk PRIMARY KEY (id); CREATE TABLE app_tab2 AS SELECT level AS id, 'Description for ' || level AS description FROM dual CONNECT BY level <= 5; ALTER TABLE app_tab2 ADD CONSTRAINT app_tab2_pk PRIMARY KEY (id);
Create a new application using the REGISTER_APPLICATION
procedure.
CONN test/test@pdb1 BEGIN DBMS_FLASHBACK_ARCHIVE.register_application( application_name => 'MY_APP', flashback_archive_name => 'FDA_1YEAR'); END; / CONN sys@pdb1 AS SYSDBA COLUMN appname FORMAT A20 COLUMN faname FORMAT A20 SELECT a.appname, b.faname FROM sys_fba_app a JOIN sys_fba_fa b ON a.fa# = b.fa#; APPNAME FANAME -------------------- -------------------- MY_APP FDA_1YEAR SQL>
Add the tables to the application using the ADD_TABLE_TO_APPLICATION
procedure.
CONN test/test@pdb1 BEGIN DBMS_FLASHBACK_ARCHIVE.add_table_to_application ( application_name => 'MY_APP', table_name => 'APP_TAB1', schema_name => 'TEST'); DBMS_FLASHBACK_ARCHIVE.add_table_to_application ( application_name => 'MY_APP', table_name => 'APP_TAB2', schema_name => 'TEST'); END; / CONN sys@pdb1 AS SYSDBA COLUMN appname FORMAT A20 COLUMN table_owner FORMAT A20 COLUMN table_name FORMAT A20 SELECT a.appname, c.owner AS table_owner, c.object_name As table_name FROM sys_fba_app a JOIN sys_fba_app_tables b ON a.app# = b.app# JOIN dba_objects c ON b.obj# = c.object_id ORDER BY 1,2,3; APPNAME TABLE_OWNER TABLE_NAME -------------------- -------------------- -------------------- MY_APP TEST APP_TAB1 MY_APP TEST APP_TAB2 SQL>
The application is built, but the tables are not currently associated with the FDA as the application is not enabled.
CONN test/test@pdb1 SET LINESIZE 150 COLUMN owner_name FORMAT A20 COLUMN table_name FORMAT A20 COLUMN flashback_archive_name FORMAT A22 COLUMN archive_table_name FORMAT A20 SELECT owner_name, table_name, flashback_archive_name, archive_table_name, status FROM dba_flashback_archive_tables WHERE table_name LIKE 'APP_TAB%' ORDER BY owner_name, table_name; no rows selected SQL>
Calling the ENABLE_APPLICATION
procedure enables FDA for all the tables in the application.
CONN test/test@pdb1 BEGIN DBMS_FLASHBACK_ARCHIVE.enable_application( application_name => 'MY_APP'); END; / SET LINESIZE 150 COLUMN owner_name FORMAT A20 COLUMN table_name FORMAT A20 COLUMN flashback_archive_name FORMAT A22 COLUMN archive_table_name FORMAT A20 SELECT owner_name, table_name, flashback_archive_name, archive_table_name, status FROM dba_flashback_archive_tables WHERE table_name LIKE 'APP_TAB%' ORDER BY owner_name, table_name; OWNER_NAME TABLE_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS -------------------- -------------------- ---------------------- -------------------- ------------- TEST APP_TAB1 FDA_1YEAR SYS_FBA_HIST_96008 ENABLED TEST APP_TAB2 FDA_1YEAR SYS_FBA_HIST_96010 ENABLED SQL>
Calling the DISABLE_APPLICATION
procedure disables FDA for all the tables in the application.
CONN test/test@pdb1 BEGIN DBMS_FLASHBACK_ARCHIVE.disable_application( application_name => 'MY_APP'); END; / SET LINESIZE 150 COLUMN owner_name FORMAT A20 COLUMN table_name FORMAT A20 COLUMN flashback_archive_name FORMAT A22 COLUMN archive_table_name FORMAT A20 SELECT owner_name, table_name, flashback_archive_name, archive_table_name, status FROM dba_flashback_archive_tables WHERE table_name LIKE 'APP_TAB%' ORDER BY owner_name, table_name; no rows selected SQL>
Tables can be removed from the application (REMOVE_TABLE_FROM_APPLICATION
) and the application dropped (DROP_APPLICATION
) as follows.
BEGIN DBMS_FLASHBACK_ARCHIVE.remove_table_from_application( application_name => 'MY_APP', table_name => 'APP_TAB1', schema_name => 'TEST'); DBMS_FLASHBACK_ARCHIVE.remove_table_from_application( application_name => 'MY_APP', table_name => 'APP_TAB2', schema_name => 'TEST'); DBMS_FLASHBACK_ARCHIVE.drop_application( application_name => 'MY_APP'); END; /
FDA Optimization (Compression)
If you are licensed for the advanced compression option in enterprise edition, you can take advantage of the OPTIMIZE DATA
clause when using flashback data archive. Using optimization enables Advanced Row Compression, Advanced LOB Compression, Advanced LOB Deduplication, segment-level compression tiering, and row-level compression tiering for the specified FDA.
The following example shows how the OPTIMIZE DATA
clause works.
-- Default and equivalent optimization. CREATE FLASHBACK ARCHIVE fda_2year_no_opt TABLESPACE fda_ts RETENTION 2 YEAR; CREATE FLASHBACK ARCHIVE fda_3year_no_opt TABLESPACE fda_ts RETENTION 3 YEAR NO OPTIMIZE DATA; -- Including optimization. CREATE FLASHBACK ARCHIVE fda_4year_opt TABLESPACE fda_ts RETENTION 4 YEAR OPTIMIZE DATA; -- Remove. DROP FLASHBACK ARCHIVE fda_4year_opt; DROP FLASHBACK ARCHIVE fda_3year_no_opt; DROP FLASHBACK ARCHIVE fda_2year_no_opt;
Hybrid Columnar Compression (HCC) Support
Flashback data archive is now fully supported against tables using Hybrid Columnar Compression (HCC), whether on Exadata or other supported storage platforms.
Multitenant Support (CDB/PDB) (12.1.0.2 Onward)
Flashback data archive was not supported in multitenant environments in the initial release of Oracle database 12c. Support for multitenant environments was introduced in patchset 12.1.0.2, as described here. All the examples in this article are performed against a multitenant database.
Relevant SYS Tables
The SYS owned tables relevant to flashback data archive are shown below.
SELECT table_name FROM dba_tables WHERE owner = 'SYS' AND table_name LIKE '%FBA%' ORDER BY table_name; TABLE_NAME ---------------------------- SYS_FBA_APP SYS_FBA_APP_TABLES SYS_FBA_BARRIERSCN SYS_FBA_COLS SYS_FBA_CONTEXT SYS_FBA_CONTEXT_AUD SYS_FBA_CONTEXT_LIST SYS_FBA_DL SYS_FBA_FA SYS_FBA_PARTITIONS SYS_FBA_PERIOD SYS_FBA_TRACKEDTABLES SYS_FBA_TSFA SYS_FBA_USERS SYS_MFBA_NCHANGE SYS_MFBA_NROW SYS_MFBA_NTCRV SYS_MFBA_STAGE_RID SYS_MFBA_TRACKED_TXN SQL>
Flashback Time Travel (19c)
From 19c onward some of the flashback functionality has been slightly rebranded as "Flashback Time Travel". Typically this is a combination of flashback data archive and AS OF
queries.
For more information see:
- FDA Support for CDBs
- Using Flashback Data Archive
- DBMS_FLASHBACK_ARCHIVE
- CREATE FLASHBACK ARCHIVE
- Flashback Data Archive (Oracle Total Recall)
- Flashback New Features and Enhancements in Oracle Database 10g
Hope this helps. Regards Tim...