Oracle Database Architecture: 11g and 19c

Introduction

Oracle Database Architecture is the framework that allows Oracle RDBMS to function efficiently, managing data storage, query processing, and maintaining the database. Though Oracle 11g and Oracle 19c share the core architecture principles, Oracle 19c includes several enhancements like the Multitenant Architecture.

Oracle 11g vs. Oracle 19c

While Oracle 11g introduced key improvements like Automatic Memory Management (AMM) and Oracle Grid Infrastructure, Oracle 19c, being the Long-Term Release, brought enhancements in Multitenancy, Database In-Memory, and security features. Despite these advancements, both versions retain similar foundational architecture, including instances, storage, and process management.

Oracle Database Architecture Overview

Oracle Database consists of two main components:

1. Oracle Instance: This consists of the memory structures and background processes required to manage the database.
2. Oracle Database: This refers to the physical files that store data and metadata.

1. Oracle Instance

The Oracle Instance is a combination of memory structures and background processes. An instance can exist without the database, and multiple instances can manage the same database in Real Application Clusters (RAC).

Memory Structures

The primary memory structures of an Oracle instance are:

• System Global Area (SGA): Shared memory area used by all Oracle background processes.
• Program Global Area (PGA): A memory region that holds data and control information for server processes.

System Global Area (SGA) Components

1. Database Buffer Cache: Stores copies of data blocks read from datafiles.
2. Redo Log Buffer: Holds redo entries that track changes made to data.
3. Shared Pool: Contains parsed SQL statements and data dictionary information.
4. Large Pool: Optional memory pool for large operations such as backup and restore, and parallel query operations.
5. Java Pool: Memory for running Java programs in Oracle.
6. Streams Pool: Memory allocated for Oracle Streams processes.

PGA Components

The PGA is a non-shared memory region dedicated to a server process, containing:

• Session Information
• Private SQL Areas
• Sort Areas

Automatic Memory Management (AMM)

• Oracle 11g introduced Automatic Memory Management (AMM), where both SGA and PGA are managed dynamically.
• In Oracle 19c, AMM is further refined, with options for Automatic Shared Memory Management (ASMM).

2. Oracle Background Processes

The background processes of an Oracle instance manage tasks like I/O operations, memory management, and process synchronization. The key background processes are:

1. Database Writer (DBWn): Writes modified blocks from the database buffer cache to datafiles.
2. Log Writer (LGWR): Writes redo log entries from the redo log buffer to online redo log files.
3. System Monitor (SMON): Performs instance recovery at startup and cleans temporary segments.
4. Process Monitor (PMON): Cleans up resources after a failed user process.
5. Checkpoint (CKPT): Signals database writes to ensure that all changes are saved to the database.
6. Archiver (ARCn): Copies redo log files to archive destinations after a log switch.
7. Recoverer (RECO): Resolves distributed transactions.

?

Oracle 19c also introduces some Multitenant Architecture-specific background processes (discussed later in this document).

3. Oracle Database Storage Structures

The Oracle Database itself is composed of physical storage and logical storage structures.

Physical Storage Structures

These are the actual files that store data on disk:

1. Datafiles: Store the actual data and are linked to tablespaces.
2. Control Files: Maintain metadata about the database, such as the structure and state of the database.
3. Redo Log Files: Contain a record of all changes made to the data to ensure recoverability in case of failure.
4. Archive Log Files: Copies of redo log files, used for recovery purposes.
5. Parameter Files (PFILE/SPFILE): Contain initialization parameters that dictate how Oracle Instance starts.

Logical Storage Structures

Logical structures enable database administrators to manage and control data efficiently:

1. Tablespaces: Logical divisions of the database where related datafiles are grouped together.
2. Segments: Space allocated for database objects like tables and indexes.
3. Extents: Specific blocks of storage allocated for a segment.
4. Data Blocks: The smallest unit of data storage in Oracle, managed by extents.

Tablespaces in Oracle 11g and 19c

• SYSTEM and SYSAUX Tablespaces: Core tablespaces that store metadata and essential database objects.
• Temporary Tablespaces: Used for temporary data during operations like sorts.
• Undo Tablespaces: Manage undo data for transaction rollback and read consistency.
• Bigfile Tablespaces: Supported in both versions, but more commonly used in 19c for larger databases.

4. Oracle Multitenant Architecture (Oracle 19c)

Oracle 19c introduced and improved the Multitenant Architecture, where a Container Database (CDB) contains one or more Pluggable Databases (PDBs). This allows for:

• Efficient resource sharing between databases.
• Easier database management, consolidation, and provisioning.
• Separation of administrative responsibilities between the CDB and PDBs.

Key Components of Multitenant Architecture

1. Container Database (CDB): The root container that manages shared resources like SGA, background processes, and redo logs.
2. Pluggable Database (PDB): A self-contained database with its own data and metadata, plugged into a CDB.

Multitenancy Background Processes in 19c

• Pmon, Smon, LGWR, DBWn continue managing CDB-level operations, while Mmon, CKPT, and RECO handle CDB/PDB interactions.
• PDB-level processes exist independently for better separation of workload.

5. Oracle Database Processes and Architecture Enhancements in 19c

Database In-Memory (Oracle 19c)

Oracle 19c provides enhancements for Database In-Memory, offering faster analytical query performance by storing data in-memory in a columnar format, while maintaining the row format on disk.

Automatic Indexing (Oracle 19c)

19c introduced Automatic Indexing, which allows the database to automatically create and maintain indexes, improving database performance without DBA intervention.

Advanced Security Features

Both Oracle 11g and 19c provide security features such as Transparent Data Encryption (TDE). However, 19c enhances security with features like unified auditing and Data Masking for better control over sensitive data.

?

Conclusion

The Oracle Database Architecture for both 11g and 19c versions offers a robust and scalable framework to manage enterprise-level databases. While both versions share core architectural components like instances, storage, and background processes, Oracle 19c introduces significant improvements in multitenancy, in-memory computing, and security.

By understanding the internal structure of Oracle databases, administrators can efficiently manage resources, optimize performance, and ensure data integrity across both traditional and modern cloud environments.

This document provides a detailed overview of the core and advanced aspects of Oracle Database Architecture for both 11g and 19c versions. You can expand this further based on specific needs like practical implementation or case studies.