Oracle Database Architecture

In this article, I will try to explain Oracle Database Architecture. The general purpose of a database is to store and retrieve related information. First thing that strikes to our mind when we talk about database is table which contains the data in the form of rows and columns. In oracle database, this table is a  logical structure that is inside another  logical entity called tablespace, which is mapped to physical files and are called as datafiles.  So for any database we need a storage. 

In order to perform any operation in the database, like inserting data into a table, fetching data from a table, we need an area called memory. This memory exist in database server main(or primary) memory.  All these database activities are carried out by processes, threads in case of windows.  In general a process is defined as a “thread of control” or a mechanism in an operating system that can run a series of steps and these are jobs that work in the memory of the computers.  Oracle database uses memory structures and processes to manage and access the database. 

So any database structure involves three components which are

• Memory structures
• Processes structures and 
• Storage structures

Oracle Database Architecture

In oracle, Memory and Background processes together called as Instance. Database is a set of physical files on disk created by  CREATE DATABASE statement. However, in common language Instance and Database together called Database. Above diagram depicts the simple architecture of a database.

Let us understand how both Instance and Database are structured.

Instance

Database files themselves are useless without the memory structures and processes to interact with the database. When a database is started on a database server, the Oracle software allocates a shared memory area called the System Global Area (SGA) and starts several Oracle background processes. On Windows, an Oracle background process is a thread of execution within a process.This combination of the SGA and the Oracle background processes is called an Oracle instance.  After starting an instance, the Oracle software associates the instance with a specific database by reading the controlfile. This is called mounting the database. The database is then ready to be opened, which makes it accessible to authorized users. Multiple instances can execute concurrently on the same computer, each accessing its own physical database.Server processes, and the process memory allocated in these processes, also exist in the instance. The instance continues to function when server processes terminate.

The basic memory structures associated with Oracle Database include

The System Global Area(SGA) :is a shared memory region that contains data and control information for one Oracle instance. Oracle allocates the SGA when an instance starts and deallocates it when the instance shuts down. Each instance has its own SGA. Note that Oracle uses shared memory for implementing the SGA, which needs to be visible to all database sessions. The total size can't exceed the SHMMAX kernel parameter set at OS level.the The SGA consists of several memory components, which are pools of memory used to satisfy a particular class of memory allocation requests. All SGA components except the redo log buffer allocate and deallocate space in units of contiguous memory called granules. Granule size is platform-specific and is determined by total SGA size.

 The SGA contains, among other things, the database buffer cache that is used to cache information read from the data files, a data dictionary cache used to cache metadata information, and a library cache that caches recently used SQL and PL/SQL statements.

The SGA includes the following data structures:

• Database buffer cache: Caches blocks of data retrieved from the database 
• Redo log buffer: Caches redo information (used for instance recovery) until it can be written to the physical redo log files stored on the disk
• Shared pool: Caches various constructs that can be shared among users.It has two sub components namely data dictionary cache and Library cache. data dictionary cache is used to cache metadata information and a library cache caches recently used SQL and PL/SQL statements.
• Large pool: Is an optional area that provides large memory allocations for certain large processes, such as Oracle backup and recovery operations, and I/O server processes
• Java pool: Is used for all session-specific Java code and data within the Java Virtual Machine (JVM)
• Streams pool: Is used by Oracle Streams

The Program Global Area : also known as the Private Global Area (PGA) is a nonshared memory region  that contains data and control information that is private to a particular oracle process. . The PGA is created by Oracle Database when an Oracle process is started. One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs. The PGA is used to allocate memory for information such as sort space, variables, arrays, and cursor information that is private to each process.

User Global Area (UGA): The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session. Essentially, the UGA stores the session state. The UGA must be available to a database session for the life of the session. UGA cannot be stored in the PGA when using a shared server connection because the PGA is specific to a single process. Therefore, the UGA is stored in the SGA when using shared server connections, enabling any shared server process access to it. When using a dedicated server connection, the UGA is stored in the PGA 

Software code areas :Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from user programs—a more exclusive or protected location.  

A database instance contains or interacts with the following types of processes 

1. Client processes run the application or Oracle tool code. This also called as user process. 

2. Oracle processes run the Oracle database code. Oracle processes including the following subtypes: 

• Background processes start with the database instance and perform maintenance tasks such as performing instance recovery, cleaning up processes, writing redo buffers to disk, and so on. 

The most common background processes are the following:

       System Monitor (SMON): Performs crash recovery when the instance is started following    a failure

       Process Monitor (PMON): Performs process cleanup when a user process fails

       Database Writer (DBWn): Writes modified blocks from the database buffer cache to the data files on the disk

       Checkpoint (CKPT): Updates all the data files and control files of the database to indicate the most recent checkpoint

        LogWriter (LGWR): Writes redo log entries to the disk

        Archiver (ARCn): Copies redo log files to the archival storage when a log switch occurs

•      Server processes perform work based on a client request. For example, these processes parse SQL queries, place them in the shared pool, create and execute a query plan for each query, and read buffers from the database buffer cache or from disk. 
•  Slave processes perform additional tasks for a background or server process. 

The process structure varies depending on the operating system and the choice of Oracle Database options. For example, the code for connected users can be configured for dedicated server or shared server connections. In a shared server architecture, each server process that runs database code can serve multiple client processes.