RE: VPD vs multiple schems vs multiple instance


        Well your quite right, we agree to disagree.  My problem with
the separate instance idea is that we did that for our CIM group and it
is causing a pile of problems, more than ever anticipated. We're in the
middle of migrating back to what I described and that too is being very
painful.  And as for the "pay me to fix it" statement, we're using an
externally hosted CRM package and those were the terms that we got from
them, so their not uncommon.

-----Original Message-----
From: Mercadante, Thomas F (LABOR)
Sent: Thursday, July 07, 2005 9:58 AM
To: Goulet, Dick; oracledba.williams@xxxxxxxxx; dubey.sandeep@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: VPD vs multiple schems vs multiple instance


Everything you and Dennis say are predicated by trade-offs.  Both
approaches have strengths and weaknesses and it really comes down to
both company and personal management.

I prefer the separate instances.  To me it's much cleaner and easier to
sort out.  Security requests are applied to one instance (let Tom see
this stuff).  Downtime is the same across both approaches, although if
you have separate instances, one client may be down for a specific
reason (say a truly ugly batch process) while the other two clients are
not affected.  Same goes for a database restore.  That was my
suggestion.  I like your hard-line response (pay me to fix it) but it
leaves me with little other options (and we know that company politics
sometimes makes these decisions).

I would have one shared Oracle home.  Migration is applied one instance
at a time.  Simple but effective.  Oracle patch sets are another animal.
No choice but to evaluate and apply them to all instances where the risk
is high.

So we disagree, but I think on personal principles.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Goulet, Dick
Sent: Thursday, July 07, 2005 9:44 AM
To: oracledba.williams@xxxxxxxxx; dubey.sandeep@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: VPD vs multiple schems vs multiple instance


        May I disagree?

        1) Easier Security, yes if you like maintaining security in
multiple instances.  What a pain in the @$$.
        2) Downtime: Yeah, sorta, unless the server crashes or needs an
OS patch in which case your still in trouble. Are you recommending
separate servers as well?
        3) Patches: Yeah again sorta, if you have separate Oracle_Homes
for each instance otherwise your doubly screwed as you've multiple
instances to upgrade.  If you have multiple homes then patching is a
real pain because of the number of times you have to do the patching.
And having multiple versions/patch levels is a real confusing matter,
especially since many clients will take the "if it ain't broke don't fix
it" attitude.  My biggest headache, security patches because things
aren't broke.
        4) Move a client to a new server: So what, move the entire
system.  If one client needs a separate server then you've got bigger

        Oracle patch sets are cumulative in nature.  If one client wants
a specific patch, which should NOT be their choice in a hosted
environment they may well have to accept other patches that are not
desired by their management.  Face it if your hosting the application
patching is for your benefit, not the client's.  They just want the
application to work and work in a secure and timely manner.  Someone
else stated that one client may want the database rolled back.  WHY??
Their using the application, not managing it.  If they messed up some of
their data they can fix it themselves or pay your company to fix it for
them.  Rolling back the database is not an option.

        My druthers, one instance, possibly rac'ed and mirrored, with
one owning schema, portioned tables.  Several users accessing the common
tables with their userid being part of the data and primary key, and
each user in a separate partition.  If you really need to allow adhoc
query access use a set of view definitions, similar to Oracle's
user_<view> definitions.   It's a lot easier and simpler to create the
views and instead of triggers for each view than to build all of those
VPD triggers.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Dennis Williams
Sent: Wednesday, July 06, 2005 5:50 PM
To: dubey.sandeep@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: VPD vs multiple schems vs multiple instance


I would advocate separate instances for the following reasons:
 1. Easier security.
 2. When downtime is required, easier to meet each client's needs.
 3. When Oracle needs patched or upgraded, easier to deal with each
 4. If you decide to move a client to another server, easily done.

For example, one client insists an Oracle patch be applied
immediately. Another client insists that they can't possibly accept
the downtime required. With a single instance, you are forced to
choose between clients. With separate instances you can treat clients
There are advantages to a single instance from a shared pool
perspective. However, in my experience the frustrations outweigh that

Dennis Williams

Other related posts: