Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: April 14, 2009 - 3:55 pm UTC

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

Version: 10.2

Viewed 100K+ times! This question is

You Asked

Tom,
I am little confused about Oracle Database and instances. If I have created a database DB1, and started it,open for all users, does it mean it is one instance?
How can I have multiple instances of a database and how do I find what Instance I am using?
So, Can I have two instances of same database or when I say two instances, it's two different databases ?

Sorry, I am not a DBA and got confused while reading about instances. Can oyu please explain me a little bit about instances [very basics]
Hope your answer can clarify my confusion :-)

Thanks,

and Tom said...

Here is a quote from my book "Expert Oracle Database Architecture" on this

<quote>
Defining Database and Instance

There are two terms that, when used in an Oracle context, seem to cause a great deal of confusion: 'instance' and 'database". In Oracle
terminology, the definitions of these terms are as follows:

* Database: A collection of physical operating system files or disk. When using Oracle 10g Automatic Storage Management (ASM) or RAW
partitions, the database may not appear as individual separate files in the operating system, but the definition remains the same.

* Instance: A set of Oracle background processes/threads and a shared memory area, which is memory that is shared across those
threads/processes running on a single computer. This the place to maintain volatile, nonpersistent stuff (some of which gets flushed to disk).
A database instance can exist without any disk storage whatsoever. It might not be the most useful thing in the world, but thinking about it
that way will definitely help draw the line between the instance and the database.

The two terms are sometimes used interchangeably, but they embrace very different concepts. The relationship between them is that a database
may be mounted and opened by many instances. An instance may mount and open a single database at any point in time. In fact, it is true to say
that an instance will mount and open at most a single database in its entire lifetime! We'll look at an example of that in a moment.
Confused even more? Some further explanation should help clear up these concepts. An instance is simply a set of operating system processes,
or a single process with many threads, and some memory. These processes can operate on a database; a database is just a collection of files
(data files, temporary files, redo log files, and control files). At any time, an instance will have only one set of files (one database)
associated with it. In most cases, the opposite is true as well: a database will have only one instance working on it. However, in the special
case of Oracle Real Application Clusters (RAC), an option of Oracle that allows it to function on many computers in a clustered environment,
we may have many instances simultaneously mounting and opening this one database, which resides on a set of shared physical disk. This gives
us access to this single database from many different computers at the same time. Oracle RAC provides for extremely highly available systems
and has the potential to architect extremely scalable solutions.

Let's take a look at a simple example. Say we've just installed Oracle 10g version 10.1.0.3 on our machine. We did a software-only
installation. No starter databases, nothing-just the software.

The pwd command shows the current working directory (this example was performed on a Linux-based computer). We're in the dbs directory (on
Windows, this would be the database directory) and the ls -l command shows it is "empty." There is no init.ora file and no SPFILES (stored
parameter files; these will be discussed in detail in Chapter 3).

 [ora10g@localhost dbs]$ pwd
/home/ora10g/dbs

[ora10g@localhost dbs]$ ls -l
total 0


Using the ps (process status) command, we can see all processes being run by the user ora10g (the Oracle software owner in this case). There
are no Oracle database processes whatsoever at this point.

[ora10g@localhost dbs]$ ps -aef | grep ora10g
ora10g    4173  4151  0 13:33 pts/0    00:00:00 -su
ora10g    4365  4173  0 14:09 pts/0    00:00:00 ps -aef
ora10g    4366  4173  0 14:09 pts/0    00:00:00 grep ora10g



We then use the ipcs command, a UNIX command that is used to show interprocess communication devices such as shared memory, semaphores, and
the like. Currently there are none in use on this system at all.

[ora10g@localhost dbs]$ ipcs -a

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status

------ Semaphore Arrays --------
key        semid      owner      perms      nsems

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages


We then start up SQL*Plus (Oracle's command-line interface) and connect as sysdba (the account that is allowed to do virtually anything in the
database). The connection is successful and SQL*Plus reports we are connected to an idle instance:

