Oracle Database Architecture

Prepared By
Product Alliance Group

Tata Consultancy Services

Ahmedabad
 Architecture
 Physical Structure
 Logical Structure
 Architecture
Instance
User
process Shared pool SGA
Library
cache Database Redo log
Server Data Dict. buffer cache buffer cache
process cache
PGA
PMON SMON DBWR LGWR CKPT Others

Data Control Redo log

Parameter files files files Archived
file log files

Password
file
 Physical Structures
 Data-files
 Control Files
 Redo Log Files
 Archive Log Files
 Parameter Files
 Alert and Trace Log Files
 Backup Files
 Datafiles
 Contain all the database data.
 Data of logical database structures, such as tables
and indexes, is physically stored in the datafiles
allocated for a database.
 A data-file can be associated with only one
database.
 Can automatically extend when the database runs
out of space.
 One or more datafiles form a logical unit of
database storage called a tablespace.
 Control Files
 Specify the physical structure of the database.
 Database name
 Names and locations of datafiles and redo log files
 Time stamp of database creation
 Multiplex the control file to protect against a failure
involving the control file.
 When an instance of an Oracle database is started, its control
file identifies the database and redo log files that must be
opened for database operation to proceed.
 When physical makeup of the database is altered, control file
is automatically modified by Oracle to reflect the change.
 Used in database recovery.
 Redo Log Files
 Every Oracle database has a set of two or more redo log
files.
 Record all changes made to data.
 Multiplexed redo log so that two or more copies of the
redo log can be maintained on different disks.
 The information in a redo log file is used only to recover
the database from a system or media failure that prevents
database data from being written to the datafiles.
 The process of applying the redo log during a recovery
operation is called rolling forward.
 Parameter Files
 Parameter files contain a list of configuration
parameters for that instance and database.
 Oracle recommends that you create a server
parameter file (SPFILE) as a dynamic means of
maintaining initialization parameters.
 A server parameter file lets you store and manage
your initialization parameters persistently in a
server-side disk file.
 Alert and Trace Log Files
 Each server and background process can write to an
associated trace file.
 When an internal error is detected by a process, it dumps
information about the error to its trace file.
 Some of the information written to a trace file is intended
for the database administrator, while other information is
for Oracle Support Services.
 Trace file information is also used to tune applications and
instances.
 The alert file, or alert log, is a special trace file. The alert
log of a database is a chronological log of messages and
errors.
 Logical Structures
 Tablespaces
 Oracle Data Blocks
 Extents
 Segments
 Tablespaces
 Groups related logical structures together.
 One or more datafiles are explicitly created for each
tablespace to physically store the data of all logical
structures in a tablespace.
 Every database contains a SYSTEM and a SYSAUX (in
database 10g Only).
 The default is a smallfile tablespace, which is the
traditional type of Oracle tablespace.
 The SYSTEM and SYSAUX tablespaces are created as
smallfile tablespaces.
 Bigfile tablespaces contain tablespaces made up of single
large files rather than numerous smaller ones.
 With Bigfile tablespaces database can scale up to 8
exabytes in size.
 Oracle Data Blocks
 At the finest level of granularity, data is stored in
data blocks.
 One data block corresponds to a specific number
of bytes of physical database space on disk.
 The standard block size is specified by the
DB_BLOCK_SIZE initialization parameter.
 In addition, you can specify up to five other block
sizes.
 A database uses and allocates free database space
in Oracle data blocks.
 Extents

 The next level of logical database space is

an extent.
 An extent is a specific number of
contiguous data blocks, obtained in a single
allocation, used to store a specific type of
information.
 Segments
 Above extents, the level of logical database
storage is a segment.
 A segment is a set of extents allocated for a certain
logical structure. The following table describes the
different types of segments.
 Data segment
 Index segment
 Temporary segment
 Rollback segment
Graphically
 Database Storage Hierarchy
Database

Tablespace Data file

Logical Segment Physical

Extent

Oracle
OS block
block
Overview of Schemas and Common
Schema Objects
 Tables
 Indexes
 Views
 Clusters
 Synonyms
 Tables
 Tables are the basic unit of data storage in
an Oracle database.
 Each table has column and row.
 A table that has an employee database, for
example, can have a column called
employee number, and each row in that
column is an employee's number.
 Indexes
 Optional structures associated with tables.
 Can be created to increase the performance of data retrieval.
 When processing a request, Oracle can use some or all of the
available indexes to locate the requested rows efficiently.
 Useful when applications frequently query a table for a range
of rows
 Indexes are created on one or more columns of a table.
 After it is created, it is automatically maintained and used by
Oracle.
 Changes to table data (such as adding new rows, updating
rows, or deleting rows) are automatically incorporated into
all relevant indexes.
 Views
 Customized presentations of data in one or more tables or other
