Oracle Database 12c Architecture

RDBMS
• Relational database
management system
(RDBMS)
• Most Common
• Different RDBMS have
different architecture
• Basic storage of data in
RDBMS is a table.
Relations are
implemented in tables,
where data is stored in
rows and columns.
 Oracle Database 12c Objects
Object Type Description
Table Basic form of data storage. A table has columns and stores rows of
data.
View A stored query. No data-storage space is occupied for view data.
Index An optional structure that is useful for fetching data faster.
Materialized view Used to summarize and store data. They are similar to views but take
up storage space to store data
Index-organized Stores the table data along with the index, instead of storing table and
table index separately
Cluster A group of tables sharing a common column. The cluster stores the
rows of the tables together with the common columns stored once.
Constraint A stored rule to enforce data integrity.
Sequence A sequence provides a mechanism for the continuous
generation of numbers.
Synonym Alias for a database schema object
 Oracle Database 12c Objects
Object Type Description
Trigger A PL/SQL program unit that is executed
when an event occurs.
Stored function A PL/SQL programs that can be used to create user-defined functions
to return a value.
Stored procedure A PL/SQL programs to define a business process
Package A collection of procedures, functions, and other program constructs.

Java Stored Java procedures can be created in Oracle to define business

processes.
Database link Database links are used to communicate between databases
to share data.
.Net Extension Stored .Net procedures and functions can be created in Oracle on
windows platforms only .

Use SQL to create database objects and to interact with application data
Interacting with Oracle Database 12c
• SQL is the language used to interact with
Oracle Database 12c. common tools for DBA
to administer Oracle Database 12c :
– SQL*Plus command-line interface utility
– SQL Developer : a GUI tool to explore and
manage the database using predefined menu
actions and SQL statements
– Oracle Enterprise Manager Database Express 12c,
a GUI tool for database administration
– Oracle Grid Control
Interacting with Oracle Database 12c
• SQL*Plus is the primary tool for an Oracle DBA
to administer the database using SQL
commands.
• You can run SQL statements, you must connect
to Oracle Database 12c.
• Start SQL*Plus from a Windows command
prompt using SQLPLUS.EXE
$ORACLE_HOME/bin/sqlplus executable on the
Unix/Linux platform.
• connecting to SQL*Plus from a Linux workstation
-
 SQL*Plus
• SQL*Plus is the
primary tool for an
Oracle DBA to
administer the
database using
SQLcommands.

• Before you can run

SQL statements, you
must connect to
Oracle Database 12c.

• start SQL*Plus from a

Windows CMD using
the SQLPLUS.EXE or
using
$ORACLE_HOME/bin/
sqlplus executable on
Unix/Linux platform.
 FREE Oracle Database IDE/GUI
 Windows, OS X, *NIX
 More than 4,100,000 users worldwide
 My Oracle Support available via your
DB license
 Oracle SQL Developer
http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

• FREE Oracle Database IDE/GUI

– Allows database users and administrators to do their database tasks easy with GUI .
– Support Oracle DB 10g, 11g, and 12c
– Run on any operating system that supports Java : Windows, OS X, *NIX
– More than 4,100,000 users worldwide

• For Developer
Provides powerful editors for working with SQL, PL/SQL, Stored Java Procedures, and XML. Run
queries, generate execution plans, export data to the desired format (XML, Excel, HTML, PDF,
etc.), execute, debug, test, and document your database programs, and much more with SQL
Developer.
• For DBA
Provided database administrators a set of interfaces for their most critical tasks :
– Recovery Manager (RMAN)
– Oracle Auditing
– User and Role management
– Storage management
– Resource Manager
– Diagnostic Pack features

• For the Application Architect & Data Modeler

– Logical, relational, physical, dimensional modeling
 Oracle SQL Developer
http://www.oracle.com/technetwork/developer-tools/sql-
developer/overview/index.html
 Oracle Enterprise Manager Express
• Enables you to perform
administrative tasks such
as managing user
security and managing
database memory and
storage.
• View performance and
status information about
your database.
Getting Started with Oracle
Enterprise Manager Express
SQL Developer Data Modeler
Included for free, also available as a separate download/program
SQL Developer Data Modeler
Strategy and Analysis

Import Models

Data Type

Domains
ERD DFD

Logical Multidimensional

Reporting
Database Design

Relational Star Schema Physical

 DBA Tasks
• Selecting the server hardware on which the database software will run
• Installing and configuring the Oracle Database 12c software on the
server hardware
• Deciding to use Oracle Database 12c Container or a traditional single
database non-Pluggable Database (PDB).
• Creating Oracle Database 12c database
• Creating and managing the tables and other objects used to manage the
application data
• Creating and managing database users
• Establishing reliable backup and recovery procedure for the database
• Monitoring and tuning database performance
• Analyzing trends and forecasting resource and storage requirements
• Decide on Service Level Agreements (SLAs) and build High Availability
(HA) and Disaster Recovery Process (DRP)
 What Is a Schema?
• A schema can be related to a real person
– example : user of your HR database who with Login and password they use
to access DB.
– This user may or may not own any schema objects.

• Schema is a collection of objects

– DBAs\Developers often define a schema to represent a collection of objects
that are related to specific applications or tasks using a common schema
name.

– The schema is a logical collection of objects associated with an application

and is not tied to any specific user

– Example HR.EMPLOYEES , SALES.ORDERS tables

• The main difference is that users are the entities that perform work, and
schemas are the collections of objects on which users perform work.
 Oracle Database Storage Structures
• RDBMS is the independence of logical data structures such as
tables, views, and indexes from physical storage structures.
• physical and logical structures are separate, you can manage
physical storage of data without affecting access to logical
structures.
– EX : renaming a database file does not rename the tables
stored in it.
 Physical Storage Structures
• An Oracle database is a set of files that store Oracle data in persistent disk storage.
• A data file is a physical file on disk that was created by Oracle DB and contains data structures such as tables and
indexes.
• A temp file is a data file that belongs to a temporary tablespace.
• DB writes data to these files in an Oracle proprietary.
• Control files
• A root file that tracks the physical components of the database. information required to recover DB including
checkpoints (SCN) .
• Online redo log files
– Set of files containing records of changes made to data.
• A database instance is a set of memory structures that manage database files.
 Logical Storage Structures
• Oracle Database allocates logical space
for all data in database.
• Logical units of database space allocation
are data blocks, extents, segments, and
tablespaces.
• At a physical level, the data is stored in
data files on disk.
• The data in the data files is stored in
operating system blocks.
• Figure 12-1 is an entity-relationship
Relationships of
diagram for physical and logical storage. logical storage
The objects and
physical
• crow's foot notation represents a one-to- storage
many relationship..
 Logical Storage Hierarchy
• At the finest level of granularity, Oracle DB stores data in data blocks.
• One Logical data block corresponds to a specific number of bytes of physical disk space, for example, 2 KB.
• Data blocks are the smallest units of storage that Oracle Database can use or allocate.
• An extent is a set of logically contiguous data blocks allocated for storing a specific type of information Ex :the 24
KB extent has 12 data blocks, while the 72 KB extent has 36 data blocks.
• A segment is a set of extents allocated for a specific database object, such as a table. Ex : Data for the employees
table is stored in its own data segment, whereas each index for employees is stored in its own index segment.
• Every database object that consumes storage consists of a single segment.
• Each segment belongs to one and only one tablespace. Thus, all extents for a segment are stored in the same
tablespace.
• Within a tablespace, a segment can include extents from multiple data files
 Overview of Data Files
• Each tablespace consists of one
or more data files .
• The data for a database is
collectively stored in the data
files .
• A segment can span one or
more data files, but it cannot
span multiple tablespaces.
• A database must have the
SYSTEM and SYSAUX
tablespaces.
• SYSTEM tablespace contains
the data dictionary, a set of
tables that contains database
metadata.
• Database also has an undo
tablespace and a temporary
tablespace (TEMP).
 Data Files Structure