[ora10g@localhost dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.3.0 - Production on Sun Dec 19 14:09:44 2004
Copyright (c) 1982, 2004, Oracle.  All rights reserved.
Connected to an idle instance.
SQL>


Our "instance" right now consists solely of the Oracle server process shown in bold in the following output. There is no shared memory
allocated yet and no other processes.


SQL> !ps -aef | grep ora10g
ora10g    4173  4151  0 13:33 pts/0    00:00:00 -su
ora10g    4368  4173  0 14:09 pts/0    00:00:00 sqlplus   as sysdba
ora10g    4370     1  0 14:09 ?        00:00:00 oracleora10g ()
ora10g    4380  4368  0 14:14 pts/0    00:00:00 /bin/bash -c ps -aef | grep ora10g
ora10g    4381  4380  0 14:14 pts/0    00:00:00 ps -aef
ora10g    4382  4380  0 14:14 pts/0    00:00:00 grep ora10g

SQL> !ipcs -a

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status

------ Semaphore Arrays --------
key        semid      owner      perms      nsems

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages

SQL>


Let's try to start the instance now:
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/ora10g/dbs/initora10g.ora'
SQL>


That is the sole file that must exist in order to start up an instance-we need either a parameter file (a simple flat file described in more
detail shortly) or a stored parameter file. We'll create the parameter file now and put into it the minimal information we need to actually
start a database instance (normally, there will be many more parameters specified, such as the database block size, control file locations,
and so on):

$ cat initora10g.ora
db_name = ora10g


and then once we get back into SQL*Plus:
SQL> startup nomount
ORACLE instance started.


We used the nomount option to the startup command since we don't actually have a database to "mount" yet (the SQL*Plus documentation has all
of the startup and shutdown options documented).

Note On Windows, prior to running the startup command, you'll need to execute a service creation statement using the oradim.exe utility.
Now we have what I would call an "instance." The background processes needed to actually run a database are all there, such as process monitor
(PMON), log writer (LGWR), and so on (these processes are covered in detail in Chapter 5).

Total System Global Area  113246208 bytes
Fixed Size                   777952 bytes
Variable Size              61874464 bytes
Database Buffers           50331648 bytes
Redo Buffers                 262144 bytes
SQL> !ps -aef | grep ora10g
ora10g    4173  4151  0 13:33 pts/0    00:00:00 -su
ora10g    4368  4173  0 14:09 pts/0    00:00:00 sqlplus   as sysdba
ora10g    4404     1  0 14:18 ?        00:00:00 ora_pmon_ora10g
ora10g    4406     1  0 14:18 ?        00:00:00 ora_mman_ora10g
ora10g    4408     1  0 14:18 ?        00:00:00 ora_dbw0_ora10g
ora10g    4410     1  0 14:18 ?        00:00:00 ora_lgwr_ora10g
ora10g    4412     1  0 14:18 ?        00:00:00 ora_ckpt_ora10g
ora10g    4414     1  0 14:18 ?        00:00:00 ora_smon_ora10g
ora10g    4416     1  0 14:18 ?        00:00:00 ora_reco_ora10g
ora10g    4418     1  0 14:18 ?        00:00:00 oracleora10g ()
ora10g    4419  4368  0 14:18 pts/0    00:00:00 /bin/bash -c ps -aef | grep ora10g
ora10g    4420  4419  0 14:18 pts/0    00:00:00 ps -aef
ora10g    4421  4419  0 14:18 pts/0    00:00:00 grep ora10g


Additionally, ipcs is, for the first time, reporting the use of shared memory and semaphores - two important interprocess communication
devices on UNIX:
SQL> !ipcs -a

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x99875060 458760     ora10g    660        115343360  8

------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0xf182650c 884736     ora10g    660        34

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages

SQL>


Note we have no "database" yet. We have a name of a database (in the parameter file we created), but no database whatsoever. It we try to
"mount" this database, then it would fail because it quite simply does not yet exist. Let's create it. I've been told that creating an Oracle
database involves quite a few steps, but let's see:
SQL> create database;
Database created.


That is actually all there is to creating a database. In the real world, however, we would use a slightly more complicated form of the CREATE
DATABASE command because we would need to tell Oracle where to put the log files, data files, control files, and so on. But here we now have a
fully operational database. We would need to run $ORACLE_HOME/rdbms/admin/catalog.sql script and other catalog scripts to build the rest of
the data dictionary we use every day (the views we use such as ALL_OBJECTS are not yet present in this database), but we have a database here.
We can use a simple query against some Oracle V$ views, specifically V$DATAFILE, V$LOGFILE, and V$CONTROLFILE, to list the files that make up
this database:

 SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/home/ora10g/dbs/dbs1ora10g.dbf
