Table of Contents
Introduction
Today, I came up with an interesting topic that is frequently asked in Oracle DBA interviews, which is Oracle Architecture. We will understand how to explain Oracle architecture in an interview, along with the Diagram, Components, and Interview Questions.
Oracle Architecture Diagram
Note: – If you want to practice this whole activity in your home lab, then you'll need a platform to perform the installation. To set that up, you first need to download and install Oracle VirtualBox, followed by the operating system, the Oracle binary software, and finally, create the database.
How to explain Oracle architecture in an interview
Oracle Architecture is a topic that always comes up during an Oracle DBA interview, and many candidates find it a bit challenging to explain clearly under pressure.
I will explain each step of how to explain Oracle architecture in an interview.
Oracle Architecture is a combination of 2 components.
1) Instance
It is again segregated into 2 parts (Memory + Background Processes)
2) Database
It is a collection of physical files like the Control files, Data files, Log files, Password file, Parameter file, and Temp files.
Let's discuss the Instance and Database section in detail.
INSTANCE
An instance is made up of Memory Structure and Background Processes, which is basically called as Logical Structure.
A) Memory Structure (SGA+PGA)
Now, let's discuss the components of Oracle architecture.
1A) SGA (Shared/Server/System Global Area)
SGA is a Part of Memory where Important data and information are stored, so that users can connect to the database and fetch their data quickly.
The SGA is a shared memory area that all users connected to the database can use together.
Whenever the Instance starts during that time, it will maintain some fixed objects. The main reason is to maintaining the fixed objects is to maintain the Instance information. The area where the fixed objects are stored is called SGA. Kindly check out the link to learn more about fixed objects.
Components of SGA –
===============
1) Shared Pool – This area helps to store SQL statements, user credentials.
Components inside the Shared pool –
===========================
1a) Library Cache – In this area, all the SQL statements are stored, but before storing it will perform checks like Syntax, Semantics, and then the parsing of the SQL statement is performed.
1b) Data Dictionary Cache – In this area, object existence and user credentials are stored.
2) Database Buffer Cache – It helps to cache the datablocks that come from physical disks or datafiles.
3) Redo Log Buffer – It helps to keep the records of the changed blocks which has been performed in the Database buffer cache, which will help in case of recovery.
Old Copy of data and New data both are recorded in the redo log buffer, after that the log writer will write the data from the redo log buffer to the redo log files permanently.
4) Large Pool – It helps to perform the large operations like RMAN backup and Recovery. It is an Optional memory in SGA.
5) Java Pool – It helps to perform Java-related operations in Oracle like Java code executions.
6) Streams Pool – It helps to move the data from one database to another database, like datapump operations, where we move data from one database to another database.
We have now covered the components of SGA. Now let's move towards the other components.
1B) PGA (Programmable/Private Global Area)
The Oracle memory space known as the PGA is utilized exclusively by a single user session to hold data and information required for processing tasks such as managing session variables and sorting data. No other users have access to it.
The major task of the PGA memory is sorting, session data, and work areas for a single user’s operations.
B) Background Processes
1) LREG – LREG stands for the Listener Registration process. It helps to register the database services with the Listener. Till version 11g, PMON was the process that was doing the same task, but due to the high load, Oracle introduced a new process called LREG (Listener Registration) to manage the database services and help them to register in the Listener.
2) DBWRn – DBWRn stands for Database Writer process. It will write DATA from Memory (Database Buffer cache) to Datafiles. Oracle allows up to 36 Database Writer processes in total, ranging from DBW0 to DBW35.
3) LGWR – LGWR stands for Log Writer process. It will help to write the logs from the Log buffer to the redo log files.
4) PMON – PMON stands for Process Monitor process. It will help to clean up the failed processes.
5) SMON – SMON stands for System Monitor process. It helps in the crash recovery during the startup of the database when the database was abruptly shut down.
6) MMON – MMON Stands for Manageability Monitor Process. It helps to capture the snapshots which are later stored in AWR. (It works based on timing.)
7) MMNL – MMNL Stands for Manageability Monitor Light Process. It helps to collect the sample for the ASH reports (It works based on the samples.)
8) RECO – RECO stands for Recovery process. It helps to perform the database recovery.
9) CKPT – CKPT stands for Checkpoint process. It helps to update the control files and the data files headers.
10) ARCn – ARCn stands for the Archiver process, which helps to copy redo logfiles to the archive location for backup and recovery purposes. The maximum number of ARCn processes in Oracle is 30 from range ARC0 to ARC29.
We have covered the 1st part of the How to explain Oracle architecture in an interview which is INSTANCE, and now let's check out the 2nd part, which is DATABASE.
DATABASE
A database is considered a Physical structure of an Oracle Architecture. Let us understand the components.
1) Parameter File – In this file DBA can put the required parameters like the Database name, the Control file path etc, to start up the Instance.
2) Control Files – When the instance is started then the next stage is to put the database in the Mount stage, for which the control file is required because it holds the crucial information like database name, database id, Database Creation Time, Datafile Location etc.
In the short term, the Control file holds the metadata of the database.
3) Data Files – Finally, all the files will get opened for read-write purposes, which means the database comes up in the Open stage. Data files are the files that actually hold the user data, indexes and segments.
4) Redo Log Files – Redo log files help to store all changes made to the database for recovery purposes.
5) Archive Log Files – It helps to copy the redo log files for the database backup and recovery purposes.
6) Password File – It helps to store the passwords of the users who have SYSDBA and SYSOPER privileges.
Oracle Architecture Interview Questions
1) When we start up the instance, how many areas will be shown?
Ans) We will see the 4 Components during startup, but actually total there are 7 components.
Fixed Size, Variable Size (Shared Pool, Java Pool, Large Pool, Streams Pool), Buffer Cache and Redo Buffer.
2) Which Background process will perform the Syntax check?
Ans) The Server process will perform the syntax check.
3) Which Background process will build the execution plan?
Ans) The engine called Optimizer will build the execution plan.
4) Where will the sql_text will get stored?
Ans) It will be stored in the view called v$sql.
5) When does the Log writer write from the Log Buffer to the redo log files?
Ans) a) Every 3 sec
b) Every checkpoint
c) 1/3 full of redo buffer
d) Every commit.
Conclusion
We have seen from the above steps how to explain Oracle architecture in an interview in Oracle.
If you enjoyed the article, please leave a comment and share it with your friends. Also, let me know which Oracle and MySQL topics you'd like to see covered in future articles.
