Oracle Basic Architecture

Hi all,

As an oracle DBA you must have the very sound knowledge of oracle instance and its architecture.you should be able to differentiate between logical and physical layer of database.

In this article we will discuss the basic architecture of oracle database, what is an instance, what are physical components and logical components of oracle database.

oracle database is consist of two main components
1. Oracle Instance (virtual)
2. Oracle files (physical)

Oracle Instance:- It is an virtual component that is mounted on your system's physical memory (RAM) when oracle services starts. It occupies some space on your RAM and it is the component of the database which deals with the physical layer of oracle database. The following figure shows all virtual components of Instance, lets see them and discuss about them:-


As we have discussed that Instance is virtual and is allocated on our system's physical memory, one may ask 'what is instance made up of?', the answer to this question is, instance is made up of 'System Global Area (SGA)' and 'Background Processes', which communicate with physical files of database to serve request made by user.

It is recommended by oracle that 50%-70% memory should be given to SGA. We will discuss how to allocate size to SGA.Now SGA is consist of following components:

1. Shared Pool:-This is area of SGA which is divided into two parts, one is library cache which stores information about commonly used SQL and PL/SQL statements. means again if the same statement is issued by another user then oracle would not require to access it from datafile (discussed later) and can prevent lot of hard work. It is maintained by Least Recently Used algorithm, means the statement which are not used frequently are thrown out of library cache.

The other part is called as dictionary cache which holds information about definition of objects in database, indexes, users and their privileges, authentication information so that no invalid user can access database etc.

2.Database Buffer Cache:-Buffer cache is used to stores the copies of data block that retrieved from datafiles. That is, when user retrieves data from database, the data will be stored in buffer cache. Its size can be manipulated via DB_CACHE_SIZE parameter in init.ora initialization parameter file.

Just don't think about other buffer pool components specified in the figure, we will discus them later.

3. Redo Log Buffer Cache:-Each DML statement (insert, update, and delete) executed by users will generates the redo entry. What is a redo entry? It is an information about all data changes made by users. That redo entry is stored in redo log buffer before it is written into the database physical structure. To manipulate the size of redo log buffer, you can use the LOG_BUFFER parameter in init.ora initialization parameter file.

you will get complete idea about redo log file in our next discussion 'Oracle Physical Components'.

4. Large Pool:-Large pool is an optional area of memory in the SGA. It is used to relieves the burden place on the shared pool. It is also used for I/O processes. The large pool size can be set by LARGE_POOL_SIZE parameter in init.ora initialization parameter file.


5. Java Pool:-As its name, Java pool is used to services parsing of the Java commands. Its size can be set by JAVA_POOL_SIZE parameter in init.ora initialization parameter file.

Now as we have discussed the SGA components, let us now switch Background process which work behind the scene in instance, in cooperation with SGA:-

1. DBWn:-Database writer (DBWn) process is used to write data from buffer cache into the datafiles (in next discussion). Historically, the database writer is named DBWR. But since some of Oracle version allows us to have more than one database writer, the name is changed to DBWn, where n value is a number 0 to 9.

2. LGWR:- Log writer (LGWR) process is similar to DBWn. It writes the redo entries from redo log buffer into the redo log files.

3. CKPT:- Checkpoint (CKPT) is a process to give a signal to DBWn to writes data in the buffer cache into datafiles. It will also updates datafiles and control files header when log file switch occurs.

4. SMON:- System Monitor (SMON) process is used to recover the system crach or instance failure by applying the entries in the redo log files to the datafiles.

5. PMON:- Process Monitor (PMON) process is used to clean up work after failed processes by rolling back the transactions and releasing other resources.

So, in this article we discussed the basic virtual (logical) components of oracle database and instance. Physical structure of database is some thing which is easy to understand and main component . So let us see it in next discussion.

Your comments are always welcome.