“Total Recall”: Brief introduction into Flashback Data Archive

With Oracle Database 11g, we have a new Option available, called Total Recall. This option extends the possibility to do Flashback Query, introduced in 9i already.

We can now designate particular tables for being able to track back all the changes on them even after years. This option addresses especially legal requirements to keep history for certain data for several years. With Total Recall resp. Flashback Data Archive, this can be achieved quite comfortable and efficient. In order to demonstrate that, I create a demo user, giving him the DBA role (kind of “quick & dirty” of course, but makes the demo easier):

SQL> grant dba to quaid identified by quaid;

Grant succeeded.

SQL> connect quaid/quaid
Connected.

SQL> create tablespace flatbs
 datafile '/u01/app/oracle/oradata/orcl/flatbs01.dbf' size 50m;

Tablespace created.

This tablespace is just an ordinary tablespace, but I am going to use it to hold the Flashback Archive, created now:

SQL> create flashback archive fla_10y
 tablespace flatbs retention 10 year;

Flashback archive created.

If I designate a table to fla_10y, I will be able to do Flashback Query on that table even after 10 years – provided I have the space to hold that history in the tablespace(s), associated to that Flashback Archive. Following creates two ordinary tables with ordinary rows in them:

SQL> create table norecall
 (id number, name varchar2(50));

Table created.

SQL> create table totalrecall
 (id number, name varchar2(50));

Table created.

SQL> insert into norecall values (1,'QUAID');

1 row created.

SQL> insert into  totalrecall values (1,'QUAID');

1 row created.

SQL> commit;

Commit complete.

I want to be able to keep the history of the second table for 10 years:

SQL> alter table totalrecall flashback archive fla_10y;

Table altered.

SQL> select to_char(systimestamp,'yyyy-mm-dd:hh24:mi:ss')
 from dual;

