Re: Multiple Instances of ORACLE

Gerald M. Lewis wrote:
>
> We are in the process of developing an application that provides
> Multi-Company support.
>
> The technical issue that we face is whether to include all records for
> all companies in a single instance of ORACLE or to place each
> companies database in their own instance of ORACLE.
>
> For example, if we have a single instance of ORACLE, our customer
> table would carry a company identifier as part of the primary key.
> This way if we wanted to view company A's customers, the select
> statement would include a where clause that would select the customers
> for company A.
>
> Alternatively, each company could have a separate instance of ORACLE
> running that would keep the databases separate. The problem is that
> the user's may need to access data from several instances
> simulatenously.
>
> In our environment, if each database had its own instance, we would
> have 22 instances running. Does this make sense or should we
> consolidate all of the data into a single instance and use a company
> identifier as the leading edge of the primary key in all tables?
>
> Any help or past experiences with this type of issue would be greatly
> appreciated.
>
> Thanks.
> Jerry

Hi Jerry,
Tough question, great for Oracle DBA exam...

I've worked with a server running 23 instances at the same time. It was a development environment with an average of 83 users accessing this server concurrently. The biggest draw back I encountered with multiple instances were with disk and memory. Each instances requires its own system, rbs, tools, user, log, control and data files; plus each requires its share of memory for SGA. You can thus imagine how small the SGAs were on this server (~ 7-10M each). Disk is cheap so we just added what we needed noting the 2G limit of our Unix and Oracle.

You could create few instances by creating a LOV (list-of-values) database with static LOV tables that is accessible by all other instances. And then maybe group your various companies into categories of 3-5 companies each; thus reducing instances down to 5-8. You will need to pay extra attention to tuning the databases to work with each other.

One benifit with multiple instances that I found is that if one crashes, or needs to be down for a bit, it does not affect the other instances to any degree. I've had cases where a disk crashes or I encounter a 'bad block' error; the users can thus use the other databases will I recover the damage instance.

I too thought of combining all 23 into one instance for the simplicity of maintaining key data files and maintaining users, yet having > 130 possible users siting around while the database was down was not an option (consider cost & wages).

Hope this helps some, you did ask for past experiences. Greg... Received on Mon May 05 1997 - 00:00:00 CDT