Flashback Data Archive (Oracle Total Recall)

I learn something new about Oracle Database pretty much every day. Recently I learned something its about Flashback Data Archive (Oracle Total Recall) in 11gR1.

            Oracle Flashback Data Archives provides the ability to track changes that occur on a table over the lifetime of the table. Until now many applications had to build in logging for this kind of transaction monitoring. You can use the Flashback Data Archives feature to satisfy security and compliance requirements. You can also use the data for data analysis and within a decision support system (DSS) environment.

The user who will be defining any Flashback Data Archives will need to have the privilege flashback archive administer granted to them.

sys@11GR1> grant flashback archive administer to scott;

Grant succeeded.

To create the archive we use the create flashback archive SQL DDL command. When you create the archive, you will assign the archive to a tablespace and assign it a space quota and a retention period.

scott@11GR1> create flashback archive archive_one_year
  2  tablespace EXAMPLE retention 1 year;

Flashback archive created.

By default, archiving is disabled. You can enable archiving on specific tables when you create the table or you can alter the table to enable archiving.

scott@11GR1> create table t_flash (x number) tablespace EXAMPLE flashback archive archive_one_year;

Table created.

scott@11GR1> insert into t_flash values (1);

1 row created.

scott@11GR1> insert into t_flash values (2);

1 row created.

scott@11GR1> insert into t_flash values (3);

1 row created.

scott@11GR1> insert into t_flash values (4);

1 row created.

scott@11GR1> insert into t_flash values (5);

1 row created.

Oracle provides views that you can use to administer Flashback Data Archives. These views include

DBA_FLASHBACK_ARCHIVE - Provides information on all flashback archives contained in the database.

scott@11GR1> SELECT *
  2  from DBA_FLASHBACK_ARCHIVE
  3  /

FLASHBACK_ARCHIVE_NAME         FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME                      LAST_PURGE_TIME                STATUS
------------------------------ ------------------ ----------------- ----------------------------------- ----------------------------------- ---------
ARCHIVE_ONE_YEAR                                1               365 25-JUL-10 05.28.01.000000000 PM  25-JUL-10 05.28.01.000000000 PM

DBA_FLASHBACK_ARCHIVE_TS - Provides information on all tablespaces that contain flashback archives

scott@11GR1> SELECT *
  2  FROM DBA_FLASHBACK_ARCHIVE_TS
  3  /

FLASHBACK_ARCHIVE_NAME         FLASHBACK_ARCHIVE# TABLESPACE_NAME                QUOTA_IN_MB
------------------------------ ------------------ ------------------------------ ----------------------------------------
ARCHIVE_ONE_YEAR                                1 EXAMPLE

DBA_FLASHBACK_ARCHIVE_TABLES   - This view indicates which flashback archive a given table is assigned to. If a table is not assigned to a flashback archive, it will not appear in this view.

scott@11GR1> SELECT *
  2  FROM DBA_FLASHBACK_ARCHIVE_TABLES
  3  /

TABLE_NAME                     OWNER_NAME                     FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME
------------------------------ ------------------------------ ------------------------------ -----------------------------------------------------
T_FLASH                        SCOTT                          ARCHIVE_ONE_YEAR               SYS_FBA_HIST_73124

So, now that we have configured archiving, how do we use it? It’s really quite simple, as the Flashback Data Archives feature is an extension of the existing Flashback technologies already in place. Before, we were subject to the availability of undo and its retention. If the undo was available, then we could flashback a table
easily and see how it looked back in time; if the undo was not available, then the flashback efforts would fail. Guaranteed retention of undo helped to stabilize the availability of undo, but this was a global parameter, and could cause the whole database undo structure to become large and difficult to manage Oracle’s Flashback Data Archives feature allowing you to preserve undo at a table level for as long as might be required. Because this feature is granular to the table as opposed to the database, the space impacts are reduced for long-term undo storage. Now, SQL queries using the as of timestamp parameter are no longer subject to limited undo
storage, so queries such as the following are possible:

scott@11GR1> select * from T_FLASH as of timestamp (systimestamp  - interval '280' second);

         X
----------
         1
         2

Note that when a table is being archived, certain DDL commands are disabled  for that object.These include certain alter table commands and the drop table, rename table, and truncate table commands. Also commands that modify a column are not allowed

scott@11GR1> drop table t_flash;
drop table t_flash
           *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

Elapsed: 00:00:00.03
scott@11GR1> truncate table t_flash;
truncate table t_flash
               *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

Elapsed: 00:00:00.03