Oracle 19c Database Architecture Overview

Oracle 19c architecture ensures optimal performance, reliability, and scalability for database management. This article provides an in-depth look into the core components and structure of Oracle 19c, focusing on the database instance, memory architecture, and background processes.

Database Architecture and Components

Oracle 19c database architecture efficiently manages data, ensuring high availability and performance. Key components include:

Database Instance

An Oracle database consists of at least one database instance and one database. The instance handles memory and processes, while the database comprises physical files known as data files. Each instance contains Oracle background processes and memory structures like the System Global Area (SGA) and Program Global Areas (PGA).

📢 You might also like: Oracle 19c Database Instance Configurations (Category: Oracle Database Admin)

System Global Area (SGA)

The SGA is a shared memory area crucial for database operations, including:

Shared Pool: Stores parsed SQL, PL/SQL code, system parameters, and data dictionary information.
Database Buffer Cache: Holds copies of data blocks read from data files to optimize physical I/O.
Redo Log Buffer: Contains information about changes made to the database for recovery purposes.
Large Pool: Used for memory allocations for the User Global Area (UGA), RMAN I/O, and other large memory operations.
Java Pool: Stores session-specific Java code and data.
Streams Pool: Utilized by Oracle Streams and Data Pump for buffering queue messages.

Program Global Area (PGA)

The PGA is a non-shared memory area containing data and control information for server processes, including:

Private SQL Area: Stores information about parsed SQL statements and session-specific details.
Session Memory: Holds session variables and other data required for database sessions.
SQL Work Areas: Used for operations like sorting and hashing.

Background Processes

Oracle 19c relies on various background processes to perform essential tasks, maintain performance, and manage resources:

DBWn (Database Writer Process): Writes modified data blocks from the SGA to data files.
LGWR (Log Writer Process): Writes redo log buffer entries to redo log files.
CKPT (Checkpoint Process): Updates control files and data file headers with checkpoint information.
SMON (System Monitor Process): Performs recovery operations and maintains database consistency.
PMON (Process Monitor Process): Cleans up after failed processes and manages resource recovery.
ARCn (Archiver Process): Archives filled redo log files to ensure data is not lost.
MMON (Manageability Monitor Process): Collects and analyzes statistical data for performance tuning.

Components of Oracle 19c

Oracle 19c includes various components designed to enhance database capabilities and performance:

Data Files: Store the actual data in the database.
Control Files: Maintain metadata about the database, such as structure and log history.
Redo Log Files: Record all changes made to the database, essential for recovery.
Archive Log Files: Store historical redo log files for recovery purposes.
Flashback Logs: Enable flashback database operations, allowing the database to revert to a previous state.
Parameter Files: Define instance configuration settings, crucial for database startup.

Important Parameters for Oracle 19c Instance Architecture

Memory Parameters

MEMORY_TARGET: Specifies the total memory size for the database instance.
MEMORY_MAX_TARGET: Sets the maximum size to which the MEMORY_TARGET can be dynamically increased.
SGA_TARGET: Sets the total size of all SGA components.
PGA_AGGREGATE_TARGET: Sets the total target PGA memory allocation for all server processes.

Oracle can use two methods of Memory management: AMM and ASMM.

Automatic Memory Management (AMM) manages both SGA and PGA dynamically.

Example of enabling AMM:

ALTER SYSTEM SET memory_target = 4G SCOPE = SPFILE;
ALTER SYSTEM SET memory_max_target = 4G SCOPE = SPFILE;

In this mode, Oracle manages the memory allocation between SGA and PGA dynamically based on workload demands.

Automatic Shared Memory Management (ASMM) allows Oracle to manage SGA components (buffer cache, sharedpool, log buffer and so on) dynamically while the DBA configures PGA manually in Oracle 19c architecture.

Example of enabling ASMM:

ALTER SYSTEM SET sga_target = 2G SCOPE = BOTH;
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE = BOTH;

Performance Parameters

DB_FILE_MULTIBLOCK_READ_COUNT: Specifies the maximum number of blocks read in one I/O operation during a full table scan.
LOG_BUFFER: Sets the size of the redo log buffer, impacting performance during heavy transaction processing.
OPTIMIZER_MODE: Determines the mode of the optimizer, affecting how SQL statements are parsed and executed.

Example of setting performance parameters:

ALTER SYSTEM SET db_file_multiblock_read_count = 16 SCOPE = BOTH;
ALTER SYSTEM SET log_buffer = 64M SCOPE = BOTH;
ALTER SYSTEM SET optimizer_mode = 'ALL_ROWS' SCOPE = BOTH;

