Please enable JavaScript.
Coggle requires JavaScript to display documents.
Chapter 2: EXPLORING THE DATABASE ARCHITECTURE (Describe the Process…
Chapter 2: EXPLORING THE DATABASE ARCHITECTURE
Describe the Single-Instance Architecture
Single-Instance Database Architecture
Consists
Memory structures
System Global Area(SGA)
Processes
Background
processes
4 interacting components
A
user
interacts with a
user process
.
A
user process
interacts with a
server process
.
Server processes are sometimes referred to
foreground
processes.
Associated with each server process is an area of
nonshareable memory
, called the
Program Global Area
, or PGA
A
server process
interacts with an
instance
.
An
instance
interacts with a
database
Distributed System Architecture
Real Application Cluster (RAC)
Multiple instances open one database
Streams
Multiple Oracle servers propagate transactions between each other
Data Guard
A primary database update a standby database
Physical standby
byte-for-byte identical with the primary
For the purpose of zero data loss
Logical standby
Same data as the primary, but possibly with different data structures
For query processing
Explain the Memory Structures of SGA
3 main data structures
The Log Buffer
Short-term
staging area for
change vectors
before they are
written
to the redo log on disk
Change vector
= Modification applied to something (executing DML generates change vectors applied to data)
The change vectors in the log buffer are written to disk in
very nearly real time
.
Cannot
create a log buffer
smaller
than the
default
Allocate and Resize
allocated at
instance startup time
.
resize must
restart
The Shared Pool
The Library Cache
Store
recently
executed
code
in its
parsed
form
Parsing is the
conversion of code
written by programmers into something
executable
.
The Data Dictionary Cache
Store
recently object definitions
The PL/SQL Area
Store PL/SQL objects in data dictionary (source code + compiled form)
The SQL Query and PL/SQL Function Result Cache
Sizing the Shared Pool
Large enough to
cache all frequently
executed
code
and frequently
needed object definitions
(library cache + data dictionary cache) but
not
so large that its cache statements that have
only been executed once
.
The Database Buffer Cache
Executing SQL process via the database buffer cache
Dirty buffer
Buffer storing a block whose image in a cache <> image on disk
A buffer will be clean when a block is first copied into it.
No correlation
between the
frequency of updates
to a buffer and when it gets
written back
to the datafiles.
Allocate and Resize
The database buffer cache is allocated at
instance startup time
.
>9i Resized up or down at
any time
Optional data structures
The Java Pool
The Streams Pool
The Large Pool
Describe the Process Structure
SMON, the System Monitor
Mounting
and
opening
a database
Database is opened -> Responsible for various housekeeping tasks (collating free space in datafiles)
PMON, the Process Monitor
Monitor
all
server processes
Detect
any
problems
with sessions
DBWn, the Database Writer
Write
dirty buffers
from the
database buffer cache
to the
datafiles
As
little
as
possible
No free buffers
Too many dirty buffers
A three-second timeout
Every three seconds, DBWn will clean a few buffers.
A checkpoint
All dirty buffer are written
LGWR, the Log Writer
It is
impossible
to do
DML faster than LGWR
can write the change vectors to disk.
LGWR to flush the log buffer
A session issues a COMMIT -
Write-on-commit
DBWn is about to write dirty buffers
Log buffer is one-third full
Stream the contents of the log buffer to the online redo log files on disk in
very nearly real time
.
COMMIT ->
Write in real time
CKPT, the Checkpoint Process
Incremental checkpointing
MMON, the Manageability Monitor
Regularly
capture statistics
from the
SGA
(snapshot) and
write
them to the
data dictionary
Launch the
ADDM
- Automatic Database Diagnostic Monitor
MMNL, the Manageability Monitor Light
Process assist the MMON
MMAN, the Memory Manager
Automatic Management of Memory Allocations
ARCn, the Archiver
RECO, the Recoverer Process
Some Other Background Processes
CJQ0, J000
Manage jobs scheduled to run periodically
D000
Send SQL calls to shared server peocess
DBRM
Setting resource plans and other Resource Manager-related tasks
DIA0
Hang
detection and deadlock
resolution
DIAG
Perform
diagnostic dumps
and Execute
oradebug
command
FBAR
Archive the
historical rows
of tracked tables into flashback data archiver
PSP0
Create and Manage Oracle process
QMNC, Q000
Queue manager coordinator
SHAD
Server processes that support user sessions
SMCO, W000
Space management coordinator
VKTM
Keep track of time
Summarize the Storage Structures
The Physical Database Structures
The Controlfile
Multiplexing the control file
Create multiple copies
Number of copies
Minimum is 1
Maximum is 8
Best is 3
Require down time to add/remove copies
Damage to any controlfile copy will cause the database instance to terminate immediately
Contains
pointer to the rest of the database
datafiles
more recent archive log files (if databases is in archive log mode)
online redo log files
information required to maintain database integrity
Critical sequence numbers and timestamps
details of Recovery tool backups (if it's enabled)
The Online Redo Log Files
Store a continuous chain
in chronological order
of every
change vector
applied to the database
2 file types
Online redo log file
At least 2 online redo log files
Require at least 2 groups of at least one member each to function
One for accepting current change written by LGWR
One for backup (maybe archive by ARCn)
Minimum size: 50GB
Archive log file (Optional)
The Datafiles
Minimum: 2 datafiles
One for the
SYSTEM
tablespace (store the
data dictionary
)
One for the
SYSAUX
(store the data that is
auxiliary
to the data dictionary)
Size and number is unlimited
Consist of number of
OS blocks
, internally
Oracle blocks
Block
Header section
If used for table segment, contain row directory
If there is a transaction, contains row locking info
Data area
Should be backed up regularly
Other Database Files
The Instance Parameter File
The Password File
Archive Redo Log Files
Alert Log and Trace Files
The Logical Database Structures
The Data Dictionary
Data Dictionary = Metadata
Creating a data dictionary is part of the database
creation process
.
Set of views to query the dictionary
Any view prefixed
USER_
populated with rows describing objects
owned by the user querying the view
Any view prefixed
ALL_
populated with rows describing objects to which you
have access.
Any view prefixed
DBA_
rows for
every object
in the database