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>