When I was in college, there was this one guy who intrigued me particularly. Because during the exam times, while everyone was trying to memorize everything they could till the last minute, this guy spent very less time studying than the other people. He even would go out and play for a while the day before the exam. I mean who does that just before the final exams? Much to our surprise, the same guy always got more than 85% in the all the exams in India. That is equivalent to getting straight As in the USA.
It took me some time to understand what he did differently than others. The point was that he had always spent more time understanding the fundamentals thoroughly. Once he gets through the basics, the content or theory, which other people found very difficult, would be a piece of cake for this guy.
Many years later, when I started learning Oracle, the instructor who taught me Oracle mentioned that everything in Oracle depends on Oracle Architecture. Oh, boy ! Was that true? In about 8 years of my career, I have realized so many times, regardless of which kind of issue I deal with, it always traces back to the architecture of Oracle.
Yet, I often come across people in Oracle interviews, where I ask them a basic question from Oracle architecture and they give me an incorrect answer. The interview wouldn’t last very long after that point.
Why is Oracle Architecture so very hard to understand for some people?
Even though Oracle’s architecture has evolved beautifully over the last two decades, the core components still remain the same. But Oracle has a way of coming up with fancy, complex-seeming terms that can scare people sometimes.
In your school or college days, do you remember reading something that you didn’t understand and that you thought was very complex? And later you called one of your genius friends and asked him to explain it. He would break this concept down in simple words in 2 minutes. You would be like “Really, thats it?”
I want to do the same for you in this article. My intention is to explain to you the core architecture of Oracle in simple words in a way even high school students could understand it.
So lets start.
Relation Database Management System (RDBMS)
For starters, lets see with what DBMS & RDBMS mean. I promise to not bore you with too much information about this. Because if you’re reading this post, you most probably already know what they mean. Database management system (DBMS) is basically a system, where you store, organize, & retrieve data from. When a DBMS complies with some or all of E. F. Codd’s 12 rules, it becomes Relation Database Management System (RDBMS). Some examples of RDBMS are Oracle, SQL Server, MySQL, PostgreSQL etc.
An Oracle database is a combination of two things.
1. Oracle Instance – Memory + Processes
2. Database files
Much like how your operating system uses your system memory to speed things up on your pc or laptop, Oracle uses memory allocated to it for the same purpose. When you create an Oracle database, you must allocate a portion of your system or server memory to Oracle. For example, if your server has 16G of memory, you probably want to allocate 10G to oracle and leave the other 6G for your operating system. These settings are modifiable, but most of them need the database to be restarted.
Before I show you how Oracle uses this memory, I want you to think about what you will do with the database at a high level. As a database user, you will typically load data into the database, update or modify what you loaded, and remove data when you dont need it. And you will use Structured Query Language (SQL) and Procedural Language Extension to SQL (PLSQL).
For example, when the user of your application or product signs up on your website, what happens behind the scenes is that your application is executing the following statements in the database.
— Signing up
Insert into customers (id, first_name,last_name,email) values (1,’John’,’Smith’,’firstname.lastname@example.org’);
— Modifying existing information
update customers set email=’email@example.com’ where id=1;
— delete the account
delete customers where id=1;
Regarding the memory part, what I am going to talk about is the most common configuration of Oracle memory. It can change a bit depending on a company’s database requirements and the nature of the database.
The memory you allocate to Oracle is divided into 2 main areas – System Global Area (SGA) & Program Global Area (PGA). SGA is divided into Shared Pool, Data Buffer Cache, and Log buffer. Shared pool is split into Library Cache and Data Dictionary Cache. Oracle stores your queries (ie SQL/PLSQL) in the Shared Pool area. The data you insert/modify/delete are put in Data Buffer Cache. The changes you make in the database are written first into log buffer as change records.
PGA is basically where users’ session information is stored. It also contains SQL Workarea where sort operations are done for your queries.
Before I explain about the various processes, I want to give you a quick peek into database files. Because oracle processes primary move data to & fro between these files & the above mentioned memory areas.
Let’s get straight into it.
Data Files: From the name, you can guess that this is where Oracle stores the actual data that you load into the database.
Redo Log Files: The change records about the changes you make to the database are stored permanently in this file. Since these files are extremely important in case your database crashes, these files are archived regularly. These files when archived become archive log files.
Control files: This file is also very important to the functionality of Oracle database. Among a wide range of information, some of the things that Oracle store here are locations of the data files, locations of the redo log files, System Change Number or SCN ( Think of it a watch that oracle looks at, which behaves like a counter ), database backup information etc.
Parameter file: When starting up the database, Oracle processes will read the parameter file to configure the database. This is where you set the memory allocation, size of database blocks, control file locations etc.
If I start writing the definition of each Oracle process which exists, this article will turn into a book. You’re going to learn only about a few important Oracle Background processes.
I can hear you asking “Background processes? What about foreground processes?”
Basically, Oracle database, in order to accomplish the tasks that you tell it to do, uses its background processes. Foreground processes are user processes, which are created when a user connects to the database via a tool like SQL*Plus, SQL Developer, a Java program etc.
I am going to explain to you about five background processes, which play very important roles in the context of an Oracle database.
DBWR – Database Writer process – This process flushes the data blocks from data buffer cache into data files.
LGWR – Log Writer Process – This process flushes the change records from log buffer cache into redo log files.
CKPT – Checkpoint Process – It kicks in at regular intervals to trigger log writer and database writer process to flush the contents of log buffer & data buffer cache into redo log and data files respectively.
SMON – System Monitor – While starting a database after it crashes, smon performs instance recovery. It will ensure only committed changes are persisted in the database and the uncommitted changes are rolled back.
PMON – Process Monitor – It basically performs the house keeping job of cleaning up database locks after a user process fails or gets killed.
ARC – Archiver Process – It archives the redo log files asynchronously so that in case the database crashes for any reason the database can be recovered to a time very close to the crash using the redo data in these files. Data loss will be minimal.
Well, there is many more details I didn’t cover in this article because this is just a 10,000 ft view of Oracle architecture. But if you know this much, you can confidently say that you know the fundamentals of Oracle architecture.
P. S. If you’re aspiring a career with Oracle database and having difficulties in understanding any underlying concept, please let me know by attending this simple survey.
I will help you out on individual basis for free. There is no catch. I don’t even have any product to sell. I am just trying to find what people struggle with respect to learning Oracle database administration.
If you don’t like taking surveys, please leave a comment below this post for this question – What is the single most challenging thing in learning Oracle Database Administration now?