A
poor man's solution might be to load the offline database with appropriate
data, then do a tablespace export and store the results on CD labelled by
date. Restoring needed data would entail a tablespace import of stuff
from the appropriate CD into the offline DB. I'm sure here's some
gotchas involved but some variation on that theme might
work.
Jim
This is a data-archival requirement, not a
data-purge requirement. It only resembles a purge requirement based on
the multiple-database-migration strategy you outlined. There are
alternatives...
Depending on the volume of data in your database
and your availability requirements, implementing table- and index-partitioning
will likely be crucial. One strategy is to have the
most-active tables partitioned by a date column and have different sets of
these partitions reside in time-variant tablespaces. With this
arrangement, you can archive data to tape by simply setting the archived
tablespaces to READ ONLY and then migrating them to tape-based (instead
of disk-based) file-systems and bringing them back online. Legato has
this file-system technology (recently purchased) and there is a
share-ware product called SAMFS which is an HSM (hierarchical storage mgmt)
filesystem used by some vendors (i.e. StorageTek, etc). By setting
tablespaces to READ ONLY it becomes very easy to move them from disk to tape
while retaining them within the same original database, simplifying the task
of later retrieval (which is really important).
Of course, Oracle's partitioning option is
enormously expensive, but in this case it is a matter of the upfront license
costs (with reduced downstream implementation costs due to
simplicity) versus a large downstream application-development
cost. In this situation, I think roughly offsets everything. Since
I'm not spending the money, I can afford such a calculation...
:-)
With the various storage technologies available,
a single database can straddle several simultaneously, optimizing performance
or cost as needed. Some files might reside on solid-state NVRAM
"disk", some on SAN-based disk, some on NAS-based storage, and then finally
reside in archive media file-systems such as tape or magneto-optical based HSM
file-systems.
----- Original Message -----
Sent: Wednesday, November 06, 2002 2:13
AM
Subject: Data Purging Strategy
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