The Oracle DB (commonly referred to as Oracle RDBMS or simply Oracle) is an RDBMS (relational database management system) which was developed by the Oracle Corporation. It provides an open, comprehensive and integrated approach to information management. It is not simply a DBMS but an RDBMS.
There are many versions of Oracle – 9i, 10g, 11g, 12c and many other previous versions.
Here, i = internet, g = grid and c = cloud.
Installing the Oracle DB
Check out the video on this link : https://www.youtube.com/watch?v=d_CyuCLC3Ls
It provides a very nice demonstration of how to install the Oracle DB.
It is recommended to install it inside virtual box, as installation on the host OS may slow down its performance. However, there’s not a great issue if one doesn’t wants to do so.
The above image shows the basic architecture of Oracle 9i.
Oracle Architecture = Oracle Instance + Oracle Database
As, can be seen in the diagram, the oracle architecture is a combination of the Oracle Instance and the Oracle Database.
The architecture has been divided into 2 parts : Virtual and Physical.
The physical part is the one where all the actual data is stored and is not accessible by the normal user and the virtual part acts as an interface between the user and the database. For example, when we use the oracle database, we get the interface wherein we can type the queries and interact with the database. We are not directly accessing the database but the virtual part is accessing it for us and returning the results based on our queries.
Now, we move on to the architecture explanation:
Oracle Instance (Virtual Part)
If we look carefully into the diagram, we see SGA and PGA. These are just memory spaces or memory architecture if we take a closer look to the component names and their functions. And, the ovals we see are nothing but some background processes.
So, Oracle Instance = Memory Architecture + Background Processes
PGA : Program Global Area – is a non-shared area in the memory and is generally allocated to server processes.
SGA : System Global Area – is an area of RAM which is shared with and globally available to the processes of the Oracle Database.
Whenever, we connect to the DB, we get an instance of the memory in the SGA. We cannot access the PGA.
SGA is of 2 types : Fixed and Variable.
Variable SGA : Shared Pool, Large Pool, Database Buffer Cache, Redo Log Buffer are the main parts of a variable SGA. We call it variable because, we can alter the size of each of these components manually with the ALTER SYSTEM command. The sizes of these components depend on the INIT.ORA parameters. The INIT.ORA parameters of each of the components, are as follows:
Shared Pool : shared_pool_size
Large Pool : Large_pool_size
Database Buffer Cache : db_block_buffers
Redo Log Buffer : log_buffer
But we cannot alter the size of fixed SGA in any case.
Now, a brief discussion of the SGA components:
Shared Pool : It has 2 components – Library Cache and Data Dictionary Cache. The Library Cache holds the parsed SQL statement and its execution plans and PLSQL codes. The Data Dictionary cache holds the information about tables & columns definitions, passwords, user privileges, etc.
Database Buffer Cache : This part is used to hold data in the memory. Whenever a user accesses a data, it gets fetched into the database buffer cache and LRU (Least Recently Used) algorithm is used to manage it. Advantage – If the user asks for some data and it gets stored into the buffer cache, then, the next time if the user asks (in a short period of time) for the same data, time-consumption will be reduced. The data will be accessed from the buffer cache & Oracle process doesn’t has to read it from the disk again and again. Another advantage is data modification by the user is faster – modification will be done in the buffer cache rather than doing it on the disk directly. So, the buffer cache offers time-efficient and fast data processing.
The buffer cache has 3 components –
- Default Cache – If we define the cache size using the DB_CACHE_SIZE (or DB_BLOCK_BUFFER) then this will be the default cache.
- Keep Buffer – The Keep Buffer pool retains the schema object’s data blocks in the memory.
- Recycle Buffer – The Recycle Buffer pool eliminates the data blocks from the memory as soon as they are no longer required.
Large Pool : This is an optional block, but if it is defined then it is used for heavy operations like bulk copy during backup & restore operations.
Java Pool : This is also an optional block. This is used for all the session-specific Java code and data within the JVM.
Redo Log Buffer : This memory block holds the data which is to be written to the redo log file. Why is it needed? – To rollback the changes if the need be. The data is first written to the redo log buffer and afterwards it is written to a redo log file.
This completes the discussion of the SGA.
Moving on to the ovals i.e. the background processes in the Instance part.
PMON : Process Monitor – performs process recovery and is responsible for cleaning up cache in the database buffer and freeing up the resources.
Snnn : Shared Server Processes – It simply serves multiple client requests in the shared server configuration. Shared server processes and dedicated server processes provide the same functionality, except shared server processes are not associated with a specific user process. Instead, a shared server process serves any client request in the shared server configuration.
Dnnn : Dispatcher Processes – The dispatcher processes support shared server configuration by allowing user processes to share a limited number of server processes. So, we can say that these are processes which help the shared server configuration.
RECO : Recoverer Process – It is a background process used with the distributed database configuration & it automatically resolves failures involving distributed transactions.
QWMn : Queue Monitor Process – This is an optional background process which monitors the message queues.
ARCn : Archiver Process – It copies redo log files to a designated storage device after a log switch occurs. ARCn processes are present only when the database is in ‘ARCHIVE LOG’ mode, and automatic archiving is enabled.
LGWR : Log Writer – It is responsible for redo log buffer management i.e. it manages the redo log buffer. It writes the data from the redo log buffer to the redo log file on the disk.
CKPT : Checkpoint Process – It is responsible for updating file headers in the database datafiles, when a checkpoint occurs. A checkpoint occurs when Oracle moves new or updated blocks (called dirty blocks) from the RAM buffer cache to the database datafiles.
DBWn : Database Writer Process – It writes the content of the buffers to the datafiles.
SMON : System Monitor – It performs crash recovery, if necessary, at instance startup. If any terminated transactions were skipped during instance recovery because of file-read or offline errors, SMON recovers them. SMON is also responsible for cleaning up temporary segments that are no longer in use.
With this, the virtual part of the Oracle architecture gets completed. The explanation for the physical part is in Part II.
Part II : YET TO COME.
If you want to dig deeper into the explanation of the above topics, kindly refer to the Oracle Documentation.