Oracle Architecture

Architecture is the complex or carefully designed structure of something—Wikipedia. Hi! 👋 . Welcome to this part of Oracle Database. After going through the background of database administration, we need to understand the tool we are going to use i.e. Oracle Database. Like its definition, architecture looks at understanding the design of something and this area, we are going to look at the architecture of this database. Why? We can’t get on to administer something we cannot understand. The architecture of Oracle Database can be discussed into three i.e. user- related processes, instance and database. So without further hullabaloo, lets start.

User-related processes.

To understand this architecture simply, we are going to use simple examples in our explanations. On one side we have the computer (computer X) on which Oracle Database is installed 🖥 and on the other side, we have a user who is using an application (any of your favourite app maybe Facebook 🤓) that uses that database to store information. Lets say the user is accessing the application from their phone or laptop 💻 (computer Y). The application and the database are on different computers. This application needs to connect to the database to get data from there as well as posting new information there.

John needs to use his favourite app (TikTok) from his smartphone (computer Y) and thus launching it. Upon launching the app, a user process is started on John’s smartphone. The role of the user process is to initiate connection to the TikTok’s database server (computer X). However, TikTok’s database server (computer X) is divided into two parts i.e. an instance and the database (more on these later). The app can’t access the database unless the instance has okayed the procedure. Therefore, when the app is launched, the connection is not made directly to the database but the instance first. Upon successful connection to the instance, a user session indicating that John has connected is created in the instance. The server process is started on computer Y’s instance. The work of the server process is to interact with the database on behalf of the user. In other-words, if John needs information from the database e.g. new posts made by other users, the request will be sent to the database via the server process and results will be returned to John via the same process again. In addition to the user and server processes, John’s connection to the instance is associated with memory called Program Global Area (PGA). This memory region stores user session information used by the server process (John’s session information)—more details later. In brief, PGA works hand in hand with the server process. When all these steps are sorted, John can now get the information from the database as well as posting any information there in form of new posts other factors remaining constant.

The Instance—Memory & Background Processes.

In the previous discussion, it is clear that computer X on which the Oracle Database software resides has two components i.e. the instance and the database. However, we haven’t yet looked at what the instance is. The instance is made up of memory and background processes. Just like any other application to run, Oracle database also needs memory and background processes to run. These are what we call the instance. So, lets see each of the instance’s components.

Memory Structures.

There are majorly two memory structures that make up the Oracle instance i.e. System Global Area (SGA) and the Program Global Area (PGA). Since we already saw briefly what PGA does, lets start with the SGA 🤩.

System Global Area (SGA)

The SGA serves a lot of functions to the performance of the database. Without this memory, the database can’t be used. It is comprised of many components of which some are mandatory (they have to be there for the database to function) and others are optional. Lets start with the mandatory ones.

Mandatory Components

Database Buffer.

This memory component stores recently modified data blocks temporarily. For example, John decides to make a change to his TikTok username from John to Jon. Before committing the new changes, this changed data is stored temporarily in the database buffer. It is the server process that copies the affected data from the data file to the database buffer.

Redo Log Buffer

This component stores recently used SQL statements that cause a change to the data. These are called DML Statements. You’re lost? Don’t worry 😇. You can check out the SQL statements categories here. For example, when John decides to save changes to his new username, by clicking save button he is using an UPDATE SQL statement. This statement is the one that is stored under this compartment.

Shared Pool

This component stores the results cache and SQL statement execution plan (the steps that Oracle database goes through to respond to a query that has been made by the user).

Other than the mandatory components, we also have the optional components of SGA.

1. Java Pool. It caches most recently used Java objects and Java’s application code.
2. Stream Pool. It caches data associated with queued message requests.
3. Large Pool. It caches data for large operations like backup.
4. Results Cache. It stores results of SQL queries for better performance.

To have a look at all SGA components, we use the code below.

SELECT * FROM v$sgainfo;

Note: For most of the codes we shall be using, we need to first log in as database administrators. To log in as a DBA, we use username SYSTEM and the password we chose when installing the software. We can also use SYS AS SYSDBA as our username. (We shall be looking at these user accounts later).

Program Global Area (PGA)

This memory structure stores user session information for example session variables. It also works together with the server process to ensure an interaction between the user and the database. To see how PGA memory is allocated, we use the code below.

SELECT * FROM v$pgastat;

Memory Configuration

There are two ways of configuring memory in Oracle.

1. Automatic configuration. Here it is Oracle to configure the memory automatically. Oracle detects the memory compartment that is starving of memory and it de-allocates some memory from other compartments to ensure the starving compartment is okay. For example, in case some memory of shared pool can be allocated to database buffer in case it needs it.
2. Manual configuration. With this, it is the DBA to configure the memory manually to all compartments of Oracle.

