Oracle Database Architecture:
An
Oracle server:
• Is a database management
system that provides an open,
comprehensive, integrated approach to information management.
• Consists of an Oracle instance and an Oracle Database.
Oracle
instance:
·
An instance is a set of
memory structures that manage database files.
·
An instance can exist
independently of database files.
Oracle Database:
·
A
database is a set of files, located on disk, that store data.
·
These
files can exist independently of a database instance.
Oracle
Database Architecture:
·
The Oracle server is the key to information
management.
·
In general, an Oracle server must reliably manage a
large amount of data in a multiuser environment so that many users can concurrently
access the same data.
·
All this must be accomplished while delivering high performance.
·
An Oracle server must also prevent unauthorized
access and provide efficient solutions for failure recovery.
Database Structures:
1. Memory structures
2. Process structures
3. Storage structures
·
Each running Oracle database is associated with an
Oracle instance.
·
When a database is started on a database server, the
Oracle software allocates a shared memory area called the System Global Area
(SGA) and starts several Oracle background processes. This
combination of the SGA and the Oracle processes is called an Oracle instance.
·
After starting an instance, the Oracle software
associates the instance with a specific database. This is called mounting
the database.
·
The database is then ready to be opened, which makes
it accessible to authorized users.
·
Multiple instances can execute
concurrently on the same computer, each accessing its own physical
database.
·
An Oracle database uses memory structures and
processes to manage and access the database.
·
All memory structures exist in the main memory of
the computers that constitute the database server.
·
Processes are jobs that work in the memory of these
computers.
·
A process is defined as a “thread of control” or a
mechanism in an operating system that can run a series of steps.
Oracle Memory
Structures:
The basic memory structures
associated with an Oracle instance include the following:
• System Global Area (SGA): Shared by all server
and background processes
• Program Global Area (PGA): Private to each
server
and background process. There is one PGA for each process.
System
Global Area (SGA):
The SGA is a memory area that
contains data and control information for the instance.
The SGA includes the following data
structures:
• Database buffer cache: Caches blocks of
data retrieved from the database.
• Redo log buffer: Caches redo information (used for instance
recovery) until it can be written to the physical redo log files stored on the
disk.
• Shared
pool: Caches
various constructs that can be shared among users.
• Large pool: Is an optional area that provides large
memory allocations for certain large processes, such as Oracle backup and
recovery operations, and I/O server processes.
• Java pool: Is used for all session-specific Java code
and data within the Java Virtual Machine (JVM).
• Streams pool: Is used by Oracle Streams.
·
When you start the instance by using Enterprise Manager or
SQL*Plus, the amount of memory allocated for the SGA is displayed.
·
With the dynamic SGA infrastructure, the size of the
database buffer cache, the shared pool, the large pool, the Java pool, and the
Streams pool changes without shutting down the instance.
·
The Oracle database uses initialization parameters
to create and configure memory structures.
·
For example: the SGA_TARGET parameter specifies
the total amount of space available to the SGA. If you set SGA_TARGET to 0, Automatic
Shared Memory Management is disabled.
Program
Global Area (PGA):
·
A Program Global Area (PGA) is a memory region that contains data
and control information for each server process.
·
An Oracle server process services a client’s requests.
·
Each server process has its own private PGA that is created when
the server process is started.
·
Access to the PGA is exclusive to that server process, and the
PGA is read and written only by the Oracle code acting on its behalf.
Process
Structures:
·
User process: Is started at the
time a database user requests a connection to the Oracle server
·
Server process: Connects to the
Oracle instance and is started when a user establishes a session.
·
Background
processes: Are
started when an Oracle instance is started
·
When you invoke an application program or an Oracle tool, such
as Enterprise Manager, the Oracle server creates a server process to
execute the commands issued by the application.
·
The Oracle server also creates a set of background processes for
an instance that interact with each other and with the operating system to
manage the memory structures, asynchronously perform I/O to write data to disk,
and perform other required tasks.
·
Which background processes are present depends on the features
that are being used in the database.
Oracle Instance
Management:
Oracle Instance
Management:
·
An Oracle database server consists of an Oracle database
and an Oracle instance.
·
An Oracle instance is made up of memory structures, known as the
System Global Area (SGA), and background processes that handle much of the
behind-the-scenes work involved in running an instance.
The most common background processes
are the following:
• System Monitor (SMON): Performs crash
recovery when the instance is started following a failure.
• Process Monitor (PMON): Performs process
cleanup when a user process fails.
• Database Writer (DBWn): Writes modified
blocks from the database buffer cache to the data files on the disk.
• Checkpoint (CKPT): Updates all the data
files and control files of the database to indicate the most recent
checkpoint
• LogWriter
(LGWR): Writes
redo log entries to the disk
• Archiver (ARCn): Copies redo log
files to the archival storage when a log switch occurs.
Server Process and
Database Buffer Cache:
·
When a query is processed, the Oracle server process looks in
the database buffer cache for any blocks that it needs.
·
If the block is not found in the database buffer cache, the server
process
reads the block from the data file and places a
copy in the database buffer cache.
·
Because subsequent requests for the same block may find the
block in memory, the requests may not require physical reads.
·
The Oracle server uses the least recently used algorithm to
age out buffers
that have
not been accessed recently to make room for new blocks in the
database buffer cache.
Buffers in the buffer cache can be
in one of the following four states:
·
Pinned: Multiple sessions
are kept from writing to the same block at the same time. Other sessions wait
to access the block.
·
Clean: The buffer is now
unpinned and is a candidate for immediate aging out, if the current contents
(data block) are not referenced again. Either the contents are in sync with the
block contents stored on the disk or the buffer contains a consistent read (CR)
snapshot of a block.
·
Free or unused: The buffer is empty because the instance
has just started. This state is very similar to the clean state, except that
the buffer has not been used.
·
Dirty: The buffer is no longer pinned but the
contents (data block) have changed and must be flushed to the disk by DBWn before it can be
aged out.
Physical Database
Structure
The files that constitute an Oracle
database are organized into the following:
·
Control files: Contain data about
the database itself (that is, physical database structure information). These
files are critical to the database. Without them, you cannot open data files to
access the data within the database.
·
Data files: Contain the user or
application data of the database.
·
Online redo log files: Allow for instance
recovery of the database. If the database crashes and does not lose any data
files, then the instance can recover the database with the information in these
files.
The following additional files are
important to the successful running of the database:
·
Parameter file: Is used to define how the instance is
configured when it starts up.
·
Password file: Allows users to connect remotely to the
database and perform administrative tasks.
·
Backup files: Are used for database recovery. You typically
restore a backup file when a media failure or user error has damaged or deleted
the original file.
·
Archive log files: Contain an ongoing history of the
data changes (redo) that are generated by the instance. Using these files
and a backup of the database, you can recover a lost data file. That is,
archive logs enable the recovery of restored data files.
·
Trace
files: Each server and background process can write to an
associated trace file.When an internal error is detected by a process, the
process dumps information about the error to its trace file. Some of the
information written to a trace file is intended for the database administrator,
whereas other information is for Oracle Support Services.
·
Alert
log files: These are special trace files. They are also known as alert
logs. The alert log of a database is a chronological log of messages and
errors. Oracle recommends that you review these files.
Tablespaces and
Data Files:
• Tablespaces
consist of one or more data files.
• Data files belong
to only one tablespace.
·
A database is divided into logical storage units called
tablespaces, which can be used to group related logical structures together.
·
Each database is logically divided into one or more tablespaces.
·
One or more data files are explicitly created for each
tablespace to physically store the data of all logical structures in a
tablespace.
SYSTEM
and SYSAUX Tablespaces
• The SYSTEM and
SYSAUX tablespaces are mandatory tablespaces.
• They are created at
the time of database creation.
• They must be
online.
• The SYSTEM
tablespace is used for core functionality (for example, data dictionary
tables).
• The auxiliary
SYSAUX tablespace is used for additional database components (such as the
Enterprise Manager Repository).
·
Each Oracle database contains a SYSTEM tablespace and a SYSAUX
tablespace. They are automatically created when the database is created.
·
The system default is to create a smallfile tablespace. You can
also create bigfile tablespaces, which enable the Oracle database to manage
ultralarge files
(up to 8 exabytes).
·
A tablespace can be online (accessible) or offline (not
accessible).
·
The SYSTEM tablespace is always online when the database is
open. It stores tables that support the core functionality of the database,
such as the data dictionary tables.
·
The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM
tablespace.
·
The SYSAUX tablespace stores many database components, and it
must be online for the correct functioning of all database components.
Segments,
Extents, and Blocks:
·
Database objects, such as tables and indexes, are stored as
segments in tablespaces.
·
Each segment contains one or more extents.
·
An extent consists of contiguous data blocks, which means that
each extent can exist only in one data file.
·
Data blocks are the smallest unit of I/O in the database.
·
When the database requests a set of data blocks from the
operating system (OS), the OS maps this to an actual file system or disk block
on the storage device.
·
Because of this, you need not know the physical address of any
of the data in your database. This also means that a data file can be striped
or mirrored on several disks.
·
The size of the data block can be set at the time of the
creation of the database. The default size of 8 KB is adequate for most
databases.
·
If your database supports a data warehouse application that has
large tables and indexes, then a larger block size may be beneficial.
·
If your database supports a transactional application where
reads and writes are random, then specifying a smaller block size may be
beneficial. The maximum block size depends on your OS.
·
The minimum Oracle block size is 2 KB and should rarely
(if ever) be used.
Logical
and Physical Database Structures:
An Oracle database is a collection
of data that is treated as a unit.
The general purpose of a database is
to store and retrieve related information. The database has logical
structures and physical structures.
Tablespaces:
·
A database is divided into logical storage units called
tablespaces, which group related logical structures together.
For example, tablespaces
commonly group all of an application’s objects to
simplify
some administrative operations. You may have a tablespace for application data
and an additional one for application indexes.
Databases,
Tablespaces, and Data Files:
·
Each database is logically divided into one or more tablespaces.
·
One or more data files are explicitly created for each
tablespace to physically store the data of all logical structures in a
tablespace.
·
If it is a TEMPORARY tablespace, instead of a data file, then
the tablespace has a temporary file.
Schemas:
·
A schema is a collection of database objects that are owned by a
database user.
·
Schema objects are the logical structures that directly refer to
the database’s data.
·
Schema objects include such structures as tables, views, sequences, stored procedures, synonyms,
indexes, clusters, and database links.
·
In general, schema objects include everything that your
application creates in the database.
Data Blocks:
·
At the finest level of granularity, an Oracle database’s data is
stored in data blocks.
·
One data block corresponds to a specific number of bytes of
physical database space on the disk.
·
A data block size is specified for each tablespace when it is
created.
·
A database uses and allocates free database space in Oracle data
blocks.
Extents:
·
The next level of logical database space is called an extent.
·
An extent is a specific number of contiguous data blocks
(obtained in a single allocation) that are used to store a specific type of information.
Segments:
·
The level of logical database storage above an extent is called
a segment.
·
A segment is a set of extents allocated for a certain logical
structure.
For
example,
the different types of segments include:
• Data segments: Each nonclustered,
non-indexed-organized table has a data segment. All of the table’s data is
stored in the extents of its data segment. For a partitioned table, each
partition has a data segment. Each cluster has a data segment. The data of
every table in the cluster is stored in the cluster’s data segment.
• Index segments: Each index has an
index segment that stores all of its data. For a partitioned index, each
partition has an index segment.
• Undo segments: One UNDO tablespace
is created by the database administrator to temporarily store undo information.
The information in an undo segment is used to generate read-consistent database
information and, during database recovery, to roll back
uncommitted transactions for users.
• Temporary segments:
Temporary
segments are created by the Oracle database when a SQL statement needs a
temporary work area to complete execution. When the statement finishes
execution, the temporary segment’s extents are returned to the instance for
future
use. Specify a
default temporary tablespace for every user or a default temporary tablespace,
which is used databasewide.
·
The Oracle database dynamically allocates space. When the
existing extents of a segment are full, additional extents are added.
·
Because extents are allocated as needed, the extents of a segment
may or may not be contiguous on the disk.
