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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Data Purging Strategy

Re: Data Purging Strategy

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Wed, 06 Nov 2002 15:08:36 -0800
Message-ID: <F001.004FD4B3.20021106150836@fatcity.com>


Agreed. The current Oracle datafile format (7.3, 8.0, 8.1, 9.0, or 9.2) may not survive 10i -- who the heck know?

However, the tape-based file-systems (i.e. SAMFS, Legato DiskExtender, etc) can be treated like a file-system in all respects (only slower). In other words, convert the datafiles in place just as you would if they are on disk. After all, they are still active parts of an active Oracle database, even if they are in READ ONLY. The last time such a conversion was necessary was between Oracle7 and Oracle8; I think that it is valid to assume that Oracle will provide a similar migration utility should another such conversion become necessary. Whatever needs to be performed for one tablespace in converting file formats should be done for all, so this is not a problem specific to the strategy I mentioned...

  Tim,

  my problem with moving data to tape is as follows:

  Your one strategy involved moving read-only tablespaces to tape. what if you upgrade Oracle versions. will these read-only files still be valid? will they still be able to be put back on-line, or will they need to be converted along with the rest of the files to the newer version and then copied back to tape. if this is the case, is there disk space to put all of these back? if there is disk space, then why copy them to tape at all? they could always be available and on-line.

  Having a plan to save data to tape in hopes of resurrecting it later on has more challenges than anything I've come across lately. It just doesn't seem to make sense to do this anymore. With disk space at an all-time low-cost, why put ourselves thru this? the logistics are just too expensive, as well as the risk of never being able to get the data back because of software compatibility issues.   Tom Mercadante
  Oracle Certified Professional

    -----Original Message-----
    From: Tim Gorman [mailto:Tim_at_SageLogix.com]     Sent: Wednesday, November 06, 2002 8:49 AM     To: Multiple recipients of list ORACLE-L     Subject: Re: Data Purging Strategy

    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.

      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: Tim Gorman
  INET: Tim_at_SageLogix.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). Received on Wed Nov 06 2002 - 17:08:36 CST

Original text of this message

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