Oracle Architecture

What is oracle server

Oracle Server:- This is the computer where oracle software has been installed and database has been created. Consist of two components.

Instance (SGA & Background Process) and Database.

Instance part (memory area) is also known as logical part. Logical part / Instance part cannot be seen at any mount point (in the case of linux) / c: drive or  d: drive(in the case of event). Instance Component can be seen only by using the various command.

SQL>Show parameter sga;

SGA (shared Global area / system global area)

SGA is a group of shared memory structure that contains data (in the Buffer cache) and control information (in for the form of Undo (old image in case user has changed the data) for one oracle Database instance. If multiple users are connected concurrently to the same instance, then the data in the instance’s SGA is shared among the users. This memory area is known as shared global area because this area is used by multiple users / being shared by multiple users. Oracle automatically creates the memory for SGA based on the information provided for the various parameters in Pfile when we start the instance and release the memory when we shut down the oracle.

Size of the SGA is depends on the various parameter defined in the PFILE / SPFILE(SGA_TARGET / MEMORY_TARGET etc.)

SGA Component:-

• Shared pool.
• Database Buffer Cache.
• Redo Log Buffer.
• Jave Pool
• Stream Pool.
•  Large Pool.

Size of these above said components is managed by the oracle itself. Size of the the SGA component cannot be more then the size of SGA_TARGET / MEMORY_TARGET value. 

Shared PooL

• Is a portion of SGA
• Is further divided in two parts

1.      Library cache

a.      Shared SQL area.

2.      Data dictionary cache

Library Cache:- Whenever the statement is executed by the user (select * from emp), oracle checks the statement in terms of syntex is known as parsing. Parsing is done in Library Cache.

What steps parsing involve

Syntax check – is the SQL statement syntactically correct

Semantic check – is the SQL statement meaningful or semantically correct. Does the table exist, are the columns in the SQL part of the table, does the user have the required privileges on the table etc

there are 2 types of parsing in the database.

Soft Parsing and Hard Parsing

Anytime a session issues SQL statement that does not already exist in the shared pool, then Oracle has to do a hard parse. essentially performing all of the above steps.  If the statement already exists, then a soft parse occurs

In other words, a soft parse does not require a shared pool reload

A hard parse is when your SQL must be re-loaded into the shared pool.  A hard parse is worse than a soft parse because of the overhead involved in shared pool RAM allocation and memory management.  Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. 

Data Dictionary Cache:- after completion of parsing, all the checking like user name and password entered is correct or not, priviledges to make the connection has been given to user or not are checked with the help of data dictionary cache.

Database Buffer Cache:-

• Is the part of SGA. 
• Hold copies of the data blocks that are read from the Data Files
• by the server process. 
• Is shared by all the concurrent users.

When first time any user requires a particular piece of data, Server Process created for that user search for the data block in the database buffer cache. If the process finds the data already in the cache (when the required data block available in the database buffer cache known as cache hit), it can read the data directly from memory. If the process cannot find the required data block in the cache / memory (when the required data block is not available in the database buffer cache and need to read from the data file known as cache miss), it must copy the data block from a data file on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss.

The buffers in the cache are managed in LRU method.

Redo Log Buffer 

Is a part of SGA.

Holds information about changes made to the database

 Redo entries are used for database recovery purpose.

Redo log buffer contains the information about the changes made to the database. This information is required in the case of instance recovery. In order to work continuously there should be minimum 2 groups of redo log of the same size in the database.

Java Pool

Is used for session specific Java Code and data in the JVM.

Stream Pool

Is exclusive used by oracle stream.

Large Pool

Is used for backup and restoration process.

Is an optional memory area.

IF we use RMAN then RMAN use Memory for own work. In that case if we do not use shared pool then It uses Large Pool and then due to that there may be problem of space and may provide less time to parsing. (details of previous statement run will be store for less time.) to avoid this problem we always should use shared pool.

Program global area (pga)

PGA:- Program Global Area is a memory area contains the data and control information of Server or Background process. PGA is not shareable area and created by the oracle database.

For every server and background process there will be separate PGA process for each server and background proces.

PGA can be accessed only by the Server Process and not by the user.

PGA minimum size is 10MB and maximum size for all the Server process can be 20% of the SGA.

Background Process:-

There are 5 mandatory background process in oracle. If these background process is not started, oracle will not start.

First bg process start in oracle is PMON.

PMON	Process monitoring	Responsible for cleaning up after abnormally terminated connections. Example. If any user make any connection with the oracle, user process is created at client level. In that particular user exit without using the proper command, in that case user process will kill but the Server process which was created for that particular user will not  kill and PMON process will check in every 3 seconds if there is any server process whose user process is not active. If PMON find any server process whose user process is not active, PMON will kill that server process. Responsible for monitoring other server processes and restarting them if necessary. Registers the instance with the listener dynamically (dynamic service registration). Restarts failed server processes and dispatcher processes
SMON	SYSTEM MONITORING	Temporary space cleanup. Crash recovery upon restart . example:- shutdown abort.
CKPT	checkpointing	The checkpoint process instruct the DBWN writer to write the pinned / modified buffer from database buffer cache to disk, it then updates the data file headers and control file header to update the checkpointing information to indicate when the Advantange of Checkpointing:- checkpoint was performed. There is a relationship with checkpoints and recovery time, the more checkpointing the less recovery time is need when a crash occurs. A checkpointing process involves the following: Flushing the database log buffers to the data files Updating the data file headers and control files after the checkpoint completes
DBWn	Database block writer	Responsible for writing dirty blocks (changed block) to disk when free space within the database buffer cache is low, it writes the dirty blocks from the buffer cache out to the disk. It uses the LRU (Least Recently Used) algorithm which retains data in the memory based on how long it has been since someone asked for that data. The database buffer cache is flushed to disk when the database issues a checkpoint when a server process can't find a clean reusable buffer after checking a threshold number of buffers every 3 seconds users process has searched to long for a free buffer when reading a buffer into the buffer cache Instance is shutdown tablespace is put in backup mode or offline segment is dropped If you have multiple CPU's then it is advised to run multiple database writers. Use the DB_WRITER_PROCESSES parameter to increase the number of database writers, the instance has to be rebooted.
LGWR	Log writer	Responsible for flushing to disk the contents of the redo log buffer located in the SGA. Both committed and uncommitted changes are written to the redo log buffer. The redo log buffer is flushed to disk before the data blocks are written to disk. The redo log buffer is flushed to disk in the following conditions.every 3 seconds whenever a user commits a transaction when the redo log buffer is a third full or contains 1 Mb of buffered data before the DBWn writes. when a checkpoint occurs

•  

PHYSICAL PART OF THE DATABASE

Database part is also known as physical part. Database part contains the following physical files.

Control File:- 

Control file is brain of the database. Control file has two parts.

Logical :- logical contains the logical information. i.e. SCN Number, Timestamp, Log sequence number.

Physical :- Physical contains the physical information i.e.. Name of datafie, location of datafile, location of logfile, information about backup, database mode (archivelog or no archivlog).

To successfully operation of database minimum 1 controlfile is required. Maximum controlfile can be created 8. At the time of database automatically 3 control files are created. 

Size of control file is not very big.

Data File:- datafile contain the actual data of the database. Minimum datafile to operation of database is required 2. Since at the time of database creation two tablespace system and sysaux is created by default and compulsory, therefore minimum two datafile is being referred as a minimum datafile. However other tablespace are also created at the time of database creation. But other tablespace can be avoided to create by using the command line steps.

Size of 1 datafile maximum can be 128 TB if my block size if 8KB. Default block size is of 8KB size.

Redo log file -  Redo log file contain all the changes details made in the database. Minimum redolog group  is required 2. size of all the redo log files should be always same. If there is less then 2 redo log group in the database, database will not be started. Redo log file is required in the case of instance recovery.