Oracle Database Architecture

The architecture of an Oracle Database is based on both logical and physical components, working together to store, retrieve, and manage data efficiently. The architecture can be broken down into several major components: Memory structures, Processes, and Storage structures. Each component plays a specific role in handling different database operations.

Here’s an overview of the main components of Oracle’s architecture:

1. Oracle Instance:

An Oracle instance consists of the memory structures and background processes that manage database operations. It is responsible for accessing and interacting with the data stored in the database.

Components of an Oracle Instance:

• Memory Structures:

  1. System Global Area (SGA):

     • The SGA is a shared memory region that stores data and control information for the Oracle instance. All users connected to the database share access to the SGA.

     • Key components of SGA:

       • Database Buffer Cache: Caches data blocks read from data files, improving performance by reducing disk I/O.

       • Redo Log Buffer: Stores redo entries, which log changes made to the database, for recovery purposes.

       • Shared Pool: Caches SQL statements, PL/SQL code, and data dictionary information.

       • Large Pool: Optional memory pool for large memory allocations, such as RMAN backup buffers.

       • Java Pool: Memory allocated for Java code execution (if Java is used in the database).

       • Streams Pool: Used for Oracle Streams (replication and data sharing).

  2. Program Global Area (PGA):

     • The PGA is a non-shared memory region used by Oracle server processes. It stores session-specific information, such as sorting operations, session variables, and query results.

     • PGA components:

       • Private SQL Area: Contains information about SQL statements for each session.

       • Sort Area: Memory used for sorting operations performed by SQL queries.

• Background Processes: Oracle uses several background processes to manage and support database functions. The most important ones include:

  1. Database Writer (DBWn):

     • Writes modified (dirty) data blocks from the database buffer cache to the data files.

  2. Log Writer (LGWR):

     • Writes redo log buffer entries to the redo log files, ensuring that changes are permanently recorded.

  3. Checkpoint (CKPT):

     • Signals when all modified data blocks have been written to disk during a checkpoint operation, ensuring data consistency.

  4. System Monitor (SMON):

     • Performs instance recovery when the database is restarted after a crash.

  5. Process Monitor (PMON):

     • Cleans up failed user processes by releasing locks and rolling back transactions.

  6. Archiver (ARCn):

     • Archives redo log files in ARCHIVELOG mode for recovery and backup purposes.

  7. Recoverer (RECO):

     • Resolves distributed transactions (across multiple databases) that are in-doubt due to network or system failures.

Additional background processes include MMON, MMNL, LCKn, DMON, and others, depending on the database configuration and features enabled (such as Data Guard, RAC, etc.).

2. Oracle Database:

The Oracle database itself consists of physical storage (data files, control files, and redo log files) and logical structures (tablespaces, segments, extents, blocks). Together, these elements store and manage the actual data within the database.

Components of an Oracle Database:

• Physical Structures:

  1. Data Files:

     • Store the actual data in the database (e.g., tables, indexes, LOBs). Data files are associated with tablespaces.

  2. Control Files:

     • Contain critical information about the physical structure of the database, such as the database name, the locations of data files and redo logs, and database creation time.

  3. Redo Log Files:

     • Record all changes made to the data in the database. Redo log files are used for instance and media recovery.

  4. Archived Redo Log Files:

     • Contain copies of redo log files and are used to recover the database in case of failure or to perform point-in-time recovery.

  5. Parameter Files (PFILE/SPFILE):

     • Store initialization parameters that define the configuration of the Oracle instance.

     • Note: If database started automatically picks spfile  instead of pfile.

       • Spfile is binary format and pfile is in readable format.

       • If any changes need to be done in parameter file first convert spfile to pfile and change the parameters in pfile & change that modified pfile to spfile.

• Logical Structures:

  1. Tablespaces:

     • Logical storage units that group related objects, such as tables and indexes. Each tablespace consists of one or more data files.

  2. Segments:

     • A segment is a set of extents that store a specific type of object (e.g., table, index).

  3. Extents:

     • Extents are collections of contiguous Oracle blocks allocated to segments as they grow.

  4. Oracle Blocks:

     • The smallest unit of data storage in Oracle. A block consists of several bytes and contains rows of data.

3. User Processes:

These are processes initiated by users or applications that connect to the Oracle database. User processes communicate with Oracle server processes to execute SQL statements, retrieve data, and perform other tasks.

4. Server Processes:

• Dedicated Server Process: Each user process gets a dedicated server process, handling SQL statements and returning results.

• Shared Server Process: In a shared server environment, multiple user processes share a pool of server processes, reducing memory consumption.

5. Data Access Flow:

Here’s a simplified overview of how Oracle handles data access:

1. User Process: A user or application submits a query (e.g., SELECT statement).

2. Server Process: The server process checks the shared pool (in SGA) to see if the SQL statement has been previously executed (SQL cache).

3. Data Access:

   • If the requested data is already in the Database Buffer Cache, it is returned to the user.

   • If the data is not in the cache, the server process reads it from the data files and loads it into the buffer cache.

4. Redo Log: Any changes made to the data (e.g., via INSERT, UPDATE, DELETE) are written to the Redo Log Buffer.

5. Background Processes:

   • The Log Writer (LGWR) writes the redo entries to the redo log files.

   • The Database Writer (DBWn) writes dirty data blocks from the buffer cache to the data files when necessary.

6. Archiving: In ARCHIVELOG mode, the Archiver (ARCn) copies filled redo log files to archived redo logs for backup purposes.

Oracle Database Architecture Diagram:

Summary:

Oracle Database architecture consists of an Oracle instance (memory structures and background processes) and the Oracle database (physical and logical storage). These components work together to manage database operations, including data access, recovery, and transaction processing. The architecture ensures high availability, performance, and data integrity, supporting enterprise-level workloads.