Oracle Database Architecture: Understanding the basics

“The Captain of a plane that knows nothing about the mechanical parts of his plane would eventually crash it one day.”

I believe it would be nice to introduce you to the inner workings of the Oracle database. This would be useful to the intending DBA, audit or control officer and finally the ordinary enthusiast. You cannot effectively manage the oracle database if you don’t know how it processes data and presents information to you.

So let’s get busy………

The ORACLE DATABASE SERVER comprises of the INSTANCE and the DATABASE.
This can be depicted in form of an architecture as seen below:

In this post we will look at each component one by one.

 INSTANCE

The Instance resides in the memory and is controlled by the database Initialization parameter. This parameter tells the computer how it should allocate the space in the physical computer memory to each component in the instance.
The Instance consist of the SGA (System Global Area) and Oracle background processes

SYSTEM GLOBAL AREA (SGA)

 The System Global Area (SGA) is the region in the instance responsible for taking up this memory allocations we talked about initially. The SGA contains all information necessary for the instance operation.

Lets query the information of the SGA. 
Type the following query:

SELECT name,bytes from v$sgainfo;

The values you see in the bytes column are the memory allocation that is allocated in the physical memory. This is often reviewed by the DBA for performance reason. (Sorry but this aspect is beyond the scope of this post.)

Now let us look at the various components of the SGA one by one.

SGA Components

Shared pool

The shared pool is used for objects that can be shared globally such as reusable execution plans etc. The environment consist of two key performance related memory structures mainly the Data dictionary and the Library cache.

Data dictionary Cache  keeps a collection of the most recently used definitions in the database. It includes information about data files, tables, indexes, columns, users, privileges and other database objects.

Let me explain further.

The multiple caching of data dictionary information both into the database buffer cache and shared pool memory improves performance. 

Information about the database is stored in the data dictionary tables.

When the information is needed by the server the data dictionary tables are read and the data that is returned is stored in the data dictionary cache.

Now what do I really mean by ‘Cache’.

Cache means memory that is set aside as a specialized storage that is continually updated. It is basically used to make data transfers faster within the computer.

In the picture above, the storage represents the BUFFER while the water represents data or information.

Confused already?

Let me give you an example of a data dictionary because we all use it every day.

The human brain can be seen as a form of a dictionary cache.

Let’s analyze this grammar:

  a.    “ORACLE DATABASE INTERESTING IS AN TOPIC”

  b.    “I HAVE 4 CHILDS”

These are poorly constructed grammar.

Example (a) has issues with sentence arrangement while Example (b) has issues with grammatical usage.

You can identify these errors because the skill was taught in schools which you attended. The school helped in building your data dictionary such that you can now detect grammatical error when you see one.

A French man who doesn’t understand English will find these errors difficult to detect. The steps taken to analyze this statement however is called PARSING.

Clear now? lets move on.

Oracle uses the data dictionary to analyse queries written, to ensure they are syntactically correct based on its inbuilt dictionary. If not then it throws up an error message. 

The dictionary of the oracle database is called a data dictionary. 

The data dictionary is a set of tables, called base tables, which contains information about user-created database objects. These base tables are owned by an Oracle database user called SYS, which is created when the database itself is created. The base tables are never accessed directly, as their names are cryptic by design just to discourage users from querying and modifying them. 

To make it easier to access the data dictionary and get information on objects in the database, a series of views are created during the database creation process. These views are commonly referred to as data dictionary views. Oracle has three sets of data dictionary views. They are as follows:

 USER_ views: These views allow users to get information on objects that are in their schema (i.e., objects that they have created and own). 

For example log on as user HR

C:\>sqlplus hr/hr

Write and execute the following query

SQL> select table­_name from  user_tables

ALL_ views: These views allow users to get information on objects they own including objects other users have given permission to access. 

For example log on as user HR

C:\>sqlplus hr/hr

Write and execute the following query

SQL> select table­_name FROM  ALL_tables;

 DBA_ views: The DBA_ views, is designed to be used by the database administrator (DBA). It provides a full (global) information on objects in the database, i.e., any object created by any user. Normal users do not have access to these views, as special privileges are needed to SELECT from them.

For example log on as the user SYS 

C:\> sqlplus sys/oracle as sysdba

Please note that "oracle" is my password.

sql> show user

User is "SYS"

Write and execute the following query

SQL> select table­_name from dba_tables

The output shows all the dictionary tables in the database

Library Cache : This stores information about the most recently used SQL and PLSQL statements. Its Enables the sharing of commonly used statements.

Let me explain further.

The library cache size is based on the sizing defined for the shared pool. Memory is allocated when a statement is parsed. If the size of the shared pool is too small, statements are continually reloaded into the library cache, which affects performance. The library cache is managed by an LRU (Least Recently Used) algorithm. As the cache fills, less recently used executions paths and processed statements are removed from the library cache to make room for newer entries.

Database Buffer Cache

When a query is processed, the Oracle server process looks in the database buffer cache for any blocks it needs. If the blocks are not found in the buffer cache, the server process reads the blocks from the data files and places a copy in the database buffer cache.

If you see the solution to your query on the screen, then it’s as a result of it being in the database buffer cache. Let’s look at this screen shot:

The database buffer cache can be in several states

Pinned: This is another fancy way of saying “locked”. Pinned state occurs when it is currently been written into. In this state it cannot be aged out.

Clean: This is when the buffer is unpinned and ready for ageing out. Ageing is another way of saying “deleting”.

Free or unused: This is when the buffer is empty because the instance just started.

Dirty: This is when the buffer is no longer pinned and the contents of the buffer need to be flushed to the data file before it can be aged out. 

Redo Log Buffer
This is a buffer that contains changes made to data file blocks. This information is stored in redo entries. Redo entries contains the information necessary to recreate the data prior to the changes made by the DML and DDL statements.
Let me illustrate what redo log is with a simple arithmetic example:

4 + 3 = 7

Analysis is as follows:
- Initial data = 4
- Final data = 7
- Change data = + 3

Now if we want to revert from the final data of 7 back to the initial data of 4, we have to apply the redo data (- 3) which is always the opposite of the change data. Thus

7 – 3 = 4


Large Pool
This is an optional area of memory in the SGA. It relieves the burden placed on the shared pool like caching data for large operations such as recovery related activities.

 Java Pool
This is required when installing and using Java. It caches the most recently used java object and application codes within the Java Virtual Machine.

 Streams Pool
This caches data associated with queued message request when Oracles advanced queuing option is used. A queue buffer is memory associated with any data queue that contains only the captured events. The queue buffering mechanism enables the database to optimize captured events by buffering them in the System Global Area (SGA), instead of always storing them in a queue table on disk which would definitely slow down the databases performance.
Click here to understand more about Oracle Streams

So we are done with the SGA. 
Let us now go to the second half of the Instance which is the Background Processes

BACKGROUND PROCESSES

An Oracle background process is a mechanism that can run a series of jobs or tasks

There are several of them which I will explain in basic details.

Database Writer (DBWR)
This background process writes dirty buffers (we discussed this earlier) from the database buffer cache to the data file. It ensures that there are sufficient free buffer in the database buffer cache for the server process to write to.
It writes when the following occurs:
- When a checkpoint occurs.
- When dirty buffers reach its threshold
- When there are no free buffers

Log Writer (LGWR)

This is responsible for writing from the redo log buffer to the redo log file.

It writes under the following situations:

- When a transaction commits (ie finalizes).
- When the redo log buffer is one third full.
- Every 3 seconds
- Before the Database writer modifies blocks in the database buffer cache to the data files.

Because the redo is needed for recovery the log writer confirms the ‘COMMIT’ operation only after the redo is written to disk. We will see this demonstration in another post

System Monitor (SMON)

Due to the fact that the Oracle instance resides in a dynamic memory, If the Oracle instance should fail, any information in the SGA that has not been written to disk is lost. For example the failure of the operating system causes an instance failure. After the loss of the instance, the SMON automatically performs instance recovery when the database is reopened.

The instance recovery consist of the following steps:
- Rolling forward to recover data that has not been recorded in the redo log.
- Opening the database for user access.
- Rolling back uncommitted transactions with the application of undo segments.

Process Monitor (PMON)

The PMON is responsible for cleaning up after a failed process. It is done by doing the following:
- Rolling back the users current transaction.
- Releasing all currently held table or row locks.
- Freeing other resources currently reserved by the user.
- Restarts dead dispatchers.

Checkpoint (CKPT)

Every 3 seconds the CKPT stores data in the control file to identify that place in the redo log file where recovery is to begin.

It’s responsible for the following:
- Signaling the DBWR at checkpoint to write all dirty buffers to data files.
- Updating data files header with checkpoint information.
- Updating the control files with checkpoint information.

There are a few porcesses that need to be intialised before a background process can start.
Lets have a look at them:

 Oracle Process Structures

1. User process: This is a program that request connection or interaction with the oracle server. It is started at the time a database user requests a connection to the Oracle server.
2. Server process: This connects to the Oracle instance and is started when a user establishes a session with the user process.
3. Background processes:  These are started when an Oracle instance is started

A graphical illustration can be seen below:

Please note that the User Process cannot establish a direct connection to the Database. It has to go through the Server Process. That is the server Process is more or less like an Interpreter or a middle man between the User process and the Oracle Database.

Finally, the last part of the Oracle server is the Database. Lets have a look at it:

Oracle Database

This environment consist of files that store data permanently for future reuse. It consist of several files which I will explain

Control File:

This is a binary file that contains the structure of the database. It can be seen as a metadata (data of data) repository for the physical database.
Lets look at its attributes:

• It has the structure of the data files and redo log files that constitutes the database.
• It is created when the database is created and also when there has been some modification on the database like adding or renaming a data file.
• It also has to be updated frequently and should be available at all times.

Here are the contents of the control file

- Name of the database to which the control file belongs ( It can only belong to one database at a time)

• Database creation timestamp.
• Name, location and status of data files and redo log files.
• Name of tablespaces.
• Current log sequence number.
• Most recent checkpoint information.
• Recovery Manager backup information.

To me I see the control file as an ‘overseer’ who must make sure all components are working fine before they are made active again.

Multiplexing of Control files

This is the process of making several copies of a control file. You can have a maximum of 8 multiplexed control files.

Let’s view the control files that we have in our database.

Log on to the database as the sysdba

C:\>SQLPLUS/ AS SYSDBA

SQL> select name from v$controlfile;

In my screen shot I have three (3) control files with their locations. These are exact copies of each other and are called MULTIPLEXED Copies.

Data file: 

This is a logical grouping of related data blocks. We will talk about the data files in a later Post (Managing the Oracle Database structures)

Redo Log file:

This is responsible for instance recovery of the database. If the database crashes and does not lose any data file, then the instance can recover the database with the information in the redo log file.

Remember that the log writer is responsible for writing the redo entries from the redo log buffer cache into the redo log files inside the database. Once it’s there it is safe and can still be referred to again if the instance is restarted again.

The redo log contains groups. Each group must have a member. This members are exact copies of each other (You can call them twins if you want). Now each member must be on a different machine to safe guard against disaster. 

Imagine yourself as the Database administrator of an organization say a Bank. Since the redo log is only interested in the changes made to transactions it wouldn’t be wise to put them all on the same database server. It’s like putting your eggs in one basket. 

So what we’ll do is to put one of the redo log members in the Banks head office and put the other redo log member in the banks disaster recovery hot site. They will communicate through a network server. If there’s anything wrong with the redo log member in the head office, probably due to corruption, the backup member in the disaster recovery hot site can be used to save the day.

The activity going on in the image above is called LOG SWITCHING. This is when the Log writer moves from one redo log group to the other in an ordered fashion. Once it gets to the last redo log group, it log switches to the beginning of the redo log groups and starts all over again erasing every content of the previous entries.

Now that is NOT a good thing as it starts erasing!! Here is a solution to it.

To save guard against this disaster, ARCHIELOG must be enabled. What this will do is that, when there’s a log switch the contents of the former redo log entry is written into a file called an ARCHIVER. Here’s an illustration.

In this way redo log entries will not be missing.

When the redo logs are not been archived, it’s known as NOARCHIVELOG and when archiving is enabled it’s known as ARCHIVELOG

Enabling NOARCHIVELOG or ARCHIVELOG

First of all this is done when the database is not available to users. Here are the following steps taken.

Log on to the database as the sysdba and go through the following steps

1. Shutdown the database

SQL> shutdown immediate

2. Start up in the Mount state

SQL> startup mount

3. Alter the Database to the desired Log mode

SQL> alter database archivelog ;      or

SQL> alter database noarchivelog;

4. Confirm the changes made

SQL> select log_mode from v$database;

5. Open the Database

SQL> alter database open

Here’s a screen shot for you

 I hope you enjoyed the blog post and most especially, I hope it has helped in enlightening you about the inner workings of the Oracle database.

Stay tuned for more.