Exploring the Oracle Database Architecture
Objectives
After completing this lesson, you should be able to:
• List the major architectural components of Oracle
Database
• Explain the memory structures
• Describe the background processes
• Correlate the logical and physical storage structures
1-2
Oracle Database
The Oracle relational database management system
(RDBMS) provides an open, comprehensive, integrated
approach to information management
1-3
Connecting to a Server
Client Middle tier Server
Multitier architecture shown
1-4
Oracle Database Architecture: Overview
Instance
SMON PMON RECO Others
SGA Shared pool
Library
Database cache
Redo log
buffer
buffer
cache Data dictionary
cache
PGA
Server
DBWn CKPT LGWR ARCn
process
User Archived
process Control Online redo log files
Data files files log files
Database
1-6
Connecting to the Database
• Connection: Communication between a user process
and an instance
• Session: Specific connection of a user to an instance
through a user process
User Server
SQL> Select … process process
User
Session
Connection
Session
1-7
Interacting with an Oracle Database
Instance
User Server
process process SGA
Shared pool
Library
Database cache
Redo log
buffer
buffer
cache Data dictionary
cache
DBWn CKPT LGWR SMON PMON ARCn RECO Others
User
1-8
Oracle Database Server Structures
Instance
Memory structures SGA Shared pool
Library
Database cache
User Server Redo log
buffer
process process buffer
cache Data dictionary
cache
Processes DBWn CKPT LGWR SMON PMON ARCn RECO Others
Database
Storage structures
Control Online redo
Data files files log files
1 - 10
Oracle Database DB structures
→Memory
Memory Structures - Process
- Storage
Server Server Background
PGA PGA PGA
process 1 process 2 process
Data dictionary SGA
Shared
cache
SQL area
Library Other
cache
Redo log Shared pool
buffer
Free
Database buffer I/O buffer
memory
cache
Response Request
Java Streams queue queue
pool pool Large pool
1 - 11
Database Buffer Cache
• Is part of the SGA
• Holds copies of data blocks that are read from data files
• Is shared by all concurrent users
Instance
SGA Shared pool
Library
Database cache
Redo log
buffer
buffer
cache Data dictionary
cache
DBWn CKPT LGWR SMON PMON ARCn RECO Others
1 - 13
Redo Log Buffer
• Is a circular buffer in the SGA
• Holds information about changes made to the database
• Contains redo entries that have the information to redo
changes made by operations such as DML and DDL
Instance
SGA Shared pool
Library
Database cache
Redo log
buffer
buffer
cache Data dictionary
cache
DBWn CKPT LGWR SMON PMON ARCn RECO Others
1 - 14
Shared Pool
• Is a portion of the SGA
• Contains:
– Library cache
— Shared SQL area
– Data dictionary cache
– Control structures
Instance
SGA Shared
Shared pool
SQL area Data dictionary
Library cache
Database cache
Redo log
buffer
buffer
cache Data dictionary
cache Library Other
cache
DBWn CKPT LGWR SMON PMON ARCn RECO Others
1 - 15
Allocation and Reuse of Memory
in the Shared Pool
• Server process checks the shared pool to see if a
shared SQL area already exists for an identical
statement.
• Server process allocates a private SQL area on behalf of
the session.
Data dictionary
Shared
cache
Server SQL area
process
Library Other
cache
Shared
pool
1 - 17
Large Pool
Provides large memory allocations for:
• Session memory for the shared server and the Oracle
XA interface
• I/O server processes
• Oracle Database backup and restore operations
Database
Redo log
buffer Shared pool
buffer
cache
Free
I/O buffer
memory
Response Request
queue queue
Java Streams Large pool
pool pool Large pool
1 - 19
Java Pool
and Streams Pool
• Java pool memory is used in server memory for all
session-specific Java code and data in the JVM.
• Streams pool memory is used exclusively by Oracle
Streams to:
– Store buffered queue messages
– Provide memory for Oracle Streams processes
Java pool Streams pool
1 - 20
Process Architecture DB structures
- Memory
→ Process
• User process - Storage
– Is started when a database user or a batch process
connects to Oracle Database
• Database processes
– Server process: Connects to the Oracle instance and is
started when a user establishes a session
– Background processes: Are started when an Oracle
instance is started Instance
SGA Shared pool
Library
Database cache
Redo log
buffer
buffer
PGA cache Data dictionary
cache
User Server
process process Background processes
DBWn CKPT LGWR SMON PMON ARCn RECO Others
1 - 21
Process Structures
Server Server Server Server Server
Server n
processes
SGA
Shared pool
Library
Database
Redo log cache
buffer
buffer SGA
cache Data dictionary
cache
CKPT RECO PMON SMON DBWn LGWR ARCn Others
Oracle
background
processes
1 - 22
Database Writer Process (DBWn)
Writes modified (dirty) buffers in the database buffer cache
to disk:
• Asynchronously while performing other processing
• Periodically to advance the checkpoint
DBWn
Database buffer Database writer Data files
cache process
1 - 24
LogWriter Process (LGWR)
• Writes the redo log buffer to a redo log file on disk
• Writes:
– When a user process commits a transaction
– When the redo log buffer is one-third full
– Before a DBWn process writes modified buffers to disk
LGWR
Redo log buffer LogWriter process Redo log files
1 - 26
Checkpoint Process (CKPT)
• Records checkpoint information in
– Control file
– Each data file header
CKPT Control file
Checkpoint
process
Data files
1 - 28
System Monitor Process (SMON)
• Performs recovery at instance startup
• Cleans up unused temporary segments
Instance
SMON
System Monitor
process
Temporary
segment
1 - 29
Process Monitor Process (PMON)
• Performs process recovery when a user process fails
– Cleans up the database buffer cache
– Frees resources that are used by the user process
• Monitors sessions for idle session timeout
• Dynamically registers database services with listeners
PMON User
Failed user
process
Process Monitor
process
Database buffer
cache
1 - 30
Recoverer Process
• Used with the distributed database configuration
• Automatically connects to other databases involved in
in-doubt distributed transactions
• Automatically resolves all in-doubt transactions
• Removes any rows that correspond to in-doubt
transactions
RECO
Recoverer process In-doubt transaction
in database A in database B
1 - 31
Archiver Processes (ARCn)
• Copy redo log files to a designated storage device after
a log switch has occurred
• Can collect transaction redo data and transmit that data
to standby destinations
ARCn
Archiver process Copies of redo log Archive destination
files
1 - 32
Other Processes
• MMON: Performs manageability-related background
tasks
• MMNL: Performs frequent and lightweight
manageability-related tasks
• MMAN: Performs automatic memory management tasks
• CJQ0: Runs user jobs used in batch processing
• QMNx: Monitors the Streams Advanced Queuing
message queues
1 - 33
Server Process and Database
Buffer Cache
Buffers:
SGA
• Pinned
Server
process
Database • Clean
buffer
cache • Free or unused
• Dirty
DBWn
Database
Data files writer
process
1 - 35
Database Storage Architecture DB structures
- Memory
- Process
→ Storage
Control files Data files Online redo log files
Parameter file Backup files Archived redo log
files
Password file Alert log and trace files
1 - 36
Logical and Physical Database Structures
Logical Physical
Database
Schema Tablespace Data file
Segment
Extent
Oracle data
OS block
block
1 - 38
Tablespaces and Data Files
• Tablespaces consist of one or more data files.
• Data files belong to only one tablespace.
Data file 1 Data file 2
USERS tablespace
1 - 40
SYSTEM and SYSAUX Tablespaces
• The SYSTEM and SYSAUX tablespaces are mandatory
tablespaces that are created at the time of database
creation. They must be online.
• The SYSTEM tablespace is used for core functionality
(for example, data dictionary tables).
• The auxiliary SYSAUX tablespace is used for additional
database components (such as the Enterprise Manager
Repository).
1 - 41
Segments, Extents, and Blocks
• Segments exist in a tablespace.
• Segments are collections of extents.
• Extents are collections of data blocks.
• Data blocks are mapped to disk blocks.
Segment Extents Data Disk
blocks blocks
1 - 42
Database Architecture:
Summary of Structural Components
• Memory structures:
– System Global Area (SGA): Database buffer cache, redo
buffer, and various pools
– Program Global Area (PGA)
• Process structures:
– User process and server process
– Background processes: SMON, PMON, DBWn, CKPT,
LGWR, ARCn, and so on
• Storage structures:
– Logical: Database, schema, tablespace, segment, extent,
and Oracle block
– Physical: Data files, control files, and redo log files
1 - 43
Summary
In this lesson, you should have learned how to:
• List the major architectural components of Oracle
Database
• Explain the memory structures
• Describe the background processes
• Correlate the logical and physical storage structures
1 - 44
Practice 1: Overview
This is a paper practice with questions about:
• Database architecture
• Memory
• Processes
• File structures
1 - 45
