Re: Data Purging Strategy

[Tim Gorman]

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 ----- From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L 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