Oracle DBA Training : DBA Technologies

1
WELCOME TO DBA TECHNOLOGIES
ORACLE DATABASE ARCHITECTURE-2
By Mr. PRASAD MYNUMPATI
18
Years Real Time in INDIA-USA
www.dbatechnologies.net
www.dbatechnologies.net Building Career With
Passion
2
database Architecture USER TS USER tablespace
is to store data permanently Entire Application
data storage and retrieval from USER
tablespaces For load balancing purpose we use
different



tablespaces on different disks for INDEX
and DATA How many tablespaces for a database?
depends on design of the application and number
of applications

3
ORACLE OS comparison
FILE SYSTEM
TABLESPACE
File system Partitions (ext3)
Tablespaces (Undo, Temp and Permanent)
Mount file system (To read and write)
By default TS is mounted
Files Directories
Tables , Indexes (objects)
OS Block size
TS Block size (Default 8K)
Multi user access based on privileges
Multi users access by assigned quota
Backup FS
Backup tablespace
FS consistency (fsck)
Database level consistency (RMAN)
4
Server Storage configuration OS and ORACLE SW
is on Server Database is going to be on high end
storage NAS (Network area Storage)

SAN (Storage area Networks)
5
Identifying OS for ORACLE SW version What kind of
OS we need ?

Need to follow certification matrix oracle
metalink. www.metalink.oracle.com Need to choose
certified OS
from


Can we install multiple versions (7,8,8i,9i,10g,11
g) of Oracle SW server? Yes we can
on same

6
Single DB vs. Multiple databases Can we create
multiple databases by using same version of
Oracle Software? Yes we can One database vs.
Multiple databases Nature of applications


Dependency Size of the database (maintenance)
Application Complexity
..
7
Instance and Database Instance will be hosted on
server


CPU MEM PROCESSES
Database (C,R,D files) is located on high end

storage solutions like SAN with fiber
optic technology. Process power and memory for
all Database operations are from server resources

8
ORACLE INSTANCE BACKGROUND PROCESSES Mandate and
Optional background process will be stated when
INSTANCE starts All the running background
processes can be viewed _at_ two different levels OS
LEVEL echo ORACLE_SID (Instance Name)



ps ef grep ORACLE_SID DATABASE LEVEL -
SQLgt Select PNAME From vprocess Where PNAME is
not null ORDER by PNAME How to see all the
available background process details
SQLgtSelect name from vbgprocess


9
Mandate Background Processes DBWn DBWn Writes
dirty buffers from

Instance
Buffer Cache
to DBF
files
SHARED POOL
DATABASE
Redo buffers
USER DATAFILE
10
Mandate Background Processes DBWn DBWn Writes
only dirty buffers that are LRU (Least
recently Used buffers) to dbf files and MRU
during CKPT


What is the difference
between
block
(file)
and
buffer
(memory)
Data Base Buffer Cache (Memory)
Dbf file Blocks (Storage) Default Block Size 8K
Non default supported block sizes 2k,4k,16k,32k
11
Types of Buffers
in
Buffer
Cache

Dirty Buffers

Free Buffers
DATABASE BUFFER CACHE

Pinned Buffers
12
Types of Buffers in Buffer Cache Dirty
Buffers Modified in buffer cache(memory)

that are not yet (storage)
written
to
data
files

Free Buffers Ready to use Pinned
Buffers Buffers that are

in use by Oracle
13
DBWn writes when? Dirty buffers threshold When
required free buffers are available

not

During graceful/consistent shutdown

Any TS status change like (read only, offline or
Tablespace Begin backup..) Checkpoint

14
Mandate Background Processes LGWR

LGWR Log writer writes change records redo
buffers to redo log files
from
SHARED POOL
DATABASE
Redo buffers
Current (In use) Active (Required for
recovery Inactive (Ready to be current)
)
Redo log files
Log Switch
15
LGWR writes when At every commit Every three
seconds When 1/3 full of redo log buffers When
worth of redo records is 1MB What contains change
vectors Change record is combination of change

vectors
Change vector contains

Scn and Time Stamp of change Transaction
Id Commit details if committed ( scn and
timestamp) Type of operation Segment name and
type Change details
16
LGWR Server Process copy redo records from user
memory space (PGA) to redo log buffers for every
DML and DDL LOG_BUFFER is the parameter to set
redo log buffers size. Min 64k. Log buffer space
(vsession_wait) wait event(in seconds)
indicates insufficient log buffers size




