We have a requirement to build an Archival Solution for an Enterprise Application with database hosted on Oracle 11g.
Problem Statement :-
1. The Primary Database which currently contains TERABYTES worth of production data
2. This Database contains reference data and transactional data
3. Every Day there is a huge growth ( several GB ) in transcational data volume of production database
4. This impacts the usage of end users as pages load slowly with poor response times
5. This also impacts the reporting capabilties becuase they need to scan whole database
6. The transactional data has referrential intergity upto 5 levels deep like order --> product --> product detail --> item --> item_detail
7. All business operationsa re being worked upon past 24 months of transcational data.
Solution Envisoned :-
1. Create an Archive Database which would be clone of Primary . This Archive DB would be on different Oracle Node on a cheaper storage space
2. Create a batch job to move the data from primary to archive
3. The inactive transactional data on need basis depending on user request on Application might need to be pulled back to Online Primary database.
But this operation need not be synchronous and can take upto 12 hrs.
4. For reference data Views would be created between two databases using alias.
How can the Archival Batch job be created ? Will have to use custom oracle packages ?
Stored Procedures ? Will Oracle Flash Back Arhcive help ?