• The data file header contains metadata about the data file such as its size and checkpoint SCN.
• Each header contains an absolute file number, which uniquely identifies the data file within the
database, and a relative file number, which uniquely identifies a data file within a tablespace.
• When Oracle Database first creates a data file, the allocated disk space is formatted but contains
no user data. However, the database reserves the space to hold the data for future segments of
the associated tablespace.
• As the data grows in a tablespace, Oracle Database uses the free space in the data files to allocate
extents for segment .
 Online Redo Log Switches
• log writer is the background
process responsible for writing
transaction information from redo
log buffer (in the SGA) to the Reuse of Online
Redo Log Files
online redo log files (on disk).
• Log writer flushes the contents of
the redo log buffer when any of
thefollowing are true:
– A COMMIT is issued. Multiple Copies of

– A log switch occurs. Online Redo Log

Files

– Three seconds go by.

– Redo log buffer is one-third
full.
– Redo log buffer fills to one
megabyte.
 Reuse of Online Redo Log
• At time 1, Block A is
read from Data File AA
into the buffer cache
and modified.
• At time 2 the redo-
change vector
information (how the
block changed) is
written to the log
buffer.
• At time 3 the log-writer
process writes the
Block Achange-vector
information to online
redo log 1.
• At time 4 a log switch
occurs, and online
redo log 2 becomes
the current online redo
log Reuse of Online Redo Log Files
 Data Blocks and Operating System Blocks
• An operating system block is
the minimum unit of data that
the OS can read or write .

• Oracle block is a logical

storage structure whose size
and structure are not known to
OS .

• The database requests data in

multiples of data blocks, not
OS blocks.

• Applications do not need to

determine the physical Reuse of Online Redo Log Files
addresses of data on disk.

• Database data can be striped

or mirrored on multiple physical
disks .
 Data Block Format
• Block header
– This part contains general
information about the block,
including disk address and segment
type.
– For blocks that are transaction-
managed, the block header contains
active and historical transaction
information.
– A transaction entry is required for
every transaction that updates the
block.
• Table directory
– For a heap-organized table
contains metadata about tables
whose rows are stored in this
block
• Row directory
– For a heap-organized table,
this directory describes the
location of rows in the data
portion of the block.
Oracle database logical and physical structure relationships
Relationships of commonly used data dictionary views
 Database Architecture : Databases and Instances
• Instance memory Structures (RAM) and
Background processes (CPU) that uses the
physical components to manipulate and
retrieve data.

• Database set of physical files (Datafiles) saved

on the disk that store information. created by
the CREATE DATABASE statement.

 One instance communicates with one database.

 The host machine is where users and applications connect

and interact.
 If the machine goes down for some reason, DB will be
unavailable. The solution : Real Application Clusters
(RAC) .
Database Architecture : Databases and Instances
Rules :
– An instance can exist without a database.

– Database can exist without an instance but would be

useless
– Every running Oracle database is associated with at
least one Oracle database instance.

– An instance can access only one database. When you

start your instance, the next step is to mount that
instance to a database. An instance can mount only one
database at a time.

– You can set up multiple instances to access the same

set of files or one database. Clustering is the basis for
the Oracle Real Application Clusters feature .

– Reason to distinguish between Instance and Database

1. Single Instance VS RAC
2. Relationship through separation .
3. Other RDBMS like SQL Server , Sybase ,DB2 terms
means different things
 Oracle Database 12c Architecture
• DB components : memory
structures, process structures
, and storage structures.
• Process and memory
structures together are called
an instance;

• Storage structure is called a

database.

• Instance and DB are called an

Oracle server.
• One instance communicates
with one database.

• The host machine is where

users and applications
connect and interact.

• If the machine goes down for

some reason, DB will be
unavailable. Solution : Real
Application Clusters (RAC)
 Oracle Database 12c Architecture
• Each Oracle database
consists of several
schemas that reside in
tablespaces.
• Tablespace is a logical
storage structure at the
highest level in the
database.
• Each tablespace consists of
one or more data files.

• The database has user data

and overhead like
database dictionary,
memory, control files,
archived log files, flashback
files, etc.
 Oracle Instance
• A database instance is a set of
memory structures that manage
database files.
• When an instance is started,
Oracle Database allocates a
memory area called the system
global area (SGA) and starts one
or more background processes.

