• Program global area (PGA)

PGA is memory specific to operating process that is not shared by other processes in the system. Because PGA is process specific, it is never allocated in the SGA. Access to the PGA is exclusive to the server process.

An analogy for a PGA is a temporary countertop workspace used by a file clerk. In this analogy, the file clerk is the server process doing work on behalf of the customer (client process). The clerk clears a section of the countertop, uses the workspace to store details about the customer request and to sort the folders requested by the customer, and then gives up the space when the work is done.

Not all of the PGA areas will exist in every case. PGA is subdivided into different areas.

– Session Memory: Also known as Stack Space (Session Memory).

– Private SQL Area:
This area holds information about a parsed SQL statement and other session specific information for processing for processing. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas.

Private SQL Area is subdivided in to following parts:
Run-Time Area: This area contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in a full table scan.
Persistent Area: Area contains bind variable values.
– SQL Work Areas:
This area is a combination of Sort Area, Hash Area and Bitmap Merge Area. A sort operator uses the sort area to sort a set of rows. Similarly, a hash join operator uses a hash area to build a hash table from its left input, whereas a bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes.
WORKAREA_SIZE_POLICY (AUTO | MANUAL) specifies the policy for sizing work areas. When set to Auto, Work areas used by memory-intensive operators (such as sort, group-by, hash-join, bitmap merge, &  bitmap create) are sized automatically.

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_AREA_SIZE parameters.
In below image, a pointer (Cursor) pointing towards ‘Private SQL Area’ to fetch information. The client process is responsible for managing private SQL areas.

* NOTE: For dedicated sessions, the UGA is a part of the RAM heap in the PGA that controls user sessions space for sorting and hash joins.  If you are forced to use shared servers (the Multi-threaded Server or MTS) the UGA is inside the SGA large_pool_size region).
In sum, when using a dedicated connection, the User Global Area (UGA) supplements the PGA with additional memory for the user’s session, such as private SQL areas and other session-specific information such as sorting and session message queues.

………………………………………………………….

 
• UGA (User Global Area)
The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session. Essentially, the UGA stores the session state.

The UGA must be available to a database session for the life of the session. For this reason, the UGA cannot be stored in the PGA when using a shared server connection because the PGA is specific to a single process. Therefore, the UGA is stored in the SGA when using shared server connections, enabling any shared server process access to it. When using a dedicated server connection, the UGA is stored in the PGA.

UGA has following sections:
Session Variables –
OLAP Pool – This sessions opens automatically whenever a user queries a dimensional object like CUBE.

Diffrence between “/ as sysdba” and “sys/pswrd as sysdba”

— sqlplus “/as sysdba” it mean is you use OS authorization and your user must member of dba(ORA_DBA) OS group,else operation will fail.
— sqlplus sys/pass@sid as sysdba it mean is you use passwordfile authorization .And in this case you need properly configure this file also need set remote_login_passwordfile= EXCLUSIVE or SHARED.

And @sid (sqlplus sys/pass@sid as sysdba) also need listener to be up, can be done either in the server or client side based on the entry in your tnsnames.ora otherwise you’ll receive error message on the SQL terminal the moment you try logging – ORA-12541: TNS:no listener
sqlplus / as sysdba does not require listener to be up but has to be done in the server side.

Here i tried to highlight some differences between DBA and oinstall groups
dba – This group identifies operating system user accounts that have database administrative privileges (the SYSDBA privilege).
oinstall – This group owns the Oracle Inventory, which is a catalog of all Oracle software installed on the system

Ever noticed while exploring ‘dbs’  folder under ORACLE_HOME location about a file name starting from ‘lk’ or ‘kl’ followed by SID in uppercase e.g lkORCL. Let me paste what are my DB’s dbs contents.

[oracle@localhost dbs]$ ls
hc_orcl.dat  init.ora  initorcl.ora  lkORCL  orapworcl  peshm_orcl_0  snapcf_orcl.f  spfileorcl.ora

These are some default files which always created under dbs, everytime your Database restarts and it if you ever tries opening this file it reads “DO NOT DELETE THIS FILE!”. lk<sid> file is used to lock shared memory for specific database but there is still no harm in deleting this file, database will keep on working normal even after deletion and will create it by it’s own after DB bounce.

for example, in our case
-rw-rw—- 1 oracle oinstall 24 Oct 9 18:04 lkORCL

