Oracle Database Architecture

Back to: Oracle Tutorials for Beginners and Professionals

Oracle Database Architecture

In this article, I am going to discuss Oracle Database Architecture in detail. Please read our previous article, where we give a brief introduction to the Oracle database. As part of this article, we are going to discuss the following pointers in detail.

1. Introduction to Oracle Database Architecture
2. Physical structures Logical structures
3. Database Memory Structures Background Process
4. 2 Tire, 3 Tire, N-Tier Architecture

Introduction to Oracle Database Architecture

Oracle database software is designed to support the architecture of instances and databases.

The database consists of data files, control files, online redo log, archived redo log, flashback log so these are database physical files. The instance consists of two things one is SGA and another one is background processes. Components of SGA are shared pool, large pool, database buffer cache, redo log buffer, java pool, streams pool. PMON, SMON, RECO, MMON, MMNL are the background processes. PGA consists of SQL work areas, session memory, Private SQL area which is consists of one server process. When the client sent a request from the client process it goes to the server process and then the server process performs the activity that is requested by the client. Like we request for a SELECT statement so the server process will perform its activity and gives a SELECT statement.

Let us pick up a basic “Select” statement ran by a user and understand what exactly happens in the database.

1. At 1, the User issues a SELECT statement which is carried over to the database (only after connection established using listeners) by the user process.
2. At 2, as there will be a server process created for this user in DEDICATED server architecture, SQL is passed into this PGA and in turn, creates a CURSOR in it. So, every SQL statement will have the cursor in the memory until query execution is completed.
3. At 3, after the cursor is created, the syntax check will let the query move to SGA.
4. At 4,5,6,7, now the system will verify if the query is already cached. If yes, picks up the execution plan and if no, parsing happens and a new execution plan is created by the optimizer.
5. At 8,9, now data is pulled into buffer cache with the help of a large pool to perform I/O operations smoothly.
6. At 10,11, in the case of the ORDER BY clause, sorting takes place in the server process that is PGA and data is sent back to the user process. Now that query executed successfully, the cursor area is released on the database server.

Background Process in Oracle Database Architecture:

The background process is a mechanism in an operating system that can run a series of steps.

1. Client processes: These processes are created and maintained to run the software code of an application program or an Oracle tool. Most environments have separate computers for client processes.
2. Background Processes: Background processes asynchronously perform I/O and monitor other Oracle Database processes to provide increased parallelism for better performance and reliability.
3. Server Processes: These processes communicate with client processes and interact with Oracle Database to fulfill requests. The processes that perform activities on behalf of clients-that is the processes that actually executes the user’s SQL statements-are referred to as server processes.

When a client program wants to execute a SQL, it relies on a server process and returns the results.

• Dedicated servers- A dedicated server performs tasks on behalf of one client. This sort of server is started when the clients connect and are destroyed when the client disconnects. Dedicated servers are usually the default.
• Shared servers– Shared servers perform tasks on behalf of multiple clients.
• Parallel Query servers– Oracle can use multiple parallel processes to execute a single SQL statement.
• Job and Scheduler servers– The job and scheduler servers execute commands in the background and schedule as required.

The processes (or threads in the Windows OS) that perform tasks on behalf of all sessions or that perform Oracle internal activities are called the background processes.

Physical and Logical Structure in Oracle:

Let us understand the Physical and Logical Structure in detail.

Physical Structure:

The architecture includes:

1. Physical components
2. Memory components
3. Processes
4. Logical structure

What is the Oracle server?

An oracle server consists of an oracle instance and an instance is basically something memory plus some background process and an oracle database files. An oracle database includes several different types of files: data files, control files, redo files, archive redo log files, parameter files, and password files. An oracle instance has two different sets of components:

1. The set of background processes like PMON, SMON, RECO, etc.
2. The set includes the memory structures that consist of the Oracle instance.

What is Oracle Physical Structure?

An oracle physical structure consists of the following files: –

1. Data Files: These files store the database information that a firm or organization needs in order to operate.
2. Control File: These files store database creation date and time, SCN information, backup and recovery information, and others.
3. Redo log files: These files are used to recover the database in the event of any failures.
4. Archived redo log files: These files are used to recover the database if there is some failure in the disk.
5. Parameter files: These files used to specify parameters for configuring an Oracle instance when it starts up.
6. Password files: Authenticates only to the special users of the database.
7. Alert and trace log files: These files store information about faults and steps taken, that affect the configuration of the database.

Logical Structure:

An oracle logical structure consists of the following files: –

1. Table Space: A tablespace is a logical container for storing objects such as tables, indexes, sequences, clusters, and database objects. Each tablespace has at least one physical data file that actually stores the tablespace at the operating system level. Large table space may have more than one data file allocated for storing objects assigned to that tablespace.
2. Segment: When a logical storage object is created within a tablespace, for example, an employee table, a segment is allocated to the object.
3. Extent: Every object has one segment which is a physical collection of extents. Extents are collections of contiguous disk storage blocks. A logical storage object like a table or index every time consists of at least one extent. Preferably, the initial extent allocated to an object will be large enough to store all data that is initially loaded.

Block: The oracle server manages data at the smallest unit which is called a block or data block. Data are actually stored in the blocks.

Database Memory Structure Background Process:

The memory structure includes two areas of memory:

1. System Global Area (SGA): SGA allocates when an oracle instance starts up.
2. Program Global Area (PGA): PGA allocates when a server process starts up.

System Global Area (SGA):

System Global Area (SGA) is a memory area that stores information shared by database processes and by users of the database. It is a read and writes memory area. It is allocated when an oracle instance (database) is started up based on values specified in the initialization parameter file (either PFILE and SPFILE).

Components of SGA:

The SGA has the following memory structures:
1. Database Buffer Cache.
2. Redo Log Buffer
3. Java Pool
4. Streams Pool
5. Shared Pool – includes two components
            Library Cache
            Data Dictionary Cache

Additional memory structures in the SGA include:
1. Large Pool

Program Global Area:

PGA is a non-shared memory region that contains data and control information exclusively for use by an oracle process. It is created by the Oracle database when an oracle process is started, only one PGA exists for each Server Process and each Background Process. It stores data and control information for a single background process. This is not a shared part of memory, one PGA to each process only. The total instance PGA is the collection of individual PGAs.

The architecture of PGA:

When we are talking about the individual PGA there under the PGA different components are there one is session memory one is a persistent area a runtime area that is called a private SQL area that is the small area size, hash area, bitmap merge area that all called as a SQL work area. When the multiple PGAs are allocated that is called one instance PGA.

The content of the PGA includes the following:

Private SQL Area: Stores information for a parsed SQL statement – stores behind variable values runtime memory allocations.

1. Dedicated Server Environment – It is the private SQL area is located in the Program Global Area.
2. Shared Server Environment – It is the private SQL area is located in the System Global Area.

Session Memory: Memory that holds session variables and other session information.

SQL Work Area: Memory area allocated for sort, hash-join, bitmap merge, and bitmap create types of operations.

2 tier, 3 tier, and N tier Architecture:

Software Architecture consists of 1 Tier, 2 Tier, 3 Tier, and N Tier, this tires also can refer as a layer. Three layers involved in the application are the Presentation layer, Business layer, and Data layer.

Presentation Layer:

It is also known as the Client Layer. The topmost layer of an application is the layer we see when we use software by using this layer, we can access the web pages. The main function of this layer is to communicate with the application layer, this layer passes the information which is given by the user in terms of keyboard actions, mouse clicks to the application layer.

Example: login page of Gmail where an end-user could see text boxes and buttons to enter user id, password, and to click on sign-in. In simple words, it is to view the applications.

Application Layer:

It is also known as Business Layer which is also known as the logic layer. As per the Gmail login page example, once the user clicks on the login button, the application layer interacts with the Database layer and sends required information to the presentation layer. It controls an application’s functionality by performing detailed processing. This layer acts as a mediator between the presentation and the database layer. Complete busyness logic will be written in this layer.

Data Layer:

The data is stored in the data layer. The application layer communicates with the database layer to retrieve the data. It contains methods that connect the database and performs the required action. In simple words, it is to share and retrieve the data.

Example: Insert, update, delete, etc.

Types of Software Architecture:

1 tier Architecture:

It is also known as stand-alone architecture, it has all the layers such as presentation layer, application layer, and database layer in a single software package application which handles all the 3 tires such as Mp3 player, MS Office comes under one-tier application, the data is stored in a local system or in a drive.

2 tier Architecture:

It is also known as client-server architecture which is divided into two parts client application and database application. The client system handles both the presentation and application layer and the server system handle the database layer, the communication takes place between the client and the serve. The client system sends the request to the server system, then the server system process the request and sends back the data to the client system.

3 tier Architecture:

It is also known as a web-based application that is divided into 3 parts presentation layer, application layer, and database layer. Client system handles presentation layer application server handles application layer and server system handles database layer.

N tier Architecture:

It is also known as the distributed application it is similar to three tiers architecture but the number of applications is increasingly represented in individual tiers in order to distribute the business logic so that the logic will be distributed.

Here, in this article, I try to explain Oracle Database Architecture in detail. I hope you enjoy this Oracle Database Architecture article.