Oracle databases have evolved significantly between versions 11g and 19c, with various enhancements in architecture and performance optimization. Let’s explore the architecture and key differences of both:

1. Oracle 11g Database Architecture

Oracle 11g introduces various advancements in terms of performance, management, and security features, building upon its predecessor Oracle 10g. Below are the components of Oracle 11g's architecture:

Main Components

Instance and Database:

• Instance refers to the collection of memory structures and processes that manage the database files.
• Database is a collection of physical files like data files, control files, and redo log files.

 Memory Structures:

• SGA (System Global Area): This shared memory area is critical in storing data for all users, such as SQL statements, cached data blocks, and redo logs. It includes components like the Database Buffer Cache, Redo Log Buffer, Shared Pool, and Large Pool.
• PGA (Program Global Area): The PGA is a memory region that holds data and control information for a server process. It is used for tasks like sorting and session handling.

Processes:

• Oracle uses background processes such as DBWn (Database Writer), LGWR (Log Writer), CKPT (Checkpoint Process), and SMON (System Monitor). These processes handle everything from writing data to disks to managing transactions.

Key Features in 11g:

• Automatic Memory Management: Oracle 11g improves the self-management features by providing Automatic Memory Management (AMM), which dynamically adjusts memory allocation between the SGA and PGA.
• Data Compression: It introduces advanced compression techniques for tables and indexes.
• Active Data Guard: It ensures better high-availability solutions.
• Real Application Testing (RAT): This feature helps users test the impact of real-world production workloads on databases.

2. Oracle 19c Database Architecture

Oracle 19c is considered the long-term support version, and it focuses heavily on automation, improved performance, and advanced cloud readiness.

Main Components

Oracle 19c retains most of the core architectural principles of Oracle 11g but with notable enhancements.

• Database Consolidation: With the introduction of Multitenant Architecture (starting from 12c), Oracle allows multiple databases to share a single Oracle instance. These are known as pluggable databases (PDBs), running within a container database (CDB).
• Memory Structures:

Similar to 11g with SGA and PGA, but in 19c, features like Automatic Database Diagnostic Monitor (ADDM) and In-Memory technologies optimize data retrieval and caching for analytics.

• Processes:

The background processes remain largely the same but with more advanced optimizations for handling larger and more complex workloads, especially when running Oracle on cloud infrastructures.

Key Features in 19c:

• Automatic Indexing: Oracle 19c introduces automatic indexing, allowing the database to automatically create, manage, and drop indexes based on workload patterns.
• JSON Support: Enhanced support for JSON data and queries, making it more suitable for NoSQL-like functionalities.
• In-Memory Option: Oracle 19c significantly improves the in-memory options for better analytics performance by allowing faster queries directly in RAM.
• Multitenant Database Support: 19c supports the consolidation of databases into a single platform for reduced resource usage and simplified management.
• Hybrid Partitioned Tables: It allows the partitioning of tables across both disk and external tables, supporting large-scale data management solutions.
• Sharding: For large-scale distributed databases, Oracle 19c allows data sharding to split databases into smaller, more manageable pieces, improving scalability and performance.

Comparison of Key Features: 11g vs. 19c