A guide to Oracle database architecture

Anyone can drive a car without knowing how an internal combustion engine works and all the subsystems associated with it. However, certain key concepts such as power utilization, compression, suspension stiffening, and traction will be foreign to you, and you’ll never be able to get the best out of the car. Moreover, if it has any problems, you’ll be left stranded on the road.

Similarly, we can’t expect our database applications to work well if we don’t know the architecture of the database engine: the server. So, let’s explore the components of Oracle Database!

Oracle databases: Instances and entities

Oracle Database is not a singular entity, but rather a collection of logical and physical components that work together. Therefore, to understand its operation, it’s crucial to differentiate between two key terms that are often used interchangeably but are fundamentally different: database and instance.

A database in Oracle terminology refers to the physical storage of information, whereas an instance is software that runs on a server and provides access to information contained in an Oracle database. An instance runs on a specific server or computer, while the database is stored on disks connected to this server. According to Oracle, from version 21c onward, “‘database’ refers specifically to the data files of a multitenant container database (CDB), pluggable database (PDB), or application container.”

The Oracle database is a physical entity consisting of files stored on disks. At the same time, an instance is a logical entity consisting of structures in RAM and processes running on the server. Interestingly, an instance can be part of only one database, whereas several instances can be associated with one database. Furthermore, an instance is limited in lifespan, while a database, relatively speaking, can exist forever.

To simplify, an instance is a bridge to the database, and the database itself is an island. When the instance is running, the bridge is running, and data can flow in and out of the Oracle database. However, if the bridge is down (the instance is stopped), users can’t access the database even though it’s still physically there.

Oracle Database is not a singular entity, but rather a collection of logical and physical components that work together.

Oracle Database architecture

Oracle Database’s architecture can be broadly categorized into physical and logical structures, each serving distinct purposes.

Physical structures

The physical layer of Oracle Database consists of files stored on the server’s disk. These include the following files:

• Control files: Control files are pivotal to the operation of an Oracle database. In particular, they store metadata about the database, including the names and locations of data files and redo log files, the database name, and other critical information. Moreover, control files are indispensable during database startup and recovery.
• Data files: Data files store the actual data within the database. These files are organized into logical structures called tablespaces, which we’ll discuss later.
• Redo log files: Redo log files capture all changes made to the database, ensuring data integrity and enabling recovery in the event of a failure.

Logical structures

The logical architecture of Oracle Database provides an abstraction layer over the physical files. Here are the key components:

• Tablespaces: Tablespaces are logical storage units within a database. Each tablespace consists of one or more data files. Tablespaces provide a mechanism for organizing and managing data, enabling administrators to allocate resources and optimize performance efficiently.
• Segments, extents, and blocks: Data within tablespaces is further subdivided into segments (e.g., tables, indexes). These segments are made up of extents and ultimately stored as blocks. These structures facilitate efficient data access and management.
• Schemas: A schema is a collection of database objects, such as tables, views, and indexes, owned by a specific user. Schemas help organize and manage database objects systematically.

Memory and process architecture

Oracle Database instances rely heavily on memory structures and processes to ensure smooth operation. The Oracle memory structure is made up of two memory areas:

• System Global Area (SGA): The SGA is a shared memory area used by an Oracle instance. Specifically, it contains data and control information crucial for database operation. Some key components of the SGA include the database buffer cache, shared pool, redo log buffer, and large pool.
• 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 user session.

The SGA is a shared memory area used to store instance data and control information. It’s created when the instance is brought up, and it’s deleted when the instance is no longer in use (when a shutdown is performed).

The information stored in this area consists of a shared pool, a database buffer, and a redo log buffer, each with a fixed size.

Oracle Database instances rely heavily on memory structures and processes to ensure smooth operation.

Shared pool

The shared pool stores the data dictionary and the most recently used SQL statements (shared SQL or library cache). This is where the parsing phase of the SQL statements takes place.

Database buffer

The database buffer stores the most recently used data. It also contains the rollback buffers, which store the previous image of the data. Rollback buffers also provide consistency in reading.

Redo log buffer

Redo log buffers provide sequential access and record all changes made to the database by insert, update, delete, create, alter, and drop operations with the minimum necessary information. Essentially, their function is to provide security against a database crash.

Background processes

Oracle instances include numerous background processes, and each serves a distinct function. Here are some of the most critical processes:

• Database writer (DBWn) writes modified data from the buffer cache to data files.
• Log writer (LGWR) writes redo log entries from the redo log buffer to redo log files.
• System monitor (SMON) performs recovery operations and instance housekeeping tasks.
• Process monitor (PMON) manages the cleanup of failed user processes and releases resources.
• Checkpoint (CKPT) updates control files and data file headers with checkpoint information.

Optional background processes include archiver process (ARCn), lock manager daemon (LMDn), queue monitor (QMNn), recoverer process (RECO), lock process (LCKn), and lock monitor (LMON).

According to Oracle, most optional processes are “specific to tasks or features. For example, background processes that support Oracle ASM are only available when this feature is enabled.”

Transaction management and concurrency

Oracle Database handles multiple simultaneous users while maintaining data consistency and integrity. This is achieved through its robust transaction management and concurrency control mechanisms.

Transactions

A transaction in Oracle Database is a logical unit of work that comprises one or more SQL statements. In addition, Oracle ensures that transactions adhere to the ACID properties (atomicity, consistency, isolation, and durability), thus making it a reliable choice for mission-critical applications. Transactions are initiated implicitly whenever a Data Manipulation Language (DML) statement is executed, and they can be committed or rolled back as needed.

Concurrency control

To handle multiple users accessing the database simultaneously, Oracle employs mechanisms such as these:

• Locks: Oracle uses locks to prevent conflicts between concurrent transactions. These include table-level and row-level locks.
• Multiversion concurrency control (MVCC): Oracle’s MVCC feature enables users to access a consistent snapshot of the data, ensuring that queries are unaffected by ongoing transactions.
• Undo tablespace: Undo tablespaces store undo information, which is used to roll back transactions and provide read consistency.

Conclusion

Oracle Database’s architecture is designed to deliver exceptional performance, scalability, and reliability across a wide range of applications. As a result, understanding its components and how they interact enhances the ability to optimize and manage database operations effectively.

Much like a car, where you don’t need to understand every intricate detail of the engine to drive it, the fundamental architecture of Oracle Database remains consistent across its various versions. In fact, even with over 20 versions of Oracle, the core architecture has stayed largely the same. Knowing this basic structure enables you to use Oracle effectively.

Similarly, just as a driver can operate any modern car without being an expert mechanic, a solid understanding of Oracle’s architecture allows you to navigate new versions, troubleshoot, and make the most of its features, thus ensuring efficient and seamless database management.

This post was written by Talha Khalid. Talha is a full-stack developer and data scientist who loves to make the cold and hard topics exciting and easy to understand.