TO_CHAR(SYSTIMESTAM
-------------------
2009-10-21:10:55:47

I will do DML on the two tables now. As you probably know, this leads to the creation of before images in the undo tablespace, which we can use to look into the past of the tables – but most likely not after 10 years, because the before images in the undo tablespace are getting overwritten somewhen.

SQL> delete from norecall;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from norecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');

 ID NAME
---------- --------------------------------------------------
 1 QUAID

SQL>  delete from  totalrecall;

1 row deleted.

SQL> commit;

Commit complete.

The select above is a Flashback Query, as it is possible since 9i. I will now make sure, that there are no before images left in the undo tablespace by creating a new one and dropping the old one.

SQL> create undo tablespace undonew datafile
 '/u01/app/oracle/oradata/orcl/undonew01.dbf' size 50m;
Tablespace created.
SQL> alter system set undo_tablespace='UNDONEW';
System altered.
SQL> select * from norecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');
 ID NAME
---------- --------------------------------------------------
 1 QUAID

As long as the old undo tablespace is still there, it may still get used for a conventional Flashback Query. But no longer after the drop tablespace. The before images of the history-tracked table, though, are saved into the Flashback Archive (getting compressed during the transfer), which would be the same, if the content of the old undo tablespace gets overwritten by new before images. That’s why I still can do Flashback Query with totalrecall:

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate

SQL> startup

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.
SQL> connect quaid/quaid
Connected.

SQL> select * from norecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');  
select * from norecall as of timestamp
 *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number  with name "" too small

SQL> select * from totalrecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');   

 ID NAME
---------- --------------------------------------------------
 1 QUAID

I will insert a new row into the ordinary table to demonstrate further differences between it and the history tracked table:

SQL> insert into norecall values (2,'John Doe');
1 row created.
SQL> commit;
Commit complete.
SQL> select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2009-10-23:02:14:28

Upto this point of the story, the shown features are the same in 11g release 1 already available. Following are new features of 11g release 2: We are now able to do DDL statements on the history-tracked tables and can still do Flashback Query!

SQL> alter table norecall drop column name;

Table altered.

SQL> select * from norecall  as of timestamp
 2  to_timestamp('2009-10-23:02:14:28','yyyy-mm-dd:hh24:mi:ss');
select * from norecall  as of timestamp
 *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL> select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2009-10-23:02:17:06

SQL> truncate table norecall;

Table truncated.

SQL> select * from norecall  as of timestamp
 2  to_timestamp('2009-10-23:02:17:06','yyyy-mm-dd:hh24:mi:ss');
select * from norecall  as of timestamp
 *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

Above demonstrated that DDL on an ordinary table makes Flashback Query behind the DDL impossible. That was nothing new. New is:

SQL> alter table totalrecall drop column name;

Table altered.

SQL> select * from totalrecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');  

 ID NAME
---------- --------------------------------------------------
 1 QUAID

SQL> truncate table totalrecall;

Table truncated.

SQL> select * from totalrecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');   

 ID NAME
---------- --------------------------------------------------
 1 QUAID

With 11g release 2, we can do Flashback Query behind DDL on tables in the Flashback Archive! In 11g release 1, DDL on history-tracked tables was prohibited, which was a show-stopper for many customers.  One DDL, we still can’t get through is of course:

SQL> drop table totalrecall;
drop table totalrecall
 *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
About these ads

, , ,

  1. #1 by Ittichai Chammavanijakul on October 23, 2009 - 15:59

    Uwe,
    Thanks always for clear and precise samples. I agree with you that before 11gR2, the FDA is not very appealing mainly because the DDLs are not allowed while under FDA. We’ll definitely take a look at it again once we move to R2. Ittichai

  2. #2 by Surachart Opun on October 23, 2009 - 20:52

    Thank you so much.

    You explain in the thing(Flashback Data Archive & Flashback Query) I want to know and very clearly….

  3. #3 by Uwe Hesse on October 26, 2009 - 08:27

    @Ittichai: Thank you! It is nice to hear that you are going to give that feature a second chance.

    @Surachart: I appreciate your feedback – glad if that posting was helpful to get an understanding about Total Recall

    Kind regards
    Uwe

  4. #4 by Surachart Opun on October 26, 2009 - 08:36

    yes, your post is helpful to understand “Total Recall” feature.
    I used your idea and example test on my TEST DB.
    ;)

  5. #5 by Emre Baransel on November 9, 2009 - 09:28

    Thank you for your useful post Uwe. It has been helpful for me.

  6. #6 by Uwe Hesse on December 9, 2009 - 18:29

    This posting got published in the Oracle University EMEA Newsletter of November 2009:

    http://www.oracle.com/global/uk/education/eblast/uk_ie_technology_newsletter_111109_ol.html

  7. #7 by Mohsin on February 12, 2010 - 07:16

    This note is very clear and helpful in understanding the Oracle Total Recall concept. Thanks for your nice effort.

  8. #8 by Viral Desai on March 11, 2010 - 18:02

    Say, If the table is enabled for flashback data archive for say 2 months. We do a flashback past recent updates to that table, then by using the undo data from undo segments and with dirty buffers, the time consistent image of the block is used to return data (atleast per the trace files for 11gR1).

    When does Oracle time based query decides to go to flashback data archive instead of looking undo ? Is only using the flashback data archive when it can’t find the data in undo? If there are a lots of update on same block, there are a lot of consistent reads done on UNDO to get time consistent data, is there any parameters that will make Oracle look at the flashback data archive instead of undo?

  9. #9 by Uwe Hesse on March 11, 2010 - 20:43

    Viral,
    indeed, to my knowledge the undo information of the history tracked tables is used if still present – it gets transmitted into the flashback archive just before it would be overwritten in the undo tablespace. Therefore, there is no possibility to demand the usage of the flashback archive. If the information is there, then it is no longer in the undo tablespace. If the information is still in the undo tablespace, it is not yet in the flashback archive. Short answer to your question: No

  10. #10 by Reddy on April 23, 2010 - 06:33

    Thanks…., Uwe

    It is very clear,
    It helpful for everyone….

    Thankss….@

  1. Log Buffer #167: a Carnival of the Vanities for DBAs | Pythian Group Blog
  2. lucrari de diploma

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,975 other followers

%d bloggers like this: