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)


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • Understanding Oracle QUERY PLAN - Part2 (Exploring SQL Joins)

    This is the second part of the article Understanding Oracle Query Plan. In this part we will deal with SQL Joins.

  • How to find out Which User is Running what SQL Query in Oracle database?

    Do you wonder how to get information on all the active query in the Oracle database? Do you want to know what query is executed by which user and how long is it running? Here is how to do it!

  • Different Methods to move data from Oracle to External File

    How to push data from Oracle Table to external files? Well, external tables in Oracle provides a way to move data in as well as out of the database with the help of SQL*Loader and Data Pump functionality.

  • Oracle UTL_FILE

    The Oracle supplied PL/SQL package UTL_FILE used to read and write operating system files that are located on the database server.

  • Oracle AUTOTRACE Explained - A 10 Minute Guide

    AUTOTRACE is a beautiful utility in Oracle that can help you gather vital performance statistics for a SQL Query. You need to understand and use it for SQL Query Tuning. Here is how!

  • Oracle Analytic Functions

    Oracle Analytic Functions compute an aggregate value based on a group of rows. It opens up a whole new way of looking at the data. This article explains how we can unleash the full potential of this.

  • Database Performance Tuning

    This article tries to comprehensively list down many things one needs to know for Oracle Database Performance Tuning. The ultimate goal of this document is to provide a generic and comprehensive guideline to Tune Oracle Databases from both...

  • Understanding Oracle QUERY PLAN - A 10 minutes guide

    Confused about how to understand Oracle Query Execution Plan? This 10 minutes step by step primer is the first of a two part article that will teach you exactly the things you must know about Query Plan.

  • Oracle Spool

    Oracle Spool for SQLplus command line utility, can be used in conjunction with OS to export data from Oracle to flat file.

  • How to find out Expected Time of Completion for an Oracle Query

    Too often we become impatient when Oracle Query executed by us does not seem to return any result. But Oracle (10g onwards) gives us an option to check how long a query will run, that is, to find out expected time of completion for a query.