Infrastructure at your Service

David Hueber

Oracle Basics (1): Oracle Database vs Oracle Instance

While beginning to work with Oracle, one of the most common confusions is between Oracle Database and Oracle Instance. The term Oracle Database is mainly used to designate the whole Oracle RDBMS architecture. However, taking a closer look at Oracle RDBMS Server, we can see that it is composed of two entities, the Database and the Instance. What is really the difference between them? Let’s find out.

Starting to work with Oracle is also facing quite a lot of new concepts. Most of the people beginning with Oracle have the same questions. So did I some years ago :roll:. Therefore I decided to write some short articles, aiming to answer some basic questions in a short and simple way.

For this first part, let’s come back to our Oracle Database and Instance. As a picture is worth a thousand words, take a look at the diagram below.
database_instance
As we can see above, the Oracle RDBMS architecture spreads across 2 places: files and memory (including processes). That’s where we find the separation between the Oracle Instance and the Oracle Database.

The Oracle Instance includes the memory part and the processes of the RDBMS Server, while the Oracle Database includes all the physical files belonging to the server  (more precisely on the storage system).

What exactely is the Instance?

Now that we have seen the difference between the Instance and the Database, let’s have a look at what exactly the Oracle Instance is.

While working with an Oracle RDBMS Server, the instance will provide the interface between the user and the data he wants to manipulate. To do so, the Instance provides processes for client communication as well as for data access. However, these processes, also called background processes, would not be enough to provide an efficient service and the RDBMS functionalities such as the ACID (Atomicity, Consistency, Isolation, and Durability) principle. Therefore, the instance is also composed of several memory buffers and caches.

Basically an instance can be divided in 3 part:

  • System Global Area (SGA)
  • Program Global Area (PGA)
  • Background processes

instance_structure2

SGA structure

The SGA is a shared memory structure allocated at instance startup and released on shutdown. At a minimum, the SGA will contain the 3 following data structures:

  • Database Buffer Cache

The database buffer cache is Oracle’s working area. When manipulating data (update, insert…) Oracle usually does not access and modify the data on disk. The data blocks are first copied in the database buffer cache and can then be accessed and/or modified in it.

  • Redo Log Buffer

The log buffer is a short term staging area for the change vectors before they are written in the Redo Logs (see database part). A change vector is a modification applied to data. All Redo information are written to disk when a session commits its changes (and under some other conditions). The DML (Data Manipulation Language) statements (insert, update, delete) of all sessions are kept in the Redo Log Buffer to avoid I/O contention and sessions to wait for accessing the Redo Log files (concept of “piggy backing”).

  • Shared Pool

The shared pool is the most complicated SGA structure. It is divided in several substructures, which are internally managed by the Oracle Server. Basically, the shared pool’s structures contain information like recently executed SQL or recently used objects structures.

Beside the 3 mandatory pools, the SGA can also contain some optional structures:

  • Stream Pool

The Stream Pool is reserved for the Oracle Stream functions. More information about Oracle Streams can be found here.

  • Java Pool

The Java Pool is only required if the application using the database is going to use Java stored procedures within the database.

  • Large Pool

The Large Pool is an optional area, which will automatically be used by several processes which would otherwise take memory from the shared pool. The Large Pool will be used for large operations like shared server processes or RMAN operations.

PGA structure

Unlike the SGA which is available for all processes, the PGA is a piece of private memory dedicated to one session. Note that each background process has its own PGA too.
The PGA is allocated at session creation and released once the session is killed.

Background processes

In addition to the memory structure, the instance has also several processes with dedicated usage. Oracle has a long list of background processes, but the main ones are the following:

  • SMON

SMON is mainly in charge of mounting and opening the database. It mounts the database by locating and validating the control files and opens the database by doing so with the data files and redo logs. In addition, SMON is also in charge of several housekeeping tasks like liberating free space in the data files. If the instance crashed during the last utilization, the SMON is charged to recover the database (rollforward and rollback process).

  • PMON

A user session is composed of a user process, on client side, and a server process on server side. PMON is in charge of monitoring all server processes. If it detects an unexpectedly terminated session, it will take care of killing the server process, liberating the PGA memory and rolling the pending transaction back.

  • DBWn

Database Writer process manages the write access to the datafiles. It will write down the data blocks from the database buffer cache to disk. Depending on the load of the database, several DBWn processes can run in parallel.

  • LGWR

As DBWn, which is in charge of the I/O management with the datafiles, the LGWR manages them for the online redo logs. It streams the content of the log buffer to the online redo logs.

  • ARCn

The Archiver process copies the redo logs to a specified storage after each log switch as archived logfile. An instance can have up to 10 archiver processes. The archiver processes exists only if the database is in archive log mode.

  • CKPT

On a regular basis, the modified blocks are written on the datafiles by DBWn. These events are called checkpoints. The checkpoint process is responsible of instructing the DBWn at checkpoint and update the controlfiles and datafile headers with the most recent checkpoint.

What exactely is the Database?

The Oracle Database is the storage part of the RDBMS server. One differentiates between the physical and the logical storage.

The database is composed of 3 file types:

  • controlfile
  • online redo log
  • datafile

database_structure

Controlfiles

The controlfile is small but vital! It points to all other database elements like datafiles and online redo logs. It also contains iinformation to maintain the database integrity (System Change Number – SCN, timestamps,…) and all backup and recovery information, like latest archivelogs and database backups. For database security, the controlfile must be multiplexed. It can handle up to 8 copies.

Online Redo Logs

The online redo logs store all the change vectors that run on the database in a chronological chain. This information is necessary to reconstruct (=redo) all modifications done. It will be used in case of datafile or complete database recovery. An Oracle database runs at least with 2 online redo logs, but as for the controlfile, it is worth to multiplex them. The online redo logs are arranged in groups, of which the LGWR writes on circular basis. dbi services advices to use at least 3 groups with 2 members each. The point where the LGWR move from one group to another is called Log Switch. If the database is in archivelog mode, the current redo log is archived by ARCn processes at each log switch.

Datafiles

The datafiles are the repository of the data. An Oracle Database requires at least 2 datafiles, which have to be created at database creation. This is when the link between the logical storage and the physical storage is created. Datafiles are physical structures visible by the system administrators. Logically, the user data is stored in tablespaces, organized in segments, typically tables, indexes, and (binary or character) large objects. Datafiles can be renamed, resized, added, moved, or dropped at any time, but remember that some operations requires downtime.

I hope that this first introduction was clear enough. Have fun with Oracle! 😀

4 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

David Hueber
David Hueber

Chief Executive Officer (CEO), Principal Consultant