Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Database Archive

RE: Database Archive

From: John Kanagaraj <>
Date: Mon, 12 Apr 2004 15:45:21 -0700
Message-ID: <>

Jared et al,

The buzz word here is ILM - Information Lifecycle Management - the new frontier (aka big bucks waiting to be collected) for EMC ;-) Personally, I thought that this article highlighted the various facets of archiving of not-so-oft-used data out to an archive in a manner that is easily and transparently accessible. It does mention Princeton Software and OuterBay which are leaders in this industry. The latter is strong in Oracle Apps area, and it is worth investigating for us Apps DBAs. The problem (or issues!) are more from the Business/Business Analyst and Regulatory rules than from a technical point of view. The point is that the Technical side (us DBAs and the Developers to some extent, or the Data/Systems Architect if you have one) will have to drive this - the Business user/analyst will NOT to take this on, as it means headaches for them.

I was analyzing the data growth (not just segment growth, but actual row count growth) of some key tables in one of our fresh 11i databases - they grew about 300% in about a year! While it is true that tuning can alleviate some of the performance issues, the fact remains that sheer size will have its effect on overall response. A badly written query will have a greater impact on a larger dataset than it would have in a smaller dataset for sure! Morever, most complex applications are OTS, where you loose control over most of the code (and hence performance). Thus, the argument being made in the article that we need trim down the database is a valid one IMHO.

We did look at Archiving prior to an upgrade from an Apps 10.7 environment to Apps 11i, but gave up as the Project was too complex to handle at that time. No one has taken that on again, as it gets nasty and complex pretty quickly, but we probably should.

As to the article, the only fault I see is that it did not come on too strongly, and did not back this up with enough data (which is probably hard to get unless the writer implemented or measured a successful archival project). I am sure that OuterBay/Princeton have case studies of successful implementations though....

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on '' for Grace and Mercy that is freely available!

>-----Original Message-----
>[] On Behalf Of Daniel Fink
>Sent: Monday, April 12, 2004 2:57 PM
>Subject: Re: Database Archive
>My reactions:
>1) A proper archiving strategy can improve performance for OLTP
>activities and reduce storage requirements for the databases.
>However, if the database has not been architected with an
>archiving strategy in mind, archiving activities may cause more
>performance problems that it solves. BTW, the sidebar does not
>even mention this. In fact, the sidebar is so generic as to be
>useless to us non-PHB types.
>2) If you archive the data to a DSS/DW using the OLTP structures
>(don't laugh, I've seen this done all too often), the OLAP
>performance will be okay for a month or two, then it will
>degrade rapidly.
>3) The databases they refer to are small. I am working on
>building a 100g play database on my single-cpu Win2k box at
>home. I am sure there are systems out there that generate 27g of
>redo per day, not per month. 2tb was huge 5 years ago, not
>Overall, it's an okay article, but it seems more fluff designed
>to sell software and services by EMC.
> wrote:
>> First off, this is not about archive logs.
>> It is about archiving data. ie. moving data from your database to an
>> offline
>> system or moving it to nearline storage (HSM, etc)
>> I was reading an article in Computerworld about this:
>> Or just go to and type in QuickLink 44949
>> What struck me about this article is twofold
>> 1) archiving data to improve performance
>> 2) the databases they refer to don't seem all that large.
>> I would think that given a decent database to work with (Oracle)
>> and someone fairly knowledgeable to run it, achieving acceptable
>> performance would not require moving data out of the database.
>> For instance, one of the databases referred to is 100G is size.
>> Many of us have tables larger than that.
>> Just wondering what others reactions to this article are.
>> I plan to write a letter to computerworld regarding this,
>but thought it a
>> good idea to weigh in here first.
>> Thanks,
>> Jared
>> ----------------------------------------------------------------
>> Please see the official ORACLE-L FAQ:
>> ----------------------------------------------------------------
>> To unsubscribe send email to:
>> put 'unsubscribe' in the subject line.
>> --
>> Archives are at
>> FAQ is at
>> -----------------------------------------------------------------
>Please see the official ORACLE-L FAQ:
>To unsubscribe send email to:
>put 'unsubscribe' in the subject line.
>Archives are at
>FAQ is at

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Mon Apr 12 2004 - 17:38:34 CDT

Original text of this message