Oracle Data Guard Architecture

Data is one, if not the main value of the business, data security is important for the database administrator, this includes timely backup and recovery. I'm not going to lie, but cost plays a big part in the decision to implement a particular solution. Oracle Data Guard can be overwhelming for many businesses. Having a solution that takes minutes will not be cheap, and many small companies do. that I work for are very happy to be able to copy the redo logs to a static machine and create a script to add those logs, this may not be the best solution but it works. This has disadvantages because it is a scalable solution that is difficult to manage or transfer to other database administrators. Oracle's Data Guard is well-tested and supported, but it is not cheap it is a solution that is almost universal in all companies and that all data protection administrators should be able to understand quickly when they start. new business.

In this series, I cover the architecture, installation, and configuration of Oracle Data Guard 11g, and I will cover operational planning and how to implement solutions to ensure data integrity. I also explain how to use unused storage databases, thus getting the most out of your system. I will also cover the new Enterprise Manager and how it fits into the database, most management tools use GUI interfaces, so there is no escaping it, the way I will learn both because the GUI will run the command line and the experience. and a good understanding of the law will help you find and solve difficult issues.

My advice is the more you practice, the more you do it the better you will do it and it will become second nature, in today's world, there are many good solutions (VMware, HyperV, etc.) that you can. Configure and do whatever you want, even some of these virtual solutions are free (VMware ESXi), all you need is a spare server or PC (see my virtual solution), extra RAM, and disk space. I can set up a solution similar to what I have on the company site, test and learn how the solution works, and get an idea of ​​what I need to do if I need to change, I can make and record the products before time. this happens, in some cases, I have worked in a company and I will never change, but I am always happy that I am doing something and have the book in hand. Okay, I'm done rambling, let's start by learning Oracle Data Guard. 

Data Guard Overview

Data security is basically a recovery and put a job because as you know, recovery is the information needed to recover the data security business. A production database called the primary database forwards recovery to one or more independent replicas called standby databases. The database is in continuous recovery mode, supporting and applying recovery to maintain relationships with the primary database. The backup database will automatically recover if it is temporarily disconnected from the primary database due to power outages, network problems, etc. 

     A Dataguard configuration consists of one primary database and one or more standby databases. The standby databases will be always in sync with the primary database. If the primary production database is unavailable due to a planned or unplanned outage, then we can switch the production to the standby database, making the application less affected by the outage.

The main features of Oracle Data Guard are

1. High Availability

2. Data protection

3. Disaster Recovery

    

                                                FIGURE 1-01. Typical Data Guard Configuration

The diagram above shows the overview of Data Guard First, the recovery service transfers the recovery data from the primary to the standby server after it is created. Second, users enter recovery data and update the standby database file. Third, independent of the database, the author of the database. This process updates the primary database files and eventually, Data Guard will automatically restore the standby database following a power or network failure using the recovery data stored in the primary database.

.If you haven't read my Oracle process on rollback, this is a rollback, Roll Index (also called rollback) is a group of changes, each of which is a description of changes made in one block. . and data protection. Restore records contain all the necessary information to restore the changes made to the database. During recovery, the database will read the change vectors in the redo record and apply changes to the affected blocks.

Redo logs are stored cyclically in the SGA's redo buffer, and the log write process (LGWR) is a backup process that manages redo buffer management. LGWR writes log entries to batch files (internet log files) at specific intervals to provide access and security, LGWR writes.

• A commit record - Each time a transaction is made, LGWR writes a log of the transaction from storage to a log file and submits a System Change Number (SCN). Only after this process is completed is the transaction confirmed.
• Redo log buffers - If the redo log is 1/3 full or if 3 seconds have passed since LGWR last wrote to the log file, all entries in the backup folder will be written to the log file. This means that a redo record can be written to the log file before the transaction is made and, if necessary, the media recovery will repeat the changes using the correction which is also part of the input recovery.

FIGURE 1-02. Data Guard Redo Transport

Note that LGWR can use "group" in log files. Essentially, all pending (uncommitted) transaction redo entries can be written to disk in a single operation, thereby reducing I/O. Even if the database cache is not written to disk, Oracle guarantees that no transaction will be lost due to the successful change log and redo log.

The Redo Data Transport Backup service coordinates the transfer of recovery from the primary database to the standby database. At the same time as the LGWR performs the recovery operation, a separate database system called the Log Network Server (LNS) reads from the recovery storage in the SGA and forwards the recovery to Oracle Net Services by forwarding to database alert, it is possible. restore data directly to nine storage databases, you can also use Oracle RAC but not all of them need to be RAC configurations. The Remote File Server (RFS) system receives the redo from the LNS and writes it to a system file called a fallback redo log (SRL) file. The LNS system supports two modes: synchronous and asynchronous.

A Dataguard configuration consists of one primary database and one or more standby databases. The standby databases will be always in sync with the primary database. If the primary production database is unavailable due to a planned or unplanned outage, then we can switch the production to a standby database, making the application less affected by the outage.

Data Guard Management

PROTECTION MODES:

It can have 3 protection modes.

1. MAXIMUM PROTECTION : 

This type provides maximum protection. It guarantees zero data loss. In this mode, the redo/transaction data must be written to both the primary redo log and the backup redo log. For some reason (mostly N/W editions), if it doesn't write while sleeping, Primary will stop.

2. MAXIMUM AVAILABILITY : 

It provides the highest possible data protection without affecting the availability of the main data storage. Transactions are not allowed until all redo data is written into the online redo log and propagated to at least one synchronized secondary database. If for some reason the primary database cannot propagate its recovery flow to a secondary database, the primary database will NOT shut down and will run in near maximum performance mode until until the problems are resolved.

3. MAXIMUM PERFORMANCE : 

This is the default mode. From this safety, a business is started once data recovery data to restore the transaction is written to the local (online) redo log. 

Oracle Logical Storage Structures

The data files in an Oracle database are grouped together into one or more tablespaces. Within each tablespace, the logical database structures, such as tables and indexes, are segments that are further subdivided into extents and blocks. This logical subdivision of storage allows Oracle to have more efficient control over disk space usage. Figure 1-01 shows the relationship between the logical storage structures in a database.

FIGURE 1-01. Logical storage structures

Tablespaces :

An Oracle tablespace consists of one or more data files; a datafile can be a part of one and only one tablespace. For an installation of Oracle 12c, a minimum of two tablespaces are created: the SYSTEM tablespace and the SYSAUX tablespace; a default installation of Oracle 12c creates six tablespaces.

Oracle Database 10g will later allow you to create a special kind of tablespace called a big file tablespace, which can be as large as 128TB (terabytes). Using big files makes tablespace management completely transparent to the DBA; in other words, the DBA can manage the tablespace as a unit without worrying about the size and structure of the underlying data files. Oracle Managed Files (OMF) can make tablespace datafile management even easier. With OMF, the DBA specifies one or more locations in the file system where data files, control files, and redo log files will reside, and Oracle automatically handles the naming and management of these files. If a tablespace is temporary, it is permanent; only the segments saved in the tablespace are temporary. A temporary tablespace can be used for sorting operations and for tables that exist only for the duration of the user’s session. Dedicating a tablespace for these kinds of operations helps to reduce the I/O contention between temporary segments and permanent segments stored in another tablespace, such as tables. Tablespaces can be either dictionary-managed or locally managed. In a dictionary-managed tablespace, extent management is recorded in data dictionary tables. Therefore, even if all application tables are in the USERS tablespace, the SYSTEM tablespace will still be accessed for managing Data Manipulation Language (DML) on application tables. Because all users and applications must use the SYSTEM tablespace for extent management, this creates a potential bottleneck for write-intensive applications. In a locally managed tablespace, Oracle maintains a bitmap in each data file of the tablespace to track space availability. Only quotas are managed in the data dictionary, dramatically reducing the contention for data dictionary tables. There is really no good reason for creating dictionary-managed tablespaces. When you install Oracle 12c, the SYSTEM and SYSAUX tablespaces must be locally managed. For importing transportable tablespaces, a tablespace can be dictionary managed but it will be read-only. 

FIGURE 1-02. Logical Storage Units

Blocks :

A database block is the smallest unit of storage in the Oracle database. The size of a block is a specific number of bytes of storage within a given tablespace within the database. A block is usually a multiple of the operating system block size to facilitate efficient disk I/O. The default block size is specified by the Oracle initialization parameter DB_BLOCK_SIZE. As many as four other block sizes may be defined for other tablespaces in the database, although the blocks in the SYSTEM, SYSAUX, and any temporary tablespaces must be of the size DB_BLOCK_SIZE. The default block size is 8K and all Oracle testing is performed using 8K blocks. Oracle best practices suggest using an 8K block size for all tablespaces unless there is a compelling reason to use a different size. One reason could be that the average row length for a table is 20K. Therefore, you might choose to use 32K blocks, but you should fully test to see if there is a performance gain.

Extents :

The extent is the next level of logical grouping in the database. An extent consists of one or more database blocks. When you enlarge a database object, the space added to the object is allocated as an extent.

Segments :

The next level of logical grouping in a database is the segment. A segment is a group of extents that form a database object that Oracle treats as a unit, such as a table or index. As a result, this is typically the smallest unit of storage that an end user of the database will deal with. Four types of segments are found in an Oracle database: table segments (non-partitioned tables and each partition of a partitioned table), index segments, temporary segments, and rollback segments.

Data Segment :

Every table in the database resides in a single data segment, consisting of one or more extents; Oracle allocates more than one segment for a table if it is a partitioned table or a clustered table. Partitioned and clustered tables are discussed later in this chapter. Data segments include LOB (large object) segments that store LOB data referenced by a LOB locator column in a table segment (if the LOB is not stored inline in the table).

FIGURE 1-03. Logical Storage Hierarchy

Index Segment :

Each index is stored in its own index segment. As with partitioned tables, each partition of a partitioned index is stored in its own segment. Included in this category are LOB index segments; a table’s non-LOB columns, a table’s LOB columns, and the LOBs’ associated indexes can all reside in their own tablespace to improve performance.

Temporary Segment :

When a user’s SQL statement needs disk space to complete an operation, such as a sorting operation that cannot fit in memory, Oracle allocates a temporary segment. Temporary segments exist only for the duration of the SQL statement.

Rollback Segment :

As of Oracle 10g, legacy rollback segments only exist in the SYSTEM tablespace, and typically the DBA does not need to maintain the SYSTEM rollback segment. In previous Oracle releases, a rollback segment was created to save the previous values of a database DML operation in case the transaction was rolled back and to maintain the “before” image data to provide read-consistent views of table data for other users accessing the table. Rollback segments were also used during database recovery for rolling back uncommitted transactions that were active when the database instance crashed or terminated unexpectedly.

Automatic Undo Management (AUM) handles the automatic allocation and management of rollback segments within an undo tablespace. Within an undo tablespace, the undo segments are structured similarly to rollback segments, except that the details of how these segments are managed are under the control of Oracle, instead of being managed (often inefficiently) by the DBA. Automatic undo segments were available starting with Oracle9i, but manually managed rollback segments are still available in Oracle 12c. However, this functionality is deprecated as of Oracle 10g, and will no longer be available in future releases. In Oracle 12c, AUM is enabled by default; in addition, a PL/SQL procedure is provided to help you size the undo tablespace. 

Background Processes

When an Oracle instance starts, multiple background processes start. A background process is a block of executable code designed to perform a specific task. Figure 1-6 shows the relationship between the background processes, the database, and the Oracle SGA. In contrast to a foreground process, such as a SQL*Plus session or a web browser, a background process works behind the scenes. Together, the SGA and the background processes compose an Oracle instance.

FIGURE 1-01. Oracle background processes

Background Processes in Oracle

To maximize performance and accommodate many users, a multiprocessed Oracle database system uses background processes. Background processes are the processes running behind the scenes and are meant to perform certain maintenance activities or to deal with abnormal conditions arising in the instance. Each background process is meant for a specific purpose and its role is well-defined.

Background processes consolidate functions that would otherwise be handled by multiple database programs running for each user process. Background processes asynchronously perform I/O and monitor other Oracle database processes to provide increased parallelism for better performance and reliability. A background process is defined as any process that is listed in V$PROCESS and has a non-null value in the pname column. Not all background processes are mandatory for instance. Some are mandatory and some are optional. Mandatory background processes are DBWn, LGWR, CKPT, SMON, PMON, and RECO. All other processes are optional and will be invoked if that particular feature is activated.

Oracle background processes are visible as separate operating system processes in Unix/Linux. In Windows, these run as separate threads within the same service. Any issues related to background processes should be monitored and analyzed from the trace files generated and the alert log.

Background processes are started automatically when the instance is started.

To find out background processes from the database:

SQL> select SID,PROGRAM from v$session where TYPE='BACKGROUND';

To find out background processes from OS:

$ ps -ef|grep ora_|grep SID

Mandatory Background Processes in Oracle

If any one of these 6 mandatory background processes is killed/not running, the instance will be aborted.

1) Database Writer (maximum 20) DBW0-DBW9,DBWa-DBWj

Whenever a log switch occurs as the redo log file is becoming CURRENT to the ACTIVE stage, oracle calls DBWn and synchronizes all the dirty blocks in the database buffer cache to the respective datafiles, scattered or randomly.

The database writer process, known as DBWR in older versions of Oracle, writes new or changed data blocks (known as dirty blocks) in the buffer cache to the data files. Using an LRU algorithm, DBWn writes the oldest, least active blocks first. As a result, the most commonly requested blocks, even if they are dirty blocks, are in memory. Up to 20 DBWn processes can be started, DBW0 through DBW9 and DBWa through DBWj. The number of DBWn processes is controlled by the DB_WRITER_PROCESSES parameter.

Database writer (or Dirty Buffer Writer) process does multi-block writing to disk asynchronously. One DBWn process is adequate for most systems. Multiple database writers can be configured by initialization parameter DB_WRITER_PROCESSES, depends on the number of CPUs allocated to the instance. To have more than one DBWn only make sense if each DBWn has been allocated its own list of blocks to write to disk. This is done through the initialization parameter DB_BLOCK_LRU_LATCHES. If this parameter is not set correctly, multiple DB writers can contend for the same block list.

The possible multiple DBWR processes in RAC must be coordinated through the locking and global cache processes to ensure efficient processing is accomplished.

DBWn will be invoked in the following scenarios:

When the dirty blocks in SGA reach a threshold value, oracle calls DBWn.

When the database is shutting down (normal, transactional, immediate) with some dirty blocks in the SGA, then Oracle calls DBWn.

DBWn has a time-out value (3 seconds by default) and it wakes up whether there are any dirty blocks or not.

When a checkpoint is issued.

When a server process cannot find a clean reusable buffer after scanning a threshold number of buffers.

When a huge table wants to enter into SGA and oracle could not find enough free space where it decides to flush out LRU blocks and which happens to be dirty blocks. Before flushing out the dirty blocks, oracle calls DBWn.

Oracle RAC ping request is made.

When Table DROPED or TRUNCATED.

When tablespace is going to OFFLINE/READ ONLY/BEGIN BACKUP.

2) Log Writer (maximum 1) LGWR

LGWR writes redo data from redo log buffers to (online) redo log files, sequentially.

The Redolog file contains changes to any data file. The content of the redo log file is file ID, block ID, and new content.

LGWR will be invoked more often than DBWn as log files are really small compared to datafiles (KB vs GB). For every small update, we don’t want to open huge gigabytes of datafiles, instead write to the log file.

LGWR, or the log writer process, is in charge of redo log buffer management. LGWR is one of the most active processes in an instance with heavy DML activity. A transaction is not considered complete until LGWR successfully writes the redo information, including the commit record, to the redo log files. In addition, the dirty buffers in the buffer cache cannot be written to the datafiles by DBWn until LGWR has written the redo information.If the redo log files are grouped, and one of the multiplexed redo log files in a group is damaged, LGWR writes to the remaining members of the group and records an error in the alert log file. If all members of a group are unusable, the LGWR process fails and the entire instance hangs until the problem can be corrected.

Redolog file has three stages CURRENT, ACTIVE, INACTIVE and this is a cyclic process. The newly created redo log file will be in a UNUSED state.

When the LGWR is writing to a particular redo log file, that file is said to be in CURRENT status. If the file is filled up completely then a log switch takes place and the LGWR starts writing to the second file (this is the reason every database requires a minimum of 2 redo log groups). The file which is filled up now becomes from CURRENT to ACTIVE.

The log writer will write synchronously to the redo log groups in a circular fashion. If any damage is identified with a redo log file, the log writer will log an error in the LGWR trace file and the alert log. Sometimes, when additional redo log buffer space is required, the LGWR will even write uncommitted redo log entries to release the held buffers. LGWR can also use group commits (multiple committed transactions' redo entries taken together) to write to redo logs when a database is undergoing heavy write operations.

In RAC, each RAC instance has its own LGWR process that maintains that instance’s thread of redo logs.

LGWR will be invoked in the following scenarios:

LGWR is invoked whenever 1/3rd of the redo buffer is filled up.

Whenever the log writer times out (3sec)

Whenever 1MB of redo log buffer is filled (This means that there is no sense in making the redo log buffer more than 3MB).

Shutting down the database.

Whenever a checkpoint event occurs.

When a transaction is completed (either committed or rollbacked) oracle calls the LGWR and synchronizes the log buffers to the redo log files and then only passes on the acknowledgment back to the user. This means the transaction is only guaranteed if we say commit unless we receive the acknowledgment. When a transaction is committed, a System Change Number (SCN) is generated and tagged to it. The log writer puts a commit record in the redo log buffer and writes it to disk immediately along with the transaction's redo entries. Changes to actual data blocks are deferred until a convenient time (Fast-Commit mechanism).

When DBWn signals the writing of redo records to disk. All redo records associated with changes in the block buffers must be written to disk first (The write-ahead protocol). While writing dirty buffers, if the DBWn process finds that some redo information has not been written, it signals the LGWR to write the information and waits until the control is returned.

3) Checkpoint (maximum 1) CKPT

Checkpoint is a background process that triggers the checkpoint event, to synchronize all database files with the checkpoint information. It ensures data consistency and faster database recovery in case of a crash.

The checkpoint process, or CKPT, helps to reduce the amount of time required for instance recovery. During a checkpoint, CKPT updates the header of the control file and the datafiles to reflect the last successful System Change Number (SCN). A checkpoint occurs automatically every time a redo log file switch occurs. The DBWn processes routinely write dirty buffers to advance the checkpoint from where instance recovery can begin, thus reducing the Mean Time to Recovery (MTTR).

When a checkpoint occurs it will invoke the DBWn and update the SCN block of all datafiles and the control file with the current SCN. This is done by LGWR. This SCN is called checkpoint SCN.

Checkpoint event can occur in the following conditions:

Whenever the database buffer cache fills up.

Whenever times out (3 seconds until 9i, 1 second from 10g).

Log switch occurred

Whenever the manual log switch is done.

SQL> ALTER SYSTEM SWITCH LOGFILE;

Manual checkpoint.

SQL> ALTER SYSTEM CHECKPOINT;

Graceful shutdown of the database.

Whenever the BEGIN BACKUP command is issued.

When the time specified by the initialization parameter LOG_CHECKPOINT_TIMEOUT (in seconds), exists between the incremental checkpoint and the tail of the log.

When the number of OS blocks specified by the initialization parameter LOG_CHECKPOINT_INTERVAL, exists between the incremental checkpoint and the tail of the log.

The number of buffers specified by the initialization parameter FAST_START_IO_TARGET required to perform roll-forward is reached.

Oracle 9i onwards, the time specified by the initialization parameter FAST_START_MTTR_TARGET (in seconds) is reached and specifies the time required for crash recovery. The parameter FAST_START_MTTR_TARGET replaces LOG_CHECKPOINT_INTERVAL and FAST_START_IO_TARGET, but these parameters can still be used.

4) System Monitor (maximum 1) SMON

If the database is crashes (power failure) and the next time we restart the database SMON observes that last time the database was not shut down gracefully. Hence it requires some recovery, which is known as INSTANCE CRASH RECOVERY. When performing the crash recovery before the database is completely open, if it finds any transaction committed but not found in the datafiles, will now be applied from redo log files to data files.

SMON is the System Monitor process. In the case of a system crash or instance failure, due to a power outage or CPU failure, the SMON process performs crash recovery by applying the entries in the online redo log files to the data files. In addition, temporary segments in all tablespaces are purged during system restart. One of SMON’s routine tasks is to coalesce the free space in tablespaces regularly if the tablespace is dictionary managed.

If SMON observes some uncommitted transaction that has already updated the table in the data file, is going to be treated as an in-doubt transaction and will be rolled back with the help of the before image available in rollback segments.

SMON also cleans up temporary segments that are no longer in use.

It also coalesces contiguous free extents in dictionary-managed tablespaces that have PCTINCREASE set to a non-zero value.

In the RAC environment, the SMON process of one instance can perform instance recovery for other instances that have failed.

SMON wakes up about every 5 minutes to perform housekeeping activities.

5) Process Monitor (maximum 1) PMON

If a client has an open transaction that is no longer active (client session is closed) then PMON comes into the picture and that transaction becomes in doubt transaction that will be rolled back.

PMON is responsible for performing recovery if a user process fails. It will roll back uncommitted transactions. If the old session is locked any resources that will be unlocked by PMON.

PMON is responsible for cleaning up the database buffer cache and freeing resources that were allocated to a process.

PMON also registers information about the instance and dispatcher processes with the Oracle (network) listener. PMON also checks the dispatcher & server processes and restarts them if they have failed. Since Oracle 12c, the LREG (Listener REGistration) process has been taking care of listener operations.

PMON wakes up every 3 seconds to perform housekeeping activities.

In RAC, PMON’s role as a service registration agent is particularly important.

If a user connection is dropped, or a user process otherwise fails, PMON, also known as the Process Monitor, does the cleanup work. It cleans up the database buffer cache along with any other resources that the user connection was using. For example, a user session may be updating some rows in a table, placing a lock on one or more of the rows. A thunderstorm knocks out the power at the user’s desk, and the SQL*Plus session disappears when the workstation is powered off. Within moments, PMON will detect that the connection no longer exists and perform the following tasks:

■ Roll back the transaction that was in progress when the power went out.

■ Mark the transaction’s blocks as available in the buffer cache.

■ Remove the locks on the affected rows in the table.

■ Remove the process ID of the disconnected process from the list of active processes.

PMON will also interact with the listeners by providing information about the status of the 

instance for incoming connection requests.

6) Recoverer (maximum 1) RECO [Mandatory from Oracle 10g]

This process is intended for recovery in distributed databases. The distributed transaction recovery process finds pending distributed transactions and resolves them. All in-doubt transactions are recovered by this process in the distributed database setup. RECO will connect to the remote database to resolve pending transactions.

Pending distributed transactions are two-phase commit transactions involving multiple databases. The database that the transaction started is normally the coordinator. It will send requests to other databases involved in a two-phase commit if they are ready to commit. If a negative request is received from one of the other sites, the entire transaction will be rolled back. Otherwise, the distributed transaction will be committed on all sites. However, there is a chance that an error (network-related or otherwise) causes the two-phase commit transaction to be left in a pending state (i.e. not committed or rolled back). It's the role of the RECO process to liaise with the coordinator to resolve the pending two-phase commit transaction. RECO will either commit or roll back this transaction.

The RECO, or recoverer process, handles failures of distributed transactions (that is, transactions that include changes to tables in more than one database). If a table in the CCTR database is changed along with a table in the WHSE database, and the network connection between the databases fails before the table in the WHSE database can be updated, RECO will roll back the failed transaction.

                                         

                                               FIGURE 1-02. Oracle background processes

Optional Background Processes in Oracle

Archiver (maximum 10) ARC0-ARC9

The ARCn process is responsible for writing the online redo log files to the mentioned archive log destination after a log switch has occurred. ARCn is present only if the database is running in archive log mode and automatic archiving is enabled. The log writer process is responsible for starting multiple ARCn processes when the workload increases. Unless ARCn completes the copying of a redo log file, it is not released to the log writer for overwriting.

If the database is in ARCHIVELOG mode, then the archiver process, or ARCn, copies redo logs to one or more destination directories, devices, or network locations whenever a redo log fills up and redo information starts to fill the next redo log in sequence. Optimally, the archiver process finishes before the filled redo log is needed again; otherwise, serious performance problems occur—users cannot complete their transactions until the entries are written to the redo log files, and the redo log file is not ready to accept new entries because it is still being written to the archive location. There are at least three potential solutions to this problem: make the redo log files larger, increase the number of redo log groups, and increase the number of ARCn processes. Up to 30 ARCn processes can be started for each instance by increasing the value of the LOG_ARCHIVE_MAX_PROCESSES initialization parameter.

The number of archiver processes that can be invoked initially is specified by the initialization parameter LOG_ARCHIVE_MAX_PROCESSES (by default 2, max 10). The actual number of archiver processes in use may vary based on the workload.

ARCH processes, running on the primary database, select archived redo logs and send them to the standby database. Archive log files are used for media recovery (in case of a hard disk failure and for maintaining an Oracle standby database via log shipping). Archives the standby redo logs applied by the managed recovery process (MRP).

In RAC, the various ARCH processes can be utilized to ensure that copies of the archived redo logs for each instance are available to the other instances in the RAC setup should they be needed for recovery.

Coordinated Job Queue Processes (maximum 1000) CJQ0/Jnnn

Job queue processes carry out batch processing. All scheduled jobs are executed by these processes. The initialization parameter JOB_QUEUE_PROCESSES specifies the maximum job processes that can be run concurrently. These processes will be useful in refreshing materialized views.

This is Oracle’s dynamic job queue coordinator. It periodically selects jobs (from JOB$) that need to be run, scheduled by the Oracle job queue. The coordinator process dynamically spawns job queue slave processes (J000-J999) to run the jobs. These jobs could be PL/SQL statements or procedures on an Oracle instance.

CQJ0 - The job queue controller process wakes up periodically and checks the job log. If a job is due, it spawns Jnnnn processes to handle jobs.

From Oracle 11g release2, DBMS_JOB, and DBMS_SCHEDULER work without setting JOB_QUEUE_PROCESSES. Before 11gR2, the default value is 0, and from 11gR2 the default value is 1000.

Dedicated Serve

Dedicated server processes are used when MTS is not used. Each user process gets a dedicated connection to the database. These user processes also handle disk reads from database datafiles into the database block buffers.

LISTENER

The LISTENER process listens for connection requests on a specified port and passes these requests to either a distributor process if MTS is configured, or to a dedicated process if MTS is not used. The LISTENER process is responsible for load balance and failover in case a RAC instance fails or is overloaded.

CALLOUT Listener

Used by internal processes to make calls to externally stored procedures.

Lock Monitor (maximum 1) LMON

The lock monitor manages global locks and resources. It handles the redistribution of instance locks whenever instances are started or shut down. The lock monitor also recovers instance lock information before the instance recovery process. The lock monitor coordinates with the Process Monitor (PMON) to recover dead processes that hold instance locks.

Lock Manager Daemon (maximum 10) LMDn

LMDn processes manage instance locks that are used to share resources between instances. LMDn processes also handle deadlock detection and remote lock requests.

Global Cache Service (LMS)

In an Oracle Real Application Clusters environment, this process manages resources and provides inter-instance resource control.

Lock processes (maximum 10) LCK0- LCK9

The instance locks that are used to share resources between instances are held by the lock processes.

Block Server Process (maximum 10) BSP0-BSP9

Block server Processes have to do with providing a consistent read image of a buffer that is requested by a process of another instance, in certain circumstances.

Queue Monitor (maximum 10) QMN0-QMN9

This is the advanced queuing time manager process. QMNn monitors the message queues. QMN is used to manage Oracle Streams Advanced Queuing.

Event Monitor (maximum 1) EMN0/EMON

This process is also related to advanced queuing and is meant to allow a publish/subscribe style of messaging between applications.

Dispatcher (maximum 1000) Dnnn

Intended for multi-threaded server (MTS) setups. Dispatcher processes listen to and receive requests from connected sessions and place them in the request queue for further processing. Dispatcher processes also pick up outgoing responses from the result queue and transmit them back to the clients. Dnnn are mediators between the client processes and the shared server processes. The maximum number of dispatcher processes can be specified using the initialization parameter MAX_DISPATCHERS.

Shared Server Processes (maximum 1000) Snnn

Intended for multi-threaded server (MTS) setups. These processes pick up requests from the call request queue, process them, and then return the results to a result queue. These user processes also handle disk reads from database datafiles into the database block buffers. The number of shared server processes to be created at instance startup can be specified using the initialization parameter SHARED_SERVERS. Maximum shared server processes can be specified by MAX_SHARED_SERVERS.

Parallel Execution/Query Slaves (maximum 1000) Pnnn

These processes are used for parallel processing. It can be used for parallel execution of SQL statements or recovery. The maximum number of parallel processes that can be invoked is specified by the initialization parameter PARALLEL_MAX_SERVERS.

Trace Writer (maximum 1) TRWR

The trace writer writes trace files from an Oracle internal tracing facility.

Input/Output Slaves (maximum 1000) Innn

These processes are used to simulate asynchronous I/O on platforms that do not support it. The initialization parameter DBWR_IO_SLAVES is set for this purpose.

Data Guard Monitor (maximum 1) DMON

The Data Guard broker process. DMON is started when Data Guard is started. This broker controller process is the main broker process and is responsible for coordinating all broker actions as well as maintaining the broker configuration files. This process is enabled/disabled with the DG_BROKER_START parameter.

Data Guard Broker Resource Manager RSM0

The RSM process is responsible for handling any SQL commands used by the broker that need to be executed on one of the databases in the configuration.

Data Guard NetServer/NetSlave NSVn

These are responsible for making contact with the remote database and sending any work items to the remote database. From 1 to n these network server processes can exist. NSVn is created when a Data Guard broker configuration is enabled. There can be as many NSVn processes (where n is 0- 9 and A-U) created as there are databases in the Data Guard broker configuration.

DRCn

These network receiver processes establish the connection from the source database NSVn process. When the broker needs to send something (e.g. data or SQL) between databases, it uses this NSV to DRC connection. These connections are started as needed.

Data Guard Broker Instance Slave Process INSV

Performs Data Guard broker communication among instances in an Oracle RAC environment

Data Guard Broker Fast Start Failover Pinger Process FSFP

Maintains a fast-start failover state between the primary and target standby databases. FSFP is created when fast-start failover is enabled.

LGWR Network Server process LNS

In Data Guard, the LNS process performs actual network I/O and waits for each network I/O to complete. Each LNS has a user-configurable buffer that is used to accept outbound redo data from the LGWR process. The NET_TIMEOUT attribute is used only when the LGWR process transmits redo data using an LGWR Network Server(LNS) process.

Managed Recovery Process MRP

In the Data Guard environment, this managed recovery process will apply archived redo logs to the standby database.

Remote File Server process RFS

The remote file server process, in the Data Guard environment, on the standby database receives archived redo logs from the primary database.

Logical Standby Process LSP

The logical standby process is the coordinator process for a set of processes that concurrently read, prepare, build, analyze, and apply completed SQL transactions from the archived redo logs. The LSP also maintains metadata in the database. The RFS process communicates with the logical standby process (LSP) to coordinate and record which files arrived.

Wakeup Monitor Process (maximum 1) WMON

This process was available in older versions of Oracle to alarm other processes that are suspended while waiting for an event to occur. This process is obsolete and has been removed.

Recovery Writer (maximum 1) RVWR

This is responsible for writing flashback logs (to FRA).

Fetch Archive Log (FAL) Server

Services requests for archive redo logs from FAL clients running on multiple standby databases. Multiple FAL servers can be run on a primary database, one for each FAL request.

Fetch Archive Log (FAL) Client

Pulls archived redo log files from the primary site. Initiates the transfer of archived redo logs when it detects a gap sequence.

Data Pump Master Process DMnn

Creates and deletes the master table at the time of export and import. The master table contains the job state and object information. Coordinates the Data Pump job tasks performed by Data Pump worker processes and handles client interactions. The Data Pump master (control) process is started during job creation and coordinates all tasks performed by the Data Pump job. It handles all client interactions and communication, establishes all job contexts, and coordinates all worker process activities on behalf of the job. Creates the Worker Process.

Data Pump Worker Process DWnn

It performs the actual heavy-duty work of loading and unloading data. It maintains the information in the master table. The Data Pump worker process is responsible for performing tasks that are assigned by the Data Pump master process, such as the loading and unloading of metadata and data.

Shadow Process

When the client logs in to an Oracle Server the database creates an Oracle process to service Data Pump API.

Client Process

The client process calls the Data Pump API.

New Background Processes in Oracle 10g

Memory Manager (maximum 1) MMAN

MMAN dynamically adjusts the sizes of the SGA components like buffer cache, large pool, shared pool, and java pool and serves as an SGA memory broker. It is a new process added to Oracle 10g as part of automatic shared memory management.

Memory Monitor (maximum 1) MMON

MMON monitors SGA and performs various manageability-related background tasks. MMON, the Oracle 10g background process, is used to collect statistics for the Automatic Workload Repository (AWR).Tracks/aggregates space utilization while performing regular space management activities.

Memory Monitor Light (maximum 1) MMNL

New background process in Oracle 10g. This process performs frequent and lightweight manageability-related tasks, such as session history capture and metrics computation. This process will flush the ASH buffer to AWR tables when the buffer is full or a snapshot is taken.

Change Tracking Writer (maximum 1) CTWR

CTWR will be useful in RMAN. Optimized incremental backups using block change tracking (faster incremental backups) using a file (named block change tracking file). CTWR (Change Tracking Writer) is the background process responsible for tracking the blocks.

ASMB

This ASMB process is used to provide information to and from cluster synchronization services used by ASM to manage the disk resources. It's also used to update statistics and provide a heartbeat mechanism.

Re-Balance RBAL

RBAL is the ASM-related process that performs rebalancing of disk resources controlled by ASM.

Actual Rebalance ARBx

ARBx is configured by ASM_POWER_LIMIT.

Autotask Background Process (ABP)

It translates tasks into jobs for execution by the scheduler. It determines the list of jobs that must be created for each maintenance window. Stores task execution history in the SYSAUX tablespace. It is spawned by the MMON background process at the start of the maintenance window.

File Monitor (FMON)

The database communicates with the mapping libraries provided by storage vendors through an external non-Oracle Database process that is spawned by a background process called FMON. FMON is responsible for managing the mapping information. When you specify the FILE_MAPPING initialization parameter for mapping data files to physical devices on a storage subsystem, then the FMON process is spawned.

Dynamic Intimate Shared Memory (DISM)

By default, Oracle uses intimate shared memory (ISM) instead of standard System V shared memory on the Solaris Operating system. When a shared memory segment is made into an ISM segment, it is mapped using large pages and the memory for the segment is locked (i.e., it cannot be paged out). This greatly reduces the overhead due to process context switches, which improves Oracle's performance linearity under load.

New Background Processes in Oracle 11g

ACMS - Atomic controlfile to Memory Server

DBRM - Database Resource Manager

DIA0 - Diagnosibility process 0

DIAG - Diagnosibility process

FBDA - Flashback Data Archiver, 

Background process fbda captures data asynchronously,

Every 5 minutes (default), more frequent intervals based on activity.

GTX0 - Global Transaction Process 0

KATE - Konductor (Conductor) of ASM Temporary Errands

MARK - Mark Allocation unit for Resync Koordinator (coordinator)

SMCO - Space Manager

VKTM - Virtual Keeper of TiMe process

W000 - Space Management Worker Processes

ABP - Autotask Background Process

New background processes in Oracle Database 12c

LREG (Listener Registration)

SA (SGA Allocator)

RM

Oracle Memory Structures

Oracle uses the server’s physical memory to hold many things for an Oracle instance: the Oracle executable code itself, session information, individual processes associated with the database, and information shared between processes (such as locks on database objects). In addition, the memory structures contain user and data dictionary SQL statements, along with cached information that is eventually permanently stored on disk, such as data blocks from database segments and information about completed transactions in the database. The data area allocated for an Oracle instance is the System Global Area (SGA). The Oracle executables reside in the software code area. In addition, the Program Global Area (PGA) is private to each server and background process; one PGA is allocated for each process. Figure 1-01 shows the relationships between these Oracle memory structures.

System Global Area :

The System Global Area is a group of shared memory structures for an Oracle instance, shared by the users of the database instance. When an Oracle instance is started, memory is allocated for FIGURE 1-01, SGA is based on the values specified in the initialization parameter file or hard-coded in the Oracle software. Many of the parameters that control the size of the various parts of the SGA are dynamic; however, if the parameter SGA_MAX_SIZE is specified, the total size of all SGA areas must not exceed the value of SGA_MAX_SIZE. If SGA_MAX_SIZE is not specified, but the parameter SGA_TARGET is specified, and Oracle automatically adjusts the sizes of the SGA components so that the total amount of memory allocated is equal to SGA_TARGET. SGA_TARGET is a dynamic parameter; it can be changed while the instance is running. The parameter MEMORY_TARGET, new to Oracle 11g, balances all memory available to Oracle between the SGA and the Program Global Area used to optimize performance. Memory in the SGA is allocated in units of granules. A granule can be either 4MB or 16MB, depending on the total size of the SGA. If the SGA is less than or equal to 128MB, a granule is 4MB; otherwise, it is 16 MB. In the next few subsections, we will cover the highlights of how Oracle uses each section in the SGA. 

FIGURE 1-01 Oracle logical memory structures

Buffer Caches :

