oracle rac dba training :dba technologies
TRANSCRIPT
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
1/29
WELCOME TO DBA TECHNOLOGIES
ORACLE DATABASE ARCHITECTURE-2
By
Mr. PRASAD MYNUMPATI18Years Real Time in INDIA-USA
www.dbatechnologies.net
www.dbatechnologies.netBuilding Career With Passion
http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/ -
7/22/2019 Oracle RAC DBA Training :DBA Technologies
2/29
database Architecture USER TSUSER tablespace is to store datapermanently
Entire Application data storage and retrievalfrom USER tablespaces
For load balancing purpose we use different
tablespaces on different disks for INDEX andDATA
How many tablespaces for a database?depends on design of the application and
number of applications
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
3/29
ORACLE & OS comparisonFILE 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)
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
4/29
Server & Storage configuration
OS and ORACLE SW is on ServerDatabase is going to be on high end
storage
NAS (Network area Storage)
SAN (Storage area Networks)
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
5/29
Identifying OS for ORACLE SW version
What kind of OS we need ? Need to follow certification matrix
oracle metalink.www.metalink.oracle.comNeed to choose certified OS
from
Can we install multiple versions
(7,8,8i,9i,10g,11g) of Oracle SW
server?Yes we can
on same
http://www.metalink.oracle.com/http://www.metalink.oracle.com/http://www.metalink.oracle.com/http://www.metalink.oracle.com/http://www.metalink.oracle.com/http://www.metalink.oracle.com/http://www.metalink.oracle.com/http://www.metalink.oracle.com/http://www.metalink.oracle.com/http://www.metalink.oracle.com/http://www.metalink.oracle.com/http://www.metalink.oracle.com/http://www.metalink.oracle.com/http://www.metalink.oracle.com/http://www.metalink.oracle.com/ -
7/22/2019 Oracle RAC DBA Training :DBA Technologies
6/29
Single DB vs. Multiple databases
Can we create multiple databases byusing same version of Oracle Software?
Yes we can
One database vs. Multiple databasesNature of applications
DependencySize of the database (maintenance)Application Complexity..
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
7/29
Instance and Database
Instance will be hosted on server
CPUMEM
PROCESSES
Database (C,R,D files) is located on high endstorage solutions like SAN with fiber optictechnology.Process power and memory for all Databaseoperations are from server resources
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
8/29
ORACLE INSTANCE BACKGROUND PROCESSESMandate and Optional background process will be
stated when INSTANCE starts
All the running background processes can be viewed@ two different levelsOS LEVEL$echo $ORACLE_SID (Instance Name)
$ps ef | grep $ORACLE_SIDDATABASE LEVEL
- SQL> Select PNAME From v$process Where PNAME
is not null ORDER by PNAME;How to see all the available background process
details
SQL>Select name from v$bgprocess;
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
9/29
Mandate Background Processes DBWn
DBWn : Writes dirty buffers fromInstanceBuffer Cacheto DBFfiles
SHAREDPOOLDATABASE
USER DATAFILE
Redo buffers
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
10/29
Mandate Background Processes DBWnDBWn : Writes only dirty buffers that are LRU (Least recentlyUsed buffers) to dbf files and MRU during CKPT
What is the differencebetweenblock(file)andbuffer(memory)
Data BaseBuffer Cache (Memory)
Dbf fileBlocks : (Storage) Default Block Size 8KNon default supported block sizes: 2k,4k,16k,32k
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
11/29
Types of BuffersinBufferCache
Dirty BuffersFree Buffers
DATABASE BUFFER CACHE
Pinned Buffers
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
12/29
Types of Buffers in Buffer Cache
Dirty BuffersModified in buffer cache(memory)
that are not yet(storage)
writtentodatafiles
Free BuffersReady to use
Pinned BuffersBuffers that are
in use by Oracle
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
13/29
DBWn writes when?
Dirty buffers thresholdWhen required free buffers are
available
notDuring graceful/consistent shutdownAny TS status change like (read only,
offline or Tablespace Begin backup..)Checkpoint
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
14/29
)
Mandate Background Processes LGWR LGWR : Log writer writes change records
redo buffers to redo log filesfrom
SHARED
POOLDATABASE
Current (In use)Active (Required for recoveryInactive (Ready to be current)
Redo log filesLog Switch
Redo buffers
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
15/29
LGWR writes when
At every commitEvery three secondsWhen 1/3 full of redo log buffersWhen worth of redo records is 1MB
What contains change vectorsChange record is combination of change
vectors
Change vector contains
Scn and Time Stamp of changeTransaction IdCommit details if committed ( scn and timestamp)
Type of operationSegment name and typeChange details
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
16/29
LGWRServer Process copy redo records from user
memory space (PGA) to redo log buffers for everyDML and DDL
LOG_BUFFER is the parameter to set redo log
buffers size. Min 64k.
Log buffer space (v$session_wait)
wait event(inseconds) indicates insufficient log buffers size
V$system_event - log file switch completion
(event,total_waits, time_waited and average_wait)
Alert log file also records the above event check forcheckpoint not complete
Highly recommended multiplexing
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
17/29
MandateDBWn
background process CKPT
LRU LRU LRUSHARED POOL
DATABASE BUFFER CACHE
REDO LOGBUFFERS
MRU MRU MRUCKPT
Data723
file 1723 723
723 723
Controlfile Datafile 2 Redo log files
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
18/29
Mandate background process CHECKPOINT
Checkpoint is a very critical background process to
reduce the instance or media recovery timeCheckpoint wont write any data but in only ensures
marking SCN in Redo, Control file and data filesDuring 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 fromCheckpoint ensures all the dirty buffers to disk
(LRU & MRU)Checkpoint position is the oldest dirty buffer in thedatabase buffer cache
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
19/29
Types of checkpointThread checkpoint :(database checkpoint)Database writes to disk all buffers
modified by redo in a specific thread,
occurs during the following situations
Consistent database shutdownAlter system checkpointOnline redo log switchAlter database begin backup
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
20/29
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 thatbelongs to a TS
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
21/29
Types of checkpoint
Incremental checkpointsIt 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 dirtybuffers, it advances the checkpointposition to the control file but not thedata file headers
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
22/29
Types of checkpoint
Incremental checkpointsIt is kind of thread checkpoint in orderto avoid large number of blocks at
online redo log switches.
DBWn checks every 3 seconds to checkany work to do, if DBWn writes dirty
buffers, it advances the checkpointposition to the control file but not thedata file headers
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
23/29
Mandate background process SMONSMON : 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 redoto dbf files after most recent checkpoint
Information from redo must be transferred todbf to make it permanent and consistent
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
24/29
SHARED POOL
DATABASE BUFFER CACHE
ICR - SMON
SMONINSTANCE STARTUP AFTER CRASH
CrashRecovery 245
245 Data file 1
245
245
Data file 2
Data file 3
245 - Control file
1.Rollforward2.Rollback (uncommitted txns)3. Recovery done Committed &
un committedCommitted &un committed
245 Undo Data file 1
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
25/29
Mandate background process PMON
PMON : Processes MonitorResponsible
resources of
for cleaning
adeadprocessesSGA
User processUser process Crash
PMON
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
26/29
Mandate background process
RECO : Recoverer
RECO Responsible for resolving in-doubttransactionsindistributed
databaseenvironment.Distributed txn
RECO RECO
User
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
27/29
SQL statements processing inServer
Processes
ORACLE DATABASE
PARSING
DATABASEBUFFER CACHE
1. Syntax check2. Semantic check3.Shared pool check
SHAREDPOOLREDO
BUFFERS
UserProcess
If SPcheck=no
SQL> Select *from employees; soft
harddata file
OPTIMIZERExecution
Row source generation Multiple EPs
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
28/29
p
Use
SQL statements processing inServer
Processes
ORACLE DATABASE
PARSING
DATABASEBUFFER CACHE
1. Syntax check2. Semantic check3.Shared pool check
SHAREDPOOLUser
ProcessRows u dated
10k 10k 10k
REDOBUFFERSDBWn 20k 20k 20k
If SPcheck=no
SQL> Updatesal=20K fromsalary where
sal=10K
softUndodata file
hardr data file
OPTIMIZERExecution
Row source generation Multiple EPs
-
7/22/2019 Oracle RAC DBA Training :DBA Technologies
29/29
Building career with passion
DSNR:944 11 72 718/040-65555689www.dbatechnologies.net
QUESTION AND ANSWER SESSION
http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/http://www.dbatechnologies.net/