SGA serves various purpose:

 Maintaining internal data
structures that many processes
and threads access Concurrently .
 Caching data blocks read from
disk
 Buffering redo data before
writing it to the online redo log
files
 Storing SQL execution plans
 Database Instance Configurations
• Single-instance configuration
– A one-to-one relationship exists between the database and a database instance.
• Oracle Real Application Clusters (Oracle RAC) configuration
– A one-to-many relationship exists between the database and database instances
 Phases of Oracle startup
$ sqlplus / as sysdba
SQL> startup;
• When you issue a STARTUP statement ORACLE instance started.
without any parameters, Oracle Total System Global Area 313159680 bytes
automatically steps through the three Fixed Size 2259912 bytes
startup phases (nomount, mount, open) Variable Size 230687800 bytes
– Most Cases Database Buffers 75497472 bytes
Redo Buffers 4714496 bytes
Database mounted.
Database opened.
Oracle database server- Real Application Cluster
• More than one
instance communicates
to a single database .

• If an instance fails, the

remaining instances in
the RAC pool remain
open and active.
Connections from
failed instances can be
failed-over to active
instances.

• Oracle manages the

connection load
balancing and failover
automatically.
Oracle RAC architecture and components
 Basic Memory Structures
• System global area (SGA)
– Data and control information for one
Oracle DB . All server and background
processes share the SGA. Ex: cached
data blocks and shared SQL areas.
• Program global area (PGA)
– A nonshared memory region that
contains data and control information
exclusively for use by an Oracle
process. Oracle Database creates the
PGA when an Oracle process starts.
One PGA exists for each server process
and background process. The
collection of individual PGAs is
instance PGA.
• User global area (UGA)
– memory associated with a user
session.
• Software code areas used to store
code that is being run or can be
run.
 Types of Processes
• A client process runs the
application or Oracle tool
code.
• An Oracle process
– A background process perform
maintenance tasks like :
instance recovery
– A server process performs work
based on a client request :
processes parse SQL query
• A system global area (SGA)
and background processes
using dedicated server
connections.
• For each user connection, a
client process runs the
application
– Each client process is
associated with its own server
process , which has its own
program global area (PGA)
 User and Server Processes
• At the user level, two types of processes allow a user to interact with instance and
DB : the user process and the server process.
• When a user runs an application, Oracle starts a user process to support the user’s
connection to the instance
– Either on the user’s own computer or on the middle-tier application server
depending on architecture of the application
• User process then initiates a connection to the instance. The process of initiating and
maintaining communication between the user process and the instance a connection.
• Once the connection is made, the user establishes a session in the instance.
• After establishing a session, each user starts a server process on the host server itself.
• Server process is responsible for performing the tasks that actually allow the user to
interact with the database.
• Server processes are allowed to interact with the instance, but not the user process
directly.
• Examples: sending SQL statements to DB , retrieving needed data from the
database’s physical files, and returning that data to the user.
• Each user process connects to one and only one server process. In some Oracle
configurations, multiple user processes can share a single server process.
 User and Server Processes
• Additional memory structure : program global area (PGA) is also created for each
server process.
• PGA stores user-specific session information like bind variables and session
variables.
• Every server process on the server has a PGA memory area.
 User and Server Processes
• PGA memory is not shared. Each server process has a PGA associated with it and is
exclusive.
• As a DBA, you set the total memory that can be allocated to all the PGA memory
allocated to all server and background processes.
• Components of PGA :
1. SQL Work Area Area used for memory-intensive
operations such as sorting or building a hash table during
join operations.
2. Private SQL Area Holds information about SQL statement
and bind variable values.
• The PGA can be configured to manage automatically by setting the database
parameter PGA_AGGREGATE_TARGET. Oracle then contains the total amount of
PGA memory allocated across all database server processes and background
processes within this target.
 User and Server Processes
Example On a Unix system :
1. User initiates SQL*Plus to connect to Oracle database. User process with process
ID 10704 by david.
2. This starts another server process that connects to the instance with process ID
10706 owned by database server user oracle.