With both methods, we need to have a memory target. This is the maximum memory value that is allocated to Oracle database. For instance, we could reserve 500M to be used by Oracle. This is what we call memory target. We configure this using the memory_target parameter. The code below is used to make changes to the database’s memory target.

ALTER SYSTEM SET memory_target = 700M scope = both;

Background Processes

After looking at memory structures, we can now focus on the background processes. Remember we are looking at the instance which is made up of memory and background processes. It is time to jump into background processes. The processes below, are mandatory. They have to be active for the database to run.

System Monitor (SMON)

The SMON is responsible for mounting, opening and closing the database.This is the mother of the database. In addition to this, it performs instance or crash recovery upon database start up with the help of the redo log files.

Process Monitor (PMON)

Responsible for monitoring of database processes. It detects failed processes and cleans them up as well as de-allocating resources from them. It wakes up periodically to check if it is needed and can be called upon by other processes if they deem its functions necessary.

Database Writer (DBWn)

This process is charged with writing content of the database buffer to the data file where it is stored permanently (checkpoint event). The process is started when the instance is started and by default, Oracle starts one database writer process. We can have up to 20 database writer processes to improve database performance. This process is called upon whenever the database buffer cache is too large, upon instance shutdown (save for abortion mode—we shall look at this later), segment is dropped, etc.

Checkpoint (CKPT)

When the DBWn writes modified data from the database buffer to the data file, this act is referred to as the checkpoint event. Oracle always updates the control files and data file headers about this checkpoint event and it is the work of the CKPT process to do the update. Therefore, the CKPT process updates the control files and data file headers about checkpoint information.

Log Writer (LGWR)

This process is charged with writing redo log buffer content to the redo log file where it is stored permanently. This is done to provide space for the server process to write new entries to the redo log buffer. The LGWR is activated every after 3 seconds, when a transaction is committed, whenever a checkpoint happens.

The above are the mandatory background processes we have in Oracle. We also have other optional processes and below are some of them.

1. Archiver process (ARCn). Charged with copying transactional recovery information from the redo log file to the archive location.
2. Memory Manager (MMAN). Manages the size of each individual SGA component when Oracle’s Automatic Shared Memory Management feature is used.
3. Recovery Writer (RVWR). Writes recovery information to disk when Oracle’s Flashback Database feature is used.
4. Diagnosability (DIAG). Performs diagnostic dumps

To have a look at all running processes, we use the SQL statement below:

SELECT program FROM v$process;

The Database (Oracle’s Storage Structures)

Remember when we said we have the instance and the database? So good of you 🤓. Without the instance, we just forget about the database. In Oracle, a database is a collection of files on disk. These files are many and serve different purposes. We are going to look at the most used.

Note: The database storage structures are explained in details under Managing Storage Structures. You can find the details about this area here.

Oracle’s storage structures are both physical (visible to the operating system) and logical (invisible to the operating system). We are going to begin with the physical files.

Physical Storage Structures

Control File

This is a physical file that stores important information about the database for instance name of the database, name and location of data files, name and location of redo log files, timestamp and language of the database, checkpoint information, etc.

Data File

This is a physical file that stores data inserted into tables permanently. Data files are stored in a tablespace (to be looked at in details soon). The data files are binary in nature and the DBWn process writes data to these files. The data dictionary view dba_data_files is used to show the data files associated with each tablespace.

Redo Log FIle

This physical file is charged with storing the redo log buffer content. Because of the nature of its content, it is multiplexed. The LGWR process updates the redo-log file with the redo log buffer content.

Logical Storage Structures

Tablespace. A tablespace is a logical storage structure in the database charged with storing data files.

Segments. This is a set of extents which in turn is made of blocks.

Extents. This is logical storage structure composed of blocks.

Blocks. This is the smallest storage unit in Oracle database.

An illustration showing how the instance relates with the database.

Wow 😲. It’s been a very lengthy topic and if you’ve made it this far, congratulations 🏆. After understanding the Oracle architecture, we can now go ahead to install the Oracle database software and creating the database. Lets go ahead and see how we do this in the next topic of Preparing Oracle Database Environment.

Summary

1. User process initiates and maintains user connection to the instance.

2. Server process interact with the database on behalf of the user.

3. Instance is made of memory structures and background processes.

4. Memory structures are: SGA (database buffer, redo log buffer and shared pool), PGA.

5. Background processes include mandatory (SMON, PMON, DBWn, CKPT, LGWR) and optional.

6. Database physical storage structures include redo log file, data file, control file, etc.

7. Database logical storage structures include tablespace, segments, extents and blocks.