Re: Archieval of data

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 - 10:54:47 CDT