Flashback Data Archive (Oracle Total Recall)

Oracle Flashback Data Archive (Total Recall) Configuration

Oracle Flashback Data Archive (FDA), also known as Oracle Total Recall, provides the ability to track and store historical changes to table data. This feature allows users to query past states of data without relying on traditional backup and recovery mechanisms.

Flashback Data Archive in Oracle 11g

In Oracle Database 11g, FDA management is primarily performed at the individual table level.

STEP 1: Create a Tablespace for FDA

A dedicated tablespace is required to store the historical data.

CREATE TABLESPACE FDA DATAFILE '+DATA_DG' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 30G;

STEP 2: Create Flashback Archive

Define the flashback archive, specifying its tablespace and retention period.

CREATE FLASHBACK ARCHIVE DEFAULT TABLESPACE FDA QUOTA 10G RETENTION YEAR;

STEP 3: Enable FDA on Respective Table

Enable FDA for a specific table.

ALTER TABLE your_table_name FLASHBACK ARCHIVE; -- Replace 'your_table_name' with the actual table name

STEP 4: Check FDA is Enabled on Respective Table

Verify the FDA status for tables.

SELECT owner_name, table_name, flashback_archive_name, archive_table_name, status
FROM dba_flashback_archive_tables
ORDER BY owner_name, table_name;

Flashback Data Archive in Oracle 12c and Later

Starting with Oracle Database 12c, FDA introduces the concept of managing logical groups of tables at an application level. This simplifies enabling or disabling FDA for multiple related tables.

STEP 1: Create a Tablespace for FDA

Similar to 11g, create a tablespace for FDA.

CREATE TABLESPACE FDA DATAFILE '+DATA_DG' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 30G;

STEP 2: Create Flashback Archive and Grant Privileges

Create the flashback archive and grant necessary privileges to the user who will manage FDA.

CREATE FLASHBACK ARCHIVE FDA_NAME TABLESPACE FDA QUOTA 10G RETENTION YEAR; -- FDA_NAME is the name of your flashback archive
GRANT FLASHBACK ARCHIVE ON FDA_NAME TO XYZ; -- Replace 'XYZ' with the username
GRANT FLASHBACK ARCHIVE ADMINISTRATOR TO XYZ;

Step 3: Enable FDA on Respective Table

Enable FDA for a specific table, associating it with the named flashback archive.

ALTER TABLE TEST FLASHBACK ARCHIVE FDA_NAME; -- Replace 'TEST' with your table name and 'FDA_NAME' with your archive name

Step 4: Set Context Level for Transaction Data

To ensure that context information (e.g., SYS_CONTEXT attributes) is stored with the transaction data, use DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL.

BEGIN
  DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL(level=>'ALL');
END;
/

Create a Group and Add a Table (Application Level Management)

Oracle 12c allows you to group tables under an application name for easier management.

-- Register an application for FDA
EXEC DBMS_FLASHBACK_ARCHIVE.REGISTER_APPLICATION(application_name=>'FDA_APP', flashback_archive_name=>'FDA_NAME');

-- Add a table to the registered application
EXEC DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION(application_name=>'FDA_APP', table_name=>'TEST', schema_name=>'XYZ');

Enable/Disable Group

You can enable or disable FDA for an entire application group.

-- Disable FDA for the application group
EXEC DBMS_FLASHBACK_ARCHIVE.DISABLE_APPLICATION(application_name=>'FDA_APP');

-- Enable FDA for the application group
EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_APPLICATION(application_name=>'FDA_APP');