Dba Oracle Architecture

Instance = SGA Memory and Background processes,  A Database = physical files (data, redo, and control Processes Memory Structures

Files

Background Processes

Background Processes SMON

SNPn

LCKn

PMON DBWR

Shared Pool

RECO

Snnn

SGA

Database Buffer Cache

LGWR

PMON (Process Monitor) • • •

Cleans up abnormally terminated connections. Rolls back uncommitted transactions. Releases locks held by a terminated process.

Dnnn

Redo Log  Buffer

Pnnn

CKPT

ARCH

• •

Frees SGA resources allocated to the failed process. Restarts failed shared server and dispatcher processes.

SMON (System Monitor) • • •

Performs automatic instance recovery. Reclaims space used by temporary segments no longer in use. Merges contiguous areas of free space in the datafiles.

CKPT (Checkpoint Process) Updates the database status information after changes are permanently written to the database file from the buffer cache.

An Oracle Database

Datafiles

Control Files

Redo Log Files

Database file -Data files -Redo log files -Control files -Parameter file -Password file -Archived redo log files

Control Files • • •

All necessary database files and log files are identified in the control file. The name of the database is stored in the control file. A control file is required to mount, open, and access the database.

• • •

Synchronization information needed for recovery is stored inside the control file. The recommended configuration is a minimum of two control files on different disks. The parameter CONTROL_FILES identifies the control files.

Sumary Oracle Instance PMON

SMON

DBWR

SNPn SNPn

Shared Pool

LCKn LCKn

Snnn

RECO

SGA

Dnnn Pnnn

Database Buffer Cache

Redo Log Buffer

CKPT ARCH

LGWR

Server  Processes

User Processes Oracle Database

Parameter File

Control Files

Datafiles

Redo Log Files

Accessing and Updating Data Overview

 System Global Area (SGA)

Shared Pool

Database Buffer Cache

Shared SQL Areas

DBWR

Server  Processes User Processes

User Request

Redo Log Buffer

Datafiles

SGA

 PGA

 Stack  Space

 User

 Session  Data

Server  Processes

User Processes

User Request

Query Processing  Parse: checks syntax  Execute:  Fetch: returns data to user Shared Pool

SGA

Database Buffer Cache

Shared SQL Areas

Server   Processes

User Processes

SQL> SELECT first_name FROM s_emp;