Archiving Oracle Applications

In my current post i will be touching upon one of the least talked about but extremely vital topic of data archiving in Oracle Applications. This post will talk about the need to archive and the pit falls of not archiving a production environment.
We will also briefly touch upon the various methods available to implement a effective archival policy for oracle applications.

Why Archive?
After an implementation of Oracle Application which has been around from some years, you would see a substantial growth in the database size.
As your Oracle Application continues to grow and upgrade the database also grows with it. A lot of data which resides in this database would no longer be needed to be updated and a significat amount of this data would rarely be used for any kind of Reporting. Nevertheless we cannot simply go ahead and purge or trash this data for obvious compliance and regulatory reasons. Another added advantage of archiving is that you can implement data masking in your non production environment while in the process of archiving, this reduces the risks from exposing your production data after subsequent clones.

There are many challenges associated with a large growing database like

* Increase in storage costs.
* Decrease in system responsiveness.
* Increase in times taken to complete cloning cycles.
* Increase in recovery times in cases of system failures.


What to archive ?
This is the most difficult when it came to implement an archive policy. Fortunately most of the archive solutions available in the market came with an out of box solution for Oracle Applications which made sure to preserve the referential integrity of the data. since data in an application is in its normalized form its important to ensure that the archived data is valid enough to be reported from when required. To ensure this a meta data repository is bundled along with the solution which defines the rules for archiving and the associated constraints.

Archiving Options
when it came to archiving options available with the ebusiness suite. these were quite a few of them available. Most of these solutions adopted a similar approach to archiving.
After going through the popular ones solutions available i can categorize them in the following groups.

* Proprietary File Type Based Solutions.
* Archive Database Based Solutions.

Proprietary File Type Based Solutions
Under these kinds of archiving solution the data is archived from Oracle Applications and stored in a flat file system which is generally in a proprietary format. The main and the biggest advantage of adopting such kind of the solution is that the need to maintain a large database is reduced. Also the archived data in the file system can be further compressed further to
shrink the size.
The major disadvantage of this approach is that you loose the capacity to do a seemless reporting from within your application. That is in order to be able to report from your archived data you would need to be depended on the proprietary reporting tool, rather than being able to do so from with oracle applications.

Archive Database Solutions
In the archive database based solutions, the archived data is stored in a separate database as opposed to a file system. This archive database could reside on a low cost second tire storage system. The main advantage in this structure is that you have the ability to a get to your archive data from within the application itself, in other words you do not need any third party
tools to access your data.
One of the disadvantages of this approach is that, this would result in atleast one more database to maintain apart from the production system despite of the fact that this database would be on a lost cost second tier storage.

Limitations.
One of the main limitations that most of these archiving solutions had that, although most of them were able to archive the data from your application they failed to do a database reorg.
After the archiving cycle the database would need to be reorged manually to reclaim the space and see an increase in the system performance.
Also in the even of applications upgrade when the structure of your database tables changed the process of propagating the changes in the archives, though available was a complex process.