/home/ora10g/dbs/dbx1ora10g.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/home/ora10g/dbs/log1ora10g.dbf
/home/ora10g/dbs/log2ora10g.dbf

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/home/ora10g/dbs/cntrlora10g.dbf

SQL>


Oracle used defaults to put everything together and created a database as a set of persistent files. If we close this database and try to open
it again, we'll discover that we can't:
SQL> alter database close;
Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed


An instance can mount and open at most one database in its life. We must discard this instance and create a new one in order to open this or
any other database.

To recap,

* An instance is a set of background processes and shared memory.
* A database is a collection of data stored on disk.
* An instance can mount and open only a single database, ever.
* A database may be mounted and opened by one or more instances (using RAC).

As noted earlier, there is, in most cases, a one-to-one relationship between an instance and a database. This is probably how the confusion
surrounding the terms arises. In most peoples' experience, a database is an instance, and an instance is a database.

In many test environments, however, this is not the case. On my disk, I might have five separate databases. On the test machine, at any point
in time there is only one instance of Oracle running, but the database it is accessing may be different from day to day or hour to hour,
depending on my needs. By simply having many different configuration files, I can mount and open any one of these databases. Here, I have one
"instance" at a time but many databases, only one of which is accessible at any point in time.

So now when someone talks about an instance, you'll know they mean the processes and memory of Oracle. When they mention the database, they
are talking about the physical files that hold the data. A database may be accessible from many instances, but an instance will provide access
to exactly one database at a time.
</quote>

</quote>

Rating

  (27 ratings)

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

Comments

Database vs instances

A reader, April 15, 2009 - 4:35 pm UTC

That's quite an explanation Tom. Not sure I am 100% clear on this but atleast I knew few things now [specially Instance is not database and database is not instance :-)]

So, let me eloborate little bit. Let's say I have installed Oracle on my machine [windows XP] and created two databases Orcl1 and Orcl2 and logged in as sysdba and did a mount.
So, there is only one instance running , but there are two databases on this machine.
1)So, which database this instance is connected to [I just logged in as sysdba and did not mention any database to start] How does oracle pick up a database for this instance? [There might be a command like "startup open orcl1" but I couldn't get it to work].

After creating databases[using DBCA], I found on Windows Services that both databases are started:
OracleservicesOrcl1 started automatic
OracleservicesOrcl2 started automatic

After that I shutdown a database
using shutdown immediate [as sysdba]. So, it closed and dismounted a database [Orcl1].

Windows services is still showing same info:
OracleservicesOrcl1 started automatic
OracleservicesOrcl2 started automatic

2)What does this mean? two instances or just two databases and no instances?

Now, when I check global_name[select * from global_name], I do not see any database [I was thinking Oracl2 should be still active [open]]. but it looks like Shutdown command closed/dismounted both the databases.

This brought a couple of questions in my mind.

3) When I login using "sqlplus / as sysdba", which database it connects to and why?
4) If I want to dismount/close only one database and not both, can I do that?
5) can I login as sysdba to only one database? [If so how?]

Can you clarify these? This kind of information is documented somewhere on Oracle.com?

Thanks as always


Tom Kyte
April 15, 2009 - 4:51 pm UTC

... 1)So, which database this instance is connected to ...

depends, there is a default database stored in the registry, you can override that by setting ORACLE_SID in the environment too....

and in windows you have those pesky services - so you need to make sure the little service is running so each instance can connect to it's bootstrap service (ugh, windows)...

but after you type startup (with nomount) you can see the instance name easily:

idle> startup nomount
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1268484 bytes
Variable Size             134219004 bytes
Database Buffers          394264576 bytes
Redo Buffers                7118848 bytes
idle> select instance_name from v$instance;

INSTANCE_NAME
----------------
ora10gr2



2) those are the tiny 'bootstrap' services - needed by windows. Windows is not as clean/easy as Linux/Unix.


3) what database and why....

that is a function of your environment or registry. you can set the ORACLE_HOME in your environment before typeing sqlplus to 'pick' one.

4) yes, just connect to the right one.

5) yes, you can only log in as sysdba in a single sqlplus session to a single instance (which has at most a single database)

You are logging into the instance, not the database :)




one more question

A reader, April 16, 2009 - 9:59 am UTC

Tom, you mentioned after installing Software-only version of Oracle [no databases yet], you connected as sysdba to startup an idle instance:
"
We then start up SQL*Plus (Oracle's command-line interface) and connect as sysdba (the account that is allowed to do virtually anything in the
database). The connection is successful and SQL*Plus reports we are connected to an idle instance:


[ora10g@localhost dbs]$ sqlplus "/ as sysdba"
"

Without Database where is sysdba connecting to? I tried removing /uninstalling oracle on my mcahine and reinstall it with s/w only version and tried to connect as sysdba and it failed with
ORA-12560 TNS:protocol adapter error

Did I miss something?
Thanks,

Tom Kyte
April 16, 2009 - 10:11 am UTC

you are on windows, windows is complex, you need to use oradim on that platform to create that tiny bootstrap service I talked about right above.

A reader, December 14, 2010 - 6:45 am UTC

Very well explained from "Expert Oracle Database Architecture".

Thx,
Shri.

Database vs Instance

Mital, May 24, 2011 - 11:14 am UTC

Hello Tom,
Thanks a lot for your wonderful explanation for database and instance.
I am heeding your advice and have started reading Oracle Concepts guide.
In chapter 13, The Oracle Database Instance, I read the following:

Every running Oracle database is associated with at least one Oracle database instance.
Because an instance exists in memory and a database exists on disk, an instance can exist without a database and a database can exist without an instance.


I am confused with the last sentence. I thought that we need instance in order to access/log on to database as it is an memory structure that manages datafiles.
Even while creating database, you create instance first(allocated SGA, memory etc) and then create database.
Please correct me, if I am wrong.

I am not able to understand how database can exist without instance. Is that useful in real-world scenario?

Thanks,
Mital
Tom Kyte
May 24, 2011 - 12:59 pm UTC

Nothing you said disagrees with what was written.


A database is a set of files, obviously it can exist all by itself - it wouldn't be running by itself, but it can "exist". the files can exist and that is what a database in Oracle speak is - a set of files.

An instance is a set of processes and memory. It too can exist all by itself (think about the instance you start to CREATE a database for example, it obviously can exist without any database).

If a database is "running", there must be AT LEAST one instance that is associated with it, the database cannot be "running" without one.



Multiple database vs Multiple Instances

Namrata, May 25, 2011 - 4:34 am UTC

Tom,

Can you provide scenarios for below statement, I read somewhere
" There can be multiple databases within an instance, and a database also can be distributed across multiple instances"
Tom Kyte
May 25, 2011 - 11:28 am UTC

for the first one - nope, but only because it isn't true.

An instance is associated with one and only one database EVER. An instance comes into being with startup - and goes away with shutdown. In between startup and shutdown - that instance can mount and open exactly ONE database - ever.


A database however, can be open by one or more instances - with real application clusters. In that environment - you can run as many instances as you like on different machines and all can mount and open the same set of database files.

Instance Vs Database

darcy, July 02, 2011 - 8:04 am UTC

HI Tom,


I am bit confused on instances and oracle? Suppose if i log in to oracle(windows) with an user a, at first one instance is created. if log in with one more user b again one more instance is created on database or not.otherwise one instance for both user a and user b?

Can you please explain me


Tom Kyte
July 05, 2011 - 7:48 am UTC

an instance is the set of oracle processes and memory. We do not create one of those per user/session.

On a given machine, there is typically a single instance that mounts and opens a single database. The instance is our software running.

Actually, if you read this original answer above, it should be clear?

instances and services/nodes

A reader, July 12, 2011 - 5:35 pm UTC

This is very good clear explanation...
Now remaining question I have is about services/nodes - how do those relate to an instance/database. Is a service/node 1:1 with an instance or...?
Tom Kyte
July 13, 2011 - 8:01 pm UTC

a service can point to one or more instances. An instance can register itself as zero, one or more services with a listener.


when an instance comes up, it contacts the listener and tells that listener what services it is able to provide. So, database A could say "I'm the OLTP and reporting instance" and instance B could say "I'm the reporting and batch instance".

If you connect to OLTP - you'd get to A
if you connect to batch - you'd get to B
if you connect to reporting - you'd be load balanced over A and B.

miraj, May 15, 2012 - 1:04 am UTC

Hi,
nice response, I am unclear of some things though.. I have read somewhere that SID=database name+ instance number.. is this true?

and if i access a database using one sid and create a table "employee", can i access the same database using a different sid and make a table "employee" which is different from the previous one?
Tom Kyte
May 15, 2012 - 1:52 am UTC

the oracle_sid is the instance name.

Nothing to do with the database name - although in many cases - they are the same as well (but they do not have to be)


the ORACLE_HOME+ORACLE_SID identify an instance (set of processes and memory). An instance may mount and open a single database. A database however can be mounted and opened by many instances (Real Application Clusters).


So, in your last paragraph there - if you access the database from node1 of a cluster and create the employee table - that would create it in the *database* (irrespective of instance) and all instances mounting that database would instantly see that new table. You would not be able to log in from node2 and create it again, it already exists.

One instance one database advantage over one instance multiple database

Biju George, May 25, 2012 - 3:41 am UTC

Apologies if this deem out of topic or not relevant to the original discussion...
What would be the advantage of Oracle's one instance-one database architecture over say Sybase/MS SQL Server's one instance-multiple database architecture. It's just out of curiosity to know. Thanks a lot for your patience...
Tom Kyte
May 25, 2012 - 9:15 am UTC

It is just a naming scheme, Sybase references objects via database.owner.object_name, Oracle references objects via owner.object_name

Sybase/sql server have but one true database per instance (an instance for all of us is a process or set of processes and some memory - a database is a collection of files)

they just have a third level of indirection, Oracle has two.

I have multiple instance running on a single unix server

Muni, December 19, 2012 - 6:49 am UTC

Hi Tom,

In my project, i have a single unix server (physical) and have 5 oracle instances (prodcopy, dev, sit, uat and training) running on it all the time. This one conflicts with your answers to the original question posted. Could you please explain why that statement does not hold true in this case. The oracle version we are running now is 11.1.0.7.0.

Please let me know if you need more information.

Thanks,
Muni
Tom Kyte
December 19, 2012 - 12:11 pm UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3167741500346545498

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4135437900346923722


you are not running production instances, so as long as you don't care if someone in DEV consumes 100% of the machine resources - effectively killing UAT, Training, etc - you have nothing to worry about.

If you are worried about that - you should be using instance caging and the resource manager in general to limit the amount of resources a given instance can consume on the machine, or vitualization.

Alexander, December 19, 2012 - 12:46 pm UTC

I would argue UAT should be identical to production and not at the mercy of whatever "what if" query etc someone might decide to run in development.

I think it is wise to have your test/stage/UAT whatever you want to call it, were you deploy before production, to be the same. In my experience, UAT is used for that purpose. Otherwise you don't truly know how something may perform there vs production with one instance running.

I suppose it's possible you could have a much bigger machine to support the 5 instances and cage it so it's the same as production but you still have to worry about disk contention of 5 instances vs 1 and I'd bet 10 bucks no one took this into consideration when ordering servers.
Tom Kyte
December 20, 2012 - 12:19 pm UTC

depends on whether that UAT is used for load testing. If not, it won't really matter - you won't be able to judge performance regardless (unless you run a single user system). So the UAT would be all about functionality.

You'd be surprised how many people do not do any sort of load test... :(

but i do agree with you in general, you should have a load test platform that is capable of simulating your production workload.

UAT Mirroring Live

DJB, December 20, 2012 - 5:50 am UTC

Absolutely agree - unfortunately 'management' doesn't usually want to spend the money.

load test

A reader, December 20, 2012 - 3:14 pm UTC

I think staging instance or UAT should be on the same production machine (not DEV/TEST machine).

Can Oracle 11g Replay Workload be used to replay the production transactions on staging for load testing?
Tom Kyte
December 20, 2012 - 5:35 pm UTC

