Oracle
Database Architecture 11g :
Oracle
Instance: Oracle
Instance means to access an Oracle database, always opens one and only one
database and consists of memory structures and background process.
Oracle
server: a DBMS that provides an open, comprehensive, integrated approach
to information management, Consists of an Instance and a database.
Oracle database: Oracle database a collection of data that is treated as a unit,
Consists of Datafiles, Control files, Redo log files. (optional param file,
passwd file, archived log)
Instance memory Structures:
System Global Area (SGA): Allocated at instance startup, and is a
fundamental component of an Oracle Instance.
SGA Memory structures: Includes Shared
Pool, Database Buffer Cache, Redo Log Buffer among others.
Shared Pool : Consists of two key performance-related memory structures Library
Cache and Data Dictionary Cache.
Library Cache: Stores information about the most recently used SQL and PL/SQL
statements and enables the sharing of commonly used statements.
Data Dictionary Cache : Stores collection of the most recently used definitions in the
database Includes db files, tables, indexes, columns etc. Improves perf. During
the parse phase, the server process looks at the data dictionary for
information to resolve object names and validate access.
Database Buffer Cache: Stores copies of data blocks that have been retrieved from the
datafiles. Everything done here.
Redo Log Buffer : Records all changes made to the database data blocks, Primary
purpose is recovery. Redo entries contain information to reconstruct or redo
changes.
User process: Started at the time a database User requests connection to the
Oracle server. requests interaction with the Oracle server, does not interact
directly with the Oracle server.
Server process: Connects to the Oracle Instance and is Started when a user establishes
a session.
fulfills
calls generated and returns results.
Each
server process has its own nonshared PGA when the process is started.
Server
Process Parses and run SQL statements issued through the application, Reads
necessary data blocks from datafiles on disk into the shared database buffers
of the SGA, if the blocks are not already present in the SGA and Return results
in such a way that the application can process the information.
In some
situations when the application and Oracle Database operate on the same
computer, it is possible to combine the user process and corresponding server
process into a single process to reduce system overhead.
Program Global Area (PGA):
Memory
area used by a single Oracle server process.
Allocated
when the server process is started, deallocated when the process is terminated
and used by only one process.
Used to
process SQL statements and to hold logon and other session information.
Background processes:
Started
when an Oracle Instance is started.
Background
Processes Maintains and enforces relationships between physical and memory
structures
There are
two types of database processes:
1. Mandatory background processes
2. Optional background processes
Mandatory background processes:
– DBWn,
PMON, CKPT, LGWR, SMON
Optional background processes:
– ARCn,
LMDn, RECO, CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn
DBWn writes when:
•
Checkpoint occurs
• Dirty
buffers reach threshold
• There
are no free buffers
• Timeout
occurs
• RAC
ping request is made
•
Tablespace OFFLINE
•
Tablespace READ ONLY
• Table
DROP or TRUNCATE
•
Tablespace BEGIN BACKUP
Log Writer (LGWR) writes:
• At
commit
• When
1/3rd full
• When
there is 1 MB of redo
• Every 3
seconds
• Before
DBWn writes
System Monitor (SMON) Responsibilities:
•
Instance recovery
– Rolls
forward changes in redo logs
– Opens
database for user access
– Rolls
back uncommitted transactions
•
Coalesces free space
•
Deallocates temporary segments.
Process Monitor (PMON) Cleans up after failed processes by:
• Rolling
back the transaction
•
Releasing locks
•
Releasing other resources
•
Restarting dead dispatchers
Checkpoint (CKPT) Responsible for:
•
Signaling DBWn at checkpoints
•
Updating datafile headers with checkpoint information
•
Updating control files with checkpoint information
Archiver (ARCn)
•
Optional background process
•
Automatically archives online redo logs when ARCHIVELOG mode is set
•
Preserves the record of all changes made to the database