Instance Architecture   «Prev  Next»

Lesson 3 Instance Overview
Objective Identify the processes and memory structures that make up an Oracle instance.

Components that make up an Oracle Instance

The diagram below illustrates the processes and memory structures that make up an Oracle instance. When you move your mouse over a box, you will see a brief description of what each particular process does. The square boxes represent processes, while the circle in the center represents an area of memory that they all share. Boxes drawn in bold are processes that are mandatory, and that will always be running when a database is open. The other processes are optional, and whether or not they will exist depends on the specific mix of Oracle features that you are using.

A memory area containing the database buffer cache, the redo log buffer, frequently used SQL statements, and other items critical to the operation of a database
Oracle Instance Components: 1) SGA, 2) DBW0 3) LGWR 4) SMON 5) PMON 6) RECO 7) SNP0
  1. SGA - System Global Area: A memory area containing the database buffer cache, the redo log buffer, frequently used SQL statements, and other items critical to the operation of a database.
  2. DBW0 - Database Writer: Writes modified blocks back to the datafiles.
  3. LGWR - Log Writer: Writes the database's redo log.
  4. SMON - System Monitor: Performs crash recovery, coalesces free space, etc.
  5. PMON - Process Monitor: Cleans up after user processes.
  6. RECO - Recoverer: Resolves distributed transactions.
  7. SNP0 - Snapshot: Runs the job queues used, among other things, for snapshot refreshes.
  8. D000 - Dispatcher: Used with the multi-threaded server configuration.
  9. Shared Server Process - Shared Server Process: Used with the multi-threaded server configuration, and not considered part of the instance.
  10. QMN0 - Queue Monitor: Used with Oracle Advanced Queueing.
  11. LCK0 - Lock: Used with parallel server for inter-instance locking.
  12. CKPT - Checkpoint: Initiates and controls the checkpoint process.
  13. ARC0 - Archiver: Copies filled redo log files to the archive log destination.
SGA - System Global Area: A memory area containing the database buffer cache, the redo log buffer, frequently used SQL statements, and other items critical to the operation of a database

Database Instance Configurations

Oracle Database runs in either a single-instance configuration or an Oracle Real Application Clusters (Oracle RAC) configuration. These configurations are mutually exclusive. In a single-instance configuration, a one-to-one relationship exists between the database and a database instance as shown in figure 4-3.1.
Single Instance Database
Figure 4-3.1: Single Instance Database

In Oracle RAC, a one-to-many relationship exists between the database and database instances. The following figure 4-3.2 shows database instance configuration for RAC.
Oracle RAC Database
Figure 4-3.2: Oracle RAC Database

Whether in a single-instance or Oracle RAC configuration, a database instance is associated with only one database at a time. You can start a database instance and mount (associate the instance with) one database, but not mount two databases simultaneously with the same instance

Simultaneous Processes
You will notice that several of the process names end with one or more zeros. DBW0 is one example. An Oracle instance can be configured to have several such processes running simultaneously. For example, if your database experiences a high volume of updates,, you might configure the instance to run with two database writers, which would be named DBW0 and DBW1. This generally only makes sense when you have multiple CPUs to run those processes. The procedures for displaying a list of running processes are different, and depend on whether you are using


Listing Processes on a UNIX system

You can use the UNIX ps command to generate a list of running Oracle processes. The way this is generally done is to use
ps -ef

to see an extended process status listing, and then grepping for the processes of interest. For example, it's very common to run multiple instances on the same server, and if you are not sure which instances you have running, you can find out by grepping for database writers. Every instance has at least one database writer, so you are sure not to miss any Here's an example:
$ ps -ef | grep dbw0
oracle 2564   1  2 07:00:01 ?     0:05 ora_dbw0_test
oracle 2588   1  2 07:00:05 ?     0:05 ora_dbw0_DEVL
oracle 1387   1  0  Mar 25  ?     0:17 ora_dbw0_orcl
oracle 2576   1  1 07:00:03 ?     0:06 ora_dbw0_SEED
oracle 3351 3336 1 17:19:02 ttyp2 0:00 grep dbw0

Notice the process names. The first part is always “ora.” The second part identifies the specific Oracle process, dbw0, lgwr, and so on. The third part is the SID , which identifies the instance. This naming convention enables you to have multiple instances, or databases, on one computer. To see a list of all processes running for an instance, just grep for the SID. For example:
$ ps -ef | grep DEVL
  oracle  2592   1  0 07:00:05 ?  0:00 ora_smon_DEVL
  oracle  2590   1  0 07:00:05 ?  0:03 ora_lgwr_DEVL
  oracle  2588   1  0 07:00:05 ?  0:05 ora_dbw0_DEVL
  oracle  2586   1  0 07:00:05 ?  0:03 ora_pmon_DEVL
  oracle  2594   1  0 07:00:05 ?  0:00 ora_reco_DEVL
  oracle  2597   1  0 07:00:05 ?  0:03 ora_arc0_DEVL

If you want to see all the Oracle processes period, you can grep for the first part of the name: “ora.”


Look at Oracle Processes under Windows

When Oracle runs on Windows, Oracle implements the various processes as threads running under one Oracle service. You can look at the list of services, and that will tell you how many Oracle instances you have running. To do this, use the Start menu, and go to
Start->Settings->Control Panel. 

When the Control Panel opens, double-click on the Services icon. You should see a list of services similiar to that in the example below.

Control Panel
List of Services 1) Network DDE 2) Network DDE DSDM 3) NTLM Security Support Provider
1) Service, 2) Status, 3) Startup
List of Services a) Network DDE b) Network DDE DSDM c) NTLM Security Support Provider

The Oracle processes, since they start with “o,” are usually far enough down in the list that you will need to scroll down in order to see them. The convention used for naming Oracle services is OracleServiceservice_name, where service_name represents the instance name. In the example just shown, you can see a service named OracleServiceORCL. That tells you that you have an instance named ORCL running on the server. With the release of Oracle, Oracle provides a utility known as the Oracle Administration Assistant for Windows. You can use this utility to see a detailed listing of the threads running within each service. To do this, first start the assistant. You will find it under
							 
Start->Programs->Oracle - OraHome1->Database Administration
->Oracle Administration Assistant for Windows Server.
After it starts, select the instance that you are interested in from the list, and you will then see a screen like this:

Assistant: Process information for coin  The list display information about Oracle threads.
Assistant: Process information for coin-> The list display information about Oracle threads.

To run the Oracle Administration Assistant for Windows NT, you must have Internet Explorer installed on your PC, and must have the Microsoft Management Console installed as well.

When an instance is started, Oracle Database allocates a memory area and starts background processes. The memory area stores information such as the following:
  1. Program code
  2. Information about each connected session, even if it is not currently active
  3. Information needed during program execution, for example, the current state of a query from which rows are being fetched
  4. Information such as lock data that is shared and communicated among processes
  5. Cached data, such as data blocks and redo records, that also exists on disk

Oracle Process Instance - Quiz

Before going on, click the Quiz link below to take this quiz and your knowledge of the process abbreviations.
Oracle Process Instance - Quiz

SEMrush Software