Exploring the Oracle Database Architecture - PowerPoint Presentation

Slide1

Exploring the Oracle Database Architecture

Slide2

Objectives

After completing this lesson, you should be able to:

List the major architectural components of Oracle DatabaseExplain the memory structuresDescribe the background processes Correlate the logical and physical storage structures

Slide3

Oracle Database

The Oracle relational database management system (RDBMS) provides an open, comprehensive, integrated approach to information management

Slide4

Connecting to a Server

Client

Middle tier

Server

Multitier architecture shown

Slide5

Notes only

Slide6

Database

Data files

Online redo log files

Control files

Oracle Database Architecture: Overview

Database

buffer

cache

Shared pool

Data dictionary

cache

Library

cache

PMON

SMON

Others

Server

process

PGA

Archived

log files

User

process

Instance

RECO

ARC

n

SGA

DBW

n

Redo log

buffer

LGWR

CKPT

Slide7

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

SQL> Select …

Session

Connection

User

User

process

Server

process

Session

Slide8

Interacting with an Oracle Database

User

Database

buffer

cache

Shared pool

Data dictionary

cache

Library

cache

PMON

SMON

Others

Instance

RECO

ARC

n

SGA

DBW

n

Redo log

buffer

LGWR

CKPT

User

process

Server

process

Slide9

Notes Only

Slide10

Database

Oracle Database Server Structures

PMON

SMON

Others

Data files

Online redo log files

Control files

Instance

RECO

ARC

n

DBW

n

LGWR

CKPT

Storage structures

User

process

Server

process

Memory structures

Processes

Database

buffer

cache

Shared pool

Data dictionary

cache

Library

cache

SGA

Redo log

buffer

Slide11

Oracle Database

Memory Structures

DB structures



Memory

- Process

- Storage

SGA

Database buffer

cache

Redo log buffer

Java pool

Streams pool

Shared pool

Large pool

PGA

PGA

PGA

Background

process

Server

process 1

Server

process 2

Shared

SQL area

Library cache

Data dictionary cache

Other

I/O buffer

Response queue

Request queue

Free memory

Slide12

Notes Only

Slide13

Database Buffer Cache

Is part of the SGA

Holds copies of data blocks that are read from data filesIs shared by all concurrent users

PMON

SMON

Others

Instance

RECO

ARC

n

DBW

n

LGWR

CKPT

Database

buffer

cache

Shared pool

Data dictionary

cache

Library

cache

SGA

Redo log

buffer

Slide14

Redo Log Buffer

Is a circular buffer in the SGA

Holds information about changes made to the databaseContains redo entries that have the information to redo changes made by operations such as DML and DDL

PMON

SMON

Others

Instance

RECO

ARC

n

DBWn

LGWR

CKPT

Database

buffer

cache

Shared pool

Data dictionary

cache

Library

cache

SGA

Redo log

buffer

Slide15

Shared Pool

Is a portion of the SGA

Contains:Library cache

Shared SQL area

Data dictionary cache

Control structures

PMON

SMON

Others

Instance

RECO

ARC

n

DBW

n

LGWR

CKPT

Shared

SQL area

Library cache

Data dictionary cache

Other

Database

buffer

cache

Shared pool

Data dictionary

cache

Library

cache

SGA

Redo log

buffer

Slide16

Notes Only

Slide17

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.

Server

process

Shared pool

Shared

SQL area

Library cache

Data dictionary cache

Other

Slide18

Notes Only

Slide19

Large Pool

Provides large memory allocations for:

Session memory for the shared server and the Oracle XA interfaceI/O server processes

Oracle Database backup and restore operations

Large pool

I/O buffer

Response queue

Request queue

Free memory

Java pool

Large pool

Shared pool

Database

buffer

cache

Redo log

buffer

Streams pool

Slide20

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

Slide21

Process Architecture

User process

Is started when a database user or a batch process connects to Oracle DatabaseDatabase processesServer process: Connects to the Oracle instance and is started when a user establishes a sessionBackground processes: Are started when an Oracle instance is started

