Oracle Database Architecture

In this tutorial we are going to see about Oracle Database and basic details of its various types of files and few background processes in brief.

Oracle is an Object Relational Database Management System (ORDBMS).  An Oracle database server consists of a database and at least one database instance. An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management. The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.

Database: It’s a set of files, located on disk which is used to store the data.

Instance: It’s a combination of System Global Area (SGA) and a set of background process which is used to manage database files.

The below diagram shows these details.

Physical Structures

Data file: It contains the actual data stored in the database objects like tables, indexes etc.

Control file: It contains the details of physical structure of the database like name of database, location of data file etc.

Redo log file: It contains details of the changes made to the database, which will be used in case of db failure. It’s a set of files always.

Parameter file: Contains the configuration details of instance and database, one machine readable (SPFILE) and human readable (PFILE).

Alert & Trace file: All background process write to these files when some abnormal activates happens in the database and for additional information when some operations is being performed.

Logical Structures

Data Blocks: It’s the lowest level where an Oracle exactly stores a data. One data block represent specific number of bytes.

Extends: It’s the next level of database space, which represent contiguous data blocks.

Segments: Its above extends, collects of extends stored for specific user object.

Tablespaces:  It’s the logical container for various segments, and each tablespace contains at least one data file.

Memory Structure

System Global Area (SGA): It’s a shared memory structure, which contain data (i.e. data block, shared SQL area) and information for a single oracle database instance. It’s shared by all server and background process.

Programmable Global Area (PGA): It’s shared memory stricter, which contain data and control information for a server or a background process. Each server process has its own PGA

User Global Area: Memory that is associated with a specific user session.

Client Process: These process are created and maintained to run the software code of an application program on an Oracle Tool.

Oracle Background Processes

PMON-Process Monitor: Manages all the process, performs recovery when a user process fails, cleaning cache, freeing resources.

SMON-System Monitor: Processes recovery after instance failure and monitors and cleans temporary segments and extends that are not used.

DBWn-Database Writer: It takes care of writing the modified blocks from the database buffer (RAM) to the data files.  

LGWR-Log writer: It writes the redo log entries to the disk.

CKPT-Checkpoint: It writes information to control files and data file headers.

MMON: Process to collect statistics for Automatic Workload Repository (AWR) report.

RECO-Recovered process: Used to resolve distributed transaction that are pending due to network or system failure in distributed database.

SGA Components

Database Buffer Cache: Contains the most recently used blocks of data. Contains both modified and unmodified blocks.  

Redo log buffer: Circular buffer in the SGA that stored the redo entries describing the changes made to the database. These contain information necessary to reconstruct, or redo, changes made to the db by DML or DDL operations.

Shared Pool: It’s the place where SGA has the library cache (SQL, PL/SQL code), dictionary cache (Oracle accessed data dictionary item used for SQL parsing) and result cache (results of SQL queries, PL/SQL that are cached).

Please provide your comments below the blog.