Yes!
Dan, August 03, 2002 - 12:53 pm UTC
Thanks a response that speaks to my slothful nature....
I know my DBA workload probably doubles if the number of instances doubles... but my workload is essentially unaffected if the number of objects in one database doubles. So I'm all for multiple apps in a single database. Problem is I get a lot of resistance from our contract developers (who lean on the project managers) who want a database all to themselves for each system (plus test databases etc...)
Now, I'm all for keeping developement systems seperate from production (big test dataloads etc. might impact production instance). But I've never understood the rational for a seperate db for each little app.
This article give me a bit more ammo - thanks!
dl, August 09, 2004 - 7:02 am UTC
This is an interesting point for me since I work for a company where they have got pretty much an instance per application. It is a nightmare for upgrades and backpups for us the dbas.
I am trying to convince them that we just need one instance per machine. Some questions that have arisen though are:
1) What about the shared pool, won't objects/sql that normally stays in the shared pool for a certain time, get aged out more quickly with multiple apps using the instance??
2) How do you best deal with backup and recovery? Have each app use its own set of tablespaces and just backup/recover the sets as required? eg we currently have an app which is loaded once a month and cold backed up at that time, therefore is in noarchivelog mode, i guess we'd need to change this to archivelog mode to be inline with the other apps in one instance.
3) Is there really no good reason that you won't need to bounce an instance?? I'm trying to think of a scenario but can't! I'm wondering about init parameters you may want to change, but i can't think of any which one app might want to have without the others using it?
4) One app manager seems to be from a sqlserver background where i believe that a "database" has a different concept.
Am i right in saying that we could equate a new oracle schema to a new sqlserver database for comparison.
eg so when he says he wants his app in its own database i can say you need a new schema?
Thanks for your help, any information that you can give to help me convince colleages that one instance is the way to go will be useful.
August 09, 2004 - 8:40 am UTC
1) what about that shared pool -- my question is "what about that libary cache", you have major portions of it duplicated over and over and over in each instance.
With all of the free memory you'll get back by consolidation, you can increase the sizes of anything that might need it!
2) exactly -- use tablespaces as an administrative tool. You backup the DATABASE, but you have tablespace point in time recovery (TSPITR) if you ever need it.
All apps would be in archivelog mode, you would not be changing anything in as much as consolidating them.
3) pretty much. and you do the bounce during scheduled downtime -- you don't just wake up on wednesday morning and say "you know what, we need to change this", you've thought about this in a production environment
4) in Oracle an SCHEMA roughly equates to a sqlserver DATABASE yes. when they ask for a "database" you give them a "schema"
very useful so far, but standby?
dxl, August 20, 2004 - 7:27 am UTC
Tom
I have been reading with great interest about the different arguements for having one instance per machine for your live databases, and i agree 100% with this arguement. However i would like to know your thoughts on the following setup:
We currently have 2 fairly large HP machines on which we have about 10 instances build between them.
So i would like to reorganise this infrastructure because as you can imagine the present setup is fairly hellish to manage and upgrade etc.
In the current setup we have the instances split between the 2 machines so that the load is balanced between the 2 machines.
I thought i would try to go with 1 instance per machine. The instance will contain all the production live schemas with separate tablespaces per application which will have only scheduled downtime.
What i would like to know is:
we want to set up standby databases for all of these current instances (soon to be schemas). But we are restricted during the day on bandwidth between the 2 machines, so instead of hot standby they will be "luke warm" -- this means that the standby databases will be closed most of the time and only brought up each morning to apply the archivelogs from the previous day which were copied overnight during low network traffic.
But i'm not sure how to structure this with these 2 machines :
1) i could have all the live schemas in an instance on machine1 which will then also exist on machine2 as a standby instance. Whilst i'm sure this would work ok it does mean because of the way we implement the standy ie the database is down and only brought up each morning to apply the archivelogs, this means that a fairly large machine is primarily sitting there not doing a lot except getting ready to spring into action if required. Machine1 is taking all the load, all the network traffic etc.
So to balance the load more and let both machines take some of the work how about
2) instead of 1 instance per machine i have 2 or even 3. Then on one of these instances on machine1 i could install some apps schemas and have their standby instance on the other machine2, then in the other instance on machine1 i would have the standby instance for the live instance on machine2. This way both machines would be sharing the load and if i lost one machine i would still have an instance containing all my apps schemas.
Is this an arguement for more than 1 instance on a machine??
What do you think about either of these approaches? which one would you choose (if you had to pick!)??
August 20, 2004 - 11:24 am UTC
seems like you would just want to use a SAN here or some other sharable disk solution? if machine one fails, machine 2 remounts machine 1's file system and just do crash recovery. seems that would be faster?
say you crashed at 3pm and wanted to failover. you would have to:
a) get the archives created from 6am to 3pm from the failed machine restored to the good machine
b) apply them all
or
a) mount the file systems and type "startup"
or am I missing something?
.
dxl, August 20, 2004 - 11:41 am UTC
Well that might be the best way but how does my previous 2 solutions add up given that we cannot use a shareable disk and the 2 HP servers are in 2 different geographic locations and we don't have the necessary bandwidth to apply the archivelogs during the day (we may do in the future)? please can you comment on the 2 solutions i provided previously or were they both not the way to go?
August 21, 2004 - 10:44 am UTC
if you don't have the archives moved during the day -- to what end is this standby????????????
think about it. at 3pm machine 1 goes poof. now what? where are the archives from 6am to 3pm? do you just write them off? how do you get them? what do you do?
.
dxl, August 23, 2004 - 4:06 am UTC
The archives are copied throughout the day to another machine on the same site via a dedicated connection. We have the users approval that it is acceptable we could even lose a few hours work during the day. (If this wasn't acceptable they would pay for more bandwidth). Therefore it is a kind of standby although i admit not a particularly good one but it serves their requirements.
If we lose machine 1 at 4pm then we can get back most of the archive logs from that day.
August 23, 2004 - 8:10 am UTC
so, I would just restore a backup to the "failover site". doesn't seem like you have the infrastructure nor the need for a standby. seems like traditional "opps, system gone, restore backup, start grabbing archives (in the time it takes to transfer the archives over this network would be the bottleneck, not restoring a backup)
you don't seem to have the infrastructure one would need/want to facilitate "standby". it would just be a pre-restored backup for all intents and purposes.
.
dxl, August 23, 2004 - 9:35 am UTC
ok so what if we did have proper hot standby??
what about my original question, should i have one large instance containing all the schemas on machine1 with a hot standby on machine2? meaning that most of the time machine 2 will not really be doing a lot except for applying logs and waiting for the day that machine1 disappears.
or
split the apps between 2 live instances:
one on machine 1 and one on machine2 at a different location (this can be decided by the location of the app servers ie put in same location as db servers to reduce network load.)
with another 2 hot standby intances
ie standby for instance 1 on machine2
and standby for instance2 on machine 1
this way if we lose (and i mean completely lose) machine 1 we lose the instance 2 standby and the live instance 1 , we then failover to the standby instance 1 on machine2 and we already have the live instance 2 on machine2.
This way machine 1 and machine 2 are splitting the workload instead of one being in standby mode all of the time. Or won't it make ANY difference whatsoever? will the workload just be the same always?
August 23, 2004 - 10:26 am UTC
for failover, if I were serious about it, i would have a machine sized right sitting there waiting for the day that hopefully never comes.
Perhaps the main machine is a 16 cpu machine, the standby is a 16 cpu with 14 of them turned off -- until we need them.
since machine1 and machine2 each need to be able to run the load of everything (given a failover situation) just put it all on machine1 and have machine2 be the failover. that way, machine1 is just big enough day to day to run it's work -- not it's work PLUS standby stuff.
I'd keep it "simple", single machine with a failover site is "simple"
one instance
Riyaz, September 11, 2004 - 1:17 am UTC
- In our server only one instacne has been created and different schemas for different applications (all inhouse - All applications is reasonably big ones and having data about 20 GB in total).
- Now we bought one moderate 3rd party application, which as pre-requisties of vendor, seperate instance is required.
- Hence one more instance has to be created.
- How to create and proceed. Please guide. (I am totally in dark about 2 instances in database)
September 11, 2004 - 8:20 am UTC
you do not create two instanced in a database.
Here the 3rd party vendor is saying "create me my own database".
Your DBA should be able to create another database for you?
instance
Riyaz, September 13, 2004 - 2:22 am UTC
Thanks. Informed DBA.
I would like to know when multiple instances are required in real situation. (in single server)
(since our DBA's is still following Black-Box appprox as you said your book)
September 13, 2004 - 8:01 am UTC
On a single server, a single "host", a single computer -- the optimal number of Oracle instances is ONE.
The only time to deviate from that is with 3rd party software -- and even then, I'd be really tempted to buy a box just for them (as they will dictate the version/patch of the database and perhaps even the OS)
A reader, September 21, 2004 - 10:32 am UTC
Tom,
How to see how many instances are running?
September 21, 2004 - 11:06 am UTC
on unix -- i typically:
[tkyte@xtkyte-pc tkyte]$ ps -auxww | grep pmon
ora9ir1 11704 0.0 0.1 877688 3560 ? S Sep17 0:31 ora_pmon_ora9ir1
ora9ir2 13198 0.0 0.4 324548 9656 ? S Sep17 0:30 ora_pmon_ora9ir2
ora10g 13235 0.0 0.5 245788 11328 ? S Sep17 0:39 ora_pmon_ora10g
tkyte 3188 0.0 0.0 3676 656 pts/1 S 10:48 0:00 grep pmon
[tkyte@xtkyte-pc tkyte]$
shows that on my TEST MACHINE (my desktop) I have three instances running currently.
Sharing an instance vs using separate instances
Raj Kathamuthu, March 10, 2005 - 11:29 am UTC
Tom,
In our DW shop, we are running two separate database instances - one for hosting the datamarts and the other for staging the data from source systems. Both are hosted in a 2 node Veritas HA cluster envirionment. In normal circumstances, NODE1 will be hosting the datamart database instance and NODE2 will be running staging database instance. There may be a situation that we would have to run both instances on the same node, when we have problems on one of the nodes.
My DBA colleague is suggesting to merge both databases into one. He actually quoted this thread to support his argument. I'm not quite convinced this ONE INSTANCE motto would be applicable to our situation. We run the ETL to throughout the day to stage the data and transform the staged data to the datamart during the off business hours.
Any thoughts or comments ?
Thanks.
-Raj Kathamuthu
March 10, 2005 - 7:17 pm UTC
well, you have 2 servers, 2 databases would not be out of the question.
if you had a single server, one database.
if you have two independent machines, two database is ok.
So, I'm not saying one or two in this case -- but what is most manageable and easiest for you. In order to have a single database in the above 2 node case, you'd have to be using RAC.
1 machine to 1 db and 1 db to 1 schema or multiple schemas
SFH, July 14, 2010 - 4:01 pm UTC
Hi Tom,
This thread is really great, enjoying reading it.
I've a situation, where "the Manager" is insisting to have just ONE schema in the database and dump all the data, in couple of tables, where as I'm insisting to have different schemas, accordingly, according to their attributes and properties.
Is this correct approach or not, where as all the schema have different attributes.
Please advice/suggest, what should be the best practice.
Thanks
July 19, 2010 - 12:57 pm UTC
I don't know what you mean by attributes. When I hear attributes, I think "columns". I don't think that is what you mean.
Use schemas as a book-keeping, logical design thing.
Just like you would not put every subroutine into a single source code file - you would not want every table in a single schema.
There are big security implications as well, if you put everything into a single schema, that single schema can see everything, use schemas to segregate objects by "type" so that you can secure each collection of tables.