Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Archiving historical data

Re: Archiving historical data

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Fri, 01 Mar 2002 07:55:08 GMT
Message-ID: <3c7f2eb3.586533085@news.saix.net>


"Sathish Balas" <sathishrani_at_home.com> wrote:

> Anybody could share with me ideas about how historical
> data is archived ? We have a database with 3 yrs data .
> we want to archive 2 yrs data . We would need to restore
> 2 years data within 3 days .

Many ways to skin this cat. But I think the problem that you currently face is that the database is already designed and populated, making it difficult to implement things like partitioning, right?

The biggest problem IMO is not the loading or unloading of data.

Okay, the easy stuff first. Loading and unloading. Essentialy it comes down to two options. Binary loads and unloads using export and import. That will not be my first choice. The data is locked into a proprietary format. Makes more sense to me to have the data in an open format, allowing me to load it into any other database system.

Second option is CSV (text file) dumps (which is what I use). This can be as easy as running a SQL*Plus script, spooling the data into a pipe, where it is automatically compress into a zip archive file (btw, same technique can be applied to auto compress exports).

Loading the CSV data via SQL*Loader - using direct loads, you can load masses of data pretty quickly.

The only real problem with the unloading partion is getting rid of the archived data after the unload has completed. I assume that the old data is within the same tables as the current data? Running SQL DELETEs will be a major job if the data quantities are large.

The complex stuff IMO is the issues relating with maintaining the integrity of the data.

How do you ensure data integrity when you load historical data into the live production database? What happens in the case of structural database changes? Is the old data properly seperated from the current data? For example, if a billing tariff changes, is a new row created, or is the existing one updated? Spooling old tariffs from the archives into production should not mess around with current tariff rows.

My take on this would be to forget about looking for a technical solution for starters. Rather look at the functional requirements and how logically these can be met - whether that should be done via exports/imports or whatever, is a decision that should only be made after the design issues has been trashed out and you know what the implications and requirements are. Maybe changes to the logical design of the database is required?

Maybe, after the full scale of what is required is know, it may make more sense to rather just simply buy additional disks? ;-)

--
Billy
Received on Fri Mar 01 2002 - 01:55:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US