(oracle owns the file and has 660 level permission)
lkORCL is used to lock shared memory for ORCL database in RAM.

Redo Log Files are used in a circular fashion.

• One log file is written in sequential fashion until it is filled, and then the second redo log begins to fill.  This is known as a Log Switch.
• When the last redo log is written, the database begins overwriting the first redo log again.

• The Redo Log file to which LGWR is actively writing is called the current log file.
• Log files required for instance recovery are categorized as active log files.
• Log files no longer needed for instance recovery are categorized as inactive log files.

·        Active log files cannot be overwritten by LGWR until ARCn has archived the data when archiving is enabled.

Log Writer Failure

What if LGWR cannot write to a Redo Log File or Group?  Possible failures and the results are:

1. At least one Redo Log File in a Group can be written – Unavailable Redo Log Group members are marked as Invalid, a LGWR trace file is generated, and an entry is written to the alert file – processing of the database proceeds normally while ignoring the invalid Redo Log Group members.
2. LGWR cannot write to a Redo Log Group because it is pending archiving – Database operation halts until the Redo Log Group becomes available (could be through turning off archiving) or is archived.
3. A Redo Log Group is unavailable due to media failure – Oracle generates an error message and the database instance shuts down.  During media recovery, if the database did not archive the bad Redo Log, use this command to disable archiving so the bad Redo Log can be dropped:

ALTER DATABASE CLEAR UNARCHIVED LOG

1. A Redo Log Group fails while LGWR is writing to the members – Oracle generates an error message and the database instance shuts down.  Check to see if the disk drive needs to be turned back on or if media recovery is required.  In this situation, just turn on the disk drive and Oracle will perform automatic instance recovery.

Sometimes a Redo Log File in a Group becomes corrupted while a database instance is in operation.

• Database activity halts because archiving cannot continue.
• Clear the Redo Log Files in a Group (here Group #2) with the statement:

ALTER DATABASE CLEAR LOGFILE GROUP 2;

Minimum size for an On-line Redo Log File is 4MB.

Maximum number of Archiver (ARCn) processes allowed in Oracle 11g DB id 1-30.  Let’s try and check changing the parameter  log_archive_max_processes

SQL> alter system set log_archive_max_processes=35;
alter system set log_archive_max_processes=35
*
ERROR at line 1:
ORA-00068: invalid value 35 for parameter log_archive_max_processes, must be
between 1 and 30

Maximum number of Database Writer (DBWRn)

DB_WRITER_PROCESSES = 2 * number of CPU
Range of value in Oracle 10g- 1 to 20 but in Oracle 11g – 1 to 36.

SQL> alter system set db_writer_processes=20 scope=spfile sid=’orcl’;
System altered.

SMON
The System Monitor carries out a crash recovery when a crashed instance is started up again. It also cleans temporary segments. SMON checks the SCN in all datafile headers when the database is started. Everything is OK if all of these SCNs matches the SCN found in the controlfile. If the SCNs don’t match, the database is in an inconsistent state. SCN Number is the System Change Number provided by Oracle for each committed transaction. When the database in inconsistent state while the database is opened, crash recovery is done. Crash recovery is the recovery of a database in a single-instance configuration or an Oracle Real Application Clusters configuration in which all instances have crashed. In contrast, instance recovery is the recovery of one failed instance by a live instance in an Oracle Real Application Clusters configuration. SMON wakes up every 5 minutes to perform housekeeping activities. SMON must always be running for an instance. If not, the instance will terminate.

SMON_SCN_TIME is the metadata table populated for every five minutes by SMON, If column_tracking_level is set to 1 SMON also updates sys.col_usage$ another metadata table.

PMON
The Process Monitor checks if a user process fails and if so, do all cleaning up of resources that the user process has acquired. In the case of shared server configuration, PMON checks dispatchers and server processes and restarts them if they have failed. It also does another important thing. During service registration, PMON provides the listener with Names of the database services provided by the database. However, if there is no listener at startup, PMON can obviously not register those information. Therefore, PMON tries then periodically to register with the listener, which might take up to 60 seconds. During service registration it provides listener with the following information:

– Name of the associated instance
– Current load and maximum load on instance
– Names of DB services provided by database.
– Information about dedicated servers and dispatchers (depends on database server mode i.e dedicated/shared server mode)

PMON process wakes up at every 60 seconds and provide information to the listener. If any problem arises and your PMON process fails then it’s not possible to register information to listener periodically. In this case you can do ‘Manual service registration’ using command:

ALTER SYSTEM REGISTER;

This command forces the registration of database information to the listener.

MMAN (Memory Manager) is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases.
This process was introduced in Oracle 10g.

DBWR

The Database Writer writes dirty blocks from the database buffer to the datafiles. is determined by the Initialization parameter Initialization parameter DB_WRITER_PROCESSES determines on how many DBWn Processes are started . DBWR also writes the actual SCN with the Block and flushes datafile blocks.

LGWR
The Log Writer writes the redo log buffer from the SGA to the online redo log file. It wakes up for every three seconds to flush from the redo log buffer to the online redo logs or Whenever the redo log buffer is filled up to _log_io_size defaults to 1/3 of the log buffer size) and LGWR is not currently writing. This parameter is defined in units measured in physical operating system blocks, not DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle. This is called X$ table owned by SYS. The entire X$ tables are memory structures generated by Oracle when instance is spawned. They die with Instance. This is a separate discussion which we do later at another point of time.

