Re: Different databases or different schemas?

On Jun 24, 11:51 pm, alessandro.ross..._at_gmail.com wrote:
> On 25 Giu, 03:15, Chuck Whealton <chuck_wheal..._at_yahoo.com> wrote:
>
>
>
>
>
> > On Jun 24, 11:55 am, alessandro.ross..._at_gmail.com wrote:
>
> > > Hi,
> > > Even if I'm using Oracle since some year, I have some (many?) doubts
> > > about the use of differents schema instead of different DBs.
> > > Generally to manage a test environment, a development enviroment and a
> > > production environment, I create 3 different databases on the same
> > > oracle instance (MYDB_TEST, MYDB_PROD, MYDB_DEV). But I was thinking
> > > if to create only 1 database and 3 different schemas for test,
> > > developing and production on the same database, could be a better
> > > solution.
>
> > > Does anybody can suggests me which is the best solution and which are
> > > the advantages (and disadvantages) of the two choices?
>
> > > Thank you in advance
> > > Alessandro Rossi
>
> > Alessandro:
>
> > There's a number of legitimate ways to go about this.
>
> > At one clients facility, we would have separate systems with their own
> > individual Oracle installations and databases on each one.  Doing it
> > this way, we we're also able to test out operating system patch sets
> > with no repercussions to production.
>
> > That particular client is also in a regulated industry so they usually
> > had separate production, pre-production, and development environments.
>
> > I'd probably think about having at least two separate systems - be
> > they physical or virtual.  That way, you can test both operating
> > system and Oracle updates without affecting production.  Just my own
> > take on it.
>
> > Charles R. Whealton
> > Charles Whealton @ pleasedontspam.com
>
> Thank you for your answers, but maybe I need to read some oracle
> tutorials :)
> Usually I install oracle server on a server, and using Database
> Configuration Assistant I create 3 different databases, so the server
> (and the oracle installation) is always the same; I don't create 3
> different separated servers, and so I have only 1 operating system and
> only 1 oracle (with 3 databases).
> My question is about the convenience to create 3 schemas on 1
> database, or 3 database on the same oracle, considering also that 3
> different databases have 3 oracle.exe processes and every database has
> its services (scheduler, console....) so it uses more resources, is it
> right? So when you should use different schemas instead of different
> databases?

When you don't have the physical resources to separate out your production and testing. It goes beyond mere performance impacts - when you blow off the production data, you will be shown the door of unemployment. Not to say that you can't do that by confusing two xwindows... but the more you can protect yourself from your own and others fumblefingers, the happier everyone will be. That becomes more important than being able to let all the resources be hogged by production, or development, or DSS.

As in every other DBA function, it depends.

>
> Thank you, and sorry if my question could be a faq....
> Alessandro Rossi

Not so much a faq as a complex decision with much room for judgement calls. Some people say flatly one db per machine, but they tend to have other machines available for development including their own laptops and desktops, and often the budget to have identical test and production machines.

jg

--
@home.com is bogus.
Hokum and handwaving:  http://it.slashdot.org/article.pl?no_d2=1&sid=08/06/24/2345223