Oracle Database Architecture

Oracle Database Architecture

Oracle Database architecture consists of several key components that work together to manage, store, and retrieve data efficiently. These components can be broadly categorized into memory structures, background processes, and storage structures. Let’s delve into each category in detail.

Unlock your career with SparkDatabox’s Oracle training in Coimbatore! Get expert-led training and 100% placement assistance. Enroll now to master Oracle Database Architecture.

Major Architectural Components of Oracle Database Server

Memory Structures:

• System Global Area (SGA): A shared memory region that contains data and control information for one Oracle Database instance. It includes several key components:
  • Database Buffer Cache: Stores copies of data blocks read from disk. It improves performance by reducing the need to read from disk.
  • Redo Log Buffer: Holds redo entries (changes made to the database) before they are written to the redo log files.
  • Shared Pool: Caches various types of program data such as SQL and PL/SQL code, execution plans, and data dictionary information.
  • Large Pool: Optional memory pool used for large memory allocations, such as for RMAN operations, parallel execution, and shared server processes.
  • Java Pool: Used for all session-specific Java code and data in the JVM.
  • Streams Pool: Used by Oracle Streams for capture, staging, and propagation
  • Program Global Area (PGA): A memory region that contains data and control information exclusively for use by an Oracle server process. It includes:
    • Sort Area: Used for sorting operations.
    • Session Memory: Contains session-specific variables and data structures.
    • Cursor State: Stores information about the state of cursors.
    • SQL Work Areas: Used for complex operations like joins, sorting, and hash-based aggregations.

    .

• 1. Background Processes:
     • Database Writer (DBWn): Writes modified data from the database buffer cache to the data files on disk.
     • Log Writer (LGWR): Writes redo log entries from the redo log buffer to the online redo log files.
     • Checkpoint (CKPT): Signals DBWn to write all dirty buffers to disk and updates the data file headers and control files with checkpoint information.
     • System Monitor (SMON): Performs crash recovery when the instance is started.
     • Process Monitor (PMON): Monitors user processes and cleans up resources if a process fails.
     • Recoverer (RECO): Resolves distributed transactions that are pending due to a network or system failure.
     • Archiver (ARCn): Copies redo log files to the archive destination when they are full or during log switches.
     • Job Queue Processes (CJQ0 and Jnnn): Execute scheduled jobs for automated maintenance tasks.
  2. Storage Structures:
     • Logical Storage Structures:
       • Tablespaces: Logical storage units that group related logical structures. Each tablespace consists of one or more data files.
       • Segments: A set of extents allocated for a specific logical structure such as a table or index.
       • Extents: A set of contiguous data blocks allocated for storing a specific type of information within a segment.
       • Data Blocks: The smallest unit of data storage in Oracle, corresponding to a number of bytes on disk.
     • Physical Storage Structures:
       • Data Files: Physical files on disk that store the data for all logical structures in a database.
       • Redo Log Files: Store all changes made to the database as redo entries.
       • Control Files: Contain metadata about the database, including the structure, synchronization information, and RMAN backups.
       • Parameter Files (PFILEs and SPFILEs): Contain configuration parameters for database startup and operation.

  Correlating Logical and Physical Storage Structures

  • Tablespaces are logical storage structures that map to one or more data files on disk. For example, a tablespace might be used to group all tables related to a particular application, and the data files will store the actual data for these tables.
  • Segments within a tablespace are used for specific logical structures like tables or indexes. These segments consist of extents, which are sets of contiguous data blocks. Each data block corresponds to a specific number of bytes on disk and is the smallest unit of I/O operation.
  • Redo log files and control files are essential physical structures that ensure data integrity and recovery. Redo log files store a sequential record of all changes made to the database, while control files maintain metadata about the database’s structure and state.

  Understanding these components and their interactions is crucial for effective database management, performance tuning, and troubleshooting in an Oracle Database environment.