Diagnostic Parameters

CONTROL_FILES: Specifies the path to the control files.
DIAGNOSTIC_DEST: Specifies the location of the diagnostic data.

Example of setting diagnostic parameters:

ALTER SYSTEM SET control_files = '/u01/oradata/control01.ctl' SCOPE = SPFILE;
ALTER SYSTEM SET diagnostic_dest = '/u01/app/oracle/diag' SCOPE = BOTH;

Security Parameters

REMOTE_LOGIN_PASSWORDFILE: Specifies whether Oracle should use the password file to authenticate users.
SEC_CASE_SENSITIVE_LOGON: Enables or disables case-sensitive password authentication.

Example of setting security parameters in Oracle 19c architecture:

ALTER SYSTEM SET remote_login_passwordfile = 'EXCLUSIVE' SCOPE = SPFILE;
ALTER SYSTEM SET sec_case_sensitive_logon = TRUE SCOPE = BOTH;

Viewing and Modifying Parameters

To view the current parameter settings, use the following query:

SELECT name, value FROM v$parameter;

This query retrieves the names and values of all initialization parameters currently in use.

To modify a parameter dynamically, use the ALTER SYSTEM command. For example:

ALTER SYSTEM SET open_cursors = 300 SCOPE = BOTH;

This command changes the value of the open_cursors parameter to 300 and applies the change to both the running instance and the SPFILE.

Memory Architecture of Oracle 19c Instance

The memory architecture of an Oracle 19c instance is critical for its performance and stability. It consists of various memory components that store different types of data and control information.

System Global Area (SGA)

The SGA is a shared memory area that contains data and control information for the instance. Key components of the SGA include:

Database Buffer Cache: Stores copies of data blocks read from the data files.
Redo Log Buffer: Stores redo entries that contain a record of changes made to the database.
Shared Pool: Caches various constructs that can be shared among users, such as SQL statements and data dictionary information.
Large Pool: Optional memory pool that provides large memory allocations for certain large processes.

Example of viewing SGA components and their sizes:

SELECT component, current_size FROM v$sga_dynamic_components;

This query retrieves information about the different components of the SGA and their current sizes in Oracle 19c architecture.

Program Global Area (PGA)

The PGA is a private memory area allocated to each server process. It contains data and control information specific to a server process. Key components of the PGA include:

Session Memory: Contains session-specific variables and session-related information.
Private SQL Area: Contains data such as bind variables and runtime buffers.
Sort Area: Used for sorting operations.

Example of viewing PGA usage:

SELECT * FROM v$pgastat;

This query retrieves statistics about PGA memory usage, providing insights into how memory is allocated and used within the instance.

Process Architecture of Oracle 19c Instance

The process architecture of an Oracle 19c instance includes various types of processes that perform different functions. These processes are divided into two main categories: user processes and background processes.

User Processes

User processes are created and maintained to run the software code of an application program or an Oracle tool. They interact with the Oracle database to perform user-requested operations.

Background Processes

Background processes are created by the Oracle instance in 19c architecture to perform various maintenance tasks and manage database operations. Key background processes include:

Database Writer (DBWn): Writes modified data from the database buffer cache to the data files.
Log Writer (LGWR): Writes redo log entries to disk, ensuring data integrity and recovery.
Checkpoint (CKPT): Updates data file headers and control files to indicate the checkpoint position.
System Monitor (SMON): Performs recovery when an instance fails.
Process Monitor (PMON): Cleans up failed user processes.
Archiver (ARCn): Copies redo log files to archive storage.

Stopping and Starting Database Instance

Below are practical scenarios and example code snippets to illustrate the implementation of Oracle 19c database architecture:

Starting and Stopping an Instance

To start an Oracle database instance:

sqlplus / as sysdba
STARTUP;

srvctl start database -d dbname --> In RAC, start all instances;
srvctl start instance -i isnt_name -d dbname --> In RAC, start the specified instance;

To stop an Oracle database instance:

sqlplus / as sysdba
shutdown immediate;

srvctl stop database -d dbname --> In RAC, stop all instances;
srvctl stop instance -i isnt_name -d dbname --> In RAC, stop the specified instance;

Conclusion

Understanding the architecture of Oracle 19c is essential for efficient database management. By leveraging components like the SGA, PGA, and various background processes, Oracle 19c ensures high performance, reliability, and scalability. Proper configuration and tuning of these components are crucial for maintaining optimal database operations.

See more on Oracle’s website!

Be Oracle Database certified Professional, this world is full of opportunities for qualified DBAs!