Understand Oracle Database 19c Architecture

Table of Contents

1. Oracle Database Architecture Fundamentals (33m 25s)

• Introduction to Oracle Database Architecture
• Logical structures vs. physical structures
• Understanding Oracle Instance (Part 1)
• Understanding Oracle Instance (Part 2)
• Oracle Storage Hierarchy (Part 1)
• Oracle Storage Hierarchy (Part 2) — Container Database (CDB)
• Oracle Storage Hierarchy (Part 3) — System Files
• Processes and interactions (Part 1)
• Processes and interactions (Part 2) — Mandatory and optional background processes

2. Memory Structures in Oracle Database (39m 59s)

• Overview of memory structures and SGA (Part 1)
• Overview of Memory Structures and SGA (Part 2) — Query Execution Flow
• Role of Program Global Area — PGA (Part 1)
• Role of Program Global Area — PGA (Part 2)
• Shared memory areas for optimization (Part 1) — Shared Pool
• Shared memory areas for optimization (Part 2) — Shared Pool in detail
• Shared memory areas for optimization (Part 3) — Database Buffer Cache
• Shared memory areas for optimization (Part 4) — Large Pool
• Memory allocation methods
• Demo — Memory Monitoring and Troubleshooting

3. Oracle Database Process (40m 7s)

• Introduction to background processes
• Redo Log Writer and Data Recovery
• Recoverer Process (RECO)
• Recovery Writer Process (RVWR)
• Checkpoints and archiving process
• Database Writer Process (DBWn)
• Archiver Process (ARCn)
• Flashback Data Archiver Process (FBDA)
• Listener Processes and dynamic recording
• Process Monitoring and Diagnostics (Part 1)
• Process monitoring and diagnostics (Part 2) — AWR and ADR

4. Logical Storage Structures (19m 11s)

• Introduction to logical storage
• Specialized tablespaces (SYSTEM, SYSAUX)
• UNDO Tablespace
• Logical Storage Management Tools

5. Module 5 — Redo Logs and Control Files (22m 39s)

• Role and structure of Redo Logs
• Importance of Control Files
• Archived Redo Logs vs Online Redo Logs
• Configuration of Redo Logs and multiplexing of Control Files
• Monitoring and Troubleshooting Control Files

1. Oracle Database Architecture Fundamentals

Introduction to Oracle Database Architecture

Oracle Database 19c is a complex relational database whose architecture is documented in an official 40-page Oracle technical document. Understanding this architecture is essential for any professional working with Oracle, whether new or experienced.

Key Components of Oracle Database 19c

The fundamental components of the Oracle Database 19c architecture are:

Physical Storage Structures

• Data files: store the actual database data.
• Control files: contain metadata about the database (physical structure, file location, etc.).
• Redo log files: save transaction history for recovery purposes.

Logical Storage Structures

• Tablespaces: logical groupings of data files.
• Segments: logical units representing objects (tables, indexes, etc.).
• Extents: contiguous blocks of storage within a segment.
• Blocks: Smallest Oracle storage unit.

Memory Structures

• System Global Area (SGA): Shared memory area for caching data and executing SQL queries.
• Program Global Area (PGA): memory allocated by user process for session-specific operations.

Processes

• Client processes: initialize database operations.
• Server processes: process requests from client processes.
• Background processes: manage critical functions such as writing to disk, log management and crash recovery.

Logical structures vs. physical structures

Oracle Database 19c organizes and manages data through two types of structures: logical structures and physical structures.

Logical structures

Logical structures are abstract representations of how data is organized and presented to users. They allow users and applications to interact with data without worrying about its physical location or storage mechanism.

Physical structures

Physical structures represent how data is stored on disk. These structures are managed by the Oracle instance to ensure reliable access and safe storage of data.

Characteristics comparison

The relationship between the two types of structures is as follows: logical structures rely on physical structures. A tablespace, for example, corresponds to one or more data files on disk. This abstraction allows administrators to manage data logically, without worrying directly about physical files.

Understanding Oracle Instance (Part 1)

Setting Oracle Instance

An Oracle Instance combines memory structures and background processes that manage database operations. It acts as an intermediary between users and the physical storage of data in the database.

Oracle Instance Components:

• System Global Area (SGA): Shared memory used for data caching, SQL execution, and other shared resources.
• Program Global Area (PGA): session-specific memory allocated for user processes.
• Background processes: responsible for tasks such as writing data to disk, managing logs and recovering data in the event of a failure.

Database vs. Instance

The database itself is a set of physical files on disk that store the actual data. It includes data files, control files and redo log files. A database can exist independently of an instance, but it cannot be accessed or managed without it.

Client login flow

The process of connecting a client application to Oracle Database is as follows:

1. The client application prepares a connect packet: a request to connect to the database server (reporting tool, web application, script, etc.).
2. Listener receives the request: The listener is like a receptionist of the database server — it constantly waits for incoming connection requests, validates the request and establishes the connection.
3. Connecting to Oracle Instance: Once validation is complete, the listener connects the client application to the database instance.
4. Creation of a server process: inside the instance, a server process is created specifically for the client application. This server process is the personal representative of the database for the client.
5. Data access: the process server interacts with the LMS and physical storage structures to respond to client requests.

Understanding Oracle Instance (Part 2)

Oracle Instance Detailed Components

The Oracle Instance can be thought of as a combination of memory and processes. Its key components are:

System Global Area (SGA)

The LMS functions as a giant workspace where data is cached, queries are optimized, and operations are managed to improve performance. For example, when a query is running, the LMS can temporarily store parts of the data so that repeated queries can be processed more quickly.

Program Global Areas (PGA)

Unlike the SGA which is shared, the PGAs are private memory areas for each process server. Every time a client application connects to the database, it triggers the creation of a server process to handle that specific connection. The PGA is where this server process keeps its working data: variables, session-specific information, and temporary results while SQL statements execute.

Background processes

These are special processes that run automatically to support the database. They function as maintenance workers who keep everything running. For example:

• Some background processes write data from memory to the database.
• Others clean up old data or make sure transactions are properly backed up.
• These processes are essential for maintaining the LMS, managing connections, and ensuring the database operates efficiently.

Server processes

Server processes connect all of these components together. When a client sends SQL queries, the server process processes the requests and interacts with the database itself, which stores the actual data (data files, tables, indexes, etc.).

Oracle Storage Hierarchy (Part 1)

Oracle Database uses a layered hierarchy to manage data storage. Each layer provides an abstraction that simplifies data management while allowing fine-grained control when necessary.

The three levels of the hierarchy

Data Files (niveau physique)
    └── Tablespaces (niveau logique)
            └── Segments
                    └── Extents
                            └── Blocks (plus petite unité)

Data Files

Data files are the physical files on disk that store the actual database data. Each database includes one or more data files. Oracle manages data files automatically, but database administrators (DBAs) can configure them as needed (resizing, etc.).

Tablespaces

Tablespaces are logical storage units that group related files together. They provide a layer of abstraction on top of data files. A tablespace can span multiple data files, and each data file belongs to a single tablespace.

Common tablespace types:

Segments

Segments are the space allocated in one or more extents within a tablespace.

Segment types:

• Table segments: store the data of a table.
• Index segments: store the data of an index.
• Undo segments: Store undo data for active transactions.
• Temporary segments: used for temporary sort operations.

Extents

An extent is a contiguous set of Oracle blocks within a segment. When a segment needs more space, Oracle allocates an extent. By allocating space in extents rather than individual blocks, Oracle ensures that related data remains physically close on disk, which improves performance.

Blocks

Blocks are the smallest unit of storage in Oracle Database. The size of a block can vary, but is typically around 8 KB. A block corresponds to a specific number of bytes in physical storage and is the fundamental read/write granularity.

Oracle Storage Hierarchy (Part 2) — Container Database (CDB)

Architecture of a Container Database (CDB)

Oracle Database 19c organizes its data files in the context of a Container Database (CDB). This architecture includes:

1. CDB Root (CDB$ROOT)

The root container contains a set of data files shared between all databases in the container:

• SYSTEM data file
• SYSAUX data file
• TEMP data file
• UNDO data file
• USERS data file

These files form the foundation for all other databases in the container.

2. Seed Pluggable Database (PDB$SEED)

This is a special Pluggable Database (PDB) preconfigured by Oracle. It works like a template: when you want to create a new database, the PDB seed is copied to form a new PDB. The PDB seed has its own set of data files.

3. Regular Pluggable Databases (PDBs)

These are the actual databases that users interact with. Each PDB is like an independent database inside the container with its own data files:

• SYSTEM and SYSAUX data files (metadata and functionalities)
• TEMP for temporary operations
• USERS for user-created tables and objects
• Optional UNDO data file for PDB-specific rollback operations

Although each PDB is independent, they share all data in the root container, allowing centralized management and reduced redundancy.

4. Application Container

Special container type designed for applications. If multiple PDBs run the same application, an application container can be used to manage objects and data shared between these PDBs, further reducing redundancy.

Oracle Storage Hierarchy (Part 3) — System Files

Essential files for database startup

The files absolutely essential to start Oracle Database are:

Automatic Diagnostic Repository (ADR)

The ADR is Oracle’s central location for storing diagnostic data: error logs, trace files and incidents. If something goes wrong, ADR is the starting point for troubleshooting and identifying the root cause.

Other important files

Processes and interactions (Part 1)

The three categories of Oracle processes

An Oracle Instance relies on three types of processes to interact with users:

1. Client processes

Initiated by user applications or tools to interact with the database. Examples: SQL*Plus, applications using the Oracle Client library. They send requests to the server process and receive the results (query outputs) back. A process customer is like a customer who places an order at a service counter.

2. Server processes

Process customer requests and interact with the database on their behalf. They operate in two modes:

• Dedicated server mode: one process server per process client.
• Shared server mode: several client processes share a pool of server processes.

Server processes parse and execute SQL statements, retrieve or modify database data, and return query results or execution status to the client process. A process server is like a store clerk who fulfills customer orders.

3. Background processes

Critical processes that keep the database running. The main ones are:

Processes and interactions (Part 2) — Mandatory and optional background processes

Mandatory processes

These processes are essential for any Oracle Database to function. They perform critical tasks to keep the database running smoothly and reliably.

Optional processes

These processes are not required for basic operations, but are extremely useful for advanced functionality.

2. Memory Structures in Oracle Database

Overview of Memory Structures and SGA (Part 1)

The System Global Area (SGA)

The SGA is a shared memory area that stores data and control information for an Oracle Instance. It is allocated at instance startup and is used by all server processes and background processes.

LMS Key Responsibilities:

• Caching frequently accessed data and SQL execution plans.
• Reduced disk I/O while maintaining essential data in memory.
• Managing communication between instance processes.

Analogy: The LMS is like the reading room of a library where the most frequently accessed books (data) and reference guides (execution plans) are kept accessible for multiple readers (processes).

LMS Components

Shared Pool in detail

The Shared Pool has two major components:

• Library cache: stores parsed SQL and PL/SQL instructions, as well as execution plans. Allows reuse.
• Data dictionary cache: stores metadata about database objects (table definitions, data types, user privileges). Avoid repeated disk accesses for this information.

Overview of memory structures and SGA (Part 2) — Query execution flow

Traversing a query in the LMS

This is what happens in the LMS when a query is executed (for example, retrieving a list of orders from a customer):

Step 1 — Arrival in the Shared Pool

The request first arrives at the Oracle Instance. The Shared Pool is the first point of interaction:

• Oracle checks the SQL query cache of the shared pool to see if the same query has already been executed recently.
• Cache hit: If a match is found, Oracle reuses the cached execution plan, saving time and resources.
• Cache miss: If the query is not found, Oracle parses the query, generates a new execution plan and stores it in the shared pool for future use.

The shared pool data dictionary cache also validates table structures, column names, and user permissions related to the query.

Step 2 — Database Buffer Cache

• The buffer cache temporarily stores data blocks read from database files on disk.
• If the requested data is already in the cache buffer (following a previous request), it is served directly from memory — very fast operation.
• If the data is not in the buffer cache, Oracle retrieves it from disk and loads it into the cache for the current query and potential future queries.
• This design minimizes disk I/O because subsequent requests for the same data can be served directly from memory.

Step 3 — Complex operations (sort, aggregation)

If the query involves operations like sorts or aggregations, Oracle uses workspaces inside the LMS:

• For simple operations: the database buffer cache or the shared pool are sufficient.
• For complex operations: temporary memory areas in the large pool or the memoptimize pool can be used.

Step 4 — Redo Log Buffer

During query execution, any changes to the data (updates, inserts, deletes) are captured in the redo log buffer. This component temporarily stores these changes before they are written to disk by the Log Writer process.

Role of the Program Global Area — PGA (Part 1)

What is the PGA?

The Program Global Area (PGA) is a private memory region allocated for each server process or background process in Oracle Database. Unlike the SGA which is shared between all sessions, the PGA is dedicated to a single process and cannot be accessed by other processes. This design ensures that session-specific operations are fast, secure, and isolated.

PGA Key Features

1. Management of session-specific data

The PGA stores session-specific data: variables, cursors and work areas for SQL operations. For example, when executing a query, the PGA may contain intermediate results (rows being sorted or aggregated) before the final result is returned.

2. Sort operations

When queries involve sorting data (such as ordering results with ORDER BY), the PGA provides the memory space needed for sorting. If the sort operation exceeds the available PGA memory, Oracle dumps the data to the temporary tablespaces on disk. Having enough PGA memory minimizes disk operations and improves query performance.

3. Hash operations

The PGA plays a key role in managing hash operations, commonly used in joins and aggregations. For example, for a join between two large tables, the PGA provides the memory to construct the hash tables, thereby speeding up the join operation.

4. Session cursors

The PGA maintains session-specific cursors, which point to currently executing SQL statements. This allows efficient execution of repeated queries in the same session.

5. Work areas

These fields in the PGA are used for specific operations such as sorting, joining, and buffering data. They are dynamically allocated and deallocated as needed.

PGA memory management

PGA memory can be managed:

• Manually: via the PGA_AGGREGATE_TARGET parameter.
• Automatically: Oracle dynamically adjusts memory based on workload.

Role of the Program Global Area — PGA (Part 2)

Query execution flow in the PGA

This is how the PGA operates when a query is executed:

1. Receipt of the request

The client sends a query to Oracle Database. The server process receives and handles the request. When the process server is created to handle this request, a dedicated PGA is allocated to it.

2. Structure of the PGA

The PGA contains two main areas: the User Global Area (UGA) and the Private SQL Area.

User Global Area (UGA)

The UGA is responsible for storing session-specific information. Its components:

These three work areas (sort, hash, bitmap merge) dynamically allocate and deallocate memory as needed during execution, thus optimizing performance.

Private SQL Area

The Private SQL Area is where the actual details of query execution are handled. It has two subcomponents:

Shared Memory Areas for Optimization (Part 1) — Shared Pool

Shared Pool Overview

Shared Memory Areas are part of the SGA and are used by all sessions connected to the database. Unlike PGA which is private to each session, Shared Memory Areas can be accessed simultaneously by multiple users and processes. This shared nature allows Oracle to reuse resources, avoid redundant calculations, and serve multiple users efficiently.

The Shared Pool — central command hub

The Shared Pool is like the central control hub of the database. It has two major components:

1. Library Cache

• Stores parsed SQL and PL/SQL statements and execution plans.
• When a query is executed, Oracle checks the library cache to see if the same query has already been processed.
• Soft parse: If a match is found, Oracle reuses the existing execution plan, avoiding parsing and optimization. Significantly reduces overload.
• Example: If multiple users run the same report, Oracle does not need to reprocess the query each time — it simply reuses the cached plan.

2. Data Dictionary Cache

• Stores metadata about database objects: table definitions, column data types, and user privileges.
• Instead of retrieving this information from disk each time, Oracle keeps it in memory for faster query validation and user permission checking.
• For example, if the query references a table called orders, the data dictionary cache provides details like table columns, data types, and constraints.

Shared Memory Areas for Optimization (Part 2) — Shared Pool in Detail

Execution flow in Shared Pool

When a query is received by Oracle Database, the first stop in the memory hierarchy is the Shared Pool. This is where the database decides whether it can reuse existing resources or whether it needs to do additional work.

1. Checking the Library Cache

At the heart of the shared pool is a library cache which contains a shared SQL and PL/SQL area. The database checks the library cache to see if the same SQL or PL/SQL statement has already been executed:

• Soft parse: the instruction is already present → Oracle skips parsing and optimization. Saves time and CPU.
• Hard parse: the instruction is not found → a new entry is created and the query goes through parsing, optimization and execution plan generation.

2. Validation via the Data Dictionary Cache

Once the query is parsed, the database must validate its structure. The data dictionary cache contains metadata about objects (table definitions, column types, user permissions). This speeds up the validation process and ensures that the query follows database rules.

3. Server Result Cache

If the query involves repetitive calculations or operations, Oracle uses the server result cache to store the results of these calculations. It includes:

• SQL query results cache: Stores the results of frequently executed SQL queries.
• PL/SQL function results cache: stores the results of PL/SQL functions, avoiding repetitive calculations.

Shared Memory Areas for Optimization (Part 3) — Database Buffer Cache

The Database Buffer Cache

The database buffer cache is at the heart of Oracle’s performance optimization strategy. It temporarily stores copies of data blocks read from or written to the database.

Operating principle:

1. When a query requests data, Oracle first checks the buffer cache.
2. Cache hit: data is already present → served directly from memory (much faster than disk access).
3. Cache miss: the data is not present → Oracle retrieves it from disk, places it in the cache buffer, then serves it to the query.

Buffer Pools

The database buffer cache is divided into several buffer pools:

LRU List (Least Recently Used)

Once the data blocks are loaded into the buffer cache, they are managed using an LRU list:

• Frequently accessed blocks are moved to the “hot” end of the list, ensuring that they stay in memory longer.
• Less recently used blocks are moved to the “cold” end and can be replaced to make room for new data.

Shared Memory Areas for Optimization (Part 4) — Large Pool

The Large Pool

The Large Pool is an optional memory area of ​​the SGA that provides memory for large allocations. Its main use cases are:

Dedicated Server Configuration

• Each client has its own dedicated process server.
• Ideal configuration for high priority, low concurrency workloads, where each client requires uninterrupted access to a server process.

Shared Server Configuration

• Multiple client applications share server resources, using a dispatcher to route requests to shared server processes.
• More efficient for environments with many simultaneous connections.

Operating mechanism with the Large Pool:

1. Requests arrive in the request queue (orderly management, prevention of bottlenecks).
2. Shared server processes monitor the request queue and take care of pending requests.
3. The Large Pool dynamically allocates memory to handle these requests, ensuring that each server process gets the necessary resources.
4. Once the data is retrieved from the database, it is temporarily stored in the I/O buffer area of ​​the Large Pool before being sent to the client.
5. The dispatcher retrieves the response from the response queue and sends it back to the client application.

Other areas of the Large Pool:

