Oracle Total Recall – Flashback Data Archive - Oracle 11gR2 Feature

 

Oracle Total Recall will help to keep the historical data in database.   Oracle Total Recall tracking the changes happen on database and keep this data’s in database based on our retention period.

Oracle Provides lot of flashback features in earlier version. 

DB version	Flashback Feature
Oracle 9i	Flashback Query
Oracle 10g	Flashback Version Query
Oracle 10g	Flashback Table
Oracle 10g	Flashback Database
Oracle 11g	Flashback Data Archive/Oracle Total Recall
Oracle 11g	Flashback Transaction Back out

Oracle Database 11g introduces Total Recall based on the Flashback Data Archive feature, which transparently tracks changes to database tables in a highly secure and efficient manner. How Total Recall is different from others. Let’s see in this article.

Already we have flashback features, How FDA is better than existing flashback feature?

Flashback query provides the old data’s from undo tablespace and Flashback based on below init parameters.

UNDO_RETENTION

DB_RECOVERY_FILE_DEST_SIZE

DB_RECOVERY_FILE_DEST

Suppose flashback retention is 24 hours and flashback  keeps 1 day flashback logs, if we need last 48 hrs old data, we don’t get the old data and received the below error. FDA will provide the solution to access the old data based on FDA table retention.

SQL> SELECT … AS OF TIMESTAMP …

ORA-01466: unable to read data - table definition has changed

OR

ORA-08180: no snapshot found based on specified time

OR

ORA-01555: snapshot too old: rollback segment number 7 with

name "_SYSSMU7$“too small

How we are tracking the Historical Data now?

1.     Application Level: This one is very complex and keep the historical data with data integrity is very difficult. Based on business requirements they are using to track the historical data. As my little knowledge rarely using this approach in some environment.

2.     Database Level:

Database Triggers:

It will help to track the db changes and keep the changes on other change tables. Here we have maintain the data’s using partitions very easily and purge the old data’ from change tables based on our retention. These database triggers will impact the database performance. Oracle privileged users able to change the historical data.

Redo Log Mining:

Extract the redo log on readable format, create and stores this data using third party tool /Oracle log miner is very difficult and managing the historical data is also very tough.

Flashback Data Archive or Oracle Total Recall:

Flashback data archive provides the complete solution for managing the historical data.

Data Retention: FDA can be enabled on all tables and there is no limit to keep the data’s and retains the data based on business requirement.  These data’s are store in tablespace.  When a record exceeds the retention period, it is automatically purged the retention records from all FDA tables.

Easy to Implement: We can enable the Flashback Data Archive for one or more tables at any time and there is no need any application or database outage.

Easy to Access: We can access the historical data using FLASHBACK SQL query at any point of time and specify the specific interval also. 

Storage Maintenance: FDA automatically compresses and partitions the FDA internal history tables to optimize the storage and performance.

Centralized Management: FDA provides a centralized and policy based process is help to easily group tables and set a common retention policy for all group.

Security: FDA internal history tables are read only tables. Oracle privileged users also don’t allowed to do DML operations against the FDA history tables.

Flashback Data Archive Architecture:

If FDA enabled on table, all transaction on the table and respective undo records are marked as archived. The FBDA new background processes sleeps and wakes up at self tuned intervals (default is 5 minutes) and processes the undo data marked for archival.

If FBDA background process and slaves are too busy, archiving may be performed inline, which significantly affects the users response time. 

  

 

FDA Supports

Oracle 11g Release 1 supports add column DDL operations.

Oracle 11g Release 2 supports below DDL operations.

1.     Adds, Drops, Renames and Modifies a column

2.     Adds, Drops, Renames a constraint

3.     Drops & Truncates a Partition & Sub partitions

4.     Rename, Truncate tables

5.     Performs a Partition & Sub partition operations

Unsupported DDL operations

1.     Alter table statement that moves or exchanges a Partition or Subpartition.
2.     Drop table statement

Flashback Data Archive Requirements:

1. FDA tablespace must be ASSM.
2. Undo tablespace must be Auto. 

Whether Oracle Total Recall Impact the Database Performance?

