11g Flashback Data Archive – Part One

Businesses are increasingly realizing the immense value that historical data can provide to help them understand market trends and customer behaviour as well. Further, many regulatory and compliance policies
like SOX and BASEL-2 mandate retention of historical data.

Until now, this unfortunately involved application rewrites,administration overheads or costly third-party software solutions. Total Recall or the new Oracle 11g Flashback Data Archive feature has greatly enhanced the flashback technology which was introduced in Oracle 9i to make it far more than just a tool to recover from logical corruptions and human error. It is now an out-of-the box Information Life Cycle Management tool and provides ease of management of historical information and long term secure data tracking with minimal performance overheads.

11g Flashback Data Archive provides the automated ability to track and store all transactional changes to a table over its lifetime without having to build this intelligence into your application. Prior to 11g, Flashback technology to a large part was based on the availability of undo data or flashback logs and both the undo data as well as flashback logs are subject to recycling when out of space pressure exists. The UNDO tablespace in Oracle was primarily meant for transaction consistency and not data archival.

Flashback Data Archive or Total Recall Features

Flashback Data Archive uses a background process fbda to capture data asynchronously. It runs every 5 minutes which is the default as well as at more frequent intervals depending on system activity. The Primary source for historical data is the Undo data, but this undo data is not reused until the historical data is written first.

Getting Started

Creating a Flashback Data Archive (lets call it FBDA)

SQL> CREATE TABLESPACE his_data_1
  2  DATAFILE '+data' SIZE 500M;

Tablespace created.

SQL> CREATE FLASHBACK ARCHIVE DEFAULT fba1
  2  TABLESPACE his_data_1
  3  RETENTION 2 DAY;

Flashback archive created.

SQL> SELECT FLASHBACK_ARCHIVE_NAME, to_char(CREATE_TIME,'dd-mon-yyyy') Created,
  2  RETENTION_IN_DAYS,STATUS FROM DBA_FLASHBACK_ARCHIVE;


FLASHBACK_ARCHIVE_NA CREATED     RETENTION_IN_DAYS STATUS
-------------------- ----------- ----------------- -------
FBA1                 02-nov-2009                 2 DEFAULT

SQL> GRANT FLASHBACK ARCHIVE ON fba1 TO scott;

Grant succeeded.

SQL> ALTER TABLE mysales FLASHBACK ARCHIVE fba1;

Table altered.

SQL> CREATE TABLE
  2  EMPSAL_HIS
  3  (EMPNO number,
  4  ENAME VARCHAR2(10),
  5  SAL NUMBER,
  6  FLASHBACK ARCHIVE;

Table created.

SQL> select TABLE_NAME,FLASHBACK_ARCHIVE_NAME,ARCHIVE_TABLE_NAME,STATUS
  2  from USER_FLASHBACK_ARCHIVE_tables;

TABLE_NAME            FLASHBACK_ARCHIVE_NA  ARCHIVE_TABLE_NAME               STATUS
-----------	  --------------------  --------------------	      ------------
MYSALES               FBA1                 SYS_FBA_HIST_77429               ENABLED
EMPSAL_HIS            FBA1                 SYS_FBA_HIST_77419               ENABLED

Note: for every table where Flashback Archive is enabled, corresponding internal history tables are created in the flashback archive tablespace as shown below. These tables are automatically partitioned and compressed as well

SQL> select object_id from dba_objects where object_name=‘DEPT';

 OBJECT_ID
----------
     73201

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           USERS
SYS_FBA_DDL_COLMAP_73201       HIS_DATA_1
SYS_FBA_TCRV_73201             HIS_DATA_1
SYS_FBA_HIST_73201


SQL> desc SYS_FBA_HIST_73201
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RID                                                VARCHAR2(4000)
 STARTSCN                                           NUMBER
 ENDSCN                                             NUMBER
 XID                                                RAW(8)
 OPERATION                                          VARCHAR2(1)
EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 MGR                                                NUMBER(10)
DEPTNO                                             NUMBER(2)
 JOB                                                VARCHAR2(20)

SQL> SELECT TABLE_NAME,PARTITION_NAME,COMPRESSION from USER_TAB_PARTITIONS;

TABLE_NAME                     PARTITION_NAME                 COMPRESS
------------------------------ ------------------------------ --------
SYS_FBA_HIST_77429            HIGH_PART                      ENABLED
SYS_FBA_HIST_77419            HIGH_PART                      ENABLED

The following two tabs change content below.

• Bio
• Latest Posts

Gavin Soorma

Latest posts by Gavin Soorma (see all)