DB structures

- Memory



Process

- Storage

PMON

SMON

Others

Instance

RECO

ARC

n

DBW

n

LGWR

CKPT

PGA

User

process

Server

process

Background processes

Database

buffer

cache

Shared pool

Data dictionary

cache

Library

cache

SGA

Redo log

buffer

Slide22

Process Structures

Server

n

processes

SGA

Oracle background processes

PMON

SMON

Others

RECO

ARC

n

DBW

n

LGWR

CKPT

Server

Server

Server

Server

Server

Database

buffer

cache

Shared pool

Data dictionary

cache

Library

cache

SGA

Redo log

buffer

Slide23

Notes Only

Slide24

Database Writer Process (DBW

n

)

Writes modified (dirty) buffers in the database buffer cache to disk:

Asynchronously while performing other processing

Periodically to advance the checkpoint

Database buffer cache

Database writer process

Data files

DBW

n

Slide25

Notes Only

Slide26

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 fullBefore a DBW

n

process writes modified buffers to disk

Redo log buffer

LogWriter process

Redo log files

LGWR

Slide27

Slide28

Checkpoint Process (CKPT)

Records checkpoint information in

Control file Each data file header

Checkpoint process

Data files

Control file

CKPT

Slide29

System Monitor Process (SMON)

Performs recovery at instance startup

Cleans up unused temporary segments

Instance

Temporary segment

System Monitor process

SMON

Slide30

Process Monitor Process (PMON)

Performs process recovery when a user process fails

Cleans up the database buffer cacheFrees resources that are used by the user processMonitors sessions for idle session timeout

Dynamically registers database services with listeners

Process Monitor process

Database buffer cache

Failed user process

User

PMON

Slide31

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

Recoverer process

in database A

In-doubt transaction

in database B

RECO

Slide32

Archiver Processes (ARC

n

)Copy redo log files to a designated storage device after a log switch has occurredCan collect transaction redo data and transmit that data to standby destinations

Archiver process

Archive destination

Copies of redo log files

ARC

n

Slide33

Other Processes

MMON: Performs manageability-related background tasks

MMNL: Performs frequent and lightweight manageability-related tasksMMAN: Performs automatic memory management tasksCJQ0: Runs user jobs used in batch processing

QMNx: Monitors the Streams Advanced Queuing message queues

Slide34

Notes Only

Slide35

Server Process and Database

Buffer Cache

Buffers:PinnedCleanFree or unused

Dirty

Database

writer process

Database

buffer

cache

SGA

Data files

DBW

n

Server

process

Slide36

Database Storage Architecture

Online redo log files

Password file

Parameter file

Archived redo log files

Control files

Data files

Alert log and trace files

Backup files

DB structures

-

Memory

- Process



Storage

Slide37

Notes Only

Slide38

Logical and Physical Database Structures

Database

Logical

Physical

Tablespace

Data file

OS block

Segment

Extent

Oracle data

block

Schema

Slide39

Notes Only

Slide40

Tablespaces and Data Files

Tablespaces consist of one or more data files.

Data files belong to only one tablespace.

USERS

tablespace

Data file 1

Data file 2

Slide41

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).

Slide42

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 blocks

Disk blocks

Slide43

Database Architecture:

Summary of Structural Components

Memory structures:System Global Area (SGA): Database buffer cache, redo buffer, and various poolsProgram Global Area (PGA)Process structures:

User process and server process

Background processes: SMON, PMON, DBW

n

, CKPT, LGWR, ARC

n

, and so on

Storage structures:

Logical: Database, schema, tablespace, segment, extent, and Oracle block

Physical: Data files, control files, and redo log files

Slide44

Summary

In this lesson, you should have learned how to:

List the major architectural components of Oracle DatabaseExplain the memory structures Describe the background processes Correlate the logical and physical storage structures

Slide45

Practice 1: Overview

This is a paper practice with questions about:

Database architectureMemoryProcessesFile structures