Warning: Creating default object from empty value in /home/customer/www/gavinsoorma.com/public_html/wp-content/themes/specular/admin/inc/class.redux_filesystem.php on line 29
11g Flashback Data Archive – Part One | Oracle DBA – Tips and Techniques
News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

11g Flashback Data Archive – Part One

  • Posted by Gavin Soorma
  • On November 6, 2009
  • 1 Comments

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

  • Easy to configure – apply to all tables, one or a group of tables with simple “enable archive” command
  • Secure – complete protection from accidental or malicious updates and deletes
  • Efficiency of performance and storage – capture process is asynchronous background process and data in history tables is partitioned as well as compressed automatically
  • Easy to access historical data using standard SQL “AS OF” constructs
  • Automated data management – historical data is automatically purged without any human intervention
  • Retention policies customised to suit business needs

  • 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

  • System Privilege – FLASHBACK ARCHIVE ADMINISTER to create and administer a flashback data archive
  • Connect as SYSDBA
  • Object Privilege – FLASHBACK ARCHIVE privilege on the specific flashback data archive to enable historical data tracking
  • Quota on the tablespace where the flashback data archive has been created
  • Creating a Flashback Data Archive (lets call it FBDA)

  • Create a new tablespace or use existing tablespace – tablespace needs to be ASSM
  • Specify the FBDA as the default (optional)
  • Assign a quota for the FBDA (optional)
  • Assign a retention period for the FBDA
  • Retention period integer denoting days,months or years
  • Enable flashback archive for a specific table via the CREATE TABLE or ALTER TABLE clause. By default it is turned off.
  • 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
    
    
    
     

    1 Comments

    David Rajkumar
    • Nov 16 2009
    hi.. article i will read... but sorry using this space to send this message.... reply to my mail... regds David (ur short pitched ball will be sent to the ropes)

    Leave Reply

    Your email address will not be published. Required fields are marked *