Oracle Concepts - RDBMS
Oracle Tips by Burleson Consulting
Oracle RDBMS Architecture
Oracle version 8i is an object-relational
database management system (ORDBMS). Oracle8i (actually 8.1.5
production release) expands upon the new features offered in Oracle8
and makes over 150 changes or additions to the Oracle tools. A
traditional RDBMS stores data in tables called relations. These
relations are two-dimensional representations of data where the rows,
called tuples in relational jargon, represent records, and the
columns, called attributes, are the pieces of information contained in
the record. Oracle8i provides new features in the object-oriented
extensions provided in Oracle8 as well as to the Oracle RDBMS. In an
object-relational database, columns can represent either a single
value (as in standard relational databases), a varray (a fixed number
of additional records), or a REF to a second table where a variable
amount of data can be stored. This takes the two-dimensional
relational view and adds a third dimension. In addition, in an
object-relational database, procedures known as methods can be tied to
the tables. Methods are above and beyond the old concept of triggers,
as we shall see later. In latter releases of Oracle8i, JAVA a new
object oriented language, can be used to create stored objects in an
Oracle consists of background processes,
files, and executables. Lets look at these items.
Oracle is more than just a collection of
programs that allow ease of data access. Oracle can be compared to an
operating system that overlays the operating system of the computer on
which it resides. Oracle has its own file structures, buffer
structures, global areas, and tunability above and beyond those
provided within the operating system. Oracle controls its own
processes, controls its own records and consistencies, and cleans up
Oracle as it exists on your system (with the
exception of DOS or OS/2) consists of executables, five to nine (or
more) detached processes, a global memory area, data files, and
maintenance files. It can be as small as a couple of megabytes, or as
large as a massive globe-spanning construction of gigabytes. A diagram
showing a typical Oracle8 and Oracle8i environment is shown in Figures
1 and 2; you may want to refer to these diagrams as you read the next
Figure 1: Oracle 8 Structures
Figure 2: Oracle8i Structures
On VMS, NT (threads), or UNIX there may be a
minimum of eight detached processes for Oracle7; for Oracle8i, this
jumps to nearly a dozen. Four of these are the base Oracle processes
and these processes are started every time Oracle is started up on a
system; the additional processes may be started if the database is
using archiving, uses TCPIP, or is being run in parallel and/or
distributed mode. The Oracle job queues, snapshot processes, advanced
queuing options, and callout processes all add to the process count.
These processes are listed below.
* DBWR—Database Writer --This process handles
data transfer from the buffers in the SGA to the database files.
* LGWR—Log Writer -- This process transfers
data from the redo log buffers to the redo log database files.
* SMON—System Monitor -- This process
performs instance recovery on instance startup and is responsible for
cleaning up temporary segments. In a parallel environment, this
process recovers failed nodes.
* PMON—Process Monitor -- This process
recovers user processes that have failed and cleans up the cache. This
process recovers the resources from a failed process.
* ARCH—Archiver Process -- This process is
active only if archive logging is in effect. It writes the redo log
data files that are filled into the archive log data files.
* RECO—Distributed Transaction -- This is an
Oracle7 process that resolves failed distributed processes.
* LCKn—Lock Process -- This process is used
for inter-instance locking in an Oracle7 parallel server environment.
* Dnnn—Dispatcher -- This process allows
multiple processes to share a finite number of Oracle7 servers. It
queues and routes process requests to the next available server.
* Snnn—Servers -- This Oracle7 process makes
all the required calls to the database to resolve a user’s requests.
It returns results to the Dnnn process that calls it.
* LISTENERTCPIP server -- If you are running
TCPIP, this process, known as the listener process, will be running as
well (only one per node).
* CKPxx -- This is the checkpoint process that
can be started to optimize the checkpoint operation for Oracle
* Snpxx -- These are snapshot process and job
queues. Thee can be up to 32 configured in 8i.
* EXTPROC -- These are the callout queues;
there will be one for each session performing callouts. It is hoped
that Oracle will multithread these processes or it could result in the
callout feature being unusable in a large multi-user environment.
* QMNn -- These are the Oracle queue monitor
processes (new for 8i). They monitor the queues used in the Oracle
advanced queuing (Oracle AQ). There can be up to 10 QMN processes.
These take the place of the single AQ_TXX process in previous
On multiuser-capable systems, each user
process may spawn several sub-processes depending on the type of
activities being done by that process. Depending on how Oracle is
configured, a single parallel query may start dozens of query slave
processes for a single user!
In Oracle datafiles are the physical
implementation of the logical concept of tablespaces. Each tablespace
contains segments which can be one of a specific set of segment types:
* Data (both normal and partitioned)
* Index (both normal and partitioned)
Each segment is made up of extents and each
extent is made from the finest level of granularity in the database,
Each datafile is allowed to sized either to 2,
4 or more gigabytes in size depending on operating system and patch
level of Oracle.
Datafile placement has grown fuzzy in the last
several years with the addition of RAID to the system administrators
toolkit. It used to be before the wide spread use of RAID that a
single disk platter could be assigned to handle a specific type of
segment (and hence their datafiles). Now with striping, plaiding and
other esoteric disk array arrangements it becomes harder and harder to
determine what segment type is located on which disk.
As a DBA it will be your responsibility to:
1. Ensure datafiles are properly sized
2. Ensure, to the best of your ability,
segment types are separated to ensure contention is realized
3. Ensure tablespaces are maintained to
prevent fragmentation and poor space utilization
Ensure datafiles are properly backed up to allow for proper recovery
in case of failure.
This is an excerpt from
the eBook "Oracle
DBA made Simple".
For more details on Oracle
database administration, see the "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam. It’s
only $19.95 when you buy it directly from the publisher