Oracle Database Architecture

05:38 Posted by Unknown

An oracle database is a collection of related data in one or more file. The database contains some logical and physical structures. While developing an application. We can create structures such as table,indexes,synonyms,views etc.

Architecture overview

An oracle instance comprises of a memory area called system global area (SGA) and background processes that interacts between SGA and database files on disk. In oracle real application cluster (RAC) more than one instance will be created and these instances are generally on different servers connected through high-speed interconnect.

Thread Based Architecture

The internal architecture of oracle database is thread based. Thread is a program that runs program instructions so that different processes of a program can run simultaneously on different processors.

Advantages of thread-level architecture on windows are:-

1. Fast context switching

2. Simple system global area (SGA)

3. Fast creation of new connection because threads creation are faster than processes

4. Less memory usage

An Oracle instance runs two types of processes - Server and Background. Server processes are created to handle requests from sessions connected to the instance.

Oracle instance

The Oracle instance consists of SGA and all the Oracle background processes. To manage the size of SGA, two initialization parameter files known as PFILE and SPFILE are used. There are a total of 250-initialization parameters. PFILE holds 30 of those 250 initialization parameters. Oracle does not recommend modifying the rest of the 220 initialization parameters.

The physical layer comprises the following files:

The control file: Though it is the smallest file (1MB-5MB) of the database, it is the most crucial and critical file. The criticality of this file is ascribed to the fact that if it is damaged then the database recovery operation becomes tedious. It contains the requisite information to start the database. The names and locations of all the control files of the database can be obtained from the V dynamic performance view.
Data files: These hold data that is stored in tables of a database. They are usually the largest files in the database, ranging in size from MB, GB, to TB.
Redo Log files: These files contain information that helps in recovery in the event of system failure. It contains information stored in the Redo Log Buffer. The information in the Redo Log Buffer is written into Redo Log files by the LGWR background process. The Redo Log files are generally multiplexed and copied for recovery purposes. Sets of redo Log files are known as Redo Log groups. Each database has minimum of two Redo Log groups. Redo Log groups are used in a circular fashion.

Database Storage Architecture

An ORACLE database has both a physical and a logical structure. By separating physical and logical database structure, the physical storage of data can be managed without affecting the access to logical storage structures.

Logical Storage Structure

An ORACLE database's logical structure is determined by:

one or more tablespaces.
Database's schema objects (e.g., tables, views, indexes, clusters, sequences, stored procedures).
Rows, Columns

Physical Storage Structure

Data files
Segments
Extents
Blocks

Database Memory Architecture

SYSTEM GLOBAL AREA (SGA)

According to the architecture of oracle database , it uses an shared area of memory called system global area(SGA) and a private area called program global area(PGA).

The SGA is a shared memory area that contains data and control information for one oracle instance. Oracle allocates the SGA when an instance starts and de-allocates it when the instance shut downs. Every instance has the SGA .The entire SGA should be large as possible to increase the system performance and to reduce disk I/O operation.

There are two types of SGA which are:-

Fixed SGA

A portion of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the Fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.

Automatic Shared Memory Management

From Oracle 10g, with its emphasis on self-management and self-tuning, introduces many features that operate automatically, as well as in an advisory role. Since we fix the values for the SGA components at instance start time, we are constrained to use them as they are during the instance runtime (with some exceptions).

With the Database 10g, we can employ the Automatic Shared Memory Management feature. This feature enables the Oracle database to automatically determine the size of each of these memory components within the limits of the total SGA size. This solves the allocation issues that we normally face in a manual method.

The information stored in the SGA is divided into three memory structures,

1) Database buffers cache.

2) Redo log buffers.

3) Shared pool.

1) Database buffers cache: The database buffer stores the most recently used blocks of data. The set of database buffers in an instance is the database buffer cache. The buffer cache contains modified as well as unmodified blocks. Because the most recently and most frequently used data is kept in memory. It improves the performance of system by reducing the disk I/O operations.

How does it store information:-in form of

Write list
LRU list

2) Redo log buffers: The redo log buffer stores redo entries. This is a log of changes made to the database. The redo entries stored in the redo log buffers are written to an online redo log. An online redo log is a set of two or more files that record all the changes made to oracle data files and control files.

3) Shared pool: The shared pool caches various constructs that can be shared among users, for example SQL shared area. For example’s SQL statements are cached so that they can be reused. Stored procedures can be cached for faster access. Note that in previous versions “out of memory” error were occurs if the shared memory was full. In oracle 10g it does not happen. It provides automatic shared memory tuning to increase the performance.

How does it store information:- in form of

Library cache

Data dictionary cache

a)Library cache:-

it caches the parse tree and the execution plan for every unique SQL statement.

What information its hold:- The library cache stores shared SQL among users.

How does it store information:- If multiple applications issue the same SQL statement, each application can access the shared SQL area. This reduces the amount of memory needed and reduces the processing-time used for parsing and execution planning.

Shared SQL areas
Private SQL areas
PL/SQL procedures and packages

Various control structures

In accordance of LRU algorithm.

b) Data dictionary cache:-

the data dictionary comprises a set of tables and views that map the structure of the database.

What information its hold:- it store information about the logical and physical structure of the database. The data dictionary contains information such as:

user information, such as user privileges
integrity constraints defined for tables in the database
names and datatypes of all columns in database tables
information on space allocated and used for schema objects

How does it store information:- provide information on object structures to sql statements being parsed

Row cache
Library cache

Database Operational architecture

Background processes

Background processes are processes running behind the scene and are invoked automatically when the instance is started which meant to perform certain maintenance activities or to deal with abnormal conditions arising in the lifetime of the instance.

SMON – System Monitor

Performs automatic instance recovery and monitors temporary segments and extents.
In a non-failed instance can also perform failed instance recovery for other failed RAC instance.
Reclaims space used by temporary segments no longer in use
Merges contiguous area of free space in the datafile

PMON - Process Monitor process recovers failed process resources. If MTS (also called Shared Server Architecture) is being utilized, PMON monitors and restarts any failed dispatcher or server processes. In RAC, PMON’s role as service registration agent is particularly important.

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

RECO -The Distributed Transaction Recovery Process finds pending (distributed) transaction and resolves them.

ARCH -The Archiver Process archives redo log files if ARCHIVELOG is enabled.

Copies redo log files to tape or disk for media failure
Operates only when a log switch occurs
Is optional and is only needed when in ARCHIVELOG mode
May write to a tape drive or to a disk

DBWR -The Database Writer writes dirty blocks from the database buffer to the datafiles. How many DBWn Processes are started is determined by the initialization parameter DB_WRITER_PROCESSES. DBWR also writes the actual SCN with the Block.

LGWR -The Log Writer writes the redo log buffer from the SGA to the online redo log file.

CKPT -The Checkpoint Process regularly initiates a checkpoint which uses DBWR to write all dirty blocks back to the datafiles, thus synchronizing the database. Since a Checkpoint records the current SCN, in a recovery only redo records with a SCN higher than that of the last checkpoint need to be applied.

Is enabled by setting the parameter CHECKPOINT_PROCESS=TRUE
If enabled, 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
CKPT improve the performance of database with many database files

Some of the main functions of background processes are:

Communication among Oracle instances
Performing computer maintenance
Writing the dirty blocks to disk

Can find the background processes by:

SQL> select * from v$bgprocess where paddr <> '00';

SQL>select * from v$session where type ='BACKGROUND';

Tags: Architecture, Database, Oracle, SGA

Share your views...

0 Respones to "Oracle Database Architecture"

Codes & Concepts