Oracle Database Architecture

 

Database Architecture                   

An Oracle database server consists of an Oracle database and an Oracle instance.

Oracle Database architecture is based on physical and logical structures. 

 

Oracle Database Physical Structure

Oracle database consists of physical files to store information. Basically there are three types of physical files required for database:

Core Files:

• Data Files                    Contains all the data of database
• Control Files              Contains data about database(metadata)
• Redo Log Files          Contains all changes made to data, allows recovery

Other Files

• Parameter File         Defines how instance will be configured when it starts up
• Password File           Allow users to connect remotely to the database
• Archive Log Files   Contains history of redo log files
• Trace Files                 Contains messages written by server and background process
• Alert File                    Special trace file, chronological log of messages and errors              

 

 

Oracle Database Logical Structure

Oracle database is divided into logical storage units known as Tablespaces, which can be used to group related logical structures together. Logical structure mainly consists of following structures starting with the bigger unit and going down to the smallest.

• Tablespace
• Segments
• Extents
• Data blocks

Tablespace

Each database is logically divided into one or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.

Segment

Tablespace consists of one or more segments. A segment is a set of extents allocated for a certain logical structure stored in the same tablespace. The segments can be of one of following types:

• Data segment  (stores user data within the database)
• Index Segment (store Indexes)
• Temporary Segment ( stores rollback segment)
• Rollback/Undo segment (created when sql statement needs a temporary work area)

Extent

An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information in one data file.

Data Blocks

Oracle database data is stored in data blocks(at the lowest logical level).  One data block corresponds to a specific number of bytes of physical database space on disk(default 8K). The standard block size is specified by the DB_BLOCK_SIZE initialization parameter.

Oracle Data blocks are the smallest unit of I/O in the database and they are mapped to OS block on the storage device

 

 

 

Oracle Instance

Oracle instance is comprised of memory structures and background processes that are required to perform different tasks to keep the instance and database running. Instance does not exist until database is started, which involves reading initialization parameter file and configuring instance according to the information within the parameter file.

Memory Structures

Instance has two major memory structures:

• System Global Area (SGA):  stores information in memory shared by processes in Oracle
• Program Global Area (PGA): contains information private to a particular process

Background Processes

Oracle creates a set of background processes for an instance that  manage the memory structures, asynchronously perform I/O to write data to disk, and do general housekeeping.
The most common background processes are the following:

• System monitor (SMON): Performs crash recovery when the instance starts after a failure
• Process monitor (PMON): Performs process cleanup when a user process fails
• Database writer (DBWn): Writes modified blocks from the database buffer cache to the files on disk
• Checkpoint (CKPT): Signals DBWn at checkpoints and updates all of the data files and control files of the database to indicate the most recent checkpoint
• Log writer (LGWR): Writes redo log entries to disk
• Archiver (ARCn): Copies the redo log files to archival storage when the log files are full or a log switch occurs

 

Related articles

• Oracle 9i Architecture (samadhandba.wordpress.com)
• Oracle 10g R2 Architecture (detailed with respect to SQL Rewrite)