Oracle Database11g Architecture

Oracle Server:

A server is a collection of database units and it provides comprehensive integrated approach to info  management

It consists of an "Instance & Database "

Oracle Instance:

It means  to access an oracle database

It always open one & only    one database

 It consists of two types :

      - Memory Structure

     - Back Ground Process

 Memory Structure:

System Global Area (SGA)

Program Global Area (PGA)

 (I)System Global Area

Once the Instance is started it allocated memory  to SGA

It is a basic component of oracle instance its size depends on RAM

The oracle 10g parameter of SGA and PGA  sga_target , sga_max_size , pga_aggregate_target

It consists of

 -Shared Pool

 - Database Buffer Cache

 - Redolog Buffer Cache

 - Large pool

 - Stream pool

 - Java pool

here we can go to see each component in detail

(1 )Shared Pool:

  - Its parameter is shared_pool_size

 - It's consists of  Library cache and Data Dictionary Cache

  I) Library Cache:

 - It stores information about recently used sql and Pl-sql Statements

 - Here it checks some of the following

     1)Semantic checking  -  it checks the privilege issued commands by user

     2)Syntax checking  -  it checks the syntax of user issued commands

     3)Soft parse - Already Executed Sql statements command

     4)Hard parse - New Sql Statements

  II)Data Dictionary Cache:

     - It stores the collection of most recently used definitions in the databases includes dbfiles,tables,indexes ,columns etc

     - It has the information about database, and it  read only

(2)Database Buffer Cache:

     - It stores copies of data block that have been retrieved from the database datafiles

     - Its parameter  is

   show parameter db_block_size =8kb is default size,

   show parameter db_cache_size

(3)Redo log Buffer Cache or Recovery Mechanism:

     - It maintains records of modification of database blocks

     - Primary purpose is recovery

   Show parameter log_file

(4)Large Pool:

Oracle Large Pool is an optional memory component of the oracle database SGA. This area is used for providing large memory allocations in many situations that arise during the operations of an oracle database instance.

Oracle's backup and restore utilities typically allocate buffers that are hundreds of kilobytes in size. These will be allocated in the large pool if one is present.

 (5) Java Pool:

    - Parsing requirement of java commands

    - Requires installation of java-based projects

   Show parameter java_pool_size

(6)Stream Pool:

     - It's Cache "Oracle Stream" Objects

    - Oracle Stream means to allow data multiplication between on oracle databases or oracle and non-oracle databases, it can be used for Replication, Message Queuing, loading data into a Data Warehouse, Event Notification, Data Protection .

Automatic Shared Memory Management  (ASMM ) was introduced in Oracle 10g.

      its taking care by oracle and allocates SGA components  size ASMM taking care of

       1)Shared pool

       2)Library cache

       3)Database buffer cache

       4)Large pool

       5)Java Pool

       6)Stream Pool    

 (II)Program Global Area 

     - It reserved memory for each user process connecting to an oracle database

     - Allocates memory   when a process is created

     - De-allocates  memory when a process is terminated

 Process Structure :  

    1)USER PROCESS:

      - A program that request interaction with oracle server

     - It's must first  establish a connection

     - It does not  interact directly with oracle server  

   2)SERVER PROCESS:

     - It directly interacts with  oracle server

     - It can be a dedicated or shared server

     - It always responds  to user requests  

3)BACKGROUND PROCESS:

    - It  enforces the relationship between memory structure and database

   - To view all background process

  !ps -ef | grep database name

It has some of components  are

  1)DBWR

  2)LGWR

  3)SMON

  4)PMON

  5)CHPKT

Let us we can see each components are

 1)DBWR:

    - Time Out Error

    - Tablespace offline

    - Tablespace Read only

    - Tablespace Drop or Truncate in above situations, Data 'll be flushed from database buffer cache into data files

 2)LGWR:

    - At commit

   - Every 3 sec

   - When there is full 1MB reached

   - Redolog Buffer reached one-third full

   - Before DBWR writes in above situations, redolog writes through LGWR  from redo log buffer

3)SMON:

   - Monitoring the system is called system monitor

   - Instance recovery

   - Rolls forward changes into redologs

   - Open database for user access

   - Rolls back uncommitted transactions

4)PMON:

   - Taking Care of All background Process

   - Cleaned up after failed process

   - Rolling Back

5)CHKPT:

   - Updating the control file with checkpoint information.

   - It's a process of writing  by DBWR ,all modified buffers in SGA cache into Data files

   Alter system checkpoint;

Background Processes

Each background process has a separate task but works with the other processes. For example, the LGWR process writes data from the redo log buffer to the online redo log. When a filled log file is ready to be archived, LGWR signals another process to archive the file. Oracle Database creates background processes automatically when a database instance starts. The background processes that are present depend on the features and options that are being used in the database. The startup and shutdown of background processes are normally seen logged in the database alert.log.

The following query can be used to list the background processes running on your database:

SQL> SELECT PNAME 

FROM V$PROCESS 

WHERE PNAME IS NOT NULL 

ORDER BY PNAME;

Following query could be used in 12c as all the shared BG process belongs to con_id=0

SQL> select PNAME,con_id,spid from V$process where con_id=0;

Type of Oracle Background Processes

The Oracle background processes can be majorly distinguished by 3 categories. These are:
1. Mandatory Background Processes
2. Optional Background Processes
3. Slave Processes

1. Mandatory Background Processes

The mandatory background processes are present in all typical database configurations. These processes run by default in a database instance started with a minimally configured initialization parameter file.

