Re: Different databases or different schemas?
Date: Tue, 24 Jun 2008 21:19:07 GMT
Message-ID: <fxd8k.63$1o6.23@edtnps83>
Mark D Powell 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
>
> 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 Received on Tue Jun 24 2008 - 16:19:07 CDT