Re: 100 instances on same server !!!

[Tanel Poder]

Hi!   Disk IO: Providing, that idle Oracle accesses all controlfiles every three seconds, then with 100 instances/databases, you'll have about 33 random IOs per second. Can your disks. If you got three controlfiles per database, then it means 100 IOs/sec, which gives 10ms per access, otherwise you'll overload your IO. And this is an idle database. In reality (even with idle db) you got much more IOs going on.   Disk space: 3GB per database * 100 = 300GB.   Memory:, an 8.1.7 instance takes at least 50MB of memory. Multiply it by 100 - 5GB. Without OS (and file system cache) memory requirements.   CPU: will probably get quite loaded as well, because for each user, there's a separate DBWR, LGWR, CKPT, monitor, queue, etc.. processes.   OS resources: you could very well run out of semaphores, file handles, shmseg's etc..   Maintenance: Hey man, you'll have 100 different databases with 100*100 of different problems!   So, unless you got a powerful server and write caching IO subsystem and you want to waste all of it for nothgint, the answer is no, you can't have 100 databases in one server.   Go with logon triggers, and row level security. You don't even have to duplicate schemas, your packaged app doesn't even have to know, that it's running on same table set with others, and it's really simple to set up. As a bonus, when granting excempt_access_policy privilege to a user, you can run reports over all 100 user's data, but "normal" users don't have slightest idea, that there could be any other data in table in addition to theirs.   The downside is, that you should add some kind of org_id or user_id columns to your tables, on which the row level security will work.   Search for fine grained access control in docs.   Tanel. ----- Original Message ----- From: Dilip Patel To: Multiple recipients of list ORACLE-L Sent: Friday, August 08, 2003 1:54 AM Subject: Re: 100 instances on same server !!! Thanks for the replies so far. Considering the fact that my database is tiny ( just around 3 GB ), How many of them can work on same server? I can just test with 5 instances, with limited hardware. Will the CPUs be able to take load of 100 instances? Is it worth experimenting this?   I am on 8.1.7.4 and the application is already built. I stand no chance of changing the code. That is why using individual schemas for individual users is not an option. Right now I am just asked if 100 instances can run on same server and I don�t have solid answer.   Just on side note, can I ask, What is the maximum number of instances anyone has ever worked/heard being installed with in same server?   Thanks again.   Dilip. ----- Original Message ----- From: AK To: Multiple recipients of list ORACLE-L Sent: Thursday, August 07, 2003 11:54 AM Subject: Re: What books recommended for Data Modeling ? 100 instances wohhhhh .  If you are in 9i look at possibilities like context or label security . or creating another schema . -ak ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, August 06, 2003 9:24 PM > I have not heard installing hundred database instances on same server. > Maybe you should think creating one instance, and then hundred schemas in > it. > > Guang > > On Wed, 6 Aug 2003, Dilip Patel wrote: > > > Hi All, > > Need some suggestions/Input. > > > > My application database is 8.1.7, NOARCHIVELOG, WIN200, > > total size 4 GB, more of single user OLTP client-server application. > > > > Now the customer wants to give training on this application to hundred trainees > > at a time. For this he wants to install hundred database instances on same > > server machine,  which *each* will be accessed simultaneously from 100 different > > client workstations. > > > > The reasons for installing all instances on same machine are > > - to avoid re-installing databases on 100 workstations after each round of > > training. > > - No user should see any other user's data. > > > > Please suggest if this approach is feasible or is it at all possible. Tested this with upto 5 instances, and > > it seems to work. The customer is willing to upgrade to any hardware needed for > > this setup. > > > > Thanks in advance for your time. > > > > Dilip. > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Guang Mei >   INET: [EMAIL PROTECTED] > > Fat City Network Services    -- 858-538-5051 http://www.fatcity.com > San Diego, California        -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from).  You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK   INET: [EMAIL PROTECTED] Fat City Network Services    -- 858-538-5051 http://www.fatcity.com San Diego, California        -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from).  You may also send the HELP command for other information (like subscribing).