1 Exploring the Oracle Database Architecture Copyright 2007

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 Copyright © 2007, Oracle. All rights reserved.

Oracle Database The Oracle relational database management system (RDBMS) provides an open, comprehensive, integrated approach to information management 1 -3 Copyright © 2007, Oracle. All rights reserved.

Connecting to a Server Client Middle tier Multitier architecture shown 1 -4 Copyright ©

Oracle Database Architecture: Overview Instance SMON PMON RECO SGA Shared pool Database buffer cache Redo log buffer DBWn CKPT PGA Server process User process Data files Others Control files Library cache Data dictionary cache LGWR Online redo log files Database 1 -6 Copyright © 2007, Oracle. All rights reserved. ARCn Archived log files

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 SQL> Select … User process Server process Connection Session 1 -7 Copyright © 2007, Oracle. All rights reserved. Session

Interacting with an Oracle Database User process Server process Instance SGA Shared pool Database buffer cache DBWn CKPT Redo log buffer Data dictionary cache LGWR SMON PMON ARCn User 1 -8 Library cache Copyright © 2007, Oracle. All rights reserved. RECO Others

Oracle Database Server Structures Instance Memory structures User process SGA Database buffer cache Server process Processes DBWn CKPT LGWR Shared pool Redo log buffer SMON PMON Library cache Data dictionary cache ARCn RECO Others Database Storage structures Data files 1 - 10 Control files Copyright © 2007, Oracle. All rights reserved. Online redo log files

Oracle Database Memory Structures Server process 1 Server process 2 PGA Library cache Database buffer cache 1 - 11 Data dictionary cache Other Shared pool Redo log buffer Java pool Background process PGA Shared SQL area DB structures Memory - Process - Storage Streams pool I/O buffer Free memory Response queue Request queue Large pool Copyright © 2007, Oracle. All rights reserved. PGA SGA

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 Database buffer cache DBWn 1 - 13 CKPT Shared pool Redo log buffer LGWR SMON PMON Library cache Data dictionary cache ARCn Copyright © 2007, Oracle. All rights reserved. RECO Others

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 Database buffer cache DBWn 1 - 14 CKPT LGWR Shared pool Redo log buffer SMON Library cache Data dictionary cache PMON ARCn Copyright © 2007, Oracle. All rights reserved. RECO Others

Shared Pool • Is a portion of the SGA • Contains: – Library cache — Shared SQL area – Data dictionary cache – Control structures Instance SGA Database buffer cache DBWn 1 - 15 CKPT LGWR Shared pool Redo log buffer SMON Shared SQL area Library cache Data dictionary cache PMON ARCn RECO Others Copyright © 2007, Oracle. All rights reserved. Library cache Data dictionary cache Other

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 SQL area Library cache Data dictionary cache Other Shared pool 1 - 17 Copyright © 2007, Oracle. All rights reserved.

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 buffer cache Java pool 1 - 19 Redo log buffer Streams pool Shared pool Large pool I/O buffer Free memory Response queue Request queue Large pool Copyright © 2007, Oracle. All rights reserved.

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 1 - 20 Copyright © 2007, Oracle. All rights reserved. Streams pool

Process Architecture DB structures - Memory Process - Storage • User process – 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 Database buffer cache PGA User process Server process Redo log buffer Library cache Data dictionary cache Background processes DBWn 1 - 21 Shared pool CKPT LGWR SMON Copyright © 2007, Oracle. All rights reserved. PMON ARCn RECO Others

Process Structures Server Server n processes SGA Database buffer cache CKPT RECO Shared pool Redo log buffer PMON SMON Library cache SGA Data dictionary cache DBWn LGWR ARCn Others Oracle background processes 1 - 22 Copyright © 2007, Oracle. All rights reserved.

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 cache 1 - 24 Database writer process Copyright © 2007, Oracle. All rights reserved. Data files

Log. Writer 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 1 - 26 Log. Writer process Copyright © 2007, Oracle. All rights reserved. Redo log files

Checkpoint Process (CKPT) • Records checkpoint information in – Control file – Each data file header CKPT Control file Checkpoint process Data files 1 - 28 Copyright © 2007, Oracle. All rights reserved.

System Monitor Process (SMON) • Performs recovery at instance startup • Cleans up unused temporary segments SMON Instance System Monitor process Temporary segment 1 - 29 Copyright © 2007, Oracle. All rights reserved.

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 Process Monitor process 1 - 30 User Failed user process Database buffer cache Copyright © 2007, Oracle. All rights reserved.

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 database A 1 - 31 In-doubt transaction in database B Copyright © 2007, Oracle. All rights reserved.

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 1 - 32 Copies of redo log files Copyright © 2007, Oracle. All rights reserved. Archive destination

Other Processes • MMON: Performs manageability-related background tasks • MMNL: Performs frequent and lightweight manageability -related tasks • MMAN: Performs automatic memory management tasks • CJQ 0: Runs user jobs used in batch processing • QMNx: Monitors the Streams Advanced Queuing message queues 1 - 33 Copyright © 2007, Oracle. All rights reserved.

Server Process and Database Buffer Cache SGA Server process Database buffer cache Buffers: • Pinned • Clean • Free or unused • Dirty DBWn Data files 1 - 35 Database writer process Copyright © 2007, Oracle. All rights reserved.

Database Storage Architecture Control files Data files Online redo log files Parameter file Backup files Archived redo log files Password file 1 - 36 DB structures - Memory - Process Storage Alert log and trace files Copyright © 2007, Oracle. All rights reserved.

Logical and Physical Database Structures Logical Physical Database Schema Tablespace Data file Segment Extent

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 Copyright © 2007, Oracle. All rights reserved.

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 Copyright © 2007, Oracle. All rights reserved.

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 1 - 42 Extents Data blocks Copyright © 2007, Oracle. All rights reserved. Disk blocks

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 Copyright © 2007, Oracle. All rights reserved.

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 Copyright © 2007, Oracle. All rights reserved.