RE: VPD vs multiple schems vs multiple instance

Tom,

        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)
[mailto:Thomas.Mercadante_at_labor.state.ny.us]
Sent: Thursday, July 07, 2005 9:58 AM
To: Goulet, Dick; oracledba.williams_at_gmail.com; dubey.sandeep_at_gmail.com Cc: oracle-l_at_freelists.org
Subject: RE: VPD vs multiple schems vs multiple instance

Dick,

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.

tom

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Goulet, Dick
Sent: Thursday, July 07, 2005 9:44 AM
To: oracledba.williams_at_gmail.com; dubey.sandeep_at_gmail.com Cc: oracle-l_at_freelists.org
Subject: RE: VPD vs multiple schems vs multiple instance

Dennis,

        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 problems.

        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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dennis Williams
Sent: Wednesday, July 06, 2005 5:50 PM
To: dubey.sandeep_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: VPD vs multiple schems vs multiple instance

Sandeep

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 client.
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 independently.
There are advantages to a single instance from a shared pool perspective. However, in my experience the frustrations outweigh that gain.

Dennis Williams

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l