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 we 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>