Online Redo Log Files

Online Redo Log Files:

Online Redo log files have the following characteristics : 

• Record all changes made to data
• Provide a Recovery mechanism
• Can be organized into groups
• At least two groups required 

Online redo log files provide the way to redo transactions in the event of a database failure. Every transaction is written synchronously to the Redo Log Buffer, then gets flushed to the online redo log files in order to provide a recovery mechanism in case of media failure. (With Exceptions such as direct load inserts in objects with the NOLOGGING clause enabled.)

This includes transactions that have not yet been committed, Undo segment information, and schema and object management statements. Online redo log files are used in a situation such as an instance failure to recover committed data that has not been written to the data files. The online redo log files are used only for recovery.

 

Structure of the Online Redo Log Files:

                          Online Redo Log File Structure 

The DBA can set up the oracle database to maintain copies of online redo log files to avoid losing database information due to a single point of failure.

Online Redo Log File Groups :

• A set of identical copies of online redo log files is called an online redo log file group.
• The LGWR background process concurrently writes the same information to all online redo log files in a group.
• The Oracle Server needs a minimum of two online redo log file groups for the normal operation of a  database.

Online Redo Log File Members :

• Each online redo log file in a group is called a member.
• Each member in a group has identical sequence numbers and are of the same size. The log sequence number is assigned each time that the Oracle Server writes to a log group to uniquely identify each online redo log file. 
• The current log sequence number is stored in the control file and in the header of all the data files.  

Creating Initial Online Redo Log Files :

The Initial set of online redo log file groups and members are created during the database creation.

The following parameters limit the number of the online redo log file : 

• The MAXLOGFILES parameter in the CREATE DATABASE command specifies the absolute maximum of online redo log file groups.
• The maximum and the default value for MAXLOGFILES are dependent on your operating system.
• The MAXLOGMEMBERS parameter used in the CREATE DATABASE command determines the maximum number of members per group. The Maximum and default value for MAXLOGMEMBERS is dependent on your operating system.
  

How Online Redo Log Files Work:

• Online Redo Log  Files are used in a cyclic fashion.
• When an online redo log file is full, LGWR (background Process ) will move to the next log group.

- Called a log Switch

- Checkpoint operations also occure

- Information Written to the control file

The Oracle Server sequentially records all changes made to the database in the Redo Log Buffer. The redo entries are written from the redo log buffer to the current online redo log file group by the LGWR process. LGWR writes under the following situations.

• When a transaction commits
• When the redo log buffers one-third full.
• When there is more than a megabyte of changed records in the Redo Log Buffer.
• Before the DBWn writes modified blocks in the database buffer cache to the Data Files.

Online redo log files are used in a cyclic fashion. Each Online redo log file group is identified by a log sequence number that is overwritten each time the log is reused.

Log Switches : 

LGWR writes to the online redo log files sequentially. When the current online redo log file group is filled, LGWR begins writing to the next group. This is called a log switch. 

When the last available online redo log file is filled. LGWR returns to the first online redo log file group and starts writing again.

Checkpoints :

During the checkpoint below activity happens and can say characteristics of Checkpoint : 

• DBWn writes a number of dirty database buffers, which are covered by the log that is being checkpointed, to the data file.
• The checkpoint backup ground process CKPT updates the control file to reflect that is it has completed a checkpoint successfully. If the checkpoint is caused by a log switch, CKPT also updates the headers of the data file.

Checkpoints can occur for all data files in the database or only for the specific data file.

In the following situation, checkpoints occur, for Example :

• At every log switch
• When an instance has been shut down with the normal, transactional, or Immediate option.
• When forced by setting the FAST_START_MTTR_TRAGET initialization parameter.
• When manually requested by the database administrator
• When the ALTER TABLESPACE [OFFLINE NORMAL | READ ONLY | BEGIN BACKUP] command causes checkpoint on specific data files.

Information about each checkpoint is recorded in the alert_SID.log file if the LOG_CHECKPOINTS_TO_ALERT initialization parameter is set to TRUE. The default value of FALSE  for this parameter does not log checkpoint.

Forcing Log Switches and Checkpoints :

• Forcing a log Switch by using Command :

 SQL> ALTER SYSTEM SWITCH LOGFILE;

• Checkpoint Can be Forced by :

- Setting FAST_START_MTTR_TARGET parameter.

FAST_START_MTTR_TARGET = 600; 

• ALTER SYSTEM CHECKPOINT  command.

SQL> ALTER SYSTEM CHECKPOINT;i

Log Switches and checkpoints are automatically performed at certain points in the operation of the database as identified previously.  However, a DBA can force a log switch or a checkpoint to occur.

Forcing Checkpoint :

FAST_START_MTTR_TARGET   Parameter replaces the deprecated parameters:

• FAST_START_IO_TARGET
• LOG_CHECKPOINT_TIMEOUT

These deprecated parameters must not be uses if the parameter FAST_START_MTTR_TARGET is used.

In the above example, the FAST_START_MTTR_TARGET parameter has been set 600 seconds, so that instance recovery should not take more than 600 seconds. The Database will adjust the other parameters to this goal.

Thank you !!

Process Structure (Oracle) and Oracle Mandatory Background Processes

Process Structure (Oracle) :

Oracle takes an advantage of various types of process :

• User Process : User Process started at the time when a database user requests to make a   connection to the Oracle Server
• Server Process : Server Process connects to the Oracle Instance and It is started when a user establishes a session.
• Background Processes : Background Processes started when an Oracle Instance is started

User Process : User process working workflow is as below.

• A program that requests interaction with the Oracle Server
• Must first Establish a connection
• Does not interact Directly with the Oracle Server

A Database user who has need some Information from the database, then it is must to make a connection request to establish a connection with the oracle server. The connection is requested using a database interface tool, such as SQL*Plus, and beginning the user process. The user process does not interact directly with the Oracle Server. Rather it Generates calls through the user program interface (UPI) Which creates a session and starts a server process.

Server Process : User process working workflow is as below.

• A program that directly interacts with the Oracle Server
• Fulfills calls generated and returns result.
• Can be Dedicated or  Shared Server

Once a user has established a connection, a server process is started to handle the user processes request. A Server Process Can be either a dedicated server process or a shared server process.

In Dedicated server environment, The Server process handles the request of a single user process. Once a user process disconnects, the server process is terminated.

In a Shared Server environment, the server process handles the request of several User Processes. The server Process communicates with the oracle server using the Oracle Program Interface (OPI).

Background Process : Background Process are Maintains and enforces relationship between Physical structure and Memory structure.

The Oracle architecture has five mandatory background process. In addition to the mandatory background process Oracle has many optional background process that are started when their option is being used.

• Mandatory Background Processes
• DBWn ( Database Writer)
• LGWR (Log Writer )
• PMON (Process Monitor )
• SMON (Syatem Monitor )
• CKPT  (Checkpoint)

• Optional Background Processes: Optional background Process are many. Below are the few optional Background processes are as below :

• ARCn  (Archiver Process)
• CJQ0  (Coordinator Job Queue background Process)
• Dnnn   (Dispatcher)
  
• LCKn  ( RAC lock manager - Instance Locks)
• LMDn (RAC DLM monitor - Remote Locks)
• LMON ( RAC DLM monitor  - Global Locks)
• LMS (RAC global cache Service )
• Pnnn ( Parallel Query Slaves )
• QMNn   (Advance Queuing)
• RECO  ( Recoverer )
• Snnn  (Shared Server)

Now we will look here working Capabilities of the mandatory background Processes including one very important optional background Process  ARCn (Archiver Process)

Mandatory Background Process:

• DBWn (Database Writer) : DBWn is a mandatory background process which is part of the SGA. It is Started when instance starts. When the server process records any changes to undo and data blocks in the Database Buffer Cache, DBWn Writes the dirty buffers from the database buffer cache to the data files. It Ensures that a sufficient number of free buffers are available in the Database Buffer Cache. Database performance is improved because server processes make changes only in the Database Buffer Cache.

• Free Buffer : Free Buffers are buffer that can be overwritten when server process need to read in blocks form the data files.

DBWn Process writes the dirty buffers to the Data files when the fallowing below Events are Occurs :

- Incremental or Normal Checkpoint

- The number of Dirty Buffers reaches to threshold Value

- A process scans a specified number of Blocks, When Scanning Free Buffers and can not find any

- Timeout Occurs

- A ping request in Real Application Cluster (RAC) environment

- Placing a Normal and temporary tablespace offline

- Placing a tablespace in read-only mode

- Dropping Or Truncating a table

- ALTER TABLESPACE  tablespace name BEGIN BACKUP

• LGWR (Log Writer) : LGWR is the mandatory background Process and part of the SGA. It is started when instance starts. LGWR performs sequential writes from the Redo Log Buffer to the online redo log file under the fallowing situations.

- When a transaction commits

- When the Redo Log Buffer is one-third full

- When there is more than 1 MB  of Changes recorded in the Redo Log Buffer

- Before DBWn writes modified blocks in the Database Buffer Cache to the Data Files

- Every Three Seconds

Because the redo is needed for recovery, LGWR confirms the commit operation only after the redo is written to disk. LGWR can also call on DBWn to  write to the data file.

• SMON (System Monitor ) : SMON is the mandatory background process and part of the SGA. It is started when instance starts. SMON Process has fallowing responsibilities :
• Instance Recovery
• Rolls Forward changes in online redo log files
• Opens database for user access
  
• Rolls back uncommitted transactions
• Coalesces Free Space
• Deallocates temporary segments

If the Oracle Instance Fails, Any Information in the SGA that has not been written to disk is lost. For Example, The failure of the operating system causes an instance failure. After the loss of the instance the background process SMON automatically performs instance recovery when the database is reopened. Instance Recovery consists of the fallowing Steps :

1. Rolling Forward to Recover data that has not been recorded in the data files but that has been recorded in the Online Redo Log file. This Data has not been written to disk because of the loss of the SGA during Instance failure. During this process, SMON reads the Online Redo Log Files and applies the changes recorded in the Online Redo Log Files to the data blocks, Because All committed transactions have been written to the Online Redo Log Files. This process Completely recovers these transactions.
2. Opening the database so that user can log on. Any data that is not locked by unrecoverable transactions is immediately available,
3. Rolling back uncommitted transactions. They are rolled back by SMON or by the individual server process as they access locked data.

SMON also performs some space maintenance functions :

- It combines , Or Coalesces , Adjacent areas of free space in the data files.

- It Deallocates temporary segments to return them as free space in data files.

• PMON (Process Monitor) : PMON is the mandatory background process and part of the SGA. It is started when instance starts. PMON process has fallowing Responsibility :

- The background Process PMON Performs Clean up after failed Process By fallowing as below:

- Rolling Back the user's current transactions

- Releasing all currently held tables or Row Locks

- Freeing other resources currently reserves by the users

- Restarts Dead Dispatchers

• CKPT (Checkpoint) : CKPT is the mandatory background process and part of the SGA. It is started when instance starts. It is Responsible for fallowing responsibilities :

- Signaling DBWn at checkpoints

- Updating Data files headers with checkpoint information

- Updating control files with checkpoint Information

Every three Seconds the CKPT process stores data in the control file to identify that place in the online redo log file from where recovery is to begin, which is called a checkpoint.

The purpose of a checkpoint is to ensure that all of the buffers in the Database Buffer Cache that were modified prior to a point in time have been written to the data files. This point in time (Called the checkpoint position) is where database recovery is to begin in the event of an instance failure. DBWn will already have written all of the buffers in the Database Buffer Cache that were modified prior to that point in time.

Prior to Oracle 9i, This was done at the end of the Online Redo Log File. In the Event of a log switch CKPT also writes this checkpoint information's to the headers of the data files.

Checkpoints are initiated for the fallowing Reasons :

- To Ensure that modified data block in memory are written to disk regularly so that data is not lost in case of a system or Database failure.

- To reduce the time required for instance recovery. Only the online redo log file entries following the last checkpoint need to be processed for recovery occurs.

- To Ensure that all committed data has been written to the data files during shut down.

Checkpoint information written by CKPT includes checkpoint Position, System Change Number (SCN), Location in the online redo log file  to begin recovery, Information About logs and So on.

• ARCn (Achiever ) Process :  ARCn process is an optional background Process but it is very Important process whose play a crucial role to recovering a database after a loss of disk or Disk failures. ARCn process functionalities as below:
• Automatically archives Online Redo Log Files when ARCHIVE LOG mode is enabled.
• Preserves the record of all changes made to the database

ARCn is an optional background process , however, it is crucial to recovering a database after the loss of a disk. As Online Redo Log files get filled, the oracle server begins writing to the next online redo log file. The Process of switching from one Online Redo Log file to another is called a Log Switch.

The ARCn process initiates backing up, or archiving, of the filled log group at every log switch. It automatically archives the online redo log file before the log can be reused, So all of the changes made to the database are preserved. This Enables recovery of the database to the point of failure even if a disk drive is damaged.

Archiving Online Redo Log Files :

One of the important decisions that a Database Admin (DBA) has to make is whether to configure the database to operate in ARCHIVE LOG or in NO ARCHIVE LOG mode.

NO ARCHIVE LOG Mode : In NO ARCHIVE LOG mode, the online redo log files are overwritten each time when a log switch occurs. LGWR does not overwrite an Online Redo Log File group until the checkpoint for the group is complete. This Ensures that the committed data can be recovered if there is an instance crash. During the instance crash, Only SGA is lost. There is no Loss of disks, Only memory.  For Example, An operation system crash causes Instance Crash.

ARCHIVE LOG Mode : If the Database configured to run in ARCHIVE LOG mode, Inactive groups of filled Online Redo Log Files must be archives before they can be used again. Because changes made to the database are recorded in the online redo log files, The DBA can use the physical backup of the data files and the archived redo log files to recover the database without losing any committed data because of nay single point of failure, Including  the loss of a disk. Usually, a production database is configured to run in ARCHIVE LOG mode.

Thank You !!

Program Global Area OR Process Global area (PGA)

Program Global Area OR Process Global Area (PGA):-  

The Program Global Area  OR  Process Global Area (PGA) is a memory reason that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and deallocated when the process is terminated. In Contrast to the SGA, Which is shared by several processes, the PGA is an area that is used by only one process.

PGA has Below Functionality :

• Memory reserved for each user process connection to an Oracle Database
• Memory Allocated when a process  is created
• Memory deallocated when the process is terminated
• Each Server Process having on its own PGA and used by only one process.

Contents of PGA :

The contents of the PGA memory varies, Depending on whether the Instance is running in a dedicated server Or Shared Server configuration. Generally, the PGA memory includes these below components :

• Private SQL Area : It contains data such as bind information and run-time memory structures. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own Private SQL Area that uses a single shared SQL area. Thus many private SQL areas can be associated with the same Shared SQL Area. 

         The Private SQL area of a Cursor is divided into two areas :

• Persistent Area : Persistent Area contains bind Information, and it is freed only when the cursor is closed.
• Run-time Area :  Run-time Area Created as the first step of an execute request. For INSERT, UPDATE,  and DELETE commands, This area is freed after the statement has been executed. For queries, This area is freed only after all rows are fetched OR the query is canceled.

The location of the Private SQL area depends on the type of connections established for the session. In a dedicated server environment, the Private SQL Area is located in the PGA of their Server Process. In a shared server environment, the Private SQL Areas are located in SGA.

The management of the private SQL area is the responsibility of the user process. The number of Private SQL Areas that a user process can allocate is always limited by the OPEN_CURSORS initialization parameter. The default value of this parameter is 50. 

• Session Memory: Session Memory consists of memory allocated to hold a session's variables and other information related to the session. For a shared server environment, the session memory is shared and not private.
• SQL Work Area: SQL Work Areas Used for memory-intensive operations such as Sort, Hash-Join, Bitmap Merge, Bitmap Create. The Size of a work area can be controlled and tuned. Beginning from Oracle 9i, the size of the work area can be automatically and globally managed. This is enabled by Setting the WORKAREA_SIZE_POLICY  Parameter to Auto, Which is the default value, and the PGA_AGGREGATE_TARGET initialization Parameter. The PGA_AGGREGATE_TARGET  parameter is set to specify the target aggregate amount of PGA  memory available to the instance. This Parameter is only a target and can be dynamically modified at the instance level. It will accept a number of bytes, kilobytes, megabytes, or gigabytes. When these parameters are set sizing of work areas becomes automatic and all *_AREA_SIZE.

Prior to oracle 9i, The DBA controlled the maximum size  of SQL work area by setting the following parameters:

SORT_AREA_SIZE , 

HASH_AREA_SIZE, 

BITMAP_MERGE_AREA_SIZE,  and 