I think staging instance or UAT should be on the same production machine

really??? UAT *on production*?????

really?



Can Oracle 11g Replay Workload be used to replay the production transactions on 
staging for load testing?


yes, but it would only be a valid load test if staging was a machine similar to production (and nothing else was running on it, just like production)

staging

A reader, December 21, 2012 - 6:57 am UTC

<<<yes, but it would only be a valid load test if staging was a machine similar to production (and nothing else was running on it, just like production) >>>

this is why we run "staging" on a production machine.

I understand your concerns about runing high load tests on staging. Actually we do not do that much. A single user customer does acceptance testing on it. There are very very few people that do load/stress testing.

to us
STAGING = PRE-PRODUCTION

There are many shops that run DEV, TEST, STAGING, PROD all on one machine.

It all depends on budgets and type of applications. The more machines generally means more oracle licenses (which are not cheap) and higher costs.

This of course also depends on the number of CPU/CORES the machine has. But many IT managers base their decisions for number and location of the instances based on the number of licenses they need to buy.
Tom Kyte
January 04, 2013 - 10:14 am UTC

this is why we run "staging" on a production machine.


if that is a production machine and you run a test on it, it is not production. Do you get that? Testing doesn't happen on a production machine, on a machine (host) currently used to run a production load.

for example, we would never do a load test on apex.oracle.com (the actual host behind asktom.oracle.com and hundreds/thousands of other apps). That would *not be smart*. You do a load test on an isolated piece of equipment - really far away from production.


A single user customer does acceptance testing on it.

that is NOT a load test, that is *not* anything remotely similar to a load test.

and guess what - it only takes one user doing something or a piece of software doing something unintended to kill a machine. It is not smart to do acceptance testing on production.

it is not smart to do anything OTHER than production on .... production.

that is why we call it "production" - and not "test", "dev", "qa", etc

The more machines generally
means more oracle licenses (which are not cheap) and higher costs.


machine = host, use virtualization if you want - but separate that stuff.


Alexander, December 21, 2012 - 7:57 am UTC

"It all depends on budgets and type of applications. The more machines generally means more oracle
licenses (which are not cheap) and higher costs."


Sounds like you are implementing Oracle like other database platforms and giving every application their own database, thus you need lots of instances. If you consolidated those applications into fewer instances you wouldn't have to cram all your environments on one machine. Oracle can handle hundreds of concurrent applications sharing an instance....

instance

A reader, December 21, 2012 - 10:25 am UTC

No, Oracle licensing has nothing to do with the number of databases.

You can buy one license and create ONE THOUSAND instances/databses or ONE database with one thousand schemas.

This is not the issue.

When you have multiple machines (i.e one for DEV, one for TEST, one for STAGING, one for PROD) you need licenses for each so depending on the number of CPUs and the cores (i.e, dual core, quad core) you have you may end up buying 16 licenses.

If you run them all on ONE machine, you only buy 4 licenses.

If you consider that each license for Enterprise sells for $40,000 + $8,000 for support, then you are taking big savings.

Of course, from an oracle sales prespective, separate environments is much better because it is more sales or $$$ for them.

Tom Kyte
January 04, 2013 - 10:17 am UTC

machine = host, host = OS image, one single machine can have zero, one or more hosts on it - each host protected from every other host.


sales has nothing to do with this, not anything to do with anything I'm saying.

Experience, past history, reality - they all come into play when you say "production is not to be used for anything other than production"


Alexander, December 21, 2012 - 10:43 am UTC

"You can buy one license and create ONE THOUSAND instances/databses or ONE database with one
thousand schemas.

This is not the issue.

When you have multiple machines (i.e one for DEV, one for TEST, one for STAGING, one for PROD) you
need licenses for each so depending on the number of CPUs and the cores (i.e, dual core, quad core)
you have you may end up buying 16 licenses.
"

I understand, but what I am suggesting is this probably happening on a larger scale, in many places as opposed to this one situation which is contributing to your licensing hurdle. Unless you are working for a small company, where a few additional servers and licenses is a problem. Forgive me if I am being presumptuous.

still missing one part here

Nafi, December 31, 2012 - 3:58 am UTC

