Different databases or different schemas?
alessandr...@gmail.com
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
Mark D Powell
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 --
GS
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
Chuck Whealton
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
alessandr...@gmail.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
joel garry
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