Archiving strategy

[Carlo Henrico_1]

I am looking for advice/guidelines to set up an archiving strategy.

We run an HP-UX 11.0 whith Oracle 8.1.6.2 and the requirement is to "remove" certain older data from the database but to still have it available for reporting purposes, as if it were still in the database. The purpose is to minimise database size for backup and search purposes.

I currently envisage designing and developing some application to store archived data on CD which could be restored temporarily...etc etc.

Anyone that can guide me to some strategy documents or existing tools or another will be appreciated.


Thanks

Carlo

[Victor BERRIDGE]

Hi,
What do you mean by:
>as if it were still in the database. The >purpose is to minimise database size for >backup and search purposes.

I mean the data are removed but are still there online?

I would create a arc_xx tablespace with "archive" tables you could recognize by its extensions table T01 archive of T01 would be T01_arc, that you maintain seprately with its own export strategy in order to cleanup regularly your regular tables and still have lets say the last archive version online, what do you think?

Good luck

All the best
Victor
I currently envisage designing and developing some application to store archived data on CD which could be restored temporarily...etc etc.

[Thierry Poels_1]

Hi,

you could move old data to a separate table in a separate tablespace (and maybe even in a separate database).
Archived data is (most of the time) less critical so you normally don't have to worry about high availabilty: mirroring, etc. So you could save some space compared to the operational database.

good luck,
Thierry.

[Edward Sedgemore]

The problem with archiving is that it involves changes to your application if you ever want to access the data archived away. This means rewiriting reports etc. which is not easy and time consuming.

Oracle 8 has a feature called partitioning. This is basically a subtable within a table which allows you to place its data on a particular disk/filesystem. So for archving we move archived data to these subtables which are located on non-fast disks, leaving the fast disks for the main tables to maintain database performance. We had to rewrite our reports to point to these subtables if someone required access to the archived data.

Normally archiving entails removing/exporting data from the database and placing on tape or CD, but to reaccess this data is a lot of work because someone has to manually pull the data back into the database.

Some sites archive data to other databases which are on old slow servers, but this allows those who need to access it much easier access.

[Thierry Poels_1]

Nice to mention partioning, Edward!! This has indeed some important advantages.
But also some drawbacks :(
- your 'operational' indexes will also refer to the archived data. The indexes can be partioned too, but many might be obsolete for the archived data.
- all full table scans (nobody can live without them :) will also search through the archived data.

Thierry