views.
 Can also be considered a stored query.
 Do not actually contain data.
 They derive their data from the base tables on which they are
based.
 Can be queried, updated, inserted into, and deleted from, with
some restrictions.
 All operations performed on a view actually affect the base
tables of the view.
 Provide an additional level of table security by restricting access
to a predetermined set of rows and columns of a table.
 Hide data complexity and store complex queries.
 Clusters
 Clusters are groups of one or more tables
physically stored together because they share
common columns and are often used together.
Because related rows are physically stored
together, disk access time improves.
 Do not affect application design.
 Whether a table is part of a cluster is transparent to
users and to applications.
 Data stored in a clustered table is accessed by SQL
in the same way as data stored in a non clustered
table.
 Synonyms
 A synonym is an alias for any table, view,
materialized view, sequence, procedure,
function, package, type, Java class schema
object, user-defined object type, or another
synonym.
 Because a synonym is simply an alias, it
requires no storage other than its definition
in the data dictionary.
 Overview of the Oracle Data
Dictionary
 It is a set of tables and views that are used as a
read-only reference about the database.
 Stores information about both the logical and
physical structure of the database.
 A data dictionary also stores
The valid users of an Oracle database
Information about integrity constraints defined
for tables in the database
The amount of space allocated for a schema
object and how much of it is in use
 Overview of the Oracle Data
Dictionary
 Automatically updated by Oracle when the
structure of the database is altered.
 The database relies on the data dictionary to
record, verify, and conduct ongoing work.
Overview of the Oracle Instance
 The combination of
Memory buffers
Background processes
 Instance Memory Structures
 System Global Area
Database Buffer Cache
Redo Log Buffer
Shared Pool
Large pool
Java pool
 Program Global Area
 System Global Area
SGA is a shared memory region 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 shuts down.
Each instance has its own SGA
Users connected to database share the data in the SGA.
Instance

Shared pool SGA

Library
cache Database Redo log
Data Dict. buffer cache buffer cache
cache
 System Global Area
Database Buffer Cache
 The database buffer cache stores copies of data
blocks that have been retrieved from the data files.
 It enables great performance gains when you
obtain and update data.
 It is managed through a least recently used (LRU)
algorithm.
 DB_CACHE_SIZE
determines the
size of database buffer cache Database buffer
cache
 System Global Area
Redo Log Buffer
 The redo log buffer cache records all changes made to the
database data blocks.
 Its primary purpose is recovery.
 Changes recorded within are called redo entries.
 Redo entries contain information to reconstruct or redo
changes.
 Size is defined by LOG_BUFFER.

Redo log
buffer cache
 System Global Area
Shared Pool
 Used to store the most recently executed SQL statements
and the most recently used data definitions.
 It consists of two key performance-related memory
structures
 Library cache
 Data dictionary cache
 Sized by the parameter SHARED_POOL_SIZE
 ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;
Shared pool
Library
cache
Data dictionary
cache
 System Global Area
Library cache
 The library cache stores information about the most
recently used SQL and PL/SQL statements.
 The library cache enables the sharing of commonly used
statements
 Is managed by a least recently used (LRU) algorithm
 Consists of two structures:
 Shared SQL area
 Shared PL/SQL area
 Has its size determined by the shared pool sizing
 System Global Area
Data dictionary cache
 The data dictionary cache is a collection of the most
recently used definitions in the database.
 It includes information about database files, tables,
indexes, columns, users, privileges, and other database
objects.
 During the parse phase, the server process looks at the data
dictionary for information to resolve object names and
validate access.
 Caching the data dictionary information into memory
improves response time on queries.
 Size is determined by the shared pool sizing.
 System Global Area
Large pool
 The large pool is an optional area of memory in the SGA
configured only in a shared server environment.
 It relieves the burden placed on the shared pool.
 This configured memory area is used for session memory
(UGA), I/O slaves, and backup and restore operations.
 Unlike the shared pool, the large pool does not use an LRU
list.
 Sized by LARGE_POOL_SIZE.
 ALTER SYSTEM SET LARGE_POOL_SIZE = 64M;
 System Global Area
Java pool

 The Java pool services the parsing

requirements for Java commands.
Required if installing and using Java.
It is stored much the same way as PL/SQL in
database tables.
It is sized by the JAVA_POOL_SIZE
parameter.
 Program Global Area (PGA)
 The PGA is memory reserved for each user process that
connects to an Oracle database.

PGA Dedicated server Shared server

Server
process Session
Stack information Stack sort area, cursor
space sort area, cursor space information
information

User SGA SGA

process Session
information

Shared SQL areas Shared SQL areas

 User Process
 A user process is a program that requests interaction with
the Oracle server.
 It must first establish a connection.
 It does not interact directly with the Oracle server.

Server
process
User
process
Connection
established

Database user
 Server Process
 A server process is a program that directly interacts with
the Oracle server.
 It fulfills calls generated and returns results.
 Can be dedicated or shared server.

Server
process
Connection
established Session created
User Oracle server
process

Database user
 Oracle Background Processes
 The relationship between the physical and memory
structures is maintained and enforced by Oracle’s
background processes.
 Mandatory background processes
DBWn PMON CKPT
LGWR SMON RECO
 Optional background processes
 ARCn LMON Snnn
 QMNn LMDn
 CJQ0 Pnnn
 LCKn Dnnn
Database Writer (DBWn)
DBWn writes when:
Instance  Checkpoint
SGA  Dirty buffers threshold reached
 No free buffers
Database  Timeout
buffer  RAC ping request
cache  Tablespace offline
 Tablespace read only
DBWn  Table DROP or TRUNCATE
 Tablespace BEGIN BACKUP

Data Control Redo log

files files files

Database
 Log Writer (LGWR)
Instance
SGA
LGWR writes:
 At commit
Redo log
 When one-third full
buffer
 When there is 1 MB of
redo
DBWn LGWR
 Every 3 seconds
 Before DBWn writes

Data Control Redo log

files files files

Database
 System Monitor (SMON)
Instance
Responsibilities:
SGA
 Instance recovery:
 Rolls forward changes in
the redo logs
 Opens the database for
user access
 Rolls back uncommitted
transactions
SMON
 Coalesces free space ever 3 sec
 Deallocates temporary
segments
Data Control Redo log
files files files

Database
 Process Monitor (PMON)
Instance
SGA Cleans up after
failed processes by:
 Rolling back the transaction
 Releasing locks
 Releasing other resources
 Restarts dead dispatchers
PMON

PGA area
Checkpoint (CKPT)
Instance
SGA Responsible for:
 Signaling DBWn at
Redo Log checkpoints
Buffer  Updating datafile
headers with
checkpoint
DBWn LGWR
DWW0 CKPT
information
 Updating control files
with checkpoint
Data
files
Control
files
Redo log
files
information
 Archiver (ARCn)
 Optional background process
 Automatically archives online redo logs when
ARCHIVELOG mode is set
 Preserves the record of all changes made to the database

ARCn
ARCn
Data Control Redo log Archived
files files files Redo log
files
 Starting Up a Database
OPEN
STARTUP All files opened as
described by the control
MOUNT file for this instance

Control file
opened for this
NOMOUNT instance

Instance
started – pfile
SHUTDOWN or spfile used
SHUTDOWN
Shutting Down the Database
Shutdown Mode A I T N

Allow new connections x x x x

Wait until current sessions end x x x o

Wait until current transactions end x x o o

Force a checkpoint and close files x o o o

Shutdown Mode:
• NORMAL x NO
• TRANSACTIONAL
• IMMEDIATE
o YES
• ABORT
 Shutdown Options
During a
Shutdown Normal,
Shutdown Transactional
On the way down: or On the way up:
Shutdown Immediate

• Database buffer • No instance

cache written to recovery
the data files
• Uncommitted
changes rolled
back
• Resources
released.

Consistent Database
(clean database)
 Shutdown Options
During a On the way up:
Shutdown Abort
or
On the way down: Instance Failure • Redo logs used to
or
Startup Force reapply changes
• Modified buffers • Undo segments
are not written to used to roll back
the data files uncommitted
• Uncommitted changes
changes are not • Resources
rolled back released

Inconsistent Database
(dirty database)
 How Oracle Works
1. An instance has started on the computer running Oracle
2. A computer running an application (a local computer or
client workstation) runs the application in a user process.
The client application attempts to establish a connection to
the server using the proper Oracle Net Services driver.
3. The server is running the proper Oracle Net Services
driver. The server detects the connection request from the
application and creates a dedicated server process on
behalf of the user process.
4. The user runs a SQL statement and commits the
transaction.
 How Oracle Works
5. The server process receives the statement and checks the
shared pool for any shared SQL area that contains a similar
SQL statement. If a shared SQL area is found, then the server
process checks the user's access privileges to the requested
data, and the previously existing shared SQL area is used to
process the statement. If not, then a new shared SQL area is
allocated for the statement, so it can be parsed and processed.
6. The server process retrieves any necessary data values from
the actual datafile (table) or those stored in the SGA.
7. The server process modifies data in the system global area.
The DBWn process writes modified blocks permanently to
disk when doing so is efficient. Because the transaction is
committed, the LGWR process immediately records the
transaction in the redo log file.
 How Oracle Works
8. If the transaction is successful, then the server
process sends a message across the network to
the application. If it is not successful, then an
error message is transmitted.
9. Throughout this entire procedure, the other
background processes run, watching for
conditions that require intervention. In addition,
the database server manages other users'
transactions and prevents contention between
transactions that request the same data.
Q&A
Thank You