Jozef Babjak, March 11, 2011 - 12:47 am UTC
What unix do you have? If you have Solaris 10, use zones for each oracle installation. It has almost none overhead (as few as 5 almost-always-idle OS processes when properly configured), but clear separation of each installation makes things clearly separated and easy to maintain. Additionally, it provides perfect starting point for later separation. We very successfully used this setup for test environments - one project in one zone, including everything necessary: database, application server, messaging, etc.
Check options for your unix system, if you don't have Solaris 10. Likely it has something similar [but not so mature, indeed :-)].
Nandini, March 11, 2011 - 8:51 am UTC
Thanks a bunch Tom! Will try now.
Robert, March 15, 2011 - 11:27 pm UTC
You said: I'm not a fan of more than one instance on a given host outside of a simple testing environment myself...
I understand the theoretical logic of that statement, but the realities of Oracle's core based licensing, and the number of cores you *have* to purchase in a server now-days means that most companies can't afford *not* to run multiple instances.
March 16, 2011 - 8:25 am UTC
Or, think about it this way, you could actually consolidate your developed applications into a single database.
I can run more on a single machine in a single database than you can on 2 databases on the same machine. And 2 databases can run more than 3 can. And 3 can run more than 4 can. And so on and so on and so on.
So, if you were really price sensitive you would be demanding a single database per host. You can run more stuff in a single database, more reliably, with better uptime and service levels than you can with 2, 3, 4, or more.
One SGA - less memory used by the database then 2 SGA's would (lots of redundancies in there)
One LGWR - less change for instance one to wipe out instance twos ability to write redo - less contention for a shared resource.
One instance = resource manager can be used to divvy up resources, two instances != resource manager having a chance.
and so on.
And in the case of the 3rd party application - look to virtualization of some sort to control things
Robert, March 16, 2011 - 5:03 pm UTC
Of course you are correct, but practical considerations come into play here as well. We use consolidation at the database level as much as we can, but are constrained by issues like matching downtime windows, usage profiles and the like.
While not as efficient a use of resources, consolidation at the server level is often more manageable, although not without its own set of constraints.
March 16, 2011 - 6:23 pm UTC
... While not as efficient a use of resources, consolidation at the server level is
often more manageable, although not without its own set of constraints. ...
and that is where some level of virtualization can come in handy like solaris zones.
You would still meet my "one host, one instance" - the goal is to be able to partition up those resources (cpu, memory, io)... If you run many instances per host (and you are not running on exadata) you can partially do cpu - but that is it (and only in 11g)
SGA and memory issue
Jayadevan, June 30, 2011 - 4:09 am UTC
I have 3 Oracle database instances running on one machine. This is a Windows Server 32 bit machine with /3GB switch. When the OS estimates the total memory used by the instances, will it add up SGA used by these instances and once it crosses the 3 GB limit, cause problems? Or it will consider each one as a separate process? Out of the 4, there is only one instance that is critical (Production) and needs to be given 1.5-2G SGA. I know it is a bad idea to run other instances on the machine and memory is just a part of the problem. But wanted to check the memory side and raise that point before "strongly" recommending that the other instances be moved to a separate machine.
July 01, 2011 - 8:28 am UTC
I have 3 Oracle database instances running on one machine.
Yuck already.This is a Windows
Server 32 bit machine with /3GB switch.
Each instance is a separate process with it's separate memory region. That said however, you should refer to microsoft for figuring out how much memory your OS can handle - since we don't know what OS version you are running: http://msdn.microsoft.com/en-us/library/aa366778(v=vs.85
Jayadevan, July 04, 2011 - 6:33 am UTC
Thank you :)
The second yuck was for "/3G", or for "Windows" or for "32bit"? (or for the combination). Just curious to know which is the most disappointing part.
The OS version is "Windows Server 2003 R2 Enterprise Edition SP 2".
July 05, 2011 - 9:00 am UTC
it was for 32bit on windows - just about the most limiting environment we've ever hosted a server platform on. Approximately 2gb limit on all addressable memory for an instance (PGA+SGA combined). At least on unix 32bit - the SGA was limited to about 2gb and *each* PGA allocated was limited to about 2gb - but you could use many many gigabytes of memory.
the /switches just made it all the more fun (as in unstable)
Jayadevan, July 06, 2011 - 7:20 am UTC
OK. Thanks. We got information about /3GB switch from metalink - document id 1036312.6. I guess official documentation rarely mentions probable stability issues.
July 06, 2011 - 8:18 am UTC
search about it - 3gb stability, we don't talk about it because the OS isn't ours.
It is a non-default setting on an OS used in attempt to utilize a bit more memory under certain circumstances. In other words: a band-aid.
LDS, July 07, 2011 - 5:11 am UTC
The /3GB switch will allow *each* process to use up to 3GB of *virtual* address apace (beware this is obtained shrinking other structures used for file cache, paged pool and nonpaged pool, which can impact performance. /USERVA can be used for a finer control on how more memory the process is allowed to use). Thereby each Oracle instance can see 3GB of memory.
Then the OS has to map this *virtual* space to the *physical* memory. Using PAE (/PAE), the OS can address more than 4GB of *physical* memory, and if enough memory is available, *virtuaL* addresses can be mapped to physical ones. Otherwise they may go (and will..) to the swap files, and performance sinks. How much physical memory a Windows OS can address depends on the version, see http://msdn.microsoft.com/en-us/library/aa366778(v=VS.85
Creating Multiple Oracle Databases in Red Hat Linux 5.0
Ram Prasad, August 05, 2012 - 8:37 am UTC
Hi tom.I am from India.I am trying to run multiple databases on same server.
I am facing some problems.I need your valuable suggestions.
I have installed VM ware.On VM ware redhat linux,followed by this oracle 10g 10.2.0 is the database I am using.
I created the primary database manually.
SID name of this database is ORCL.
I want to create another database with DBA as the second SID.So, this time I created the database with DBCA.
DBCA created the second database with DBA as SID.
But when I try to start the database with startup command,It is only showing orcl.So,I opened another tab in shell prompt
and tried to connect with system user.
Sytem user has a password.It's password is dba.
I logged in the database as 'CONNECT SYSTEM/DBA AS SYSDBA'.
At this instance,it should show DBA as the instance name.But it is showing only ORCL.
To practice rman and dataguard,I need two databases on single server.For this purpose I am asking you to help me.
And also please tell me, how to configure the listener and tnsnames.ora in a multiple database environment.
August 17, 2012 - 11:50 am UTC
set your oracle_sid environment variable first.
using values appropriate for YOUR system
A reader, September 19, 2012 - 11:46 pm UTC
i have two databases one is REDDY and PROD. the problem is once i give sqlplus / as sysdba it shows only PROD
in my .bash_profile i set O_sid= REDDy
September 20, 2012 - 7:34 am UTC
$ORACLE_HOME/bin/sqlplus / as sysdba
show us that and show us how you are figuring out what database you are in.
OK. So how about 20 instances on a single server?
Me, December 28, 2012 - 7:42 pm UTC
and there's more. one listener. shared_servers=1 and max_shared_servers=null. and parallel_max_servers = the amount dictated by lots of CPUs on the box, which is quite a bit. so other than just putting everything into a single database, for which there might be a logical reason for not having done so, which i kind of doubt .... any ideas would very much appreciated. i have some ideas but i was wondering about other opinions, architecturally speaking.
January 04, 2013 - 11:17 am UTC
I'm not sure what you are asking?
ram, March 20, 2014 - 11:18 am UTC
in real time how many database can we run in a single server?
if it is one database why?
Multiple DB Instance on same Linux Host
vpa, May 08, 2014 - 7:25 am UTC
If I need to have multiple Instances for single database on the same host. Are below points true?
1. Use DBCA for creating new instance
2. I need to set unique ORACLE_SID for each instance
3. Use same parameter file as the database is the same
Though this may not be a practical approach. Can you please answer this to clear the confusion?
Two Versions on Same Server
Mohammed Haris, April 05, 2019 - 12:08 pm UTC
I would like to install 11g and 18c on same server. I have tried installing 11g first and 18c later. After selecting proper DBHOME, both versions show separately in sqlplus. But I can't connect using TNS.
April 09, 2019 - 1:34 am UTC
I have several installs on one machine - but don't forget, you probably only need a single listener.
So perhaps a configuration like:
- 18c listener on the default 1521
- both 18c and 11g databases by default will register with a listener on 1521
then you should be able to connect to each
Virtual RAC Just for understanding purpose.
Asim, September 06, 2022 - 6:40 am UTC
So far, here, the discussion is for having multiple instances of oracle on same machine, and each instance will open different database.
My question is can I have multiple instances of oracle on SAME machine simultaneously, and all of them accessing SAME ONE database simultaneously? A kind of virtual RAC or instead of REAL its VAC virtual application cluster.
I know its doesnt make much sense practically, but just to know if technically its possible?
September 07, 2022 - 3:45 am UTC
Not really. You can of course run multiple VM's on one machine in a RAC "cluster" which can be useful for testing out RAC concepts and the like