Hi Tom/All,
after reading this explanation and answers to the questions in this thread i finally get it how multiple instances and multiple databases work together,
what is still missing is the listener part, i guess since it's a process then it's related to an instance ,right?
if so then two instances means two listeners,right ? ,
then on my machine (windows 7) i installed 10g xe and 11g xe , i'm trying to access both of them at the same time,so why when i give the following command (lsrnctl status) it does show only one instance of listener ?which is related to 11g xe.
and Happy New Year :)
Tom Kyte
January 04, 2013 - 11:31 am UTC

what is still missing is the listener part, i guess since it's a process then it's related to an instance ,right?

nope, not at all. A listener is completely detached from an instance - you need one listener per host - regardless of the number of instances running on the host. In fact - a listener doesn't even have to run on the same machine as the instance!

You typically run ONE listener on a host and let all of the instances on that host register with it.

ok that's interesting

Nafi, February 01, 2013 - 4:13 am UTC

one listener and multiple instances , make sense , but how do i register my instances to that listener ? can you put it in example please.
thanks.
Tom Kyte
February 01, 2013 - 8:42 am UTC

http://docs.oracle.com/cd/E11882_01/network.112/e10836/listenercfg.htm#i490264




(hint: the same exact way you get your single instance to register with a listener!!! the same exact way... the second instance will do the same exact thing the first instance did!)

multiple instances and listner on non RAC environment

Srikar, July 17, 2013 - 1:17 pm UTC

Hi Tom,

Thank you very much for the details.

I am not a DBA, but I believe that a single host can have multiple instances running opening different databases at the same time (i am on unix environemt) -- its not RAC environment
I have seen in my environment (host) that different instances for different databases are running at the same time.
1. Is the above statment correct, can we have multiple instances running to open different database at the same time on non RAC environment.
2. In the above scenario, how many listerns are running, do I hold multiple listners for each database or how it is, where can i find the listner file for each database on the unix machine.

Thank you very much in advance.

Regards,
Srikar
Tom Kyte
July 17, 2013 - 6:11 pm UTC

I am not a DBA, but I believe that a single host can have multiple instances
running opening different databases at the same time (i am on unix environemt)
-- its not RAC environment


yes you can, i advise against that whenever possible, but yes you can

1) yes

2) you only need one for all databases on a server. you can have as many as you like. the listener file is typically in $ORACLE_HOME/network/admin regardless of how many times you start the listener (but the dba can move it if they want - or even have multiple ones - they would use the TNS_ADMIN environment variable to point to it)

A reader, July 18, 2013 - 10:01 am UTC

Thank you very much Tom.

Regards,
Srikar

A reader, September 30, 2014 - 5:27 pm UTC

Thank you man you are great

Question about Enterprise Manager and Listener on same ODB install

Errol Alvarez, October 05, 2015 - 4:17 pm UTC

Hi
Great post! I have a minor question , lets say that using dbca ( a tool for creating new databases) I will create a new database on the same machine I created my first database with its own service, own instance name and so on ... the question is , when installing a new database, does a new listener is created ? (havent run the dbca yet) a new database console is created or the same one could be used


Chris Saxon
October 05, 2015 - 11:55 pm UTC

Typically you would re-use an existing listener - you only need one per server.

(When you get into RAC, things get a lot more interesting with multiple listeners and SCAN listeners, but thats a different topic)

A reader, March 15, 2019 - 10:40 am UTC


sumeet yaduwanshi, April 08, 2021 - 9:29 am UTC

Good article Tom and it will help and clear many people doubts. Very nicely explained..
Connor McDonald
April 12, 2021 - 5:13 am UTC

glad we could help

abhishek ghosh, January 24, 2022 - 8:08 pm UTC

Thanks.
Connor McDonald
January 25, 2022 - 6:47 am UTC

glad we could helpo

Reopen close database

Asim, September 06, 2022 - 6:21 am UTC


1.
What is difference between mounting acdata base and opening a database

2.
Is there something like unmounting the database, if yes how it differs from closing database.

3.
Why the instance is unable to reopen the database in the same life in which it closed the database.


.






Connor McDonald
September 07, 2022 - 3:44 am UTC

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.