How Oracle database works
Certifications:
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
- How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024
One of the most famous RDBMS(database management system) with one of the largest percentages in the market is the Oracle Database.
What a special offer
Compared to the competition, Oracle DB offers active/active technology, i.e. a clustering of different servers that all have the ability to read and write to the database at the same time that you name RAC (Real Application Cluster). However, it is very important that it does not block the select independent isolation level, which means that when a user reads a piece of information, a second user can simultaneously modify this piece of information without affecting the first one. It is also known for the ease of managing databases of several terabytes.
How does it work
The architecture of Oracle Database in version 12c is this.
You didn't get any idea did you?
Makes sense!! So I made a much more simplified diagram with its most basic elements:
Let's explain each part of it
Instances and Databases
In Oracle's RDBMS, all RDBMS software that manages the database is defined as an instance, and its physical files are defined as a database.
Control file
The control file is a file that contains information about the structure of the database such as the names and location of datafiles and redo files as well as the log sequence number (LSN) it is currently in.
SPFILE and PFILE
In the SPFILE or otherwise Server parameter file, all the parameters of the database are stored such as its name, the location of the control file, the memory (ram) it occupies, the location and the size of the FRA (recovery area). But we cannot modify this file with a text editor. For this reason we can create a PFILE which contains all the information of the SPFILE with the only difference that it is in text and we can modify it. Finally we have the possibility to replace the old one or to make a new SPFILE from the PFILE we modified.
Listener
It is the service responsible for allowing a database connection to the correct database instance.
Program Global Memory (PGA)
It is the memory (ram) that occupies each session-connection in the database.
System Global Memory (SGA)
It is the memory (ram) that the instance occupies as shared for all its tasks through buffers and cache.
Buffer cache
Loads blocks from datafiles into memory (ram) for reading or processing. As long as it has them loaded in it, the hard disk does not need to be accessed, otherwise, as many blocks as are still needed are loaded in the buffer cache.
Redo Logs
Every action performed on the database is recorded in the redo logs, this gives it the ability to recover in case of failure or restore up to this moment.
Archived redo logs
When the database is in archived log mode, the redo logs do not delete the information once they are full until they are archived. This mode allows you to backup the database online, i.e. while it is accessible by everyone, as well as to be able to restore it at any time in the event of a restore.
Undo tablespace
The undo has 2 uses, firstly, blocks of the data that are going to be changed are written in it so that in case the transaction is rolled back, they can be restored from there and secondly, in case we start the execution of a select statement (to read data) they are written there so that the datafiles with the actual data continuing to update normally without delay or blocking.
Users tablespace
It is the default tablespace (storage space) for non-system objects, we can make many tablespaces with different names to have better management.
System/SysAUX/TEMP DB
They are system tablespaces required for database operation.
Flashback logs
They are the logs needed for the flashback query and physical flashback functions of the base (we will explain in another article what these functions do)
Fast/Flash Recovery Area (FRA)
It is the default space for flashback logs, archived logs and backups.
In closing, I would like to inform you that for the description through the diagram several oversimplifications have been made and some intermediate paths have been removed so that it can be easily understood.
