Let's have take a look on Database Architecture :
Oracle Server consists of several components however, not all of them are used while processing a sql statements. Some of them are used to increase the performance of the database and to recover the database as well.
Oracle Instance : Oracle Instance means to access the data from the physical database. It is the combination of Memory structure and the Background Processess. Every time an instance is started, an SGA is allocated and the Background processess are started.
System Global Area (SGA) : System Global Area is also called the Shared Global Area because it stores the control information of oracle server which is shared by all database processess (User process, Server process, Background process). Size of the SGA can be determined as SGA_MAX_SIZE in oracle 9i and SGA_TARGET in oracle 10g.
It has the various mandatory components available :
Shared Pool :
It is used to store recently used sql/pl-sql statements.
It is used to store recently used data definitions.
Size of the Shared Pool can be determined as SHARED_POOL_SIZE.
It can be further divided into 2 categories as Library cache and Data Dictionary Cache.
Library Cache :
It is used to store most recently used sql/pl-sql statements.
Size of library cache depends on to the size of shared pool.
Server process looks for any sql/pl-sql statement in library cache first. If it doesn't find in that, it places a copy of that statement into the library cache so that any similar statement executes next time will be fetch very easily.
Data Dictionary Cache :
It is used to store most recently used Data Definitions.
Size of the Data Dictionary cache depends on to the size of shared pool.
you can view the Database structure in data dictionary cache.
Contents of Data Dictionary Cache is :
Roles
Privileges
Users
Auditing, etc
Shared pool is the most important component in the SGA as it stores the information about whole database.
It also applies an LRU (Least Recently Used) algorithm. It keeps the sql/pl-sql statements which is used frequently and aged out the statements which is less used so that more new entries can come in.
Database Buffer Cache :
It stores copies of Data blocks retrieved from the data files.
size of the Database Buffer cache can be determined as DB_CACHE_SIZE.
It also applies an LRU algorithm so that more copies of data blocks can be stored in it.
Two more parameters we can set into the database buffer cache so that Oracle Server will get to know which copies of data blocks should be retain and which should be aged out i.e., DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE respectively.
Server Process looks for any data blocks it needs in Database Buffer Cache first. If it find that block in DB cache, it will fetch it as an output of that query. Otherwise it will go to the data files in physical database, pick that up from data files and retrieve it as an output and simultaneously places a copy of that data block into the Database buffer cache so that any similar query executes next time, it will directly retrieve it up from DB cache and fetch it as an output of that query.
Redo Log Buffer Cache :
It records changes made to the database and that records are known as "Redo Entries".
It is used for the recovery purpose.
Size of the Redo log buffer cache can be determined as LOG_BUFFER.
During the mechanism of placing a copy of data blocks from data files in Database Buffer Cache, again server process will place an another copy of same data block in Redo log buffer cache.
Note : Server Process will place a copy of data block in Database Buffer Cache only when, the executed query is for "SELECT". And it will place an another copy of data block in Redo log Buffer cache only when, the executed query is for any "UPDATE" (for example INSERT, UPDATE, DELETE, CREATE, ALTER DROP).
There are some Optional Components also available in SGA are :
Large Pool :
It is used to relieve the burden of the shared pool.
It is also used to store the RMAN Backup and Recovey operations.
Size of the Large pool can be determined as LARGE_POOL_SIZE.
Oracle recommends that the size of the large pool is large enough to share its size with the shared pool.
Java Pool :
It is also an optional component which is used to store Java commands and coding only if the associated UI's is made up of by using Java code.
Size of the Java Pool can be determined as JAVA_POOL_SIZE.
Establish a connection :
To establish a connection to the Oracle server, you need two more processess i.e., User Process and Server Process.
User Process :
User process is a request to connect to the Oracle Server by a Privileged User.
User process cannot directly interact to the Oracle Server.
Server Process :
Server Process is responsible for connecting the User Process request to the Oracle Server.
It is used to establishes the connection between the User Process and the Oracle Server.
Server process can be a Dedicated or a Shared mode. In Dedicated mode, one server process can handle only one user process. And in Shared mode, one server process can handle several user process.
Program Global Area (PGA) :
PGA is used to store the control information of a single user process which resides in Server Process.
It can be Active only when the database is in Dedicated mode.
User Global Area (UGA) :
UGA is used to store the control information of all the user Processess which is currently associated with one Server Process. It resides in Large Pool of the SGA.
It can be Active only when the database is in Shared mode.
Now, Before having a look into Background process, I would prefer to go to the Physical Database first..!!
Data files :
This is a place where actual data gets stored in physical database.
Here, Data gets logically stored in tablespaces but physically stored in Datafiles.
The extension of these type of files is known as .dbf.
Control files :
It stores the information which is necessary to maintain and verify the database integrity.
It stores the current state of physical Database.
To be very precise, Control file is the most important file in any Oracle Database. Without this file, database cannot be open and the whole data which is already stored in database will be lost.
The extension of these type of file is known as .ctl.
Online Redo log files :
It records changes made to the database in circular fashion by LGWR background process.
The main purpose of Online redo log files is "To Recover the Database".
Oracle recommends to have atleast 2 groups of Online Redo log files and 1 member in each group in a database.
The extension of these type of files is known as .log.
Parameter file :
A Parameter file is a file that contains a list of initialization parameters and a value for each parameter. For example, it stores the sizes of some of the components available in SGA.
Password file :
A password file is a file that contains password information for all the privileged users in database.
It contains the information about user's password, privileges, roles, resource limit information, etc..
Archived Redo log files :
Archived Redo log files are the offline copies of the Online redo log files.
It stores the Redo information fetched from the Online Redo log files, depending on the mode of the database.
There are 2 modes where Database can run :
Archive log mode
Noarchive log mode
If the database is in Archive log mode, the Archived Redo log files are active and the information in Online Redo log files will get Archived.
But if database is in Noarchive log mode, the Archived Redo log files are Inactive and the information in Online Redo log files will get Overwritten.
Background Processess :
There are lots of mandatory and optional background processess in Oracle database.
Background Process performs function on behalf of the invoking process.
Some of them are used to improve the performance of the database while some of them are used in case of database failure.
Let's start with each one of them briefly :
Log Writer (LGWR) :
LGWR writes redo entries from Redo Log Buffer Cache to Online Redo log files in the following scenario :
(a) At every transaction commits,
(b) In every 3 seconds,
(c) When more than 1 MB of data is filled in Redo log buffer cache,
(d) When there is one third of data available,
(e) Before Database Writer (DBWR) writes.
It works in a circular fashion by filling first group and start filling next available group of Online Redo log file just after the log switch occurs.
Log Switch : A log switch is a point where the database stops writing to one group and start writing to next available group. Normally, the log switch occurs when the current online redo log group is filled and writing continue with the next group. You can also force log switch to occur by using the following command :
ALTER SYSTEM SWITCH LOGFILE;
CheckPoint (CKPT) :
CKPT is probably the most important background process available in database.
The main purpose of CKPT is to signalling the other background processess so that they can perform their functions by invoking the process.
Checkpoint comes in action when log switch occurs. Once Checkpoint activates it provides signal to DBWR and updates the control file with checkpoint information and Sequence log Change (SCN) number and simultaneously it also updates the header of Datafiles so that database must be in a Sync.
You can also force checkpoint to occur :
ALTER SYSTEM CHECKPOINT;
Database Writer (DBWR) :
Database writer writes dirty buffers from Database Buffer Cache to Datafiles.
Just after getting the signal from CKPT, DBWR activates and it starts writing dirty buffers from DB Buffer to Datafiles and at the mean time CKPT also ensures that sufficient number of free space available in Database Buffer Cache.
DBWR also starts writing dirty buffers in following scenario :
(a) When Checkpoint occurs,
(b) When there are no free space available in Database Buffer Cache,
(c) When Dirty Buffers reach Threshold,
(d) When any tablespace is taken offline, Read only, Drop, Truncated and when Begin Backup,
(e) When Timeout occurs.
System Monitor (SMON) :
System Monitor is a Background process which is responsible for instance recovery in database.
It also rolls forward the uncommitted transactions.
SMON is activated when the database gets shut down abnormally and users who currently working on a database gets disconnected from the session.
When you startup your database, SMON will perform recovery activities.
SMON will always be running for Oracle Instance. If not, Instance will be terminated.
Process Monitor (PMON) :
Process Monitor is a Background Process which cleans up after every failed transactions.
It also rolls back user's uncommitted transaction and releases the memory which was occupied by their transactions.
Like SMON, PMON also gets activated when the database gets shut down abnormally and users who currently working on a database gets disconnected from the session.
When you startup you database again, PMON starts cleaning-up the uncommitted data which was occured by the failed transactions.
PMON constantly checks the status of user and database processess. In some cases, failed database processess can be restarted by PMON.
Archiver Process (ARCn) :
Archiver Process (ARCn) is responsible for archiving the redo data from the Online redo log files to disk.
Depending on the mode of database, Archiver Background process gets activated only if the database is in Archive log mode but if the database is in Noarchive log mode, ARCn is disabled by default.
You can also manually start archiving by using following command :
ALTER SYSTEM ARCHIVE LOG START/STOP;
Above all mentioned are the basic introduction of Oracle Database Architecture...I will briefly describe you about each and every file of physical database in my upcoming blog...In the next section, We will study about the Creation of the Databases.
Thank You !!! :)
Oracle Server consists of several components however, not all of them are used while processing a sql statements. Some of them are used to increase the performance of the database and to recover the database as well.
Oracle Instance : Oracle Instance means to access the data from the physical database. It is the combination of Memory structure and the Background Processess. Every time an instance is started, an SGA is allocated and the Background processess are started.
System Global Area (SGA) : System Global Area is also called the Shared Global Area because it stores the control information of oracle server which is shared by all database processess (User process, Server process, Background process). Size of the SGA can be determined as SGA_MAX_SIZE in oracle 9i and SGA_TARGET in oracle 10g.
It has the various mandatory components available :
Shared Pool :
It is used to store recently used sql/pl-sql statements.
It is used to store recently used data definitions.
Size of the Shared Pool can be determined as SHARED_POOL_SIZE.
It can be further divided into 2 categories as Library cache and Data Dictionary Cache.
Library Cache :
It is used to store most recently used sql/pl-sql statements.
Size of library cache depends on to the size of shared pool.
Server process looks for any sql/pl-sql statement in library cache first. If it doesn't find in that, it places a copy of that statement into the library cache so that any similar statement executes next time will be fetch very easily.
Data Dictionary Cache :
It is used to store most recently used Data Definitions.
Size of the Data Dictionary cache depends on to the size of shared pool.
you can view the Database structure in data dictionary cache.
Contents of Data Dictionary Cache is :
Roles
Privileges
Users
Auditing, etc
Shared pool is the most important component in the SGA as it stores the information about whole database.
It also applies an LRU (Least Recently Used) algorithm. It keeps the sql/pl-sql statements which is used frequently and aged out the statements which is less used so that more new entries can come in.
Database Buffer Cache :
It stores copies of Data blocks retrieved from the data files.
size of the Database Buffer cache can be determined as DB_CACHE_SIZE.
It also applies an LRU algorithm so that more copies of data blocks can be stored in it.
Two more parameters we can set into the database buffer cache so that Oracle Server will get to know which copies of data blocks should be retain and which should be aged out i.e., DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE respectively.
Server Process looks for any data blocks it needs in Database Buffer Cache first. If it find that block in DB cache, it will fetch it as an output of that query. Otherwise it will go to the data files in physical database, pick that up from data files and retrieve it as an output and simultaneously places a copy of that data block into the Database buffer cache so that any similar query executes next time, it will directly retrieve it up from DB cache and fetch it as an output of that query.
Redo Log Buffer Cache :
It records changes made to the database and that records are known as "Redo Entries".
It is used for the recovery purpose.
Size of the Redo log buffer cache can be determined as LOG_BUFFER.
During the mechanism of placing a copy of data blocks from data files in Database Buffer Cache, again server process will place an another copy of same data block in Redo log buffer cache.
Note : Server Process will place a copy of data block in Database Buffer Cache only when, the executed query is for "SELECT". And it will place an another copy of data block in Redo log Buffer cache only when, the executed query is for any "UPDATE" (for example INSERT, UPDATE, DELETE, CREATE, ALTER DROP).
There are some Optional Components also available in SGA are :
Large Pool :
It is used to relieve the burden of the shared pool.
It is also used to store the RMAN Backup and Recovey operations.
Size of the Large pool can be determined as LARGE_POOL_SIZE.
Oracle recommends that the size of the large pool is large enough to share its size with the shared pool.
Java Pool :
It is also an optional component which is used to store Java commands and coding only if the associated UI's is made up of by using Java code.
Size of the Java Pool can be determined as JAVA_POOL_SIZE.
Establish a connection :
To establish a connection to the Oracle server, you need two more processess i.e., User Process and Server Process.
User Process :
User process is a request to connect to the Oracle Server by a Privileged User.
User process cannot directly interact to the Oracle Server.
Server Process :
Server Process is responsible for connecting the User Process request to the Oracle Server.
It is used to establishes the connection between the User Process and the Oracle Server.
Server process can be a Dedicated or a Shared mode. In Dedicated mode, one server process can handle only one user process. And in Shared mode, one server process can handle several user process.
Program Global Area (PGA) :
PGA is used to store the control information of a single user process which resides in Server Process.
It can be Active only when the database is in Dedicated mode.
User Global Area (UGA) :
UGA is used to store the control information of all the user Processess which is currently associated with one Server Process. It resides in Large Pool of the SGA.
It can be Active only when the database is in Shared mode.
Now, Before having a look into Background process, I would prefer to go to the Physical Database first..!!
Data files :
This is a place where actual data gets stored in physical database.
Here, Data gets logically stored in tablespaces but physically stored in Datafiles.
The extension of these type of files is known as .dbf.
Control files :
It stores the information which is necessary to maintain and verify the database integrity.
It stores the current state of physical Database.
To be very precise, Control file is the most important file in any Oracle Database. Without this file, database cannot be open and the whole data which is already stored in database will be lost.
The extension of these type of file is known as .ctl.
Online Redo log files :
It records changes made to the database in circular fashion by LGWR background process.
The main purpose of Online redo log files is "To Recover the Database".
Oracle recommends to have atleast 2 groups of Online Redo log files and 1 member in each group in a database.
The extension of these type of files is known as .log.
Parameter file :
A Parameter file is a file that contains a list of initialization parameters and a value for each parameter. For example, it stores the sizes of some of the components available in SGA.
Password file :
A password file is a file that contains password information for all the privileged users in database.
It contains the information about user's password, privileges, roles, resource limit information, etc..
Archived Redo log files :
Archived Redo log files are the offline copies of the Online redo log files.
It stores the Redo information fetched from the Online Redo log files, depending on the mode of the database.
There are 2 modes where Database can run :
Archive log mode
Noarchive log mode
If the database is in Archive log mode, the Archived Redo log files are active and the information in Online Redo log files will get Archived.
But if database is in Noarchive log mode, the Archived Redo log files are Inactive and the information in Online Redo log files will get Overwritten.
Background Processess :
There are lots of mandatory and optional background processess in Oracle database.
Background Process performs function on behalf of the invoking process.
Some of them are used to improve the performance of the database while some of them are used in case of database failure.
Let's start with each one of them briefly :
Log Writer (LGWR) :
LGWR writes redo entries from Redo Log Buffer Cache to Online Redo log files in the following scenario :
(a) At every transaction commits,
(b) In every 3 seconds,
(c) When more than 1 MB of data is filled in Redo log buffer cache,
(d) When there is one third of data available,
(e) Before Database Writer (DBWR) writes.
It works in a circular fashion by filling first group and start filling next available group of Online Redo log file just after the log switch occurs.
Log Switch : A log switch is a point where the database stops writing to one group and start writing to next available group. Normally, the log switch occurs when the current online redo log group is filled and writing continue with the next group. You can also force log switch to occur by using the following command :
ALTER SYSTEM SWITCH LOGFILE;
CheckPoint (CKPT) :
CKPT is probably the most important background process available in database.
The main purpose of CKPT is to signalling the other background processess so that they can perform their functions by invoking the process.
Checkpoint comes in action when log switch occurs. Once Checkpoint activates it provides signal to DBWR and updates the control file with checkpoint information and Sequence log Change (SCN) number and simultaneously it also updates the header of Datafiles so that database must be in a Sync.
You can also force checkpoint to occur :
ALTER SYSTEM CHECKPOINT;
Database Writer (DBWR) :
Database writer writes dirty buffers from Database Buffer Cache to Datafiles.
Just after getting the signal from CKPT, DBWR activates and it starts writing dirty buffers from DB Buffer to Datafiles and at the mean time CKPT also ensures that sufficient number of free space available in Database Buffer Cache.
DBWR also starts writing dirty buffers in following scenario :
(a) When Checkpoint occurs,
(b) When there are no free space available in Database Buffer Cache,
(c) When Dirty Buffers reach Threshold,
(d) When any tablespace is taken offline, Read only, Drop, Truncated and when Begin Backup,
(e) When Timeout occurs.
System Monitor (SMON) :
System Monitor is a Background process which is responsible for instance recovery in database.
It also rolls forward the uncommitted transactions.
SMON is activated when the database gets shut down abnormally and users who currently working on a database gets disconnected from the session.
When you startup your database, SMON will perform recovery activities.
SMON will always be running for Oracle Instance. If not, Instance will be terminated.
Process Monitor (PMON) :
Process Monitor is a Background Process which cleans up after every failed transactions.
It also rolls back user's uncommitted transaction and releases the memory which was occupied by their transactions.
Like SMON, PMON also gets activated when the database gets shut down abnormally and users who currently working on a database gets disconnected from the session.
When you startup you database again, PMON starts cleaning-up the uncommitted data which was occured by the failed transactions.
PMON constantly checks the status of user and database processess. In some cases, failed database processess can be restarted by PMON.
Archiver Process (ARCn) :
Archiver Process (ARCn) is responsible for archiving the redo data from the Online redo log files to disk.
Depending on the mode of database, Archiver Background process gets activated only if the database is in Archive log mode but if the database is in Noarchive log mode, ARCn is disabled by default.
You can also manually start archiving by using following command :
ALTER SYSTEM ARCHIVE LOG START/STOP;
Above all mentioned are the basic introduction of Oracle Database Architecture...I will briefly describe you about each and every file of physical database in my upcoming blog...In the next section, We will study about the Creation of the Databases.
Thank You !!! :)