FBDA background processes can spawn multiple parallel thread process while DML statement ran against the table and bulk archiving of small transactions. As of now no one report, FDA caused any performance impact on database.

Test Case:

Reduced the undo retention from 900 seconds to 300 seconds

SQL> show parameter undo

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

SQL> alter system set undo_retention= 300

  2  ;

System altered.

SQL> show parameter undo_rete

NAME                                 TYPE        VALUE

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

undo_retention                       integer     300

Create a Separate tablespace for FDA

SQL> create tablespace fda_totalrecall

  2  datafile 'C:\APP\RAGA\ORADATA\TROY\fda_totalrecall01.dbf' size 500m;

Tablespace created.

Create Flashback archive and retention period is 2 years

SQL> create flashback archive fda_troy

  2  tablespace fda_totalrecall

  3  retention 2 year;

Flashback archive created.

SQL> select owner_name,flashback_archive_name,retention_in_days,create_time,last_purge_time from dba_flashback_archive;

OWNER_NAME  FLASHBACK_ARCHIVE_NAME    RETENTION_IN_DAYS CREATE_TIME                      LAST_PURGE_TIME

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

SYS         FDA_TROY                                730 04-FEB-13 05.45.56.000000000 AM  04-FEB-13 05.45.56.000000000 AM

Create test schema troy

SQL> create user troy

  2  identified by troy

  3  default tablespace USERS

  4  quota unlimited on USERS

  5  temporary tablespace temp;

User created.

SQL> grant connect,resource,dba to troy;

Grant succeeded.

Create test table under troy schema

SQL> conn troy

Enter password:

Connected.

SQL> create table test as select * from dba_objects;

Table created.

Alter the test table in FDA

SQL> alter table test flashback archive FDA_TROY;

Table altered.

SQL> select * from dba_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME  FLASHBACK_ARCHIVE_NAME    ARCHIVE_TABLE_NAME                            STATUS

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

TEST                           TROY        FDA_TROY                  SYS_FBA_HIST_73104                            ENABLED

SQL> select object_id,owner,object_name,object_type from dba_objects where  object_name='TEST';

 OBJECT_ID OWNER                OBJECT_NAME     OBJECT_TYPE

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

     73104 TROY                 TEST            TABLE

Version Query against undo tablespace

SQL> SELECT object_id,owner,object_name,object_type

  2  FROM TEST

  3  AS OF TIMESTAMP TO_TIMESTAMP('04022013 18:30:21','ddmmyyyy hh24:mi:ss')

  4  WHERE object_id=73104;

FROM TEST

     *

ERROR at line 2:

ORA-08180: no snapshot found based on specified time

SQL>  SELECT object_id,owner,object_name,object_type

  2    FROM TEST

  3    VERSIONS BETWEEN TIMESTAMP

  4    TO_TIMESTAMP('04022013 18:30:21','ddmmyyyy hh24:mi:ss') AND

  5    TO_TIMESTAMP('04022013 18:35:21','ddmmyyyy hh24:mi:ss')

  6    WHERE object_id=73104;

  FROM TEST

       *

ERROR at line 2:

ORA-01466: unable to read data - table definition has changed

SQL> select to_char(sysdate,'ddmmyyyy hh24:mi:ss') ddate from dual;

DDATE

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

04022013 06:49:21

SQL> delete from test;

71772 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)

----------

         0

Retrieve the Data from FDA

SQL> select count(*) from test as of timestamp to_timestamp('04022013 06:49:21','ddmmyyyy hh24:mi:ss');

  COUNT(*)

----------

     71772

SQL> select object_id,owner,object_name,object_type from test as of timestamp to_timestamp('04022013 06:49:21','ddmmyyyy hh24:mi:ss') where object_id=73104;

 OBJECT_ID OWNER                OBJECT_NAME     OBJECT_TYPE

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

     73104 TROY                 TEST            TABLE

Administration:

Flashback Archive Administer – New system privilege managing the FDA

Flashback Archive – New object Privilege for enabling flashback data archive.

Reference: Oracle Documentation and Oracle Total Recall white Paper

I Hope this article helped to you. I am expecting your suggestions/feedback.