Flashback Data Archive (Oracle Total Recall)

Have you ever had to to perform a tablespace point in time recovery to retrieve data that was erroneously deleted? How many triggers have you had to create/troubleshoot in order to meet data retention or change control requirements?

Flashback Data Archive is a new feature in Oracle Database 11g that automatically track and maintain changes to data in a user/application transparent manner.

This document will detail the steps to configure Flashback Data Archive. The step performed in this document were done using Oracle 11gR2 11.2.0.1.0 on Oracle Enterprise Linux 5.

Flashback Data Archive cannot be enabled for nested, clustered, temporary, remote or external tables or tables that have LONG or nested columns. In order to enable

Licensing Notice: Flashback Data Archive requires a license for Oracle Total Recall.

Create the archive administrator and flashback data archive

An archive administrator is a user that has the FLASHBACK ARCHIVE ADMINSTER privilege. This privilege allows the user to create and maintain the flashback data archive and is the only user that can disable data archiving on a table other than SYSDBA.

SQL> create user fla_admin identified by password;

User created.

SQL> grant flashback archive administer to fla_admin;

Grant succeeded.

SQL> grant create session to fla_admin;

Grant succeeded.

SQL> alter user fla_admin quota unlimited on flashback_archive;

User altered.

SQL> 

The flashback data archive is one or more tablespaces. The tablespaces could be used exclusively by flashback data archive or you have the option of using part of an existing tablespace via a quota. Create the flashback data archive as the flashback archive administrator

SQL> connect fla_admin/password
Connected.
SQL> create flashback archive data_archive  
  2  tablespace flashback_archive quota 2G retention 1 year;

Flashback archive created.

SQL> 

The retention clause states how long to keep data in the flashback archive. In the example above the retention was set to 1 year. The data stored in the flashback archive will be limited to 2 GB of space. The tablespace flashback_archive was tablespace created to be used as the flashback archive.

Enable history tracking on a table

The owner of the table can enable flashback data archive on the tables they own if they have the FLASHBACK ARCHIVE object privilege. As either flashback archive administrator or SYSDBA grant FLASHBACK ARCHIVE to the hr user.

SQL> grant flashback archive on data_archive to hr;

Grant succeeded.

SQL> 

Users with FLASHBACK ARCHIVE object privilege can enable flashback archiving on existing tables using ALTER TABLE … FLASHBACK ARCHIVE or at table creation with the FLASHBACK ARCHIVE clause.

Now as the HR user enable flashback data archive on the employees table.

SQL> alter table employees flashback archive data_archive;

Table altered.

SQL>

The flashback data archive can be specified in the FLASHBACK ARCHIVE clause. If no data archive is specified the default flashback data archive will be used if defined.
You can find which tables have flashback data archive enabled by querying the USER_FLASHBACK_ARCHIVE_TABLES view.

SQL> describe user_flashback_archive_tables;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 OWNER_NAME                                NOT NULL VARCHAR2(30)
 FLASHBACK_ARCHIVE_NAME                    NOT NULL VARCHAR2(255)
 ARCHIVE_TABLE_NAME                                 VARCHAR2(53)
 STATUS                                             VARCHAR2(8)

SQL>

Note while a user with FLASHBACK ARCHIVE can enable data archiving only users with FLASHBACK ARCHIVE ADMINISTER or SYSDBA can disable data archiving.

Access archived data

To retrieve data stored in the flashback data archive you use the AS OF TIMESTAMP|SCN clause of the SELECT statement. What follows next is demonstration of using Flashback Data Archive to restore data that was removed from the table.

First we will remove a row from the employees table.

SQL> delete from employees where employee_id = 206;

1 row deleted.

SQL> commit;

Commit complete.

SQL> 

Next we issue a SELECT statement with the AS OF TIMESTAMP clause to view the table at a time prior to the DELETE.

SQL> select first_name, last_name
  2  from employees
  3  as of timestamp to_timestamp('2010-10-13 13:30:00', 'YYYY-MM-DD HH24:MI:SS') 
  4  where employee_id = 206;

FIRST_NAME           LAST_NAME
-------------------- -------------------------
William              Gietz

SQL> select first_name, last_name
  2  from employees
  3  where employee_id = 206;

no rows selected

SQL>

DDL Restrictions

There are some restrictions on the DDL statements that can be used on tables that have flashback archiving enabled and the restrictions are different between 11gR1 and 11gR2.

If you are using Oracle Database 11gR1 using any of the following DDL statements on table with flashback data archive enabled will result in error ORA-55610.

ALTER TABLE statements that DROP, RENAME or MODIFY a column.
ALTER TABLE statements that perform partition or sub-partition operations.
ALTER TABLE statements that either add or rename a constraint.
ALTER TABLE statements which Includes an UPGRADE TABLE with or withINCLUDING DATA clause
TRUNCATE TABLE
RENAME TABLE
DROP TABLE

In Oracle Database 11gR2 the following DDL statements that could not performed on table with flashback data archive enabled can now be performed without error.

ALTER TABLE statements that DROP, RENAME or MODIFY a column.
ALTER TABLE statements that drop or truncate a partition or sub-partition.
ALTER TABLE statements that either add or rename a constraint.
TRUNCATE TABLE
RENAME TABLE

There are still some DDL statements that will result in error ORA-55610 in Oracle Database 11gR2.

ALTER TABLE statements which Includes an UPGRADE TABLE with or with INCLUDING DATA clause
ALTER TABLE statements that move or exchange a partition or sub-partition
DROP TABLE

If you find that you need to use DDL that is not supported by flashback data archive you can use the DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA prior to the DDL statement to disassociate the table from its flashback data archive. The procedure DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA is used to re-associate a table with its flashback data archive.

Flashback Data Archive Maintenance

The ALTER FLASHBACK ARCHIVE ... PURGE commands can be used to manually purge data
from the flashback archive. You can purge all of the data or data older than a
specific SCN or TIMESTAMP.

ALTER FLASHBACK ARCHIVE PURGE ALL
ALTER FLASHBACK ARCHIVE PURGE BEFORE SCN|TIMESTAMP

The ALTER FLASHBACK ARCHIVE .. ADD TABLESPACE commands are used to add tablespaces
to the named data archive. A quota for how much space is to be used for flashback data archive can be set at the time the tablespace is added using the QUOTA option. If not specified the entire tablespace can be used for data archive.

ALTER FLASHBACK ARCHIVE ADD TABLESPACE QUOTA 2G

Conversely the ALTER FLASHBACK ARCHIVE ... REMOVE TABLESPACE is used to remove a tablespace from the flashback data archive. An attempt to remove all tablespaces in a flashback data archive will result in an error.

If the flashback data archive is no longer needed use the DROP FLASHBACK ARCHIVE statement. Note the both the REMOVE TABLESPACE and DROP FLASHBACK ARCHIVE only remove historical data they do not remove the tablespace or the datafiles associated with the tablespace.