How much data are we really talking about anyway? I suspect the requirement
is way overblown with buying more disk. Think about all the time and effort
and recovery procedures etc. that will have to be implemented and then
having a way of looking at the data if it is requested. (does someone fill
out a form? What about querying old data, its missing so results will not
foot.) Even at some very low labor rate I think disk space (even mirrored )
would be much cheaper.
Jim
"Shafeek Khalidh" <itsprobablyme_at_msn.com> wrote in message
news:bi2qvh$4m2aa$1_at_ID-137434.news.uni-berlin.de...
> Jim, I came up with few methods, check this.
>
> Different methods of data archival
>
>
> a.. Need to keep only 6 month's data in the production server.
> b.. Need to archive the old data in such a way that a retrieval should
be
> done with in three days.
> c.. The version of database is oracle 9.
> d.. After archival, the data from the production should be purged using
> procedures/scripts, derived using business rules.
> e.. Master tables will not be archived.
> f.. Unwanted records criteria should be clearly defined and it should
not
> be based on the time frame. So, we can archive all closed claims instead
of
> going for time of insertion. There has to be a field in all transaction
> tables to determine the criteria of data to be purged.
>
>
> 1. Data transfer using procedures
>
>
> Assumptions: - A separate archival system is provided to hold all the
> archived data.
>
>
>
> Description: - The records that have to be removed from the production
> database using the defined business logics are selected. These selected
data
> are moved to an archived database and purged from the production database.
>
>
>
> Advantage: Easy and direct method, with out much complexity.
>
> Whenever it is required the old data is readily available avoiding the
time
> delay.
>
>
>
> Disadvantage: The size of the archived database will grow.
>
>
>
> 2. Table space backup
>
>
>
> Description: - Create 12 different table spaces, create table with
> partitioning in such a way that the records of the January will written
into
> the first table space and the February will written into the second table
> space and so on.
>
>
>
> In this the table space, which needs to be archived can be made offline
and
> the backup can be taken. We can then clear the data from the specific
table
> space.
>
>
>
> Advantage: The data can be segregated on months. This could improve the
> performance.
>
>
>
> Disadvantage: Maintaining of the tablespace.
>
>
>
>
>
>
>
> 3. Import & Export
>
>
>
> Assumption: Using of external device (like tape) to store the archived
> data.
>
>
>
> Description: Data can be exported to a dump file using the export utility
> with conditions. The production data could be purged after the dump is
> taken. This dump can be taken for each month and maintained on a backup
> device. Whenever required these dumps can be imported to the database
using
> the append enable option in the import utility.
>
>
>
> Advantage: Easy coding and can use the utility provided by Oracle.
>
>
>
> Disadvantage: Every dump should be tested.
>
>
>
> 4. Separate Table Space For Closed Records
>
>
>
> Assumption: Using of external device (like tape) to store the archived
> data.
>
>
>
> Description: Here separate table spaces are created for open data (data
we
> need to maintain in the database) and closed data (data we can remove from
> the database). When a set of records or a transaction is complete, that
> records are to be moved to another table space and make it offline and
> delete it, to recover, copy it back and make it online.
>
>
>
> Shafeek
>
>
Received on Thu Aug 21 2003 - 20:38:03 CDT