Memory allocation methods

Oracle Database provides two primary methods for managing memory: manual management and automatic management.

1. Manual Memory Management (MMM)

Manual memory management is the traditional approach. It requires that DBAs manually allocate memory to the different components of the SGA and PGA.

Configuration:

DBAs explicitly configure memory parameters:

Each component size is fixed according to the configured parameters. Depending on the setting, adjustments require either dynamic resizing or a database restart.

Advantages:

• Fine control: Precisely allocate memory where it is needed depending on the workload. For example, if the load involves many complex joins, more memory can be allocated to the PGA for sort operations.
• Predictability: By having full control, one can predict the behavior of the database under specific loads.

Disadvantages:

• Time-consuming: Manual memory configuration and tuning can be laborious, especially in environments with dynamic workloads.
• Misallocation Risk: If too much memory is allocated to one area and not enough to another, this can result in suboptimal performance. For example, allocating too much to the SGA can starve the PGA, slowing down session-specific tasks.

2. Automatic Memory Management (AMM)

Automatic memory management greatly simplifies memory administration. Oracle automatically manages memory distribution between the SGA and the PGA.

Automatic management modes:

Advantages of AMM:

• Simplicity: eliminates the need for manual configuration of each memory component.
• Adaptability: Oracle dynamically redistributes memory in response to workload changes. For example, if the workload changes from web server to intensive batch processing, Oracle automatically reallocates memory accordingly.
• Optimization: Oracle uses internal algorithms to ensure that memory is used efficiently.

Disadvantages:

• Less control: The administrator has less control over precise memory allocation.
• Unpredictable behavior: In some workload scenarios, automatic behavior may not be optimal.

Demo — Memory Monitoring and Troubleshooting

This section presents views and SQL queries used to monitor memory in Oracle Database.

Monitor overall SGA

The V$SGA view provides an overview of the total memory allocated to the SGA and its components (shared pool, buffer cache, large pool):

SELECT NAME, VALUE / (1024*1024) AS "MB"
FROM V$SGA;

This output shows the fixed and variable memory sections of the SGA.

Details of SGA memory usage by component

The V$SGASTAT view allows you to break down SGA memory usage by individual component:

SELECT POOL, NAME, BYTES / (1024*1024) AS "MB"
FROM V$SGASTAT
ORDER BY BYTES DESC;

The cache buffer generally appears at the top of the ranking.

Identify memory-intensive queries in the Shared Pool

SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM
FROM V$SQL
ORDER BY SHARABLE_MEM DESC;

This query is used to identify SQL statements consuming excessive memory in the shared pool and to optimize them.

Diagnose Shared Pool Memory Usage

SELECT NAME, BYTES / (1024*1024) AS "MB"
FROM V$SGASTAT
WHERE POOL = 'shared pool'
ORDER BY BYTES DESC;

This query explicitly checks the memory usage of the different components of the shared pool and displays the components with the number of bytes used.

3. Oracle Database Process

Introduction to background processes

Oracle background processes run automatically to manage fundamental database operations. They function as specialized workers, each assigned to a specific task.

Database Writer Process (DBWn)

The Database Writer is responsible for writing dirty data blocks from the database buffer cache in memory to the data files on disk. This process allows data to be written in batches, reducing disk I/O and improving performance.

Disk write triggers:

• When the cache buffer is full.
• During checkpoints.
• When a certain threshold of dirty blocks is reached.

Log Writer Process (LGWR)

The Log Writer writes redo log entries from the redo log buffer to online redo log files on disk. Redo log entries record every change made to the database, ensuring that no committed transactions are lost.

Why LGWR is critical:

• In the event of a database crash, Oracle uses redo logs to recover committed transactions.
• The Log Writer ensures that transaction changes are logged before Oracle considers them successful.

Write triggers:

• Each time a COMMIT or ROLLBACK is sent.
• When the redo log buffer is one-third full.
• Before dirty blocks are written to disk.

System Monitor (SMON)

This process handles instance recovery when restarting a database instance after a crash. It also cleans up temporary segments that are no longer in use and coalesces free space into tablespaces.

Process Monitor (PMON)

The PMON cleans the resources after the failure of a user or server process: release of locks, cleaning of memory. It also notifies the listener so that new connections can be established.

Checkpoint Process (CKPT)

This process signals the Database Writer to synchronize data in memory with data on disk, creating a consistent recovery point. It also updates the control files and data files to indicate that a checkpoint has occurred.

Redo Log Writer and Data Recovery

Importance of the Log Writer Process (LGWR)

The Log Writer Process is one of the most important and critical components of Oracle Database. Understanding how it works is essential because it plays a vital role in data security, recoverability, and consistency, even in the face of unexpected failures.

Write flow during a transaction

This is what happens when running a query that modifies data:

1. Reception by the process server

The process server supports the SQL statement. Instead of writing the change directly to the data file on disk (which would be slow), Oracle writes the change to the buffer cache inside the SGA.

2. Creating a redo entry

In parallel, the server process writes a redo entry into the redo log buffer. A redo entry is a detailed record of the change: what was modified, which data block was affected and how to rebuild the change if necessary. This step ensures that even if the system crashes at this time, Oracle will be able to recover the change later.

3. Intervention of the Log Writer Process (LGWR)

The LGWR continuously monitors the redo log buffer. At key times (during a COMMIT, when the buffer is almost full, or every few seconds), it writes these entries to the redo log files on disk. These redo log files are stored in two important areas:

• Database area: location of regular redo logs.
• Fast Recovery Area: optimized for recovery and backup operations.

4. Commit and sustainability

The transaction is only considered committed once the redo entry has been securely written to the redo log files. This is how Oracle ensures data durability.

Integration with Oracle Data Guard

In advanced configurations using Oracle Data Guard for disaster recovery, an additional step is considered: the redo transport slave process transmits the redo data to the standby databases, thus maintaining their synchronization with the primary database.

Recoverer Process (RECO)

Role of the Recoverer Process

The Recoverer Process is a specialized background process that plays a critical role in maintaining data consistency in distributed databases. It is the database’s personal problem-solver, intervening when distributed transactions encounter unexpected problems.

Usage scenario

Let’s imagine a company with two interconnected Oracle Databases (database A and database B):

1. A user executes a transaction that must be performed on both databases.
2. The network connection between the two bases fails just after base A processes the update, but before base B can finish its game.
3. Result: A partially completed transaction that has not been fully committed or rolled back.

RECO intervention:

1. RECO detects the in-doubt transaction.
2. It connects to the other database (base B) to determine whether the transaction should be committed or rolled back.
3. If the other system confirms that the transaction has been committed → RECO finalizes the commit.
4. Otherwise → it performs the rollback to ensure consistency.
5. When completed, RECO deletes the outstanding transaction record and the system returns to a clean state.
6. All this is done automatically, without intervention from the DBA.

Importance of RECO

The Recoverer Process is crucial for environments where databases interact across networks, for three reasons:

1. Data consistency: ensures that transactions are either fully committed or fully rolled back, preventing data corruption.
2. Automatic Recovery: Automatically resolves issues, reducing manual recovery efforts.
3. Maintainability: Simplifies the management of distributed transactions in multi-database environments.

Recovery Writer Process (RVWR)

Role of the Recovery Writer Process

The Recovery Writer Process (RVWR) plays a vital role in Oracle Flashback Technology. This technology is one of Oracle’s most powerful features — it allows you to rewind the database to an earlier date without requiring restoration from backups.

Flashback features enabled by RVWR

Without the Recovery Writer Process, none of these features would be possible.

RVWR Operation Flow

Example: a user accidentally executes a DELETE command on all of a department’s data.

Step 1 — Capture in Flashback Buffer

Before the DELETE command physically deletes the data, Oracle writes a before image of the affected data blocks to the SGA flashback buffer. The flashback buffer temporarily stores recent data changes in memory — meaning that the original data still exists in memory, even after deletion.

Step 2 — Read by RVWR

The RVWR continuously reads these before images from the flashback buffer. The timing of these reads depends on several factors such as transaction frequency and memory size.

Step 3 — Writing to the Flashback Log

The RVWR writes the before images to the flashback log stored in the fast recovery area. These logs allow Oracle to return to a previous state by having historical records of changes.

Checkpoints and archiving process

The Checkpoint Process

The Checkpoint Process forces all modified data in memory to be written to physical data files on disk. This ensures that the database is in a consistent state and can recover more efficiently after a crash or unexpected shutdown.

Checkpoint Triggers

A checkpoint can be triggered by:

• Redo log files become full (causing a log switch).
• The database is shutting down.
• A manual checkpoint is issued.

Checkpoint Flow

1. The process server manages the request

The data change is first recorded in the database buffer cache (in-memory) for performance reasons. This change is not immediately written to disk.

2. Triggering the checkpoint

Over time, Oracle triggers a checkpoint.

3. The Checkpoint Process instructs the Database Writer

The Checkpoint Process does not write data directly to disk. It instructs the Database Writer Process (DBWn) to handle this operation.

4. The Database Writer writes to disk

The DBWn reads dirty data blocks from the database buffer cache and writes them to the appropriate data files in the database area:

• SYSTEM (database objects)
• SYSAUX (auxiliary system data)
• User data
• Temporary data for sorts and large operations
• Information for rolling back uncommitted changes

5. Updating Control Files

While the DBWn is writing data, the Checkpoint Process is also updating the control files to record the checkpoint information. This metadata is crucial for Oracle to know the latest consistent state of the database.

6. Fast Recovery Area

Oracle can also write to the fast recovery area for backup and recovery operations.

Database Writer Process (DBWn)

Role of the Database Writer Process

The Database Writer Process (DBWn) is responsible for moving changed data from memory to disk. Instead of writing each small change directly to disk, Oracle optimizes performance by first holding changes in memory and then writing them to disk in bulk.

Multiple Database Writer Processes

Oracle may have multiple database writer processes depending on the workload. These processes (named DBW0, DBW1, DBW2, etc.) work in parallel to efficiently handle large volumes of data.

DBWn operation flow

Step 1 — User request and in-memory modification

User executes SQL commands (INSERT, UPDATE, DELETE). The server process handles the request. The change is recorded in the database buffer cache, a memory structure of the SGA. The updated data is now in memory but not yet written to disk.

Step 2 — Reading the Database Buffer Cache

The DBWn regularly reads data from the cache. It focuses on:

• Dirty buffers: data blocks modified but not yet written to disk.
• Least recently used buffers: Data that might need to be cleaned to make room for new data.

Step 3 — Write to disk

Once the DBWn identifies modified dirty data, it writes this data to the physical data files on disk. By writing in bulk, DBWn reduces disk I/O and improves performance.

Step 4 — Database Smart Flash Cache (optional)

In some high-performance environments, Oracle leverages the database smart flash cache. The DBWn can also write data to flash storage (SSD), which is faster than traditional disks.

DBWn write triggers

The DBWn does not write data immediately after each change. It writes data only:

• When the cache buffer is full.
• When new transactions require space.
• During checkpoints.

Archiver Process (ARCn)

Role of the Process Archiver

The Archiver Process is a critical process that ensures data recoverability. It is responsible for copying the filled redo log files to a secure storage location known as the fast recovery area, or to another specified archiving destination.

This process is essential when the database is operating in archive log mode, ensuring that any changes made to the database can be recovered in the event of a failure.

Oracle may have multiple archiver processes (ARCn). These processes work in parallel to efficiently manage large volumes of redo data.

ARCn operation flow

Step 1 — Transaction and redo log

The user is executing a query. The change is first recorded in the SGA redo log buffer. The Log Writer Process writes this redo data to the redo log files in the database area.

Step 2 — Log switch

Redo log files have a finite size. When a redo log file becomes full, the next file is used. When all logs are full, Oracle performs a log switch — which triggers ARCn.

Step 3 — Archiving

The ARCn is notified that a redo log file has been completed. It then copies the filled redo file to the archived redo files.

Step 4 — Release for reuse

Once the redo log is archived, it becomes available for reuse. This ensures continuous recording of database changes without interruption.

Why ARCn is critical

• Full database recovery: If a disk failure or corruption occurs, the database can be restored using the latest backups of the data files. Archived redo logs allow point-in-time recovery, allowing the database to be restored to a specific point in time before an error.
• Continuous Operations: Without archiving, redo logs would fill and the database would shut down until space is freed.
• Oracle Data Guard: archived redo logs are sent to standby databases to keep them replicated and synchronized with the primary database.

Archiving Monitoring and Management Commands

-- Vérifier le mode d'archivage
ARCHIVE LOG LIST;

-- Voir les archived logs
SELECT DEST_ID, STATUS, ARCHIVED, DELETED
FROM V$ARCHIVED_LOG;

