Recommendation on more instance or more users?

[CaoCao]

Hi,

I'm consolidating many production databases from multiple machines into one big Sun machine. Is it recommended to have 2 database users in one instance or create separate instance for different database users? Separate instance require more physical RAM. The load and DB access are considered low to average. Or what should be the best practice?

Thank you.

[bolero]

It depends on the design of your application.Are the two database work independent???Check with your application design.

Whatz the concurent users in each database.

[slimdave]

Unless there are really compelling reasons to do otherwise, i would stick to a single database/instance.

Good compelling reasons might be ...
* One app is OLTP, the other is decision support
* the two apps aren't certified to run on the same oracle version.

By default I would assume that you can put them into a single database, and then look for any reasons why this would be a really bad idea, rather than the other way around

[CaoCao]

Yes both database users are accessed by different application and they are independent from each other. I thought of saving resources by using 1 instance to create multiple users. Though I understood that the SGA needs to be re-rune. Each user will have less than 200 concurrent connections.

[abhaysk]

Originally posted by slimdave
Good compelling reasons might be ...
* One app is OLTP, the other is decision support

Cant we have mixed kinda env set up which will suffice both requirements with ofcourse some trade off?

[marist89]

Tom Kyte regularly advocates one instance per server. One example.

I kind of agree, however, there are certain circumstances that warrent seperate instances.

o If the applications might be seperated onto two distinct machines sometime in the future.
o If the two applications might need different versions of oracle.
o The security policy is different for each application (one grants ALL to public, one is done correctly).

[Mr.Hanky]

If it is two separate applications then I would go for two instances, UNLESS there will be too much data redundancy. From a maintenance point of view the two applications will probably have different downtimes and overall scheduling maintenance will be more complicated. Not to mention if you need to bounce the instance for one application, the other will have to suffer and vice versa.

So a few questions would be…

Will there be an overwhelming amount of data redundancy?
Do the apps have very different schedules?
What is the capitol of Malaysia?


MH

[TimHall]

Hi.

After being a devout one-instance-per-application DBA for years I've recently taken to shoving multiple application into a single instance where possible.

I now have 3 instances (DEV, TEST, PROD) on my 2 node RAC. Each instance holds schemas for all applications in their respective parts of the lifecycle. I've not been brave enough to consolidate the different parts of the lifecycle, but I'm now saving loads of disk space, reducing my memory and CPU usage and having very few administration issues. In addition adding the multitude of security patches is loads easier due to the reduction in instances.

I agree with SlimDave, use a single instance unless something happens to make you think different.

Cheers

[slimdave]

I wouldn't consolidate test,dev and prod at all -- i think you're right to keep those separate. You never know that you aren't going to have to change init parameters or version for dev, then test, then prod -- you don't want to do them all at once.

[Mr.Hanky]

You also have to keep in mind what is best for "The Business" ,not just for Oracle and or the DBA.

MH