CYARNITECH

Understanding Oracle Database Architecture: A Comprehensive Guide

Oracle Instance Overview

The Oracle Instance forms the core of the Oracle Database architecture. It consists of memory structures and processes, each playing a vital role in database functionality and efficiency.

Memory Structures

Oracle’s memory structures can be categorized into:

1. System Global Area (SGA)

• Shared Pool:
  • Library Cache: Stores parsed SQL statements.
  • Data Dictionary Cache: Contains metadata about database objects.
• Database Buffer Cache: Temporarily stores data blocks read from the database.
• Redo Log Buffer Cache: Captures changes made to the database.
• Large Pool: Used for large memory allocations.
• Java Pool: Supports Java execution within the database.

2. Program Global Area (PGA)

• Dedicated memory for individual processes to handle session data and sort operations.

Processes

1. System Monitor (SMON)

• Locates and validates control files to mount the database.
• Ensures proper housekeeping, such as collating free space in datafiles.

2. Process Monitor (PMON)

• Monitors server processes, resolves session-level issues, and reclaims memory from terminated sessions.

3. Database Writer (DBWR)

• Writes modified (dirty) buffers to disk under these circumstances:
  • No free buffers available.
  • Too many dirty buffers.
  • Timeout: Writes every three seconds, even when idle.
  • Checkpoint (CKPT): Synchronizes buffer cache with datafiles.
• Supports incremental checkpoints for optimized disk writes.

4. Log Writer (LGWR)

• Writes redo log entries from the redo log buffer to online redo log files.
• Triggered by:
  • A COMMIT operation.
  • Redo log buffer reaching one-third capacity.
  • When DBWR is writing dirty buffers to disk.

5. Checkpoint (CKPT)

• Updates file headers with the latest SCN (System Change Number) to ensure synchronization.

6. Archiver Process (ARC)

• Archives online redo log files for recovery purposes.

Database Structure

The physical components of an Oracle database include:

• Control Files: Tracks the structure of the database.
• Datafiles: Stores the actual database data.
• Online Redo Log Files: Contains a record of all changes made to the database.
• Archive Log Files: Copies of redo log files for backup and recovery.

Parameter Files

Oracle uses two types of parameter files to manage database configurations:

• SPFILE: Binary and dynamically updated.
• PFILE: Static and requires manual updates.

Investigating the Memory Structure

To analyze memory structures in Oracle, follow these steps:

1. View SGA Summary

Connect to the database as SYS and run:

2. Inspect SGA Components

To view current, maximum, and minimum sizes of SGA components:

3. Check PGA Allocations

Determine the allocated and maximum allowed PGA:

Sample Output:

Diagrams

Diagrams explaining Oracle architecture can include:

1. Memory Structure Diagram:
   • Showcasing SGA and PGA components.
2. Process Flow Diagram:
   • Illustrating SMON, PMON, DBWR, LGWR, and ARC interactions.

Key Insights

• The SGA and PGA are critical to database performance.
• Processes like DBWR and LGWR ensure data integrity and prevent data loss.
• Dynamic memory management allows for real-time resizing of memory components like SGA.

This guide provides a foundational understanding of Oracle’s database architecture. Properly configured memory structures and processes ensure a highly performant and reliable database environment. For more information, consult Oracle’s official documentation.