$ ps -ef |grep sqlplus | grep -v grep

david 10704 10511 0 03:51 pts/2 00:00:00 sqlplus

$ ps -ef |grep 10604 | grep -v grep

david 10704 10511 0 03:51 pts/2 00:00:00 sqlplus

oracle 10706 10704 0 03:52 ? 00:00:00 oracleC12DB1

(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=tcp)))
 Connections and Sessions
• A database connection is a physical communication pathway between
a client process and a database instance.
• A database session is a logical entity in the database instance memory
that represents the state of a current user login to a database.
• session lasts from the time the user is authenticated by the database
until the time the user disconnects or exits the database application.
• A single connection can have 0, 1, or more sessions established on it.
• The sessions are independent: a commit in one session does not affect
transactions in other sessions.
Oracle Database 12c Architecture
 Oracle Database 12c Architecture:
Major background processes

CKPT: The checkpoint process writes checkpoint information to the control

DBWn:
ARCn:
files The
The archiver
and data database
file copies
writerthe
headers. writes
content
blocks
of from
online
the
redo
database
logs tobuffer
archive
cache
redoto
log
theLGWR:
files. The log writer writes redo information from the log buffer to the online
data files.
redo logs.
 Oracle Database 12c Architecture
major background processes
• DBWn: The database writer writes blocks from the database buffer cache to the data
files.
• CKPT: The checkpoint process writes checkpoint information to the control files and
data file headers.
• LGWR: The log writer writes redo information from the log buffer to the online redo
logs.
• ARCn: The archiver copies the content of online redo logs to archive redo log files.
• RVWR: The recovery writer maintains before images of blocks in the fast recovery
area.
• MMON: The manageability monitor process gathers automatic workload repository
statistics.
• MMNL: The manageability monitor lite process writes statistics from the active
session history buffer to disk.
• SMON: The system monitor performs system level clean-up operations, including
instance recovery in the event of a failed instance, coalescing free space, and
cleaning up temporary space.
• PMON: The process monitor cleans up abnormally terminated database connections
and also automatically registers a database instance with the listener process.
• RECO: The recoverer process automatically resolves failed distributed transactions.
 Oracle Database 12c Architecture
• Communication with the database is initiated through a sqlplus user process.
Typically, the user process connects to the database over the network. This
requires that you configure and start a listener process.
• listener process hands off incoming connection requests to an Oracle server
process, which handles all subsequent communication with the client process.
• The instance consists of memory structures and background processes.
• When the instance starts, it reads the parameter file, which helps establish the size
of the memory processes and other characteristics of the instance.
• When starting a database, the instance goes through three phases: nomount
(instance started), mount (control files opened), and open (data files and online
redo logs opened).
• The number of background processes varies by database version (more than 300
in the latest version of Oracle).
• You can view the names and descriptions of the processes via this query:
• SQL> select name, description from v$bgprocess;
 Checkpoint Process (CKPT)
• Mandatory Background Processes
• Updates the control file and data
file headers with checkpoint
information and signals DBW to
write blocks to disk.
• Checkpoint information includes
the checkpoint position, SCN,
location in online redo log to begin
recovery, and so on
• CKPT does not write data blocks to
data files or redo blocks to online
redo log files
How Many Databases on One Server?
• Architecture with one server per database
– Profitable for the hardware vendor but in many
environments isn’t an economical use of
resources.
 How Many Databases on One Server?
• Multiple databases sharing one set of Oracle binaries on a server
– If you have enough memory, CPU, and disk resources consider creating multiple
databases on one server.
– You can create a new installation of the Oracle binaries for each database or have
multiple databases share one set of Oracle binaries.
– If you have requirements for different versions of Oracle binaries, you must have
multiple Oracle homes .
 How Many Databases on One Server?
• One database used by multiple applications and users
– If you don’t have the CPU, memory, or disk resources to create multiple databases
on one server
– To save Oracle License because it is usually per cpu core .
– be careful not to use public synonyms, because there may be collisions between
applications.
– It’s typical to create different schemas and tablespaces to be used by different
applications in such environments.