-- Voir les destinations d'archivage
SELECT DEST_ID, STATUS, DESTINATION
FROM V$ARCHIVE_DEST;

Flashback Data Archiver Process (FBDA)

Role of the Flashback Data Archiver Process

The Flashback Data Archiver Process (FBDA) is responsible for capturing and storing historical changes to data. Its main role is to archive row versions of tables configured for flashback operations, thus allowing:

• To audit changes over time.
• To retrieve data from a specific point in the past.
• Track historical data without custom triggers or audit logic.

FBDA operation flow

Example: a user executes an UPDATE query.

Step 1 — Capture in Database Buffer Cache

The change is initially captured in the database buffer cache in the undo blocks. The original unmodified data before the change is stored in the undo segments of the data files.

Step 2 — FBDA Processing

Before the transaction is committed, the FBDA processes and reads data from the undo blocks in the database buffer cache. This allows the process to capture the original version of the data before it is permanently changed. The process ensures that each historical version of a row is securely saved for future retrieval.

Step 3 — Writing to the Flashback Data Archive

Once the FBDA has captured the original data, it writes this data to a dedicated flashback data archive residing in a specific tablespace. This flashback data archive stores all historical versions of data, allowing you to query or restore data as it existed at any time in the past.

Use with Flashback Queries

-- Interroger les données telles qu'elles existaient à un moment précis
SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

Advantages of FBDA

• Compliance and audit: maintains historical versions of data without impacting application performance.
• Automation: Automates the management of historical data and eliminates the need for complex custom auditing mechanisms.
• Read Before COMMIT: Reads data before executing a COMMIT, thereby capturing the actual state of the data before modification.

Listener Processes and dynamic recording

The Listener Registration Process (LREG)

The Listener Registration Process is responsible for registering the Oracle Database Instance with the listener. The listener is a separate server-side process that handles incoming connection requests from client applications.

Dynamic recording flow

1. Starting the database

When Oracle Database starts, the LREG initializes automatically. This process dynamically notifies the listener of the status of the database instance, including the database name and the instances available for connection.

2. Processing a customer connection request

Example: A user attempts to connect to the database using SQL*Plus. The listener already knows the database instance because the LREG has already registered it. The listener forwards the connection request to the Oracle Instance. Without this record, the listener would not know where to route the connection and the client request would fail.

Dynamic Service Registration

One of the most powerful features of LREG is dynamic service registration. This means that Oracle services are automatically registered with the listener when the database starts. It is therefore not necessary to manually configure static entries in the listener.ora file. If new services or instances are added while running, the LREG will notify the listener in real time.

Why LREG is critical

Process monitoring and diagnostics (Part 1)

The Process Monitor (PMON)

The Process Monitor is one of the critical background processes. If a user session or server process crashes during a request, the PMON quickly detects the failure:

• Cleans the resources (memory, logs) held by the faulty process.
• Release locks on data to avoid blocking other users.
• Notify the listener so that new connections can be established.

By doing this automatically, PMON ensures that the database remains stable and responsive, even when failures occur in the middle of query execution.

The Process Manager

When a query is executed, the Process Manager plays a key role in managing the different background processes and server processes:

The System Monitor Process (SMON)

When executing a query, SMON works quietly in the background. It recovers interrupted transactions left by failing sessions, cleaning up any incomplete activity that could have affected data integrity. It also performs periodic maintenance tasks to optimize storage and resource usage.

Process monitoring and diagnostics (Part 2) — AWR and ADR

Active Session History (ASH) and AWR

Oracle captures session activity every second. This data is stored in the active session history buffer, located in the SGA shared pool.

Performance data collection flow:

1. Capture in the Active Session History Buffer

The active session history buffer temporarily contains session activity details: wait events and resource usage. The Manageability Monitor Lite Process (MMNL) continually monitors and fills the active session history buffer with session data.

2. Flush to the Automatic Workload Repository (AWR)

When the active session history buffer is full, the MMNL flushes this data into the Automatic Workload Repository (AWR), specifically into the WRH$_ACTIVE_SESSION_HISTORY table located in the SYSAUX tablespace. This ensures that session performance data is preserved for historical analysis.

3. MMON Snapshots

The Manageability Monitor Process (MMON) collects and filters system performance statistics from the active session history buffer and other sources. Every 60 minutes, the MMON creates snapshots of system performance and stores this information in the AWR. These snapshots help DBAs analyze performance trends and troubleshoot issues over time.

4. Using historical data

Historical data captured by these two processes helps:

• Identify slow queries.
• Spot bottlenecks.
• Detect inefficient resource usage.

Automatic Diagnostic Repository (ADR)

The ADR is a centralized storage location for all diagnostic data, helping DBAs troubleshoot and resolve problems efficiently.

ADR content:

4. Logical Storage Structures

Introduction to Logical Storage

Logical storage hierarchy

Oracle Database is designed to handle large volumes of data efficiently. To do this, it divides its storage into logical units that are easier to manage and optimize.

Tablespace (niveau le plus haut)
    └── Segment (objets de la base de données)
            └── Extent (ensembles de blocks alloués)
                    └── Block (plus petite unité, ~8 KB)

Tablespaces

At the top of the hierarchy are tablespaces. A tablespace is a logical container for all data. Each tablespace is associated with one or more physical data files on disk. Tablespaces allow you to group related data, apply storage policies, and control disk space allocation for different types of data.

Tablespaces predefined by Oracle:

Segments

Inside a tablespace are segments, which represent the actual objects stored in the database:

• Segments for tables.
• Segments for indexes.
• Segments for undo data.
• Temporary segments.

Extents

Each segment type has a specific role. When a segment requires more space, Oracle allocates an extent. By allocating space in extents rather than individual blocks, Oracle ensures that related data remains physically close on disk, which improves performance. For example, when inserting rows into a table, the database automatically adds another extent from the tablespace.

Blocks

At the lowest level of the hierarchy are the blocks. These are the smallest storage unit in Oracle Database. The size of a block is typically around 8 KB, although it can be configured differently.

Specialized tablespaces (SYSTEM, SYSAUX)

The SYSTEM tablespace

The SYSTEM tablespace is automatically created when configuring a new database. It’s literally the brains of Oracle Database. Its main function is to host the data dictionary — a collection of metadata that the database uses to define and manage all objects in the system.

What the data dictionary contains:

• Tables, indexes, users.
• Privileges and structure of the tablespaces themselves.

Role in daily operations:

• When creating a new table: Oracle updates the data dictionary to save details (table name, columns, constraints).
• When a user executes a query: Oracle consults the data dictionary to validate that the table exists, check user permissions, and optimize the execution plan.

The SYSTEM tablespace also contains critical system objects such as PL/SQL packages and procedures used internally by Oracle. This is why Oracle strongly recommends keeping the SYSTEM tablespace dedicated to its default purpose and avoiding storing user data or application objects there. This could risk database stability and cause performance issues.

The SYSAUX tablespace

Introduced in Oracle 10g, the SYSAUX tablespace is the supporting pillar of the SYSTEM tablespace. Its role is to lighten the load on SYSTEM, allowing the SYSTEM tablespace to remain streamlined and focused on core operations.

Oracle components hosted in SYSAUX:

Monitoring SYSAUX components:

-- Identifier quels composants occupent le plus d'espace dans SYSAUX
SELECT OCCUPANT_NAME, SCHEMA_NAME,
       SPACE_USAGE_KBYTES / 1024 AS "Space (MB)"
FROM V$SYSAUX_OCCUPANTS
ORDER BY SPACE_USAGE_KBYTES DESC;

UNDO Tablespace

What is the UNDO tablespace?

The UNDO tablespace is a special type of tablespace in Oracle Database that stores undo data — snapshots of the data before any changes are committed. This data is generated automatically every time a transaction modifies data.

Undo Data Functions

Undo Data storage mechanism

When a transaction modifies data (for example, updating a row in a table):

1. Writing the new value: the database writes the new value into the database buffer cache to possibly be saved in the data file.
2. Write old value: The database also writes the old value (before image) to the UNDO tablespace.

Undo data is stored in undo segments inside the UNDO tablespace. Each undo segment is essentially a container that tracks changes made by active transactions.

When the transaction is committed, undo data is no longer necessary for rollback. However, they may still be retained for read consistency or flashback operations until the undo retention period expires.

ACID and UNDO properties

Undo data is central to Oracle’s implementation of ACID properties:

Temporary UNDO

Oracle supports a feature called Temporary UNDO, which stores undo data for temporary tables in the temporary tablespace rather than the UNDO tablespace. This optimizes performance for scenarios involving heavy transactions on temporary tables.

Logical Storage Management Tools

This section presents useful SQL queries for managing logical storage in Oracle Database.

Monitor tablespace usage

-- Vue d'ensemble de chaque tablespace : taille totale, espace utilisé et libre
SELECT df.tablespace_name,
       ROUND(SUM(df.bytes) / (1024*1024), 2) AS "Total (MB)",
       ROUND(SUM(df.bytes) / (1024*1024) - NVL(SUM(fs.bytes) / (1024*1024), 0), 2) AS "Used (MB)",
       ROUND(NVL(SUM(fs.bytes), 0) / (1024*1024), 2) AS "Free (MB)"
FROM DBA_DATA_FILES df
LEFT JOIN DBA_FREE_SPACE fs ON df.tablespace_name = fs.tablespace_name
GROUP BY df.tablespace_name
ORDER BY "Used (MB)" DESC;

Track segments in a tablespace

-- Identifier les segments dans un tablespace spécifique
SELECT segment_name, segment_type,
       ROUND(bytes / (1024*1024), 2) AS "Size (MB)"
FROM DBA_SEGMENTS
WHERE tablespace_name = 'USERS'
ORDER BY bytes DESC;

This query is ideal for optimizing the tablespace layout.

Identify free blocks

-- Repérer la fragmentation ou les blocks inutilisés pouvant être récupérés
SELECT tablespace_name,
       COUNT(*) AS "Free Extents",
       ROUND(SUM(bytes) / (1024*1024), 2) AS "Free (MB)"
FROM DBA_FREE_SPACE
GROUP BY tablespace_name;

Monitor UNDO tablespace activity

-- Surveiller l'activité UNDO (undo blocks utilisés, nombre de transactions)
SELECT begin_time, end_time,
       undoblks AS "Undo Blocks",
       txncount AS "TXN Count"
FROM V$UNDOSTAT
ORDER BY begin_time DESC;

undoblks: space used for undo operations; txncount: total number of transactions during the interval. Useful for spotting trends or peaks in activity.

Quick summary of the UNDO tablespace

-- Taille, taille maximale et auto extension du tablespace UNDO
SELECT tablespace_name,
       ROUND(SUM(bytes) / (1024*1024), 2) AS "Size (MB)",
       autoextensible
FROM DBA_DATA_FILES
WHERE tablespace_name = 'UNDOTBS1'
GROUP BY tablespace_name, autoextensible;

Tip: it is always recommended to enable auto extension (AUTOEXTEND ON) for UNDO tablespaces.

Historical view of UNDO activity

-- Vue historique de l'activité UNDO
SELECT begin_time, end_time,
       maxquerylen AS "Longest Query (sec)",
       undoblks AS "Undo Blocks Used"
FROM V$UNDOSTAT
ORDER BY begin_time;

Performance of undo in instances

-- Vue haut niveau de l'utilisation de l'espace UNDO dans les instances
SELECT inst_id, ssolderrcnt, nospaceerrcnt
FROM GV$UNDOSTAT
ORDER BY inst_id;

Status of undo segments

-- Statut des undo segments (actifs, inactifs, expirés)
SELECT status, COUNT(*) AS "Count",
       ROUND(SUM(blocks) * 8192 / (1024*1024), 2) AS "Space (MB)"
FROM DBA_UNDO_EXTENTS
GROUP BY status;

5. Redo Logs and Control Files

Role and structure of Redo Logs

What is a Redo Log?

At its core, a redo log is a set of files in Oracle Database that record every change made to the database. It’s like a continuous log of all operations that modify data — whether it’s an INSERT, an UPDATE or a DELETE.

Crucial Point: Redo logs only capture changes to the data, not the data itself.

Example of what is recorded

For a table with two rows and two UPDATE transactions:

-- Transactions exécutées
UPDATE orders SET status = 'SHIPPED' WHERE order_id = 1001;
UPDATE orders SET status = 'DELIVERED' WHERE order_id = 1002;

The redo logs will record:

1. The state before order status update (before image).
2. The state after the update (after image).

Oracle thus allows these changes to be replayed or reversed if necessary.

The three critical roles of Redo Logs

Operation of Redo Logs during normal operations

Write-ahead logging:

Before a transaction is committed, Oracle writes the change to the redo log buffer in memory. This is called write-ahead logging — it ensures that changes are recorded even before they are written to the database files.

