Learn Oracle Server Architecture in 10 minutes
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 fileOnline Redo Log fileData FileTemp 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.
Shared Memory Segment
| Shared PoolShared 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) |