CREATE_BITMAP_AREA_SIZE

Setting these parameters can be difficult because the maximum work area size is ideally selected on the basis of the data input size and the total number of the work area active in the system. These  two factors vary considerably from one work area to another and from one time to another.

Differences in Memory Allocation Between Dedicated Server and Shared Server :

The content of the PGA memory varies, Depending on whether the instance is running in a dedicated server or shared server configuration. Generally, the PGA memory includes Fallowing components. 

Memory Area	Dedicated Server	Shared Server
Nature of Session Memory	Private	Shared
Location Of Persistent Area	PGA	SGA
Location Of  Run-time Area (SELECT)	PGA	SGA
Location Of  Run-time Area (DML/DDL)	PGA	PGA

Thank You !!

Large Pool And JAVA Pool

Large Pool : 

Large Pool is an optional area in SGA. It relieves the burden of the Shared pool also.

It provides an area of memory from which large allocations can be made. 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 the large pool memory allocation is present.

Large Pool Reduces the overhead of the shared pool, in case of shared server connection mode.

A large Pool is used for the following :

• Session memory for the shared server and the Oracle XA Interface when distributed transactions are involved
• I/O server processes
• Backup and restore operations for RMAN
• Parallel execution message buffers

              PARALLEL_AUTOMATIC_TUNING set to TRUE

Large Pool does not use an LRU list. It is sized by LARGE_POOL_SIZE and can be resized dynamically.

For shared server or parallel query buffers, Oracle uses the shared pool primarily for caching shared SQL statements. For a shared server or parallel query buffers, Oracle Uses the large pool memory when we set below Parameter value to TRUE.

PARALLEL_AUTOMATIC_TUNING=TRUE

Allocating session memory from the large pool for a shared server or parallel query buffers, Thus relieving the burden on the area within the shared pool.

By allocating session memory from the large pool for shared SQL, the database avoids performance overhead caused by increasing and shrinking the shared SQL cache in shared pool memory.

By allocating memory in large buffers for RMAN operations, I/O server processes, and parallel buffers, the large pool can satisfy large memory requests better than the shared pool.

By allocating memory form the large pool, for caching SQL parse tree in favor of shared server session information, I/O, and backup and recovery processes, The shared pool does not have to give up own memory for these processes. So the Performance gain happens from the reduction of overhead from increasing and shrinkage of the shared SQL cache in the shared pool.

The multi-threaded server will also take advantage of the large pool, allocating session memory there instead of in the shared pool, thus leaving more of the shared pool open for SQL statements and execution plans.

Below is t use Cases of Large Pool:

Backup And Restore :

Recovery Manager (RMAN) uses the large pool when the parameter set as below.

• BACKUP_DISK_IO = n
• BACKUP_TAPE_IO_SLAVE = TRUE

If the large pool is configured but is not large enough, the allocation of memory from the large pool fails. RMAN writes an Error message to the alert log file and does not use I/O slaves for backup and restore.

Parallel Execution :

Large Pool is used for Parallel execution when the below parameter is set to TRUE, Otherwise,  these buffers are allocated and use the shared pool memory.

PARALLEL_AUTOMATIC_TUNING=TRUE

Sizing The LARGE POOL :

A large pool area will only be available if the parameter has set in the initialization parameter. For example, to allocate a large pool of 10 megabytes, you would add the following line to your database's initialization parameter file.

LARGE_POOL_SIZE = 10M

The large pool is sized in bytes defined by the LARGE_POOL_SIZE  Parameter. This Parameter can be dynamically resized using the ALTER SYSTEM SET command.

ALTER SYSTEM SET LARGE_POOL_SIZE=50M;

Large Pool and LRU List :

The large Pool does not have an LRU list.  It is different from reserved space in the shared pool, which uses LRU List.

JAVA POOL :

The java pool is a RAM region within the Oracle SGA and the java pool RAM is used to provide:

• Services Parsing of Java code and scripts. 
• Installation tasks related to Java applications with Oracle versions. 
• Java stored procedure code parsing.
• Sized by JAVA_POOL_SIZE Parameter

The JAVA pool is an optional setting but is required if you are installing and using JAVA. Its Size is set in Bytes using the JAVA_POOL_SIZE. The Default Size of the JAVA POOL  is 24MB (Oracle 9i)

Thank You !!