Oracle Background Processes

In an earlier lesson we presented a diagram identifying various components of the Oracle instance. Included in the diagram were various processes

1. DBWR,
2. SMON,
3. LGWR,
4. PMON,
5. CKPT, and
6. ARCH

that reside inside the instance. There are also two groups of processes that reside outside the instance, and that are used to communicate from the user to the instance.

1. First group of Processes: The first group of processes is a set of user or client processes, that request information from the server processes.
2. Second group of Processes:
   The second group is a set of processes that communicate user requests to the instance. A good analogy is Applebee's restaurant. Customers (client applications) come into the restaurant for lunch (create user processes). A server process takes the order (client data requests) and pass it on to the order taker (the instance). The cook would prepare the food (request data from the database) and let the servers know it is ready. The then takes the food (data) and presents it to the customer (client application). We will investigate the four processes that reside as part of the instance:
   
   1. LGWR,
   2. ARCH,
   3. DBWR and,
   4. CKPT.
   
   Of these four processes ARCH is optional, while the other three are created during the creation of an instance. We will discuss these four processes in detail in the next four lessons. There is a V$ view that provides some information about the background and server processes, V$PROCESS. View the image below to see an example of the output from this view.

The Oracle script and its corresponding output can be analyzed as follows:
Oracle Script:

1. Describe Command:
   

   desc v$process;
   

   • This command provides the structure of the v$process dynamic performance view, listing columns, their nullability, and data types.
2. Select Query:
   

   select pid, program from v$process;
   

   • This query retrieves the pid (process ID) and program (program name) columns from the v$process view.

Output:
#Result of desc v$process:

| Column Name  | Null? | Type          |
|--------------|-------|---------------|
| ADDR         |       | RAW(4)        |
| PID          |       | NUMBER        |
| SPID         |       | VARCHAR2(9)   |
| USERNAME     |       | VARCHAR2(15)  |
| SERIAL#      |       | NUMBER        |
| TERMINAL     |       | VARCHAR2(30)  |
| PROGRAM      |       | VARCHAR2(64)  |
| BACKGROUND   |       | VARCHAR2(1)   |
| LATCHSPIN    |       | VARCHAR2(8)   |
| LATCHWAIT    |       | VARCHAR2(8)   |

#Result of select pid, program from v$process:

| PID | PROGRAM                 |
|-----|-------------------------|
| 1   | PSEUDO                 |
| 2   | ORACLE80.EXE           |
| 3   | ORACLE80.EXE           |
| 4   | ORACLE80.EXE           |
| 5   | ORACLE80.EXE           |
| 6   | ORACLE80.EXE           |
| 7   | ORACLE80.EXE           |
| 8   | c:\oracle\bin\oracle80.exe |

This output reflects the active processes associated with an Oracle instance, where `PID` represents the process ID and `PROGRAM` indicates the associated executable or pseudo process.

1. Would the result of v$process be different in Oracle 19c?

• No significant difference: The v$process view exists in Oracle 19c and includes similar columns as in Oracle 8. However, additional columns may be present in Oracle 19c, such as CON_ID, which reflects multitenant container databases (CDBs) and pluggable databases (PDBs).
• Compatibility: The specific PID and PROGRAM values in the v$process output will depend on the instance configuration, processes, and program executions specific to the Oracle 19c environment.

Regenerating Legacy Output in Oracle 11g R2 or 19c:
1. Result of desc v$process:
To match the legacy output:
- Query the `v$process` view's metadata using the following SQL in Oracle 11g R2 or 19c:

SELECT column_name, nullable, data_type
FROM all_tab_columns
WHERE table_name = 'V$PROCESS'
ORDER BY column_id;

- Expected result (filtered for legacy fields):

| Column Name  | Null? | Type          |
|--------------|-------|---------------|
| ADDR         |       | RAW(4)        |
| PID          |       | NUMBER        |
| SPID         |       | VARCHAR2(9)   |
| USERNAME     |       | VARCHAR2(15)  |
| SERIAL#      |       | NUMBER        |
| TERMINAL     |       | VARCHAR2(30)  |
| PROGRAM      |       | VARCHAR2(64)  |
| BACKGROUND   |       | VARCHAR2(1)   |
| LATCHSPIN    |       | VARCHAR2(8)   |
| LATCHWAIT    |       | VARCHAR2(8)   |

Note: Oracle 19c may include additional columns like `CON_ID`. These should be excluded to replicate the legacy output.

---

2. Result of `select pid, program from v$process`: To replicate the legacy output:
- Use the following SQL query in Oracle 11g R2 or 19c:

SELECT pid, program
FROM v$process
WHERE rownum <= 8; -- Limit rows to replicate legacy output

- Example Result (Simulated Output):

| PID | PROGRAM                 |
|-----|-------------------------|
| 1   | PSEUDO                  |
| 2   | ORACLE.EXE              |
| 3   | ORACLE.EXE              |
| 4   | ORACLE.EXE              |
| 5   | ORACLE.EXE              |
| 6   | ORACLE.EXE              |
| 7   | ORACLE.EXE              |
| 8   | /u01/app/oracle/bin/oracle |

Key Considerations:

• File Paths: In Oracle 19c, the PROGRAM field may include Unix-style paths or container database details instead of the Windows-specific legacy path (e.g., c:\oracle\bin\oracle80.exe).
• Row Limitation: Use rownum to limit the result to 8 rows, as in the legacy output.
• Column Inclusion: Ensure no additional columns, such as CON_ID, appear in the result set for compatibility with the legacy layout.

This approach ensures accurate emulation of the Oracle 8 legacy output in Oracle 11g R2 or 19c.

The ARCH process, short for Archiver Process, plays a crucial role in Oracle's recovery and backup strategy within an Oracle instance. Its primary purpose is to manage the archiving of online redo log files to ensure data recoverability and continuity. Here's a detailed explanation of its functions and context:
Primary Purpose of the ARCH Process

1. Archive Online Redo Logs:
   • When an online redo log file is filled, the ARCH process copies it to a designated archive location, either on disk or to an external storage system.
   • This action is critical for enabling point-in-time recovery and ensuring that no committed transactions are lost.
2. Enable Media Recovery:
   • Archived redo logs are essential for recovering a database to a specific point in time after a failure, such as disk corruption or accidental deletion.
   • This supports roll-forward operations during recovery, where changes recorded in the redo logs are reapplied to restore the database to a consistent state.
3. Support for Standby Databases:
   • The ARCH process transmits archived redo logs to standby databases in Data Guard environments, ensuring that the standby database remains synchronized with the primary database.

When Is the ARCH Process Enabled?

• The ARCH process is enabled when the database is in ARCHIVELOG mode. This mode is a prerequisite for advanced recovery features and is typically used in production environments.
• In NOARCHIVELOG mode, redo logs are overwritten when full, and the ARCH process is not active.

Operational Workflow of the ARCH Process

1. The database generates changes in redo log files.
2. When an online redo log file becomes full, a log switch occurs, and the LGWR process starts writing to the next redo log file.
3. The ARCH process copies the full redo log file to the archive destination(s) specified by the LOG_ARCHIVE_DEST parameter(s).
4. Multiple ARCH processes can be configured to handle high workloads or complex configurations (e.g., multiple archive destinations).

Benefits of the ARCH Process

• Data Protection: Provides a reliable mechanism to safeguard all database changes for recovery.
• Disaster Recovery: Enables recovery from physical or logical failures using archived redo logs.
• Replication: Facilitates data replication in standby databases for high availability and disaster recovery solutions.
• Compliance: Meets regulatory requirements for preserving historical database changes.

Key Parameters Related to the ARCH Process

1. LOG_ARCHIVE_DEST: Specifies the location(s) for storing archived redo logs.
2. LOG_ARCHIVE_FORMAT: Defines the naming convention for archived redo logs.
3. LOG_ARCHIVE_MAX_PROCESSES: Determines the number of Archiver processes that can run concurrently.
4. ARCHIVE_LAG_TARGET: Sets a target time for how often redo logs are archived.

Summary The "ARCH process" ensures that the database's recovery capabilities are robust by copying redo logs for safe storage, enabling recovery and high availability solutions like Oracle Data Guard. It is an integral part of Oracle's strategy for safeguarding transaction data and supporting enterprise-grade database environments.
The next lesson explores database writer and archive functions.

Before you move on to the next lesson, click the Quiz link below to answer some questions about instances.
Oracle Memory Structures - Quiz