Name	Expanded Name	Description
PMON	Process Monitor	Monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally PMON is responsible for recovering processes when the user process fails. PMON does the process cleanup. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes.
SMON	System Monitor	Performs critical tasks such as crash recovery when the instance is started following a failure, dead transaction recovery, and maintenance tasks such as temporary space reclamation, data dictionary cleanup, and undo tablespace management The SMON process performs instance recovery, cleans up after dirty shutdowns
DBWn	Database Writer	Writes modified blocks from the database buffer cache to the data files
LGWR	Log Writer	Writes redo entries to the online redo log
CKPT	Checkpoint	Signals DBWn at checkpoints and updates all the data files and control files of the database to indicate the most recent checkpoint
MMON	Manageability Monitor	Performs many tasks related to manageability, including taking Automatic Workload Repository snapshots and performing Automatic Database Diagnostic Monitor analysis
MMNL	Manageability Monitor Lite	Performs tasks relating to manageability, including active session history sampling and metrics computation
RECO	Recoverer Process	Resolves distributed transactions that are pending because of a network or system failure in a distributed database

2. Optional Background Processes

Name	Expanded Name	Description
ARCn	Archiver	Copies the redo log files to archival storage when they are full, or an online redo log switch occurs
CJQ0	Job Queue Coordinator	Selects jobs that need to be run from the data dictionary and spawns job queue slave processes (Jnnn) to run the jobs
SMCO	Space Management Coordinator	Coordinates the execution of various space management tasks

 An optional background process is any background process not defined as mandatory. This would include background processes that are specific to tasks or features installed in a database. For example, the ARCn process only exists when the database is in ARCHIVELOG mode and automatic archiving is enabled. Also, there are many background processes only needed to support Oracle Automatic Storage Management (ASM).

3. Slave Processes

Slave processes are background processes that perform work on behalf of other processes. This section describes some slave processes used by Oracle Database.

Name	Expanded Name	Description
Innn	Disk and Tape I/O Slave Process	Serves as an I/O slave process spawned on behalf of DBWR, LGWR, or an RMAN backup session
Pnnn	Parallel Query Slave Process	Perform parallel execution of a SQL statement (query, DML, or DDL)
Jnnn	Job Queue Slave Process	Executes jobs assigned by the job coordinator

V$BGPROCESS view

V$BGPROCESS displays information about the background processes.

Column	Datatype	Description
PADDR	RAW(4 | 8)	Address of the process state object
PSERIAL#	NUMBER	Process state object serial number
NAME	VARCHAR2(5)	Name of this background process
DESCRIPTION	VARCHAR2(64)	Description of the background process
ERROR	NUMBER	Error encountered

 Here is a short note on ASM background processes and their purpose in managing Oracle ASM instances.

Background Process	Description
ASMB	ASM Background Process
RBAL	ASM Rebalance Master Process
ARBn	ASM Rebalance Process
DBWR	Database Writer Process
SMON	System Monitor Process
CKPT	Checkpoint Process
PSP0	Process Spawner Process
GMON	ASM Disk Group Monitor Process
MARK	Mark AU for Resynchronization Coordinator Process
VKTM	Virtual Keeper of Time Process
LGWR	Log Writer Process
Onnn	ASM Connection Pool Process
bnnn	ASM Blocking Slave Process for GMON
xnnn	ASM Disk Expel Slave Process
VDBG	Volume Driver Process
VBGn	Volume Background Process
VMB	Volume Membership Process

 

New Background Processes in Oracle Database 10g

Background Process	Description
MMAN	Memory Manager
RVWR	Recovery Writer
CTWR	Change Tracking Writer
MMNL	Memory Monitor Light
MMON	Manageability Monitor
M000	MMON background slave
CJQn	Job Queue monitoring
RBAL	ASM rebalancing process
ARBx	Process managed by RBAL to do the actual rebalancing of ASM
ASMB	Process used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources

New Background Processes in Oracle Database 12c

Background Process	Description
BWnn	Database Writer Processes
FENC	Fence Monitor Process
IPC0	IPC Service Background Process
LDDn	Global Enqueue Service Daemon Helper Slave
LGnn	Log Writer Worker
LREG	Listener Registration Process
OFSD	Oracle File Server Background Process
RPOP	Instant Recovery Repopulation Daemon
SAnn	SGA Allocator
SCRB	ASM Disk Scrubbing Master Process
SCRn	ASM Disk Scrubbing Slave Repair Process
SCVn	ASM Disk Scrubbing Slave Verify Process

Database :

   The Database is a collection  of data which  contains data files ,control files ,redolog files

  1) Data file:

    - It  is a portion  of  an oracle database ,it stores the data  which includes user data and undo data

    - It's extension ".dbf"

    - The default location is " $ORACLE_BASE/oradata"

    - To view the location in database, use this command

   Select name from V$datafile;

2) Control file:

    - It's heart  of the database

    - It holds the information of data file ,redo log file  locations and backup information starting time and  ending time

    - It's extension ".ctl"

    Show parameter control files

   - By default,   oracle has  copied  the control  files into  flash_recovery_area

3) Redo log File:

   - It's part of an oracle database

   - It's the main purpose is to recover the database

   - It's extension ".log"

   - When transaction is committed that details  in  redo log buffer are written to a redo log file

   select * from V$log; or Select * from V$logfile ;   

4) Archive log          

Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log, or more simply the archive log. The process of turning redo log files into archived redo log files is called archiving.

Oracle placed archive logs in a default directory of $ORACLE_HOME/dbs and, if flash recovery was enabled during installation, in the USE_DB_RECOVERY_FILE_DEST, which equated to the init.ora parameter db_recovery_file_dest

Must enable archive log mode in the database then only  ll be saved on archive log folder otherwise  the log buffer overwrites  on  redo log files through Lgwr