Process Architecture in Oracle Database

Posted by Shaik Ahmed Ali on November 12, 2020 in 12c, Background process, Beginners, checkpoint, database, Database writer, logwriter, Oracle, Oracle Architecture | 1 comment

 

In this blog I will discuss about the process architecture of oracle database how the client gets the connection which contains the following:

1)      Introduction to Process

2)      Client process

3)      Server Process

4)      Background Process

 

1)      Introduction to process

                   In Linux process is nothing but a running instance which serves request. Whenever we run a command a process is created for it which is responsible to serve request. Linux is a Multi-tasking OS in which multiple program can be run at a time. Oracle process is also a multiple-process oracle or Multiuser Oracle.

               In Oracle there will be a single process for a connected user or single process shared by multiple user depends on how there are connected either by shared or dedicated server process.

2)      Client Process:

                 Oracle Client is connection from Application to your database. Generally, application connect to the database using either directly such as sqlplus or by using JDBC, ODBC connection.

Connection and Sessions

Connection and sessions are most two common concepts which confuse the users to understand that both are the same but it is not.

i)                    Connection:

                Connection is a physical communication pathway between the client process and database instance. It is establish using the available interprocess communication mechanisms. Connection it occurs between an oracle client process and server process or dispatcher. Using Oracle Connection manager (CMAN) a connection may be established between client process and CMAN and the database.

ii)                  Sessions:

      session is a logical entity in the database which reside in memory and having state of current user connected to database, whenever a connection is comes from application a session is started at database level after the user get authenticated and ends the session when the user disconnects the application. Session is independent of other sessions for example if commit happens at one session it doesn’t affect the other session.

3)      Server process:

      Server process in oracle are created to handle requests from sessions connected to the instance. A client process always communicates with the database using the server process.

                         Serve process performs the task which include the following below:

·         It will do parse and run SQL statements issue by the user or application including the query plan

·         Execute PL/SQL code

·         It will also read the data block from datafiles into the Database Buffer Cache

·         It will also return the result back to the user or application.

Server process are of 2 types which are

i)        Dedicated Server Process

ii)      Shared Server Process

 

i)                    Dedicated Server Process:

        In dedicated server process we have a one-to-one connection between the application and server process, for example if we have 100 dedicated server connections then there will be 100 processes executing the request.

In dedicated server process client process communicates directly with its server process and keeps the connection until the user disconnect it. In dedicated server process Private SQL area present in PGA.

       If client process and dedicated process run on the same machine, then the program interface uses the host OS interprocess communication mechanism to connect to the database.

                    If client process and dedicated server process run on the different machine, then the program interface provides the communication mechanism (network services and Oracle net      service) between the programs.

ii)                    Shared Server Process:

                        In Shared server process the connection from the client receive at the dispatcher not at server process. For example, so 40 connections can connect to a single dispatcher which is responsible for the connection to a database.

                       The dispatcher process receives the connection from the client and put them into the request queue in the large pool. From large pool available shared server process takes the request and process it. after performing the request shared server process put the result into the dispatcher response queue.

                       Dispatcher process monitor this queue and send back the request to the client.

 4)      Background Process:

       Oracle background process are the process which run in the background/behind the scene and are meant to perform IO activities or to deal with abnormal conditions arising while the instance is running.

           There are two types of background process which are:

i)                    Mandatory BP

ii)                  Optional BP

i)                    Mandatory Background Process:

       This mandatory BP must always be running when the instance is up and running. Each background process has a specific purpose and its role is well defined.

Mandatory background process are below:

a)      System Monitor (SMON)

b)     Process Monitor (PMON)

c)      Log Writer (LGWR)

d)     Database Writer (DBW0)

e)      Check Point (CKPT)

f)       Recovere (RECO)

