Another poor man's solution would be to unload the tables into flat files and attach
to them as needed using Oracle's external table feature from 9i. That solution
should hold for quite a while into the future since the external table function is
very
much like SQL*Loader, which is so integral to so many systems that Oracle is not
going to think about making it 'go away'. You would still run into problems if there
is
some substantive change that makes the external tables from 9i invalid, but that still
leaves you with flat files that you can load back into the DB with SQL*Loader.
Chris Gait
On 6 Nov 2002 at 6:43, Conboy, Jim wrote:
Date sent: Wed, 06 Nov 2002 06:43:38 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-
[EMAIL PROTECTED]>
Send reply to: [EMAIL PROTECTED]
Organization: Fat City Network Services, San Diego, California
> 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
>
>
> -----Original Message-----
> Sent: Wednesday, November 06, 2002 8:49 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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 -----
> To: Multiple recipients of list ORACLE-L <mailto:ORACLE-L@;fatcity.com>
> Sent: Wednesday, November 06, 2002 2:13 AM
>
>
> 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:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
