"Larry Lowe" <llowejr@usa.ne twrote in message
news:ad17577.03 08140714.1b14d7 78@posting.goog le.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
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
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