Skip to Main Content
  • Questions
  • To create multiple instances and databases on same unix machine

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Nandini.

Asked: March 10, 2011 - 3:35 pm UTC

Last updated: September 07, 2022 - 3:45 am UTC

Version: 11.2.0

Viewed 50K+ times! This question is

You Asked

I have a requirement to create 2 instances and 2 databases on same unix machine. Do I need multiple homes, I think not because it is same version (11g). I already created one instance/database using OUI. Are these steps right for creating second one?

1.Login as oracle (dba group user)
2. Set ORACLE_SID
check environ variables
-do we need another ora_home and ora_base?
3. Authentication
4. Create init.ora (Copy existing)-do we need two init.ora files?
5. Connect to the instance
sqlplu /nolog
sql> connect / as sysdba
6. create spfile from pfile;

6. Start instance
sql> startup nomount
7.Create database
8.Create additional tablespaces

Thanks a lot for help!

and Tom said...

use dbca to create a new instance, it will take care of everything for you.

You need only one oracle_home and one oracle_base, you'll have multiple oracle_sids.

DBCA will do the details of creating a new parameter file (yes, you want another one separate and distinct, no it is not mandatory but you want a new one) and such for you.


I'm not a fan of more than one instance on a given host outside of a simple testing environment myself...

Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Use virtualization.

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 :-)].

Thanks!

Nandini, March 11, 2011 - 8:51 am UTC

Thanks a bunch Tom! Will try now.

Multiple instances

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.
Tom Kyte
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

And yet...

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.
Tom Kyte
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

Hi Tom,
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.
Regards,
Jayadevan
Tom Kyte
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.


double yuck.


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 ).aspx

Details

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".

Tom Kyte
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)

/3GB

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.
Tom Kyte
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.

/3GB meaning

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 ).aspx

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.

Regards,
Ram Prasad
Tom Kyte
August 17, 2012 - 11:50 am UTC

set your oracle_sid environment variable first.


export ORACLE_HOME=/home/ora11gr2/app/ora11gr2/product/11.2.0/dbhome_3
export ORACLE_SID=ora11gr2
export PATH=~tkyte/bin:$ORACLE_HOME/bin:$PATH


using values appropriate for YOUR system

A reader, September 19, 2012 - 11:46 pm UTC

hi tom
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
error

Tom Kyte
September 20, 2012 - 7:34 am UTC

echo $ORACLE_SID
$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.
Tom Kyte
January 04, 2013 - 11:17 am UTC

I'm not sure what you are asking?

question

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.
Connor McDonald
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?




Connor McDonald
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