1. Oracle Database Architecture Overview

Two terms that are used with Oracle are Database & Instance

• Database — A collection of physical operating system files.
• Instance — A set of Oracle background processes and a SGA also called as memory allocated for db operations.

These two terms very closely connected & related.

A db can be mounted and opened by many instances.

An instance will mount and open only a single database at any one point in time.

The File Structure

The are different file types that make a database.

• Parameter File — These file tells Oracle were to find the control files. Also they detail how big the memory area will be, etc…
• Data Files — These hold the tables, indexes and all other segments
• Temp Files — Used for disk-based sorting and temporary storage
• Redo Log Files — Our transaction logs
• Undo files — allows a user to rollback a transaction and provides read consistency.
• Archive Log Files — These are copies of Online Redo Log files which get generated whenever there is a log switch
• Control File — Details the location of data and log files and other relevant information about their state.
• Password File — Used to authenticate users logging in into the database.
• Log files — alert.log contains database changes and events including startup information.
• trace files — are debugging files.

Parameter Files

In order for Oracle to start it needs some basic information, this information is provided by using a parameter file. The parameter file can be either a pfile or a spfile:

• pfile — A simple text file which can be manually edited via vi or notepad
• spfile — A binary file which cannot be manually edited. we can read the contents of the file using “strings” tool in Linux.

The parameter file in Oracle is know as init.ora or init<oracle sid>.ora, the file contains key-value pairs of information that Oracle uses when starting the db. The file contains information such as db name, caches sizes, location of control files, etc.

By Default the location of the parameter file is

• Windows — $ORACLE_ HOME\database
• Unix — $ORACLE_ HOME/dbs

To convert the pfile to spfile you have perform the following.

Data Files

By Default Oracle will create two data files, the system data file which holds the data dictionary and sysaux data file which non-dictionary objects are stored, however there will be many more which will hold various types of data, a data file belongs to one tablespace only

Data files are made of the following

• Segments — are database objects like, a table, a index, rollback segments. Every object that consumes space is a segment. Segments themselves consist of one or more extents.
• Extents — are a contiguous allocation of space in a file. Extents, in turn, consist of data blocks
• Blocks — are the smallest unit of space allocation in Oracle. Blocks normally are 2KB, 4KB, 8KB, 16KB or 32KB in size but can be larger.

The relationship between segments, extents and blocks looks like below.

The DB_BLOCK_SIZE parameter determines the default block size of the database. Determining the block size depends on what you are going to do with the database, if you are using small rows then use a small block size, if you are using LOB’s then the block size should be larger.

You can have different block sizes within the database, each tablespace can have a different block size depending on what is stored in the tablespace. For an example: System tablespace could use the default 8KB and the OLTP tablespace could use a block size of 4KB.

There are few parameters that cannot be changed after installing Oracle and the DB_BLOCK_SIZE is one of them, so make sure to select the correct choice when installing Oracle.

A data block is made up of the following, the two main area’s are the free space and the data area.

Tablespaces

A tablespace is a container which holds segments. Each and every segment belongs to exactly one tablespace. Segments never cross tablespace boundaries. A tablespace itself has one or more files associated with it. An extent will be contained entirely within one data file.

The minimum tablespaces required are the system and sysaux tablespace, the following reasons are why tablespaces are used.

• Tablespaces make it easier to allocate space quotas to users in the database
• Tablespaces enable you to perform partial backups and recoveries based on the tablespace as a unit
• Tablespaces can be allocated to different disks and controllers to improve performance
• You can take tablespaces offline without affecting the entire database
• You can import and export specific application data by using the import and export utilities at the tablespace.

There are different types of a tablespace:

• Bigfile tablespaces, will have only one file which can range from 8–128 terabytes.
• Smallfile tablespaces (default), can have multiple files but the files are way smaller compared tobigfile tablespace.
• Temporary tablespaces, contain data that only persists for the duration a users session, used for sorting
• Permanent tablespaces, any tablespace that is not temporary one.
• Undo tablespaces, Oracle uses undo to rollback or undo changes to the db.
• Read-only, no write operations are allowed.

So in summary the Oracle hierarchy is as follows:

• A database is made up of one or more tablespaces
• A tablespace is made up of one or more data files, a tablespace contains segments
• A segment (table, index, etc) is made up of one or more extents. A segment exists in a tablespace but may have data in many data files within a tablespace.
• An extent is a continuous set of blocks on a disk. An extent is in a single tablespace and is always in a single file within that tablespace.
• A block is the smallest unit of allocation in the database. A block is the smallest unit of i/o used by the database.

Temp Files

Oracle uses temporary files to store results of a large sort operations when there is insufficient memory to hold in RAM. Temporary files never have redo information generated for them, although they have undo information generated which in turns creates a small amount of redo information. Temporary data files never need to be backed up ever as they cannot be restored.

Redo log files

All the changes made to the db are recorded in the redo log files, these files along with archived redo logs enable a dba to recover the db to any point in time in past. Oracle will write all committed changes to the redo logs first before applying them to the data files. The redo logs guarantee that no committed changes are ever lost. Redo log files consist of redo records which are group of change vectors each referring to specific changes made to a data block in the db. The changes are first kept in the redo buffer but are quickly written to the redo log files.

There are two types of redo log files online and archive. Oracle uses the concept of groups. A minimum of 2 groups are required, each group having at least one file, they are used in a circular fashion when one group fills up oracle will switch to the next log group.

Archive Redo log

When an Online redo log file fills up and before it is used again the file is archived for safe keeping, this archive file with other redo log files can recover a database to any point in time. It is best practice to turn on ARCHIVELOG mode which performs the archiving automatically.

Undo File

When you change data you should be able to either rollback that change or to provide a read consistent view of the original data. Oracle uses undo data (change vectors) to store the original data, this allows a user to rollback the data to its original state if required. This undo data is stored in the undo tablespace.

Control file

The control is one of the most important files within Oracle, the file contains data and redo log location information, current log sequence numbers, RMAN backup set details and the SCN. This file should have multiple copies due to it’s importance. This file is used in recovery as the control file notes all checkpoint information which allows oracle to recover data from the redo logs. This is the first file that Oracle consults when starting up.

The view V$CONTROLFILE can be used to list all controlfiles, you can also use the V$CONTROLFILE_RECORD_SECTION to view the controlfile’s record structure.

You can also log any checkpoints while the system is running by setting the LOG_CHECKPOINTS_TO_ALERT to true.

Password file

This file optional and contains the names of the database users who have been granted the special SYSDBA and SYSOPER admin privilege.

Log files

The alert.log file contains important startup information, major database changes and system events, this will probably be the first file that will be looked at when you have issues. The file contains log switches, db errors, warnings and other messages. If this file is removed Oracle creates another one automatically.

Trace Files

Traces files are debugging files which can trace background process information (LGWR, DBWn, etc), core dump information (ora-600 errors, etc) and user processing information (SQL).

Oracle Managed Files

The OMF feature aims to set a standard way of laying out Oracle files, there is no need to worry about file names and the physical location of the files themselves. The method is suited in small to medium environments, OMF simplifies the initial db creation as well as on going file management.

System Change (Commit) Number (SCN)

The SCN is an important quantifier that oracle uses to keep track of its state at any given point in time. The SCN is used to keep track of all changes within the database, its a logical timestamp that is used by oracle to order events that have occurred within the database. SCN’s are increasing sequence numbers and are used in redo logs to confirm that transactions have been committed, all SCN’s are unique. SCN’s are used in crash recovery as the control maintains a SCN for each data file, if the data files are out of sync after a crash oracle can reapply the redo log information to bring the database backup to the point of the crash. You can even take the database back in time to a specific SCN number (or point in time).

Checkpoints

Checkpoints are important events that synchronize the database buffer cache and the datafiles, they are used with recovery. Checkpoints are used as a starting point for a recovery, it is a framework that enables the writing of dirty blocks to disk based on a System Change or Commit Number and a Redo Byte Address validation algorithm and limits the number of blocks to recover.

The checkpoint collects all the dirty buffers and writes them to disk, the SCN is associated with a specific RBA in the log, which is used to determine when all the buffers have been written.