Oracle Database Architecture: Components and Structure

Posted on Jan 10, 2025 in Computing and Communications

Key Components of an Oracle Database

  • Control File: Contains information about physical files, the name of the database, block sizes, and information for recovery. Control files are required to open the database.
  • Data File: Contains information that records end-user applications.
  • Redo Log: Records all changes made to the database and the recovery status.
  • Pfile and Spfile: These are settings of the System Global Area (SGA) and optional features of Oracle background processes.

Oracle Instance and Server

  • An Oracle instance comprises a memory architecture and background processes.
  • An Oracle server consists of an instance and an Oracle database.
  • An instance is a temporary structure, but the database memory is based on physical files (control file, data file, and redo log).

Background Processes

  • PMON (Process Monitor): Cleans up failed connections to the database.
  • SMON (System Monitor): Executes the recovery of a database after a failure.
  • DBWn (Database Writer): Writes modified memory blocks in the SGA to disk.
  • LGWR (Log Writer): Writes information from the SGA (redo log buffer) to the redo log files.
  • CKPT (Checkpoint): Updates the database files following checkpoint events.

Memory Areas: PGA and SGA

  • PGA (Program Global Area): A memory region associated with each server process, containing control data and information for each user session. There is also a shared memory area.
  • SGA (System Global Area): A shared memory set that contains data and control information for a specific Oracle instance. It is composed of:
    • Shared Pool: Memory that stores cached SQL and PL/SQL.
    • Library Cache: Stores the text of the instructions.
    • Dictionary Cache: Stores the most recent usage information on the data dictionary.
    • Database Buffer Cache: Contains the most recently loaded data blocks.
    • Redo Log Buffer: Stores information about transactions for recovery purposes.

Tablespace and Data Dictionary

  • A tablespace can occupy one or more data files.
  • Data Dictionary: A set of tables and views that store information about the components of the database, as well as logical and physical structures. It includes two types of objects:
    • Base Tables: Automatically created with the CREATE DATABASE command.
    • Views: Created by running the catalog.sql script.
  • The Data Dictionary contains information about tables, indexes, synonyms, procedures, functions, packages, triggers, users, and privileges.

Data Dictionary Views

  • DBA: All objects in the database.
  • ALL: Objects accessible to the current user.
  • USER: Objects owned by the connected user.

Control File Details

  • The control file is a binary file. It is impossible to start the database without it.
  • It is read when mounting the database and is associated with a single database.
  • Contains information such as the database name, creation date, tablespace names, names and locations of control files and redo logs, redo log sequence numbers, ongoing checkpoint information, and backup information.

Control File Backup

  • Stop the database.
  • Make a physical copy of the file at the operating system level.
  • Include the new copy in the init.ora file in the control parameter.
  • Support after the database has changed:
    • ALTER DATABASE BACKUP CONTROLFILE TO 'filename'; This creates a file with the SQL statements necessary to recreate the control file.

Redo Log File

  • Redo log files provide transaction information in the event of a database failure.
  • A database requires at least two groups of redo log files.
  • Log Switch: Occurs when a redo log group fills up, and LGWR switches to the next group.
  • Force a log switch: ALTER SYSTEM SWITCH LOGFILE;
  • Force a checkpoint: ALTER SYSTEM CHECKPOINT;
  • LGWR writes on commit or every three seconds.
  • Add or remove a redo log group:
    • ALTER DATABASE [database_name] ADD LOGFILE [GROUP group_number];
    • ALTER DATABASE [database_name] DROP LOGFILE [GROUP group_number];
  • Clearing a member: ALTER DATABASE [database_name] DROP LOGFILE MEMBER 'filename';

Archivelog and Noarchivelog Modes

  • Noarchivelog: Redo log files are overwritten each time a log switch occurs. LGWR does not overwrite a group until the group checkpoint is complete.
  • Archivelog: Full, inactive redo log groups are archived. This allows for physical backups to restore the database without losing updated information.

Database Structure

  • Logical Structure: Database, Tablespace, Segment, Extent, Oracle Block.
  • Physical Structure: Data Files, Redo Log Files, Control Files.
  • Tablespace Types: Data, Index, Rollback, Temporary.
  • Undo tablespaces can only contain rollback segments.
  • Temporary tablespaces are used when memory is full, for ORDER BY operations, joins, and creating indexes that do not fit in memory.

Example of User Alteration

ALTER USER <user>
IDENTIFIED BY <password> / EXTERNALLY
DEFAULT TABLESPACE <tablespace_name>
TEMPORARY TABLESPACE <tablespace_name>
QUOTA <size> K / UNLIMITED ON <tablespace_name>
DEFAULT ROLE <role_name> / ALL / ALL EXCEPT <role_name> / NONE
PROFILE <profile_name>
PASSWORD EXPIRE
ACCOUNT LOCK / UNLOCK;