RECO
The Distributed Transaction Recovery Process finds pending (distributed) transaction and resolves them.

CKPT
The Checkpoint Process regularly initiates a checkpoint, which uses DBWR to write all dirty blocks back to the datafiles, thus ensuring a consistent state to the database. Since a Checkpoint records the current SCN, in a recovery only redo records with a SCN higher than that of the last checkpoint need to be applied. The ckpt process also updates the datafile headers. The ckeckpoint process was optional in Oracle 7 (set CHECKPOINT_PROCESS=TRUE) and could be enabled to speed-up checkpointing on databases with a large number of files. Starting with Oracle 8i, the checkpoint process is automatically started with the other Oracle processes at instance startup.

ARCH
The Archiver Process archives redo log files if ARCHIVELOG is enabled. This means you have the capability of Media Recovery in case of failure.
Dnnn
The Dispatcher Process is used in a shared server environment. Dispatcher Processes, suppor multi-threaded configuration by allowing user processes to share limited number of server processes.

Snnn
The Shared Server Process is used in a shared server environment. shared Server Processes serve multiple client requests in the multi-threaded server configuration

LMON
The lock manager.

LMD0
AQ Time Manager

TRWR
Trace writer. The System Tracking process.

WMON
The wakeup monitor process.

LCKnnn
Inter-instance locking process.

SNPnnn
The snapshot process.

Qmnn (Queue Monitor)
Optional for Oracle Advanced Queuing which monitors the message queues. Used by Oracle Streams Advanced Queuing.
MMON
It makes snapshots of the database health (statistics) and stores this information in the automatic workload repository. MMON is also responsible for issuing alerts for metrics that exceed their thresholds.  This process was introduced in Oracle 10g.

Automated Storage Management ASM Instance Background Processes(10g)
Below provided are some new background processes added for an ASM instance

RBAL
This processes coordinates re-balance activity for disk groups. In an ASM instance coordinated rebalancing operations. In a DB instance, opens and mount diskgroups from the local ASM instance.

ARBx – Oracle backgroud processes. In an ASM instance, a slave for rebalancing operations

GMON – Oracle backgroud processes. In an ASM instance, diskgroup monitor.

ASMB – Oracle backgroud process. In an DB instance, keeps a (bequeath) persistent DB connection to the local ASM instance. Provides hearthbeat and ASM statistics. During a diskgroup rebalancing operation ASM communicates to the DB AU changes via this connection.

list of the new background processes in Oracle 11g:

VKTM
The virtual keeper of time provides wall-clock time and reference time for other sessions/processes.

FBAR  
The flashback data archiver writes old row-versions of tables with ‘flashback archive’ enabled into flashback data archives on commit.

DIAG
The diagnosibility process (DIAG) runs oradebug commands and triggers diagnostic dumps as part of the new ADR (automatic diagnostic repository) feature.

DIA0
The “other” diagnosibility process detects hangs then triggers DIAG do take diagnostics.

SMCO/Wnnn
The space management coordinator and slaves perform space allocation and reclamation.

There are many more. Please check below link to check various other back-end processes.

http://docs.oracle.com/cd/E14072_01/server.112/e10820/bgprocesses.htm