Oracle database vs. Instance

Most novices tend to get confused by these two terms. Are they referring to the same thing? To add to the confusion, the term database instance is used  as well. Database and instance are not the same thing. They are two different things, but they are related.

An instance is a collection of background processes and a shared memory area.

A database is a collection of files in the OS (data files, control files, temp files and redo files).

So how are they related and how do they co-exist?

- An instance can exist without a database. What? You can start an Oracle instance but not have access to any database files? Yes, you can. In fact, this is how you create a database. First you start an instance and then you create a database. This is exactly what happens when you install Oracle software only option. There is no database to mount and open [mount and open? why don't you just open? why are there two steps? I will explain it a little later in this post.]. Very first thing you do is to create one after connecting to an instance.

- An instance can access only one database. In Oracle terms, you can mount only one database per instance.

- A database can be accessed by multiple instances. Why would you want to do that? This provides scalability by letting multiple processes to process user requests in parallel and also provides higher availability by continuing to server even if a few instance are down. This is exactly what Oracle RAC (Real Application Cluster) installations do!

Still confused? Let me go through an example to show these concepts. I am using the most popular version of Oracle database here (11g). (Hint: stay tuned for the latest database 12c posts)

You can install Oracle either software only or software + database. To explain the concept of instance and database, I have performed a software only installation. That is, no default database is created at the time of installation.

First we connect to an idle instance:

bob@example> cd $ORACLE_HOME/bin
bob@example> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jun 17 10:32:58 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to an idle instance.


"Connected to an idle instance" usually indicates that you connected "as sysdba" and the instance you connected to is not started. This gives the privilege to start the database. You will see a process by the name oraclemysid created at this point. Instance name is selected as oracle + SID (System Identifier) name. You specify the SID at the time of installation.

bob@example> ps -ef | grep mysid
bob  15680 15679  0 10:32 ?        00:00:00 oraclemysid (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Now let's try the following command at the SQL prompt:

SQL> startup nomount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/scratch/bob/app/bob/product/11.1.0/db_1/dbs/initmysid.ora'

Ooops, errors! Before I explain the above error. Let's go through main startup modes as I promised at the beginning of the post.

There are three main modes of startups:
1. startup nomount
2. startup mount
3. startup open (or simply startup)


startup nomount - you start an instance without mounting a database. Why would you do that? Well, if you don't have a database you have no other option but to start the instance without mounting. You would also do this when you want to create a new database or re-create control files.

startup mount (if you have already issued startup nomount, you can type alter database mount; to do the same) - you start an instance and mount the database, but leave the database closed. But why would you simply mount and still have the database closed? Isn't it practically same as no mount? No, this mode is different. Even though this mode does not allow general database activities, it does allow to perform certain DBA tasks on the database such as backing up or archieving.

startup open (if you have already issued startup mount, you can type "alter database open;" to do the same) - This opens the database with full access. There are ways to restrict access to database by providing additional parameters with these commands.

Now let's get to the error. We got the error because Oracle cannot locate the parameter file. Unless you have specified the PFILE parameter with the startup nomount command, Oracle look for the default parameter file $ORACLE_HOME/dbs/initmysid.ora  (that is init + SID name + .ora).

Let's create one. It should have the db_name parameter to identify the database that we want to associate with the instance.

cd $ORACLE_HOME/dbs

Create the following file and add the following name. orcl is the global database name given at the time of installation.

vim initmysid.ora

db_name = orcl
:wq

Now connect to an idle instance and do startup nomount again. It should succeed.

bob@example> cd $ORACLE_HOME/bin
bob@example> sqlplus / as sysdba
SQL> startup nomount
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2142976 bytes
Variable Size             159386880 bytes
Database Buffers           50331648 bytes
Redo Buffers                5296128 bytes
SQL> 

Now let see what processes it created.

bob@example> ps -ef | grep mysid
bob  16097     1  0 10:41 ?        00:00:00 ora_pmon_mysid
bob  16099     1  0 10:41 ?        00:00:00 ora_vktm_mysid
bob  16103     1  0 10:41 ?        00:00:00 ora_diag_mysid
bob  16105     1  0 10:41 ?        00:00:00 ora_dbrm_mysid
bob  16107     1  0 10:41 ?        00:00:00 ora_psp0_mysid
bob  16111     1  0 10:41 ?        00:00:00 ora_dia0_mysid
bob  16113     1  0 10:41 ?        00:00:00 ora_mman_mysid
bob  16115     1  0 10:41 ?        00:00:00 ora_dbw0_mysid
bob  16117     1  0 10:41 ?        00:00:00 ora_lgwr_mysid
bob  16119     1  0 10:41 ?        00:00:00 ora_ckpt_mysid
bob  16121     1  0 10:41 ?        00:00:00 ora_smon_mysid
bob  16123     1  0 10:41 ?        00:00:00 ora_reco_mysid
bob  16125     1  0 10:41 ?        00:00:00 ora_mmon_mysid
bob  16127     1  0 10:41 ?        00:00:00 ora_mmnl_mysid
bob  16128 15679  0 10:41 ?        00:00:00 oraclemysid (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

As you can see it creates a set of background processes such as PMON, MMAN, SMON, DBWn, LGWR, etc. We will go over Oracle background processes in a future post. For now, it is suffice to know that there are many background processes involved to get an Oracle instance up and running. This also creates a shared memory area in your OS (not shown here).

We still don't have a database. Next, we are creating one.

SQL> create database;

Database created.

The above statement creates and opens the database in read/write mode.

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

Note that we used most simplest create database command. It creates the files at default locations. You can specify where to store data, control, log, redo files.

You can query the following views to find the default locations in our case:

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/scratch/bob/app/bob/product/11.1.0/db_1/dbs/cntrlmysid.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/scratch/bob/app/bob/product/11.1.0/db_1/dbs/log1mysid.dbf
/scratch/bob/app/bob/product/11.1.0/db_1/dbs/log2mysid.dbf

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/scratch/bob/app/bob/product/11.1.0/db_1/dbs/dbs1mysid.dbf
/scratch/bob/app/bob/product/11.1.0/db_1/dbs/dbx1mysid.dbf
/scratch/bob/app/bob/product/11.1.0/db_1/dbs/dbu1mysid.dbf

I hope you got a good hang of what an Oracle database and an instance are.

PS: In order master advanced concepts, know the basics (the foundation) very well. The goal of these posts is to give you a good foundation so that you can figure out advanced techniques yourself.