Monday, 3 October 2016

Flashback Data Archive (FDA) Enhancements in Oracle Database 12c Release 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.

  • 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
SYS FDA_1YEAR 1 365

OWNER_NAME CREATE_TIME LAST_PURGE_TIME STATUS
SYS 06-JAN-2015 19:30:57 06-JAN-2015 19:30:57 DEFAULT

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_NAMEQUOTA_IN_MB
FDA_1YEAR 1FDA_TS10240

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_NAMETABLE_NAMEFLASHBACK_ARCHIVE_NAME
TESTTAB1FDA_1YEAR 

OWNER_NAMEARCHIVE_TABLE_NAMESTATUS
TESTSYS_FBA_HIST_95999ENABLED 

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 the USERENV context are stored.
  • ALL : All contexts available to the user via the SYS_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_STARTSCNVERSIONS_ENDSCNVERSIONS_XIDV
25366992536792060010000B0A0000I
253679225368260A00110076060000U
2536826
020003005B080000U

DESCRIPTIONSESSION_USERCLIENT_IDENTIFIERMY_ATTRIBUTE
ONETESTPeter PanFirst Action
TWOTESTPeter ParkerSecond Action
THREETESTPeter RabbitThird Action

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.

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 containing the relevant data.

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>

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_NAMETABLE_NAMEFLASHBACK_ARCHIVE_NAME
TESTAPP_TAB1FDA_1YEAR 
TESTAPP_TAB2FDA_1YEAR 

OWNER_NAMEARCHIVE_TABLE_NAMESTATUS
TESTSYS_FBA_HIST_96008ENABLED
TESTSYS_FBA_HIST_96010ENABLED 

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.

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