Data archiving strategy
Hi,
Does anybody know of any good book/article about strategies to archive old data from an OLTP type Oracle database? If it has any hands-on examples it's even better. Since that's probably a common requirement, I hope there exists some material about this....
Maybe it's better if I describe the requirements I have and the solutions I thought of. We have an 11gR2 database where the growth of data is approximately 30GB/month. The largest part of it is due to some log activity tables which store XML data in CLOB columns (not XMLTYPE). None of the tables are partitioned. We still have some time till the disks will be full, but I'd like to establish a process to periodically archive old data to tape. For now we don't need data mining functionalities (i.e. a data warehouse) since I'd only like to archive log tables for legal compliance.
Roughly, I thought of something like the following:
1) Since XMLs are only inserted once and never updated again, I thought to convert them to SECUREFILE LOBs and apply HIGH compression on them. Now they are defined as BASICFILE CLOBs.
2) Turn log tables into range partitioned or interval partioned ones (partition by month). This should have been done before the application was put in production, but unfortunately it wasn't.
3) At the end of the month exchange/truncate the partition
4) Somehow export the old exchanged to tape (datapump export; transportable tablespaces if they are moved/created in its own tablespace; maybe using RMAN and READ-ONLY tablespaces?)
Anyway I have some questions/doubts, like:
1) How to archive data from multiple tables if data for a single entity is spread across many tables and some of them are partitioned and some are not. For example if there's a parent-child relationship and the child is partitioned but the parent is not.
2) Which technique is advisable for exporting/archiving data out the database? As I wrote above it could be expdp, transportable TS, RMAN
3) How to deal with table definitions changes in the OLTP database when an old partition has to be plugged again in the database?
So if anyone knows any good paper about questions I wrote about, I'll appreciate it very much.
Thanks in advance and regards,
Jure