The database buffer cache holds blocks of data from disk that have been recently read to satisfy a select statement or that contain modified blocks that have been changed or added from a DML statement. As of Oracle9i, the memory area in the SGA that holds these data blocks is dynamic. This is a good thing, considering that there may be tablespaces in the database with block sizes other than the default block size; tablespaces with up to five different block sizes (one block size for the default, and up to four others) require their own buffer cache. As the processing and transactional needs change during the day or during the week, the values of DB_CACHE_SIZE and DB_nK_CACHE_SIZE can be dynamically changed without restarting the instance to enhance performance for a tablespace with a given block size. Oracle can use two additional caches with the same block size as the default (DB_CACHE_SIZE) block size: the KEEP buffer pool and the RECYCLE buffer pool. As of Oracle9i, both of these pools allocate memory independently of other caches in the SGA. When a table is created, you can specify the pool where the table’s data blocks will reside by using the BUFFER_POOL KEEP or BUFFER_POOL_RECYCLE clause in the STORAGE clause. For tables that you use frequently throughout the day, it would be advantageous to place this table into the KEEP buffer pool to minimize the I/O needed to retrieve blocks in the table.

Shared Pool :

The shared pool contains two major subcaches: the library cache and the data dictionary cache. The shared pool is sized by the SHARED_POOL_SIZE initialization parameter. This is another dynamic parameter that can be resized as long as the total SGA size is less than SGA_MAX_SIZE or SGA_TARGET.

Library Cache  :

The library cache holds information about SQL and PL/SQL statements that are run against the database. In the library cache, because it is shared by all users, many different database users can potentially share the same SQL statement. Along with the SQL statement itself, the execution plan and parse tree of the SQL statement are stored in the library cache. The second time an identical SQL statement is run, by the same user or a different user, the execution plan and parse tree are already computed, improving the execution time of the query or DML statement. If the library cache is sized too small, the execution plans and parse trees are flushed out of the cache, requiring frequent reloads of SQL statements into the library cache. 

Data Dictionary Cache :

The data dictionary is a collection of database tables, owned by the SYS and SYSTEM schemas, that contain the metadata about the database, its structures, and the privileges and roles of database users. The data dictionary cache holds a subset of the columns from data dictionary tables after first being read into the buffer cache. Data blocks from tables in the data dictionary are used continually to assist in processing user queries and other DML commands. If the data dictionary cache is too small, requests for information from the data dictionary will cause extra I/O to occur; these I/O-bound data dictionary requests are called recursive calls and should be avoided by sizing the data dictionary cache correctly.

Redo Log Buffer : 

The redo log buffer holds the most recent changes to the data blocks in the data files. When the redo log buffer is one-third full, or every three seconds, Oracle writes redo log records to the redo log files. As of Oracle Database 10g, the LGWR process will write the redo log records to the redo log files when 1MB of redo is stored in the redo log buffer. The entries in the redo log buffer, once written to the redo log files, are critical to database recovery if the instance crashes before the changed data blocks are written from the buffer cache to the datafiles. A user’s committed transaction is not considered complete until the redo log entries have been successfully written to the redo log files.

Large Pool : 

The large pool is an optional area of the SGA. It is used for transactions that interact with more than one database, message buffers for processes performing parallel queries, and RMAN parallel backup and restore operations. As the name implies, the large pool makes available large blocks of memory for operations that need to allocate large blocks of memory at a time. The initialization parameter LARGE_POOL_SIZE controls the size of the large pool and is a dynamic parameter.

Java Pool : 

The Java pool is used by the Oracle JVM (Java Virtual Machine) for all Java code and data within a user session. Storing Java code and data in the Java pool is analogous to SQL and PL/SQL code cached in the shared pool.

Streams Pool : 

New to Oracle 10g, the stream pool is sized by using the initialization parameter STREAMS_POOL_SIZE. The streams pool holds data and control structures to support the Oracle Streams feature of Oracle Enterprise Edition. Oracle Streams manages the sharing of data and events in a distributed environment. If the initialization parameter STREAMS_POOL_SIZE is uninitialized or set to zero, the memory used for Streams operations is allocated from the shared pool and may use up to 10 percent of the shared pool.

FIGURE 1-02 Oracle memory structures

Program Global Area :

The Program Global Area is an area of memory allocated and private for one process. The configuration of the PGA depends on the connection configuration of the Oracle database: either shared server or dedicated. In a shared server configuration, multiple users share a connection to the database, minimizing memory usage on the server, but potentially affecting response time for user requests. In a shared server environment, the SGA holds the session information for a user instead of the PGA. Shared server environments are ideal for a large number of simultaneous connections to the database with infrequent or short-lived requests. In a dedicated server environment, each user process gets its own connection to the database; the PGA contains the session memory for this configuration. 

The PGA also includes a sorting area. The sorting area is used whenever a user request requires a sort, bitmap merge, or hash join operation. from Oracle9i, the PGA_AGGREGATE_TARGET parameter, in conjunction with the WORKAREA_SIZE_POLICY initialization parameter can ease system administration by allowing the DBA to choose a total size for all work areas and let Oracle manage and allocate the memory between all user processes. 

As I mentioned the parameter MEMORY_TARGET manages the PGA and SGA memory as a whole to optimize performance.

Oracle Database Architecture Part1

Software Code Area :

Software code areas store the Oracle executable files that are running as part of an Oracle instance. These code areas are static in nature and only change when a new release of the software is installed. Typically, the Oracle software code areas are located in a privileged memory area separate from other user programs. Oracle software code is strictly read-only and can be installed either shared or non-shared. Installing Oracle software code as sharable saves memory when multiple Oracle instances are running on the same server at the same software release level.