Re: Archieval of data

On Fri, 22 Aug 2003 01:38:03 GMT, "Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote:

>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
>>
>>
>

And then there is another issue that I haven't seen mentioned .. . What happens when you (this is the generic "you") archive off a chunk of the EMP table and 18 months later you get a request to pull it back in. In the mean time, due to application enhancements, the structure of the EMP table has changed? Oopsies! Received on Fri Aug 22 2003 - 07:53:18 CDT