Re: Instance question

"Larry Lowe" <llowejr_at_usa.net> wrote in message news:ad17577.0308140714.1b14d778_at_posting.google.com...
> I have a question about weather it is better to have multiple
> instances on a system or multiple users in the database? Say we have
> a development, testing and training copies of the schema. Is it
> better to have 3 instances or one instance and 3 users?
>
> Thanks
> Larry

Hi Larry. Some responses [which we will ignore] aren't very helpful. I'll try to do a little better.

The first point to note is that each Oracle instance requires a reasonable amount of system resouces as usually a considerable amount of memory is allocated to the instance and on UNIX there is typically half a dozen background processes hanging around just waiting to do your bidding. For this reason you would normally expect to keep the number of instances to a minimum.

The second point to note is that each extra instance is going to increase the administration work load for the DBA and system admionistrators. We are not just talking about a directory or folder. Each instance must be started, backed up and checked to ensure all is well. The more instances you have the more work you have. For this reason also, you would normally expect to keep the number of instances to a minimum.

With that said one has to address the issues of isolating developers from the production and acceptance databases [I'm using database interchangeably with instance]. This tends to demand separate databases for these at least. So what you have left is a desire to keep the number of databases (and thus instances) to a minimum and most likely a requirement to support a number of different environments. Typically it is desirable to isolate some developers from other developers. Depending on the way the database side of your applications are put together it may be quite simple to have multiple copies of the app installed in a single database. If your app is contained in a single schema and does not reference much outside it then this is likely to be the case. On the other hand, if your app is spread across half a dozen schemas and references all sorts of external things then you are going to have to work a lot harder to support multiple implementations of your app in the one database. Unfortunately, Oracle does not support any concept of specifying a catalog. Oracle supports a single global catalog and all schema names must be unique within this catalog. What this means is that if you have DDL scripts to create your applications schemas and your schemas reference objects in other schemas you will end up having to use a combination of [hopefully private] synonyms and a liberal sprinkling of lexical substitution variables in your DDL scripts. These lexical substitution variables [using the SQL*Plus terminology] allow you to specify the schema names when you build your schemas.

If you are familiar whith Oracle schemas then this should be pretty evident. If not, then you have a lot of reading to do. If you can contain your application to a single schema, things will be pretty straight forward.

As a final thought, I've seen some people recommending that each developer have their own personal database [typically on the own workstation]. This does avoid most of the issues above except for these two:

1. Every developer has to become a kind of mini DBA. I'm not sure what I think about this concept. I'm sure many people all have different opinions on this one. Ultimately, its success would depend on the level of expertise of your developers.
2. It most likely exacerbates the administration problem mentioned in point two above. Do your maths, If you have 15 developers each with their own Oracle database and you want to apply the latest upgrade....

I hope this is some help.

Cheers Received on Fri Aug 15 2003 - 12:34:10 CEST