One Minute Oracle SAP Database Administrator DBA: Server Architecture – Database Instance

One Minute Oracle SAP Database Administrator DBA: Server Architecture – Database Instance

Oracle Server Architecture

One of the first things that should be understood about the Oracle RDBMS (Relational Database Management System) is the difference between the terms: “database” and the “instance.” This article will discuss the term “instance.” Briefly, the term instance refers to the processes running in memory that go away when the database is shutdown. This is different from the database files that stay on the operating system file systems, whether the database is running or not.

Oracle Instance

The Oracle instance is comprised of a shared memory – SGA (System Global Area), and a set of operating system processes and memory structures that manage database files.

The SGA is comprised of 3 main areas in memory:

  • Shared Pool, that contains various structures for running SQL and dependency tracking
  • Buffer Cache, that contains images of data blocks read from the database
  • Log Buffer, that’s used to store redo information before it’s flushed out to disk

Instance Background Processes:

The below are the main (there can be many more) UNIX processes that are started and remain running whenever the database is up:

  • PMON, the process monitor, periodically checks for processes that need cleaning up
  • SMON, the system monitor, coalesces free space within the database and checks that all the required instance processes are active
  • DBWR, the database writer (DBWn if multiple processes) writes data blocks back out to disk from the buffer cache
  • LGWR, the log writer (LGWn if multiple processes) writes redo information to disk from the log buffer
  • CKPT, updates file headers during a checkpoint
  • ARCH, is an optional process that copies redo information into an archive area so changes can be reapplied during recovery, if the database is in archive log mode

A common UNIX command run to see the Oracle background processes (i.e. if the database instance is running) is:

ps -ef | grep ora

This command output below shows the main processes and some other typical processes seen in a running, single instance SAP Oracle database. In this case, the database SID (System Identifier) name is “CLQ.” SAP database names, or SIDs, tend to be 3 characters long. The middle section is the abbreviation of the processes:

ora_mman_CLQ

ora_arc0_CLQ

ora_arc1_CLQ

ora_gen0_CLQ

ora_dbrm_CLQ

ora_vktm_CLQ

ora_dia0_CLQ

ora_diag_CLQ

ora_smco_CLQ

ora_pmon_CLQ

ora_psp0_CLQ

ora_dbw0_CLQ

ora_dbw1_CLQ

ora_lgwr_CLQ

ora_ckpt_CLQ

ora_smon_CLQ

ora_reco_CLQ

ora_mmon_CLQ

ora_mmnl_CLQ

ora_arc2_CLQ

ora_arc3_CLQ

ora_qmnc_CLQ

ora_q002_CLQ

ora_q000_CLQ

ora_w000_CLQ

ora_w001_CLQ

ora_vkrm_CLQ

ora_cjq0_CLQ

Congratulations!You have now learned one part of the Oracle Server Architecture: what is meant by the term “instance.” This short treatment is not meant to cover the topic in detail, but is meant as an introduction, and an enjoyable, painless way toward mastering a complex topic. You should next learn about the Oracle database parameter files.


Original Source

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*