Flashback Data Archive (Total Recall) 11gr2

Today i am going to write about Flashback data archive feature introduced in the Oracle 11gr1.

FAQ's

1. What is Flashback data archive?

A. Flashback data archive is a feature to track all the transaction changes over life time on a tables. ( it is like AUDITING without additional configuration).

2. Is it available in oracle 10g?

A. No, this feature is introduced in Oracle 11g.

3. What privileges required to enable FLASHBACK DATA ARCHIVE FEATUER?

A. Flash archive object privilege required to enable flashback data archive. 

4. How to configure FLASHBACK DATA ARCHIVE?

A. see the below live example

5. By default FLASHBACK ARCHIVE is enable or disable?

A. By default flashback archive is off for a table.

6. How to get details about FLASHBACK DATA ARCHIVE?

A. There are data dictionary view available in the system , from there we can get the details:

Table 12-3 Static Data Dictionary Views for Flashback Data Archive Files

View	Description
*_FLASHBACK_ARCHIVE	Displays information about Flashback Data Archive files.
*_FLASHBACK_ARCHIVE_TS	Displays tablespaces of Flashback Data Archive files.
*_FLASHBACK_ARCHIVE_TABLES	Displays information about tables that are enabled for Data Flashback Archive files.

Live Example:

Step:

1. create separate tablespace for flashback

2. create flashback data archive and assigned default tablespace and quota.

3. Enable flashback data archive for tables

4. Viewing flashback data archive

SQL> conn sys/oracle as sysdba
Connected.
SQL> create tablespace FLASHBACK
  2  datafile 'd:\app\administrator\oradata\fakpropr\flashback01.dbf' size 1g
  3  autoextend on;


Tablespace created.


SQL> --create flashback archive
SQL> create flashback archive default fp_pr_flash tablespace flashback
  2  quota 10g retention 10 year;


Flashback archive created.


SQL> --ENABLE FLASHBACK ARCHIVE ON THE FOLLOWING  HR TABLES


SQL> alter table fgc.PMTERMINATIONS flashback archive;


Table altered.


SQL> alter table fgc.PMSALARYDTLSUB flashback archive;


Table altered.


SQL> alter table fgc.PMSALARYDTL flashback archive;


Table altered.


SQL> alter table fgc.PMSALARYMSTR flashback archive;


Table altered.


SQL> alter table fgc.PMLOAN flashback archive;


Table altered.


SQL> alter table fgc.PMEMPLOYEE flashback archive;


Table altered.


SQL> alter table fgc.PMANULLEAVES flashback archive;


Table altered.


SQL> alter table fgc.PMADVANCE flashback archive;


Table altered.


SQL> alter table fgc.PMADDITION flashback archive;


Table altered.


--Viewing FLASHBACK DATA ARCHIVE DETAILS
1. Displays information about data archive files



SQL> column owner_name format a20
SQL> column flashback_archive_name format a30
SQL> select owner_name,flashback_archive_name,retention_in_days,status
  2  from dba_flashback_archive;


OWNER_NAME           FLASHBACK_ARCHIVE_NAME         RETENTION_IN_DAYS STATUS
-------------------- ------------------------------ ----------------- -------
SYS                  FP_PR_FLASH                                 3650 DEFAULT



In the above query output : FLASHBACK ARCHIVE NAME : FP_PR_FLASH / OWNER IS SYS AND RETENTION IS 3650 DAYS (10 YEARS)


2. Displays TABLESPACES OF FLASHBACK data archive files

SQL> column quota_in_mb format a20
SQL> column flashback_archive_name format a30
SQL> column tablespace_name format a20
SQL> select flashback_archive_name,tablespace_name,quota_in_mb
  2  from dba_flashback_archive_ts;


FLASHBACK_ARCHIVE_NAME         TABLESPACE_NAME      QUOTA_IN_MB
------------------------------ -------------------- --------------------
FP_PR_FLASH                    FLASHBACK            10240

In the above query output: Tablespace which is used for flashback data archive is "FLASHBACK" and allocated space on tablespace is 10240 MB (10GB).

3. Displays information about TABLEs that are enabled for flashback data archive files

SQL> column flashback_archive_name format a15

SQL> column owner_name format a10

SQL> column table_name format a15

SQL> column archive_table_name format a20

SQL> select table_name,owner_name,flashback_archive_name,archive_table_name

  2  from dba_flashback_archive_tables;

TABLE_NAME      OWNER_NAME FLASHBACK_ARCHI ARCHIVE_TABLE_NAME

--------------- ---------- --------------- --------------------

PMTERMINATIONS  FGC        FP_PR_FLASH     SYS_FBA_HIST_52126

PMSALARYDTLSUB  FGC        FP_PR_FLASH     SYS_FBA_HIST_52114

PMSALARYDTL     FGC        FP_PR_FLASH     SYS_FBA_HIST_52110

PMSALARYMSTR    FGC        FP_PR_FLASH     SYS_FBA_HIST_52119

PMLOAN          FGC        FP_PR_FLASH     SYS_FBA_HIST_52096

PMEMPLOYEE      FGC        FP_PR_FLASH     SYS_FBA_HIST_52086

PMANULLEAVES    FGC        FP_PR_FLASH     SYS_FBA_HIST_52074

PMADVANCE       FGC        FP_PR_FLASH     SYS_FBA_HIST_52069

PMADDITION      FGC        FP_PR_FLASH     SYS_FBA_HIST_52058

9 rows selected.

In the above query output table name is a tables where data archive is enabled. owner is a owner of the tables and archive table name (last column) is system generated tables name for archive details for each base table.

Important notes:

DDL Statements on Tables Enabled for Flashback Data Archive

Flashback Data Archive supports many DDL statements, including some that alter the table definition or move data. For example:

• ALTER TABLE statement that does any of the following:
  
  • Adds, drops, renames, or modifies a column
  • Adds, drops, or renames a constraint
  • Drops or truncates a partition or subpartition operation
• TRUNCATE TABLE statement
• RENAME statement that renames a table

Some DDL statements cause error ORA-55610 when used on a table enabled for Flashback Data Archive. For example:

• ALTER TABLE statement that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
• ALTER TABLE statement that moves or exchanges a partition or subpartition operation
• DROP TABLE statement

If you must use unsupported DDL statements on a table enabled for Flashback Data Archive, use the DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA procedure to disassociate the base table from its Flashback Data Archive. To reassociate the Flashback Data Archive with the base table afterward, use the DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA procedure.

General Guidelines for Oracle Flashback Technology

• Use the DBMS_FLASHBACK.ENABLE and DBMS_FLASHBACK.DISABLE procedures around SQL code that you do not control, or when you want to use the same past time for several consecutive queries.
• Use Oracle Flashback Query, Oracle Flashback Version Query, or Oracle Flashback Transaction Query for SQL code that you write, for convenience. An Oracle Flashback Query, for example, is flexible enough to do comparisons and store results in a single query.
• To obtain an SCN to use later with a flashback feature, use DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER.
• To compute or retrieve a past time to use in a query, use a function return value as a time stamp or SCN argument. For example, add or subtract an INTERVAL value to the value of the SYSTIMESTAMP function.
• Use Oracle Flashback Query, Oracle Flashback Version Query, and Oracle Flashback Transaction Query locally or remotely. An example of a remote Oracle Flashback Query is:
  

  (SELECT * FROM employees@some_remote_host AS OF 
      TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
  

• To ensure database consistency, always perform a COMMIT or ROLLBACK operation before querying past data.
• Remember that all flashback processing uses the current session settings, such as national language and character set, not the settings that were in effect at the time being queried.
• Remember that DDLs that alter the structure of a table (such as drop/modify column, move table, drop partition, truncate table/partition, and add constraint) invalidate any existing undo data for the table. If you try to retrieve data from a time before such a DDL executed, error ORA-01466 occurs. DDL operations that alter the storage attributes of a table (such as PCTFREE, INITRANS, and MAXTRANS) do not invalidate undo data.
• To query past data at a precise time, use an SCN. If you use a time stamp, the actual time queried might be up to 3 seconds earlier than the time you specify. Oracle Database uses SCNs internally and maps them to time stamps at a granularity of 3 seconds.
  For example, suppose that the SCN values 1000 and 1005 are mapped to the time stamps 8:41 AM and 8:46 AM, respectively. A query for a time between 8:41:00 and 8:45:59 AM is mapped to SCN 1000; an Oracle Flashback Query for 8:46 AM is mapped to SCN 1005. Therefore, if you specify a time that is slightly after a DDL operation (such as a table creation) Oracle Database might use an SCN that is just before the DDL operation, causing error ORA-01466.
• You cannot retrieve past data from a dynamic performance (V$) view. A query on such a view always returns current data.
• You can perform queries on past data in static data dictionary views, such as *_TABLES.

Performance Guidelines for Oracle Flashback Technology

• Use the DBMS_STATS package to generate statistics for all tables involved in an Oracle Flashback Query. Keep the statistics current. Oracle Flashback Query uses the cost-based optimizer, which relies on these statistics.
• Minimize the amount of undo data that must be accessed. Use queries to select small sets of past data using indexes, not to scan entire tables. If you must scan a full table, add a parallel hint to the query.
  The performance cost in I/O is the cost of paging in data and undo blocks that are not in the buffer cache. The performance cost in CPU use is the cost of applying undo information to affected data blocks. When operating on changes in the recent past, flashback operations are CPU-bound.
• For Oracle Flashback Version Query, use index structures. Oracle Database keeps undo data for index changes and data changes. Performance of index lookup-based Oracle Flashback Version Query is an order of magnitude faster than the full table scans that are otherwise needed.
• In an Oracle Flashback Transaction Query, the xid column is of the type RAW(8). To take advantage of the index built on the xid column, use the HEXTORAW conversion function: HEXTORAW(xid).
• A Oracle Flashback Query against a materialized view does not take advantage of query rewrite optimization.
  
  Reference : http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_flashback.htm#BJFBCABH