Re: Instance vs Schema pro/cons

Marc Eggenberger <marc.eggenberger_removeME_at_itc.alstom.com> wrote in message news:<MPG.1a2ee28e17cdcf229896dc_at_iww.cacti.ch.alstom.com>...
> Hi there.
>
> I need some opinions ...
>
> Its about if its better to have one oracle instace and several
> applications in schemas or if I can be better to have several instances
> on one server.
>
> I would normally go the one instance / everything else in schemas way
> because of the overhead (having multiple sga's etc)
>
> Multiple instances are more independant. So if you need different
> settings (that are instance wide). Or maybe to seperate customers more.
>
> Do you have other pros and cons?

I have actually done it both ways. One of the main criteria's I use when deciding is the criticality and availability requirements of the databases. I have some applications that are true 24x7 applications. I never share databases between applications when one is 24x7 and the other is 7am-7pm Monday-Saturday. Additionally, if I have a mission critical OLTP database where performance is a must, I wouldn't combine that database with another database.

Where I do share a single database among applications is when the amount of data for each application is relatively small (a swag is under 50 Gb), the maintenance windows for the applications are similar, when they can predictably be upgraded to the latest and greatest Oracle version at the same time (3rd party apps sometimes throw a monkey wrench into this one), whether the databases are internet facing or not, and when the backup/recovery, High Availability and DR requirements fit well together.

I have a couple of consolidated databases that support 10 -15 applications each. I must say, that we have one application that was consolidated, but has a very bad habit of corrupting all of it's user information. This application requires a restore from a backup that is two or three days old a few times a year. Unfortunately, if it's part of a 50 Gb consolidated database where the other application's data is jsut find, we run into some restore issues. It's a pretty straight forward process, after the first 2AM call that is.

In any case, wether to share a database among several applications must be determined on a case by case basis. Hopefully I given you some additional criteria to think about and have managed to answer your question somewhere in my ramblings.

HTH,
Patrick Received on Wed Nov 26 2003 - 13:02:57 CST