Oracle Basic Concepts and Terms

A top-down View

Oracle Instance

An Oracle Server allows clients and administrators to connect to one or more instances.  The RAM memory associated with the server is known as the Program Global Area (or PGA).

A Database instance is composed of the SGA (the System Global Area) and various background processes that Oracle uses to serve the database.

Each instance is identified by a service name (or system identifier (or SID) which was used in older Oracle versions).  The service name identifies the connection information needed (other than the host name) for a client to use to connect to the instance.  When connecting to an instance the client supplies this name as the Host String.  The service names and their related information are stored in a file called tnsnames.ora.

Note many DBAs use the terms instance and database interchangeably.  But strictly speaking in Oracle a database refers only to the data that is stored on a disk.

The SGA is the RAM managed by an Oracle instance, which contains per-process memory and shared (global) memory.  The SGA is used to manage the interaction between the processes that make up an instance and the database itself.  At any given moment the SGA may contain rollback segment blocks, SQL statements, cached data segment blocks, and multi-threaded server information.

The processes that make up an instance are:

SMON
(System Monitor) handles recovery.
PMON
(Process Monitor) handles cleanup tasks after user processes terminate.
DBW#
(DataBase Writer, a.k.a. DBWR) writes modified RAM buffers (blocks) back to the datafiles.
LGWR
(LoG WriteR) writes redo log buffers to the redo log files every so often.
CKPT
(ChecKPoinT process) monitors the data blocks in the SGA and signals the DBWR process when it is time to update the datafiles.  Each such checkpoint is assigned a system change number (or SCN).
RECO
(RECOverer Process) Used to synchronize changes between different database instances, when using a distributed database.
ARC#
(ARChiver Process) Used when running Oracle in ARCHIVELOG mode, to have the server copy the redo log files to a separate location (outside of the database).
D#
(Dispatcher Process) assigns connections (user processes) to server processes, when Oracle is running is shared server mode.
LMS
(Lock Manager Server Process) controls locking between databases when the Oracle server is part of a cluster (or Oracle Real Application Cluster).  This allows updates that affect multiple databases.
CJQ#
(Job Queue Coordinator Process, a.k.a. SNP#) Runs jobs submitted in the background (a.k.a. batch jobs), which are stored in the JOB$ table, by creating a J# process for each job.
others
Other processes may be present, depending on the version of Oracle you have and what setup you chose.

The SMON, PMON, DBWR, LGWR, and CKPT processes are required.  The others may or may not be used on any given server, depending on the configuration.

User Connections and Database Instance States

To access a database, the instance is STARTed using the Oracle Server Manager or the Oracle Enterprise Manager (the OEM).  Once the instance is STARTed (a.k.a. the nomount state), the database is then MOUNTed, and then it can be OPENed.  Users can then CONNECT to the OPEN instance in order to access the data.  (When the database instance is SHUTDOWN it enters a CLOSED state.)

When a user CONNECTs to an Oracle server, a user process is created at the user's host that handles the communication to the Oracle server.  When a connection request is received, the server creates a server process.  This is known as a dedicated server.  For systems that support multi-threading efficiently an alternative is to use a single server process.  This is known as a shared server.  In this case it is a dispatcher process that assigns connections to server processes.

Normally there is one instance for each database.  However when using a Real Application Cluster or RAC (formally known as an Oracle Parallel Server or OPS) a single database may be MOUNTed and OPENed on multiple instances.

Oracle Instance Structures

When accessing an Oracle server a user generally only cares about the logical structure.  However a DBA is concerned with both the logical views of the instance as well as the physical structure of the database.

Logical Versus Physical Oracle Database Structures
    Logical Structure     Physical Implementation
 Instance Database
 Tablespace
Schema
 Datafile
 Object Segment
 Extent Block

An instance is composed of one or more tablespaces.  Each tablespace holds and manages the memory for one or more objects such as tables.  A tablespace is thus the logical view of a database as a collection of objects.  Each tablespace is directly associated with one or more datafiles.

Tables are objects that represent a collection of similar data.  Tables and other objects that represent data are composed of one or more extents, which are contiguous "chunks" of memory.  As an object grows and shrinks (as data is added and removed), the number of extents can grow and shrink as well.  (Not all extents are the same size.  This is controlled by the DBA.)  Tables are composed of rows (or records) and columns (or attributes).

An object in Oracle is any "thing" that can be owned by some user (i.e., belong to some collection of objects known as a schema).  Examples include tables, indexes, views, sequences, ...  A tablespace may be composed of objects from a number of different schemas.

Each object that relates directly to data in the database (e.g., tables and indexes) is stored in the datafiles for that database in a segment.  Some objects (e.g., views, sequences, stored procedures, synonyms) are not stored in their own segments.  However segment is sometimes used synonymously with object.  In addition there are three special types of segments that do not contain objects:

Segments are composed of one or more extents.  In turn extents are composed of a whole number of blocks, which is the smallest unit of memory managed by Oracle.  Choosing correct block and extent (and SGA) sizes is an important part of performance tuning an Oracle server.

Oracle Files

The physical structure of Oracle uses four types of files:

Filenames in Oracle usually obey the following naming convention: "<prefix><number>.<extension>", where the common extensions and prefixes are: