Oracle

Learn Oracle Server Architecture in 10 minutes

Saurav Mitra

Here is an easy to understand primer on Oracle architecture. Read this first to give yourself a head-start before you read more advanced articles on Oracle Server Architecture.

We need to touch two major things here- first server architecture where we will know memory and process structure and then we will learn the Oracle storage structure.

Database and Instance

Let’s first understand the difference between Oracle database and Oracle Instance.

Oracle database is a group of files that reside on disk and store the data. Whereas an Oracle instance is a piece of shared memory and a number of processes that allow information in the database to be accessed quickly and by multiple concurrent users.

The following picture shows the parts of database and instance.

Database Instance
  • Control file
  • Online Redo Log file
  • Data File
  • Temp File
  • Shared Memory (SGA)
  • Processes

Now let's learn some details of both Database and Oracle Instance.

Oracle Database

The database is comprised of different files as follows

Control file Control file contains information that defines the rest of the database like names, location and types of other files etc.
Redo Log file Redo Log file keeps track of the changes made to the database. All user and meta data are stored in data files
Temp file Temp file stores the temporary information that are often generated when sorts are performed.

Each file has a header block that contains metadata about the file like SCN or system change number that says when data stored in buffer cache was flushed down to disk. This SCN information is important for Oracle to determine if the database is consistent.

Oracle Instance

This is comprised of a shared memory segment (SGA) and a few processes. The following picture shows the Oracle structure.

Oracle Instance

Shared Memory Segment

Shared Pool
Shared SQL Area
Contains various structure for running SQL and dependency tracking
Database Buffer Cache Contains various data blocks that are read from database for some transaction
Redo Log Buffer It stores the redo information until the information is flushed out to disk

Details of the Processes are shown below

PMON(Process Monitor) - Cleans up abnormally terminated connection
- Rolls back uncommitted transactions
- Releases locks held by a terminated process
- Frees SGA resources allocated to the failed processes
- Database maintenance
SMON(System Monitor) - Performs automatic instance recovery
- Reclaims space used by temporary segments no longer in use
- Merges contiguous area of free space in the datafile
DBWR(Database Writer) - write all dirty buffers to datafiles
- Use a LRU algorithm to keep most recently used blocks in memory
- Defers write for I/O optimization
LGWR(Log Writer) - writes redo log entries to disk
CKPT(Check Point) - If enabled (by setting the parameter CHECKPOINT_PROCESS = TRUE), take over LGWR’s task of updating files at a checkpoint
- Updates header of datafiles and control files at the end of checkpoint
- More frequent checkpoint reduce recovery time from instance failure
Other Processes LCKn (Lock), Dnnn (Dispatcher), Snnn (Server), RECO (Recover), Pnnn(Parallel), SNPn(Job Queue), QMNn(Queue Monitor) etc.

Oracle Storage Structure

Here we will learn about both physical and logical storage structure. Physical storage is how Oracle stores the data physically in the system. Whereas logical storage talks about how an end user actually accesses that data.

Physically Oracle stores everything in file, called data files. Whereas an end user accesses that data in terms of accessing the RDBMS tables, which is the logical part. Let's see the details of these structures.

Physical storage space is comprised of different datafiles which contains data segments. Each segment can contain multiple extents and each extent contains the blocks which are the most granular storage structure. Relationship among Segments, extents and blocks are shown below.

Data Files
|
^
Segments (size: 96k)
|
^
Extents (size: 24k)
|
^
Blocks (size: 2k)