Vsystem_event
- log file switch completion
(event,total_waits, time_waited and
average_wait) Alert log file also records the
above event check for checkpoint not
complete Highly recommended multiplexing


17
Mandate DBWn
background process CKPT
LRU
LRU
LRU
SHARED POOL
DATABASE BUFFER CACHE
REDO LOG BUFFERS
MRU
MRU
MRU
CKPT
Data 723
file 1
723
723
723
723
Control
file
Data
file 2
Redo log files
18
Mandate background process CHECKPOINT
Checkpoint is a very critical background process
to reduce the instance or media recovery
time Checkpoint wont write any data but in only
ensures marking SCN in Redo, Control file and
data files During checkpoint DBWn writes data
to dbf files and marks Redo, control file and
data files with scn Checkpoint position (SCN) in
redo is the pointer



where recovery must start from Checkpoint ensures
all the dirty buffers to disk (LRU
MRU) Checkpoint position is the oldest dirty
buffer in the database buffer cache


19
Types of checkpoint Thread checkpoint
(database checkpoint) Database writes to disk all
buffers modified by redo in a specific thread,
occurs during the following situations


Consistent database shutdown Alter system
checkpoint Online redo log switch Alter
database begin backup
20
Types of checkpoint TS and data file
checkpoints Tablespace checkpoint is with
respect to a particular table space during the
status change like read only, read write, begin

backup. Only for the data files that belongs to
a TS
21
Types of checkpoint Incremental checkpoints It
is kind of thread checkpoint in order to avoid
large number of blocks at online redo log
switches. DBWn checks every 3 seconds to check


any work to do, if DBWn writes dirty buffers, it
advances the checkpoint position to the control
file but not the data file headers
22
Types of checkpoint Incremental checkpoints It is
kind of thread checkpoint in order to avoid large
number of blocks at online redo log switches.


DBWn checks every 3 seconds to check any work to
do, if DBWn writes dirty buffers, it advances the
checkpoint position to the control file but not
the data file headers
23
Mandate background process SMON SMON System
Monitor Responsible for Instance crash recovery
(ICR) Cleans up temporary segments. For example
if index creation failed it cleans up all the
temporary segments. What is ICR?


Instance recovery is applying records from redo
to dbf files after most recent checkpoint
Information from redo must be transferred to dbf
to make it permanent and consistent
24
ICR - SMON
SHARED POOL DATABASE BUFFER CACHE
SMON
INSTANCE STARTUP AFTER CRASH
Crash
Recovery
245
245
Data file 1
245 245
Data file 2 Data file 3
245 - Control file
1.Rollforward 2.Rollback (uncommitted txns) 3.
Recovery done
Committed un committed
Committed un committed
245
Undo Data file 1
25
Mandate background process PMON PMON Processes
Monitor

Responsible resources of
for cleaning
a
dead
processes
SGA
User process
User process Crash
PMON
26
Mandate background process RECO Recoverer
RECO

Responsible for resolving in-doubt
transactions
in
distributed
database
environment.
Distributed txn
RECO
RECO
User
27
SQL statements processing in Server Processes
ORACLE DATABASE
PARSING
DATABASE BUFFER CACHE
1. Syntax check 2. Semantic check 3.Shared pool
check
SHARED POOL REDO BUFFERS
User Process
If SP check no
SQLgt Select from employees
soft
hard
data file
OPTIMIZER
Execution
Row source generation
Multiple EPs
28
SQL statements processing in Server Processes
ORACLE DATABASE
PARSING
DATABASE BUFFER CACHE
1. Syntax check 2. Semantic check 3.Shared pool
check
SHARED POOL
User Process Rows u dated
10k
10k
10k
REDO BUFFERS
p
DBWn
20k
20k
20k
If SP check no
SQLgt Update sal20K from salary where sal10K
soft
Undo
data file
hard
Use
r data file
OPTIMIZER
Execution
Row source generation
Multiple EPs
29
Building career with passion
QUESTION AND ANSWER SESSION
DSNR944 11 72 718/040-65555689 www.dbatechnologie
s.net