basic oracle
TRANSCRIPT
-
8/11/2019 basic oracle
1/52
ORACLE
INSTANCE
ARCHITECTURE
-
8/11/2019 basic oracle
2/52
-
8/11/2019 basic oracle
3/52
INTRODUCTION TO THE ORACLE DATABASE
INSTANCE
A database instance is a set of memory
structures that manage database files a a ase s a se o p ys ca es on s
The instance manages its associated data and
Every running Oracle database is associated
Instance can exist without Database
-
8/11/2019 basic oracle
4/52
DATABASE INSTANCE STRUCTURE
What happens When an instance is started, Oracle Database allocates a memory area called the
system global area (SGA)
.
serves various purposes, including the following:
-
8/11/2019 basic oracle
5/52
DATABASE INSTANCE STRUCTURE
What does SGA does
Maintaining internal data structures that are accessed
Caching data blocks read from disk
Bufferin redo data before writin it to the online redo
log files Storing SQL execution plans
-
8/11/2019 basic oracle
6/52
-
8/11/2019 basic oracle
7/52
DATABASE INSTANCE CONFIGURATIONS
You can run Oracle Database in either of the
following mutually exclusive configurations: -
A one-to-one relationship exists between the database and an
instance
Oracle Real Application Clusters (Oracle RAC)
one-to-many relationship exists between the database andinstances
-
8/11/2019 basic oracle
8/52
-
8/11/2019 basic oracle
9/52
DATABASE INSTANCE CONFIGURATIONS
Whether in a single-instance or Oracle RAC
configuration, a database instance is associated.
(associate the instance with) one database, but not
mount two databases simultaneousl with the same
instance
Multiple instances can run concurrently on the
same computer/ Server, each accessing its own
database
-
8/11/2019 basic oracle
10/52
DATABASE INSTANCE CONFIGURATIONS
Duration of an Instance
An instance begins when it is created with the
Durin this eriod an instance can associate itself with
one and only one database
-
8/11/2019 basic oracle
11/52
INSTANCE AND DATABASE STARTUP
-
8/11/2019 basic oracle
12/52
DATABASE AND INSTANCE SHUTDOWN
-
8/11/2019 basic oracle
13/52
SHUTDOWN MODES
-
8/11/2019 basic oracle
14/52
CHECKPOINTS
A checkpoint is a crucial mechanism in
consistent database shutdowns, instance,
Reduce the time required for recovery in case of an
instance or media failure
Ensure that dirty buffers in the buffer cache are written
to disk regularly Ensure that all committed data is written to disk during a
consistent shutdown
-
8/11/2019 basic oracle
15/52
INSTANCE RECOVERY
Instance recovery is the process of applying
records in the online redo log to data files to
checkpoint
Instance recovery occurs automatically when an
administrator attem ts to o en a database that was
previously shut down inconsistently.
Instance recovery ensures that the database is in a
consistent state after an instance failure.
-
8/11/2019 basic oracle
16/52
WHEN ORACLE DATABASE PERFORMS
INSTANCE RECOVERY
e er ns ance recovery s requ re epen s onthe state of the redo threads.
A redo thread is marked open in the control filewhen a database instance opens in read/write
mode, and is marked closed when the instance isshut down consistently.
The database opens for the first time after the
-of an Oracle RAC database
Some but not all instances of an Oracle RACdatabase fail
-
8/11/2019 basic oracle
17/52
PARAMETER FILES
To start a database instance, Oracle Database
must read either a server parameter file, which is,
file, which is a legacy implementation.
These files contain a list of configuration
arameters
-
8/11/2019 basic oracle
18/52
PARAMETER FILES
Server Parameter Files
A server parameter file is a repository for initialization.
Characteristics of Server Parameter file.
The server parameter file is written to and read only byOracle Database
The server parameter file is binary file
-
8/11/2019 basic oracle
19/52
PARAMETER FILES
Text Initialization Parameter Files
A text initialization parameter file is a text file that.
.
Reside on the same host
A text initialization parameter file is text-based, not
binary
Oracle Database can read but not write to the text
-
8/11/2019 basic oracle
20/52
-
8/11/2019 basic oracle
21/52
ORACLE DATABASE MEMORY STRUCTURES
When an instance is started, Oracle Database
allocates a memory area and starts background.
The memory area stores information such as the
Program code
Information about each connected session
Information needed during program execution
Information such as lock data that is shared andcommunicated among processes
Cached data, such as data blocks and redo records,
-
8/11/2019 basic oracle
22/52
BASIC MEMORY STRUCTURES
ys em g o a area
The SGA is a group of shared memory structures,known as SGA com onents that contain data andcontrol information for one Oracle Databaseinstance
A PGA is a nonshared memory region that contains dataand control information exclusively for use by an Oracleprocess
User Global Area (UGA)
.
Software code areas
Software code areas are ortions of memor used tostore code that is being run or can be run.
-
8/11/2019 basic oracle
23/52
DATABASE MEMORY MANAGEMENT
Memory management involves maintaining optimal
sizes for the Oracle instance memory structures as
Automatic memory management
Partially automated
Manual memory management
Instead of setting the total memory size individually set SGA
and PGA memmory.
-
8/11/2019 basic oracle
24/52
USER GLOBAL AREA
The UGA is session memory
Memory allocated for session variables ogon n orma on
P/L SQL Package State.
-
8/11/2019 basic oracle
25/52
PROGRAM GLOBAL AREA
The PGA is memory specific to an operating
process or thread that is not shared by other
- ,
SGA.
-
8/11/2019 basic oracle
26/52
CONTENTS OF THE PGA
-
8/11/2019 basic oracle
27/52
CONTENTS OF THE PGA
Private SQL Area
The run-time area
The ersistent area
This area contains variable valuesA variable value is supplied to a SQL statement at run time
SQL Work Areas A work area is a private allocation of PGA memory
used for memory-intensive operations.
-
8/11/2019 basic oracle
28/52
SYSTEM GLOBAL AREA
The SGA is a read/write memory area that, along
with the Oracle background processes, make up a
can read information in the instance SGA
Each database instance has its own SGA.
-
8/11/2019 basic oracle
29/52
-
8/11/2019 basic oracle
30/52
SGA COMPONENTS
The most important SGA components are the
following
Redo Log Buffer
Large Pool
Java Pool
Fixed SGA
-
8/11/2019 basic oracle
31/52
DATABASE BUFFER CACHE
The database buffer cache, also called the buffer
cache, is the memory area that stores copies of
.
Why Buffer Cache
Keep frequently accessed blocks in the buffer cache
and write infrequently accessed blocks to disk
-
8/11/2019 basic oracle
32/52
BUFFER STATES
The database uses internal algorithms to manage
buffers in the cache
u er can e n any o e o ow ng mu ua y
exclusive states:
The buffer is available for use because it has never been usedor is currently unused
Clean
This buffer was used earlier and now contains a read-
consistent version of a block as of a point in time. Dirty
The buffer contain modified data that has not yet been written
-
8/11/2019 basic oracle
33/52
-
8/11/2019 basic oracle
34/52
REDO LOG BUFFER
The redo log buffer is a circular buffer in the SGA
that stores redo entries describing changes made
.
reconstruct, or redo, changes made to the databaseb DML or DDL o erations.
to reconstruct lost changes.
-
8/11/2019 basic oracle
35/52
-
8/11/2019 basic oracle
36/52
SHARED POOL
The shared pool caches various types of
program data
System Parameters
.
-
8/11/2019 basic oracle
37/52
LARGE POOL
The large pool is an optional memory area
intended for memory allocations that are larger
with multiple databases
Message buffers used in the parallel execution of
statements
Buffers for Recovery Manager (RMAN)
-
8/11/2019 basic oracle
38/52
JAVA POOL
The Java pool is an area of memory that stores all
session-specific Java code and data within the Java
.
-
8/11/2019 basic oracle
39/52
FIXED SGA
The fixed SGA is an internal housekeeping area
General information about the state of the database and
the instance, which the background processes need to
access
Information communicated between processes, such as
information about locks
-
8/11/2019 basic oracle
40/52
-
8/11/2019 basic oracle
41/52
INTRODUCTION TO PROCESSES
A process is a mechanism in an operating
system that can run a series of steps
All connected Oracle Database users must run the
Application or Oracle Database utility
A rocess normall runs in its own rivate memorarea
-
8/11/2019 basic oracle
42/52
TYPES OF PROCESSES
Client processes
run the application
rac e oo co e
Oracle processes
Background processes start with the database instance
perform maintenance tasks (instance recovery, cleaning up
processes , writing redo buffers to disk .)
perform additional tasks for a background or server process
-
8/11/2019 basic oracle
43/52
-
8/11/2019 basic oracle
44/52
CLIENT PROCESSES
When a user runs an application such as a Pro*C
program or SQL*Plus, the operating system creates
to run the user application.
The client application has Oracle Database librarieslinked into it that rovide the APIs re uired to
communicate with the database.
-
8/11/2019 basic oracle
45/52
CONNECTIONS AND SESSIONS
Connection
A connection is a physical communication
pa way e ween a c en process an a
database instance
A communication pathway is established using
mechanisms or network software
-
8/11/2019 basic oracle
46/52
CONNECTIONS AND SESSIONS
Session
A session is a logical entity in the database
ns ance memory a represen s e s a e o a
current user login to a database
, ,
sessions established on it
session does not affect transactions in other
sessions
-
8/11/2019 basic oracle
47/52
-
8/11/2019 basic oracle
48/52
SERVER PROCESSES
Oracle Database creates server processes to
an e e reques s o c en processes connec e
to the instance
A client process always communicates with a.
-
8/11/2019 basic oracle
49/52
DEDICATED SERVER PROCESSES
In dedicated server connections, the client
connection is associated with one and only one
Each client process communicates directly with its
This server process is dedicated to its clientrocess for the duration of the session.
-
8/11/2019 basic oracle
50/52
SHARED SERVER PROCESSES
In shared server connections, client applications
connect over a network to a dispatcher process, not
The dispatcher process receives requests from
queue in the large pool The first available shared server rocess takes the
request from the queue and processes it.
shared server place the result into the dispatcherresponse queue
-
8/11/2019 basic oracle
51/52
BACKGROUND PROCESSES
Mandatory Background Processes
Optional Background Processes
Slave Processes
-
8/11/2019 basic oracle
52/52
MANDATORY BACKGROUND PROCESSES
The mandatory background processes are present
in all typical database configurations
System Monitor Process (SMON)
Log Writer Process (LGWR) Checkpoint Process (CKPT)
Manageability Monitor Processes (MMON and MMNL)
Recoverer Process (RECO)