Oracle-L: RE: Data Purging Strategy

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Wed, 06 Nov 2002 08:58:56 -0800
Message-ID: <F001.004FCD1F.20021106085856@fatcity.com>

LOL! Thanks John! ;)

As a matter of fact.. ;P

I do actually know of another tool that does just this, it's called Checkmate from a company called BitByBit http://www.bitbybit.co.uk (which I have just checked and it now seems they have been acquired by OuterBay!).. We actually used to promote Checkmate for them, but in all honesty it was a little hard for the every day DBA to use.. When we came across DataBee, which performs the function that most DBA's wanted anyway (subsetting), we decided to go that route instead..

Prepare to have some $$'s if your going to look at Checkmate. Checkmate does take a referentially correct archive, and purges the data after it has been archived (and checked for data integrity etc).. Checkmate also has the ability to archive to an "historic" database, so that all data is still "online", and accessible through database links if need be, whilst keeping the size of the "live" system down.. This also give the benefit of all historic reporting being run against a separate system as well. This is along the same lines as what Tom is saying really, although you will still get the historical reporting loads against your prod system with his guidelines.

And no, I don't get any gains from saying any of this :) Hey, they also have modules for Oracle Apps, and Peopl$lop! Go get 'em Dick!

<G,D&RLH>

Mark

-----Original Message-----

John.Hallas_at_vodafone.co.uk
Sent: 06 November 2002 15:56
To: Multiple recipients of list ORACLE-L

Hey Dennis,
Mark Leith is the only person on this list allowed to mention 3rd party products.
I am sure he bought the franchise from Jared :)

John

-----Original Message-----

Sent: 06 November 2002 14:15
To: Multiple recipients of list ORACLE-L

Prem - You are receiving some excellent advice from Tom and Tim. I would mention two items in addition:

If you ever hope to re-use the data you archive off-line, you must also archive all the related tables, because after all, this is a RELATIONAL database.
PrincetonSoftech has a product Active Archiving that looks pretty good from the demos I've seen. I haven't used it myself.

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----

Sent: Wednesday, November 06, 2002 6:54 AM To: Multiple recipients of list ORACLE-L

Prem,

I would re-visit the requirement. Why do you feel the need to delete the data from the database? What is the purpose for this type of requirement? It would be far easier to modify the requirement than to do what you are thinking of doing.

Adding columns to database tables indicating that a record has passed it's retention policy and thus, is not included in queries, would be a much easier solution.

Or, simply moving these records to historical tables in the database - and NOT deleting them from the system - is a much better solution. The data is always accessible and not available in the current tables. And you will not be playing the "get the data from tape and reload it" game with all of it's problems (writing an offload program, table structure changes & offload program versions).

Try and keep this as simple as possible.

Hope this helps

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

Sent: Wednesday, November 06, 2002 4:13 AM To: Multiple recipients of list ORACLE-L

Dear List,

I need some inputs from you all regarding purging data from the database.

This is the requirement

We define a retention period for all the data in the system. When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data.

This is the strategy we have designed for this.

When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database.

In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates. i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables.

Regards
Prem

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


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

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: John.Hallas_at_vodafone.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


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

To REMOVE yourself from this mailing list, send an E-Mail message

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mark Leith
INET: mark_at_cool-tools.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


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

To REMOVE yourself from this mailing list, send an E-Mail message