Best way to archive old data

[matthew00]

I have one table that was over 100G and I only need to keep 1 year of current data, the rest can be archived. What is the best way to handle this situation.

I am thinking to move everything except one year of archived data to a file and used External table if someone needs to look at it.

thoughts?

[PAVB]

Is the DATE column that defines how old the row is part of the predicate of most queries? can it be included?

If the answer is Yes... I would partition table by that DATE column then archive and purge entire partitions.

As to where to archive those partitions it all depends on business specs... one alternative would be to export/compress partitions then either store them on disk or tape. Import when needed.

[matthew00]

thanks for your input.

One of the option I also thought about was partition the table by date; however, if you partition by date, the table you archive the old data still in the database and the backup job still had to pick up that table and I am trying to cut down the backup run time.

thoughts?

[PAVB]

One of the option I also thought about was partition the table by date; however, if you partition by date, the table you archive the old data still in the database and the backup job still had to pick up that table and I am trying to cut down the backup run time.

Not really.

There are two alternatives.

Alternative #1 -
Offline archive. Export partition to be archived the truncate partition. Dump file can be either compressed and kept on disk or moved to tape.

Alternative #2 -
Online archive. Swith partition to be archived to a table sitting on a read-only tablespace temporarily open for writing then set it back to read-only. Once you have one good backup of archive tablespace you can safely skip it during normal backups.

[matthew00]

great thank

[matthew00]

Not sure if I fully understand option 2 so please clarify.

Not really.


Alternative #2 -
Online archive. Swith partition to be archived to a table sitting on a read-only tablespace temporarily open for writing then set it back to read-only. Once you have one good backup of archive tablespace you can safely skip it during normal backups.

so you saying, copy the archived data into the table that sitting on the read only tablespace. backup this tablespace one time and remove it from backup schedule. what about new data that coming? we need to set up the job to refresh the archived data?

[PAVB]

Not totaly sure what you mean by "refresh the archived data".

I was assuming archived data will not change after been archived - is that correct?

The beauty of a read-only tablespace is that after taking a good backup tablespace can be excluded from further backup processes - provided tablespace remains in backup mode.

Using this archiving approach - in general - archive process runs once a month switching an active partition to a table in an archive tablespace.

Process looks like...
1- Put archive tablespace in read-write mode
2- Switch a.k.a. archive partition to archive tablespace
3- Put archive tablespace in read-only mode
4- Take a backup

Archive tablespace doesn't need to be backup until next archive window.

[matthew00]

Thanks so much for taking time out to help me on my question.

What I mean was, let say archived data of one year of today is different from archived data of one year 6 months from today. another word, we just have to keep archiving data and only keep one year of current data.

Thanks again

[PAVB]

Not sure if I'm following.

Archival process should run lets say once a month. If this is the case I would partition "live" table by YYYYMM. At the end of each month archival process will take care of taking out the oldest partition leaving 12 month worth of data in "live" table.

Each time archival process runs it moves out - switches - a whole partition out of "live" table into an archival table sitting on archive tablespace.

In regards to the "archive" you can either opt for having an individual archival table per month - the same you get when switching partition - or create a partitioned archival table which you will load by switching the table that just got the switched partition from "live" table.

Hope this helps.

[matthew00]

I am so sorry if what I said is confusing. Here is an example and please let me know:

1. I have table named TEST and one column named EXPORT_DATE and the date column type.
2. I then partition named TEST table on column EXPORT_DATE with date range. (one year as current data).
3. create a temp table named ARCHIVED_TEST which will hold the data that was older than 1 year

question:

1. What do I need to do to move the archived data that was older than 1 yr from TEST table to ARCHIVED_TEST? If I understand correctly, you recommended that the archival process should be running at least one per month? Are you referring to a job that I need to set up?

Thanks for your patience.