Prev: Connecting to oracle (via JDBC) from unix through a firewall
Next: DECODE
From: alessandro.rossi74 on 24 Jun 2008 11:55
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
From: Mark D Powell on 24 Jun 2008 13:33
On Jun 24, 11:55 am, alessandro.ross...(a)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

An Oracle instance supports only one Oracle database at a time, ever.

If you want three databases: test, development, and production you
will have three instances.

Using separate databases for test, development, and production allows
separation of resources (disk, server) so that test and development do
not interfere with the production database performance. It also
allows the use of separate Oracle Homes so that you can test a new
release using the test database before bringing production up on a new
release.

HTH -- Mark D Powell --
From: GS on 24 Jun 2008 17:19
Mark D Powell wrote:
> On Jun 24, 11:55 am, alessandro.ross...(a)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
>
> An Oracle instance supports only one Oracle database at a time, ever.
>
> If you want three databases: test, development, and production you
> will have three instances.
>
> Using separate databases for test, development, and production allows
> separation of resources (disk, server) so that test and development do
> not interfere with the production database performance. It also
> allows the use of separate Oracle Homes so that you can test a new
> release using the test database before bringing production up on a new
> release.
>
> HTH -- Mark D Powell --

I agree about the different versions of the same database being on
different servers, in rare cases where the prod db is small and not too
resource hungry I have put test instances/databases on the same server
as production, but I usually don't (vm's are great for this)

That being said though, I am looking at consolidating more databases per
machine where I can, one reason being to keep under the processor limit
for my number of named users, Oracles new pricing if I need to add more
named users will also be another. This has pluses and minuses, patching
means testing on both instances etc.. but then the patch is only
applied to one machine etc. Also when you need to bounce the server then
you have more users to contact etc.

As far as different schema's vs different databases go, I have thought
about this with some of our smaller "in house" database applications,
where I think I could easily have several apps run against one database
instance, with each app having its own schema(s), after all this is how
MSSQL more or less works with it's "databases". In fact as I migrate
some db's to 10G I am going to take a serious look at testing this
scenario. IIRC one of Tom K's talks I attended talked about this very
thing, that is combining multiple schema's under one database rather
than having all these separate databases.

my $.02
From: Chuck Whealton on 24 Jun 2008 21:15
On Jun 24, 11:55 am, alessandro.ross...(a)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
From: alessandro.rossi74 on 25 Jun 2008 02:51
On 25 Giu, 03:15, Chuck Whealton <chuck_wheal...(a)yahoo.com> wrote:
> On Jun 24, 11:55 am, alessandro.ross...(a)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?

Thank you, and sorry if my question could be a faq....
Alessandro Rossi
 |  Next  |  Last
Pages: 1 2
Prev: Connecting to oracle (via JDBC) from unix through a firewall
Next: DECODE