Hi All,

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.

Question :-

How can the Archival Batch job be created ? Will have to use custom oracle packages ?
Stored Procedures ? Will Oracle Flash Back Arhcive help ?

Hi Sunil,

With data volumes the insurance industry has, you definitely want to hire an expert to build you a roadmap.

There are many perils, and you don't want to fail, all for the lack of a few hours of architectural advice.

Call 800-766-1884 or e-mail here:

http://www.dba-oracle.com/data_warehouse.htm

**************************************************

>> How can the Archival Batch job be created ?

You need an experienced DBA to do this, there are a dozen methods. Get the books:

"Oracle Shell Scripting" for examples:

http://www.rampant-books.com/book_0701_shell_scripting.htm

and "Oracle Job Scheduling" to see job chaining and scheduling:

http://www.rampant-books.com/book_0501_scheduling.htm

***********************************************
>> Will have to use custom oracle packages ?

You could use OWB but it is VERY hard to learn.

I would hire an expert who has done this before, just a few hours to advise you on the right approach:

Call 800-766-1884 or e-mail here:

http://www.dba-oracle.com/data_warehouse.htm

************************************************
>> Stored Procedures ? Will Oracle Flash Back Arhcive help ?

It depends on many other factors

But yes, stored procedures could work, but some stuff is done at the OS level, you you would want shell scripts also.

Thanks a lot Mr. Burleson appreciate it.

Is Oracle Data Partitioning also an option to this issue ?
Can you suggest your thoughts on the same ?
What would be impact on an application written in Java with JSP pages
to connect to this oracle database with archive data being partioned.

Hi Sunil,

>> Is Oracle Data Partitioning also an option to this issue ? Can you suggest your thoughts on the same ?

It's absolutly required for the of app.


*****************************************************************
>> What would be impact on an application written in Java with JSP pages
to connect to this oracle database with archive data being partioned.

None.

Like I mentioned before, have me join your group for a memtorimg to establish the atchitectutue.

Just call 800-760-1884.

Don don't need a nebie designing this!