Flashback Data Archive (Oracle Total Recall)

With Oracle 11g, FDA had to be managed at table level. Starting with Oracle 12c, it is possible to create logical groups of tables for an application: Enabling or disabling of FDA is performed at the application level and it prevents the listing of all application tables to enable or disable FDA for each one.


11G

===

STEP 1: Create a tablespace for FDA

CREATE tablespace FDA datafile '+DATA_DG' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 30G;

STEP 2: Create flashback archive

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

STEP 3: Enable FDA on respective table

ALTER TABLE FLASHBACK ARCHIVE ;

STEP 4: Check FDA is enabled on respective table

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


12C

===

STEP 1: Create a tablespace for FDA

CREATE tablespace FDA datafile '+DATA_DG' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 30G;

STEP 2: Create flashback archive

CREATE FLASHBACK ARCHIVE TABLESPACE FDA QUOTA 10G RETENTION YEAR;
grant FLASHBACK ARCHIVE on FDA_NAME to XYZ;
grant FLASHBACK ARCHIVE ADMINISTRATOR to XYZ;

Step 3 : Enable FDA on respective table

ALTER TABLE TEST FLASHBACK ARCHIVE FDA_NAME;

Step 4 : To make sure the context information is stored with the transaction data, we need to use the DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL procedure

begin
dbms_flashback_archive.set_context_level(
level='>'ALL');
end;
/


Create a group and add a table

============================

dbms_flashback_archive.register_application(application_name='>'FDA_APP',flashback_archive_name=">";'FDA_NAME');
exec dbms_flashback_archive.add_table_to_application(application_name=">"'FDA_APP',table_name=">";'TEST',schema_name=">";'XYZ');


Enable/Disable group

============================
exec dbms_flashback_archive.disable_application(application_name='>'FDA_APP');
exec dbms_flashback_archive.enable_application(application_name='>'FDA_APP');