jump to navigation

Constraints With Oracle Total Recall (Remember A Day) October 7, 2008

Posted by Richard Foote in 11g, Constraints, Flashback, Total Recall.
trackback

11g has many new and potentially useful features which I’m slowly investigating and evaluating.

One feature that has the potential to be particularly useful where I work is “Oracle Total Recall“. It provides the facility to automatically capture all changes to any tables you choose, in a “secure, tamper proof” manner. This enables all changes to be easily tracked and viewed for historical, auditing, security control, etc. purposes.

Thought I might “introduce” Total Recall to those of you that may not have seen it in action and to offer an initial word of warning of a little trap one can easily fall into …

One nice aspect with it all is that it’s very easy to setup and configure.

To begin with, you need to create a storage area called a “flashback data archive” where all the historical changes can be stored, e.g.:

SQL> CREATE FLASHBACK ARCHIVE bowie_archive
        TABLESPACE total_recall_ts QUOTA 500m RETENTION 1 YEAR;

You simply specify the tablespace you wish to use, how much space within the tablespace you wish to allocate for archiving purposes and a retention period to specify how long to keep the archived data.

To create a table with the “Total Recall” change tracking capability, simply specify which flashback archive area to use:

SQL> CREATE TABLE bowie_recall (id number, text varchar2(20), hist_date date) FLASHBACK ARCHIVE bowie_archive;

Table created.

All changes to the table are now automatically tracked and archived and can easily be accessed as necessary. To illustrate, I’m just going to simply insert a row into the table.

SQL> insert into bowie_recall values (1, ‘Bowie’, sysdate);

1 row created.

SQL> commit;

Commit complete.

Sometime later, I perform an update on the row:

SQL> update bowie_recall
  2  set text = ‘Ziggy’, hist_date = sysdate
  3  where id = 1;

1 row updated.

SQL> commit;

Commit complete.

OK, so the current row basically looks like thus:

SQL> select id, text, to_char(hist_date, ‘dd-mm-yyyy hh24:mi:ss’) hist_date
         from bowie_recall;

  ID TEXT     HIST_DATE
---- -------- -------------------
   1 Ziggy    06-10-2008 10:09:55

 

However, if I want to see what the row looked like at a previous point in time, I can use the AS OF TIMESTAMP clause such as this:

SQL> select id, text, to_char(hist_date, ‘dd-mm-yyyy hh24:mi:ss’) hist_date
         from bowie_recall
         as of timestamp to_timestamp(‘2008-10-06 10:07:00′,
                     ‘yyyy-mm-dd hh24:mi:ss’)
         where id = 1;

  ID TEXT     HIST_DATE
---- -------- -------------------
   1 Bowie    06-10-2008 10:06:15

 

This is what the row looked like when it was initially inserted.

If I want to look at all the different versions of the row between any specific times of interest, I can write something such as this using the VERSIONS BETWEEN clause:

SQL> select id, text, to_char(hist_date, ‘dd-mm-yyyy hh24:mi:ss’) hist_date
         from bowie_recall
         versions between timestamp
         to_timestamp(‘2008-10-06 10:07:00′, ‘yyyy-mm-dd hh24:mi:ss’)
         and sysdate
         where id = 1;

  ID TEXT     HIST_DATE
---- -------- -------------------
   1 Ziggy    06-10-2008 10:09:55
   1 Bowie    06-10-2008 10:06:15

 

It lists all versions of the rows between the two points of time …

Like I said, very easy to setup and very easy to subsequently query.

One of the disadvantages however of enabling the tracking of changes to a table is that it prevents a number of subsequent operations on the table. For example, you can’t simply drop a column:

SQL> alter table bowie_recall drop column status;
alter table bowie_recall drop column status
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

You also for example can’t modify a column to say add a NOT NULL constraint:

SQL> alter table bowie_recall modify text not null;
alter table bowie_recall modify text not null
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

Now the trap …

Oracle does however allow you to add any other type of constraint, such as say a Primary Key, without complaint:

SQL> alter table bowie_recall add primary key (id);

Table altered.

All well and good, except for when you want to now retrieve some of the historical changes from the table:

SQL> select id, text, to_char(hist_date, ‘dd-mm-yyyy hh24:mi:ss’) hist_date from bowie_recall versions between timestamp to_timestamp(‘2008-10-06 10:07:00′, ‘yyyy-mm-dd hh24:mi:ss’) and sysdate where id = 1;
select id, text, to_char(hist_date, ‘dd-mm-yyyy hh24:mi:ss’) hist_date from  bowie_recall versions between timestamp to_timestamp(‘2008-10-06 10:07:00′, ‘yyyy-mm-dd hh24:mi:ss’) and sysdate where id = 1
                                                                             *
ERROR at line 1:
ORA-01466: unable to read data – table definition has changed

Remember, one of the reasons for implementing Total Recall on a table is to implement a “tamper proof” data tracking solution and yet the addition of any constraint on a table (Primary Key, Unique Key, Foreign Key or Check Constraint) will cause you to be unable to automatically read the historical data, without directly accessing the underlining SYS_FBA_ archive tables.

Be careful with constraint management and the historical tracking of tables …

Total Recall is of course all relatively new and these sorts of early glitches are to be expected. More on Total Recall later, including possible performance implications when both modifying and querying historical data.

About these ads

Comments»

1. Brian Tkatch - October 7, 2008

Very nice feature. Ultimately, i can rid of those ridiculous SO-x COLUMNs that exist in *every* TABLE. Sheesh!

2. Raj - October 8, 2008

Can you add a table to “Total Recall” after it has been created? i.e. Alter table <> Flashback archive <>

3. Robert Klemme - October 8, 2008

Richard, I am a bit confused: can you please point out more explicitly which of the above is new or changed in 11g? Flashback and table history are present in 10g already so I am wondering where the exact difference is. Thanks!

Cheers

4. Richard Foote - October 8, 2008

Hi Brian

It can certainly simplify things but as with most things it comes at a cost. Hopefully, it many scenarios, the benefits will outweigh the costs.

5. Richard Foote - October 8, 2008

Hi Raj

Yes, you can alter a table to start using a flashback archive. You can also turn it off with a NO FLASHBACK ARCHIVE command. In fact before you can drop such a table, you must first turn off the flashback archive.

6. Richard Foote - October 8, 2008

Hi Robert

You can’t run the above scenario with 10g as you can’t create a flashback archive for a table.

You can run a flashback query as described in 10g but it may or may not work depending on whether the undo is still floating around. With 11g, the above queries are guaranteed to work, so long as they reference data within the retention period of the flashback archive. You retain data for 10 years, the query will guarantee to flashback 10 years in time if necessary.

7. Robert Klemme - October 8, 2008

Richard,

so the basic differences are the level of guarantee we get and that 11g will allow flashback to be configured on a per table level. Is this correct?

IIRC in 10g you can set undo size based on needed retention time and Oracle will do some math with the current undo generation rate. But you are right, there is no guaranteed time.

Thanks for clarifying!

8. Richard Foote - October 8, 2008

Hi Robert

That’s right, it’s the guarantee that’s the important part and the period for which it can be guaranteed.

Undo is primarily used to undo changes and to support read consistency. You can set a retention period for undo but it’s typically designed to be in the order of hours. Flashback query makes use of this undo more as an aside.

However, with a flashback archive, these changes are permanently saved in various tables, or at least for as long as the rentention period is set in the flashback archive. But this could be for many months or even many many (100s) of years.

That’s the difference.

9. Gabor Kecskemeti - October 8, 2008

Hi,

From this description it seems this can actually do less than Worksapace Manager, which already exists in 10g. WM can do all the above things with just a slightly more complex syntax. You can also use (some) DDL statements on a version enabled table in WM. Does this new feature have better performance then WM? Or any other advantage beside simpler syntax? A comparison would be nice, if you have the time :).

10. Gabor Kecskemeti - October 8, 2008

Just to answer my own question : One possible advantage is the ability to store the archive and live data in different tablespaces. Anything else?

11. Richard Foote - October 9, 2008

Hi Gabor

I don’t have time for a full comparison (although one would be nice to do one day).

WM is another way to skin a similar, yet different cat. Yes, you can capture the history of a table changes if you enable versioning on the table, but the manner and purpose for doing so differs somewhat.

Note Total Recall (TR) only prevents some DDL operations, you can still add additional columns, compress, change physical attributes, etc.etc. and you can do so without the need for special procedures. With WM you also have restrictions on what DDL can be performed and you must version enable all related tables, not necessary with TR.

It’s much much easier to setup and maintain TR and much much easier and flexible to list and query historical data performed by any/all users and processes as this is what TR is specifically designed just to do.

Just some initial thoughts !!

12. Rob van Wijk - October 9, 2008

Hi Richard,

Did you test this on 11.1.0.6 or 11.1.0.7?
Reason I ask is that I did a similar post on flashback data archives within the context of journaling in 11.1.0.6 (http://rwijk.blogspot.com/2007/11/setting-up-journaling-using-flashback.html) and was planning to see if 11.1.0.7 has resolved anything.

Looking forward to your possible performance implications post on this subject.

Regards,
Rob.

13. Richard Foote - October 10, 2008

Hi Rob

I’ve been playing around with 11.1.0.6 on both Windows and AIX environments although patching up to 11.1.0.7 will be on my to do list when possible.

14. Frederick Tang - October 15, 2008

Hi Richard,

Would you consider Flashback data archiving as a backup solution? and what about performance, redo generation… etc, if I am running a long delete on the table?

rgds,
fred

15. Richard Foote - October 15, 2008

Hi Frederick

Most definitely not a backup solution !! Because it simply wouldn’t be useful in most recovery scenarios unless it’s to restore an accidently updated / deleted row or some such. It certainly wouldn’t replace any of your current backup strategies.

Yes, most definitely there’s a performance hit because now Oracle needs to maintain other data structures in the background. Not just a long delete, but any DML get’s a hit. And this of course generates more redo, more resources, etc

I’ll expand on all this at some future time I promise.


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 1,853 other followers

%d bloggers like this: