471,075 Members | 809 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

Re: Instance question

"Larry Lowe" <ll*****@usa.netwrote in message
news:ad*************************@posting.google.co m...
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?

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

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.

Jun 27 '08 #1
0 1061

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

30 posts views Thread by Joost Ronkes Agerbeek | last post: by
5 posts views Thread by allison | last post: by
6 posts views Thread by Dmitry Karneyev | last post: by
9 posts views Thread by manstey | last post: by
12 posts views Thread by Analizer1 | last post: by
19 posts views Thread by =?Utf-8?B?WWFua2VlIEltcGVyaWFsaXN0IERvZw==?= | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.