The Log Writer (LGWR):

The Log Writer is the process responsible for flushing the redo log buffer to the redo log files on disk:

• When a transaction is committed.
• When the redo buffer reaches a certain threshold.

Structure of Redo Logs

Redo logs consist of groups. Oracle requires a minimum of two redo log groups to operate. Each group can have multiple members — copies of the same log on different disks for redundancy. Groups are used in a circular manner: when a group is full, Oracle moves to the next group via a log switch. Once all groups are full, Oracle recycles from the beginning, overwriting old redo logs (provided they have been archived if in archive log mode).

Importance of Control Files

What is a Control File?

We can think of control files as the blueprint or the roadmap of the database. They tell Oracle:

• Which data files and redo log files belong to the database.
• The current state of the database.
• Whether the database is in archive mode or not.
• And much more.

Control files contain all the critical information Oracle needs to open, operate, and recover the database.

Why Control Files are essential

Location of physical files:

Control files store the names and locations of all physical database files:

• Data files: contain the real data of the tables and indexes.
• Redo log files: critical for data recovery.
• Temp files: used for temporary operations like sorting.

Without control file, Oracle would not know which files belong to the database or where to find them.

Transaction integrity:

Control files maintain the current log sequence number and checkpoint information.

Database recovery support:

In the event of a failure, the control files are critical to restore the database. They tell Oracle which redo logs are needed for media recovery and provide the recovery sequence.

Contents of Control Files

This metadata is constantly updated as the database runs.

Multiplexing of Control Files

Given their critical importance, Oracle strongly recommends multiplexing control files — that is, maintaining multiple copies on different disks. If one disk fails, Oracle can continue operating using the other copies.

Archived Redo Logs vs Online Redo Logs

General context

The two types of redo logs play distinct and critical roles in ensuring data recoverability in the event of failures — whether a crash, system failure, or full database recovery. Whenever data is modified, Oracle writes the changes to redo logs before committing them to the actual data files.

Online Redo Logs

Online redo logs record changes in real time as transactions are processed.

Features:

Archived Redo Logs

Archived redo logs are saved copies of online redo logs once they have been populated and are about to be overwritten.

Features and use cases:

Comparative summary

Configuration of Redo Logs and multiplexing of Control Files

Multiplexing of Redo Logs

Multiplexing involves creating multiple copies of critical files (redo logs, control files) and storing these copies on separate physical devices. If one copy is lost or corrupted, the database can continue to function based on the other copies.

Steps to multiplex Redo Logs:

1. Identify current redo log groups

SELECT GROUP#, MEMBERS, STATUS
FROM V$LOG;

SELECT GROUP#, MEMBER
FROM V$LOGFILE;

2. Add a new member to an existing redo log group

ALTER DATABASE ADD LOGFILE MEMBER
'/u02/oradata/orcl/redo01b.log' TO GROUP 1;

ALTER DATABASE ADD LOGFILE MEMBER
'/u02/oradata/orcl/redo02b.log' TO GROUP 2;

ALTER DATABASE ADD LOGFILE MEMBER
'/u02/oradata/orcl/redo03b.log' TO GROUP 3;

Oracle then writes the same redo data to all members of the group. If one file fails, the others remain intact.

3. Check changes

SELECT GROUP#, MEMBER, STATUS
FROM V$LOGFILE
ORDER BY GROUP#;

Multiplexing of Control Files

Control files are even more critical because they store metadata about the database. It is strongly recommended to maintain multiple copies on different disks.

Steps to multiplex Control Files:

1. Check current locations of control files

SELECT NAME FROM V$CONTROLFILE;
-- Ou :
SHOW PARAMETER CONTROL_FILES;

2. Shut down the database properly

SHUTDOWN IMMEDIATE;

3. Copy the existing control file to the new location

# Linux/Unix
cp /u01/oradata/orcl/control01.ctl /u02/oradata/orcl/control02.ctl

4. Update the CONTROL_FILES parameter in the init file

-- Dans le fichier init.ora ou spfile
CONTROL_FILES = ('/u01/oradata/orcl/control01.ctl',
                  '/u02/oradata/orcl/control02.ctl')

5. Start database

STARTUP;

6. Check that the database uses both control files

SELECT NAME FROM V$CONTROLFILE;

By following these steps, multiple copies of the control files exist on different disks. If one disk fails, the database can continue to operate using the other control files.

Handling a missing or corrupt member redo log

-- Supprimer un membre défaillant
ALTER DATABASE DROP LOGFILE MEMBER '/u01/oradata/orcl/redo01b.log';

-- Ré-ajouter le membre
ALTER DATABASE ADD LOGFILE MEMBER
'/u01/oradata/orcl/redo01b.log' TO GROUP 1;

Monitoring and Troubleshooting Control Files

Analyze allocated space in Control Files

The following query reveals how space in the control files is allocated and used in different sections. It identifies:

• The record type (type of information stored in each section).
• The total number of allocated slots (RECORDS_TOTAL).
• The usage percentage (space used / total).

SELECT TYPE, RECORD_SIZE, RECORDS_TOTAL, RECORDS_USED,
       ROUND(RECORDS_USED / RECORDS_TOTAL * 100, 2) AS "Utilization %"
FROM V$CONTROLFILE_RECORD_SECTION
ORDER BY "Utilization %" DESC;

Interpretation of results

Here is an analysis of typical sections of a control file:

Other monitoring requests

-- Surveiller les redo log groups et leur statut
SELECT GROUP#, SEQUENCE#, BYTES/1024/1024 AS "Size (MB)",
       MEMBERS, STATUS
FROM V$LOG;

-- Taille des control files
SELECT BLOCK_SIZE * FILE_SIZE_BLKS / (1024*1024) AS "Control File Size (MB)"
FROM V$CONTROLFILE;

-- Informations sur les archive logs récents
SELECT SEQUENCE#, BLOCKS * BLOCK_SIZE / (1024*1024) AS "Size (MB)",
       COMPLETION_TIME
FROM V$ARCHIVED_LOG
WHERE STANDBY_DEST = 'NO'
ORDER BY SEQUENCE# DESC;

Training: Understand Oracle Database 19c Architecture — Content based on official Oracle 19c documentation.

Search Terms

oracle · database · 19c · architecture · databases · sql · process · memory · redo · flow · undo · control · logs · pool · shared · storage · structures · processes · writer · role · tablespace · data · logical · monitor