Splitting database into 2. Any ideas?

[charity]

Friends, I need some ideas. Below is my scenario
I have a very large database(about 1.5 terabytes) that has a lot of historical data that goes as far back as 9 years. It's partitioned based on months. The only partition that is always active with very high insert,update,delete and select operations is the current month's partition.
The old partitions does not get updated,deleted or inserted. The old partitions and their indexes are in read only tablespaces.

Among other factors, It's like hell when once in a while I get requests to scan through the old data doing varoius ad-hoc requests.
Backing up is also begining to be a problem.

With the way the non-technical business managers keep adding non-application related objects/data to this database (because they simply think oracle can handle everything and they refuse to listen to technical implications of trying to mix all of these together), I can see performance going to hell in the near future

I'm thinking about breaking this database into two parts. Leave the old partitioned data in a separate database and the heavy insert,update, delete, select stuff goes into a separate database. With this idea, the only activity that happens in the old partition database is for querries and one-time loads. While the only activity that would occur in the other database is daily activity of high insert,delete,update and select activity on the current months stuff.

Can any one give me some ideas about the best way to break this database into two (one for current activity and the other for the old data) in order to suit the above described criteria? I'm just brain storming right now so any and all ideas are welcomed.

Thanks,
Charity

[gbrabham]

I have done something similar with a client of mine. I created an
"history" database and left the "production" database mainly with
current activity. That serves a couple of purposes:

1. An adhoc user queries don't affect the performance of the current database.

2. Backup times go down, since the "History" database was not updated, it didn't need to be backed up every day.

The only real issue other than additional maint. (maybe), user
security, connectivity issues ... is that you need to ensure that all foreign key and related data is carried in the "history" database.
In some instances, it is not worth trying to split databases apart if the tables are intermingled...

Bottom line, I like the idea. It worked well for my client...

Gregg

[slimdave]

Have you considered placing the old partitions in a recycle cache, and the new one in the keep cache?

[tamilselvan]

Splitting a huge DB into 2 is a good idea - one for history, another for current.
However, when the user needs to "select" from both the databases, then you will have to create views.
Since views' perf is very bad, then users will say "keep only one database" .

Study the pros and cons before you take up a decision.

[abhaysk]

We have 1TB data & all tables are NON partioned, we have very good perormance...it depends on how you manage.

As per DOCS, oracle can handle data upto 10TB efficiently ( if designed properly )....

I would suggest, take SLIMDAVE's suggestion.


Ok IF,

It's partitioned based on months. The only partition that is always active with very high insert,update,delete and select operations is the current month's partition.

business users dont want to see the OLD data ( Or rather they use ocassionally )...then you can do some thing like this...

Make the present DB as History DB...
Create New DB with objects in Histroy DB...

Populate data into the NEW DB, after a month when its no longer required move it to History DB.

Abhay.