a)      System Monitor (SMON):

         System monitor performs recovery when a failed instance start up again.it also free up the temporary segments that are no longer in use. These transactions are recovered by SMON when tablespace or datafile brought back online.

         In RAC environment SMON process of one instance can perform the instance recover of other instances present in cluster if failed.

          SMON checks the SCN in all datafile headers when the database is started.it checks the SCN of datafile and compare it with the SCN of control file so if it matches then everything is OK if not the database is in inconsistent state.

b)     Process Monitor(PMON):

      This process monitor performs process recovery when a user process fails. It will rollback uncommitted transactions. PMON is also responsible for cleaning up the database buffer cache and freeing resources that were allocated to a process.it also registers information about the instance and dispatcher processes with network listener. PMON also checks on the dispatcher processes and server processes and restarts them if they have failed.

c)      Log Writer (LGWR):

         Log writer process writes data from redo log buffer cache to the redo files on disk.

The LGWR writes the data to redo files on below conditions

·         When a transaction is committed

·         When a transaction is rollback

·         Every 3 seconds

·         When the redo log buffer is 1/3 full.

When a transaction is committed, a system change number(SCN) is generated and tagged to it.log writer puts a commit record in the redo buffer and writes to disk immediately along with transaction redo entries.

              Log writer writes the data to redo log group in a circular fashion. If any error occurred with the redo log file, the log writer will log this error in to the LGWR trace file and system alert log file.

d) Database Writer(DBWRn):

    This process writes the dirty buffers present in the database buffer cache to the datafiles. The process does the multi-block writing to disk asynchronously. Oracle allows a maximum of 20 DBWR processes.

DB_WRITER_PROCESSES is an initialization parameter which specifies the number of database writer processes to start. Database select an appropriate default setting based on the number of CPUs and the number of processor groups available for the server.

The DBWR writes the dirty block to datafile on following conditions:

·         When a checkpoint is issued.

·         When a server process cannot find a clean reusable buffer.

·         Every 3 seconds time-out

·         When a log switch occurs

·         When Dirty-Blocks are becoming LRU Blocks

·         When it reaches the threshold value

·         When database shuts down.

e)     Checkpoint Process (CKPT):

    This process signals the synchronization of all database files with the checkpoint information.it make sure that the data consistency and faster database recovery in case of database crash. CKPT also ensure that the changes happen to the block at database buffer cache at that point are written to the datafiles but the writing is done by DBWR.

       When the checkpoint occurred the log writer process will update the datafile headers and the control file with the latest SCN. This process invoked under the following condition:

·         When a log switch is done

·         When time specified in the LOG_CHECKPOINT_TIMEOUT exists between the incremental checkpoint and tail of log

·         When ALTER SYSTEM SWITCH LOGFILE command is issued

·         When ALTER SYSTEM CHECKPOINT command is issued.

f)      Recoverer Process (RECO):

       This RECO process is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. It can connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

A distributed database is where the application can access the data from local and remote databases.

ii)                    Optional Background Process:

There are many optional BP running behind the database when the database is started some of them are:

a)      Archiver: this process is responsible for writing the online log files to the archive log destination after log switch has occurred. This process present only if the database is in archive log mode.

b)      Recovery Writer: this process came with 10g database which is responsible for maintaining the before image of blocks in the FRA used with FLASHBACK DATABASE command.

c)      Lock Monitor: this process if use for parallel server setups.it manages global locks and resources.it co-ordinates with the process monitor to recover dead processes that hold instance lock.

d)      Memory Manager: it came from 10g database and used for internal database tasks. MMAN can dynamically adjust the sizes of following SGA components:

·         Database Buffer Cache

·         Large Pool

·         Shared Pool

·         Java Pool

e)      Parallel Execution Slaves: these processes are used for parallel processing. It can be used of parallel execution of query or recovery.

f)       DMON: a DMON process is started when the DG monitor is started.

g)      Manageability Monitor(MMON): this process came with 10g database and responsible for in associated with the AWR.it writes required statistics for AWR on scheduled basis.

So in this blog you have learn about the Process Architecture of oracle database in detail.