As an Oracle DBA, you must understand the
concepts of Oracle architecture clearly.
It is a basic step or main point that you need before you go to manage your
database. By this article, I will try to share my knowledge about it. Hope it
can be useful for you.
What is An Oracle Database?
Basically, there are two main components of
Oracle database –– instance and database itself. An instance
consists of some memory structures and the background processes, whereas a
database refers to the disk resources.
Figure 1 will show you the relationship.Figure 1.
Two main components of Oracle database
Instance
As we cover above, the memory structures and
background processes contitute an instance. The memory structure itself
consists of System Global Area (SGA),
Program Global Area (PGA), and an
optional area ––Software Area Code. In the other hand, the mandatory background
processes are Database Writer(DBWn),
Log Writer (LGWR), Checkpoint (CKPT), System Monitor (SMON), and Process Monitor (PMON).And another optional background
processes are Archiver (ARCn),
Recoverer (RECO), etc. Figure 2 will
illustrate the relationship for those components on an instance.
Figure 2. The instance components
System Global Area
SGA is
the primary memory structures. When Oracle DBAs talk about memory, they usually
mean the SGA. This area is broken into a few of part memory –– Buffer Cache,
Shared Pool, Redo Log Buffer, Large Pool, and Java Pool.
Buffer
Cache
Buffer
cache is used to stores the copies of data block that retrieved from data files.
That is, when user retrieves data from database, the data will be stored in
buffer cache. Its size can be manipulated viaDB_CACHE_SIZE parameter in
init.ora initialization parameter file.
Shared
Pool
Shared
pool is broken into two small part memories –– Library Cache and Dictionary
Cache. The library cache is used to stores information about the commonly used
SQL and PL/SQL statements; and is managed by a Least Recently Used (LRU)
algorithm. It is also enables the sharing those statemens among users. In the
other hand, dictionary cache is used to stores information about object
definitions in thedatabase, such as columns, tables, indexes, users,
privileges, etc. The shared pool size can be set via SHARED_POOL_SIZE parameter
in init.ora initialization parameter file.
Redo
Log Buffer
Each DML
statement (insert, update, and delete) executed by users will generates the
redo entry. What isa redo entry? It is an information about all data changes
made by users. That redo entry is stored in redolog buffer before it is written
into the redo log files. To manipulate the size of redo log buffer, you can
usethe LOG_BUFFER parameter in init.ora initialization parameter file.
Large
Pool
Large
pool is an optional area of memory in the SGA. It is used to relieves the
burden place on the sharedpool. It is also used for I/O processes. The large
pool size can be set by LARGE_POOL_SIZE parameter ininit.ora initialization
parameter file.
Java
Pool
As its
name, Java pool is used to services parsing of the Java commands. Its size can
be set byJAVA_POOL_SIZE parameter in init.ora initialization parameter file.
Program
Global Area
Although
the result of SQL state men parsing is stored in library cache, but the value
of binding variable will be stored in PGA. Why? Because it must be private or
not be shared among users. The PGA is also used for sort area.
Software
Area Code
Software
area code is a location in memory where the Oracle application software
resides.
Oracle
Background Processes
Oracle
background processes is the processes behind the scene that work together with
the memories.
DBWn
Database
writer (DBWn) process is used to write data from buffer cache into the
datafiles. Historically, thedatabase writer is named DBWR. But since some of
Oracle version allows us to have more than onedatabase writer, the name is
changed to DBWn, where n value is a number 0 to 9.
LGWR
Log
writer (LGWR) process is similar to DBWn. It writes the redo entries from redo
log buffer into the redolog files.
CKPT
Checkpoint
(CKPT) is a process to give a signal to DBWn to writes data in the buffer cache
into datafiles. Itwill also updates datafiles and control files header when log
file switch occurs.
SMON
System
Monitor (SMON) process is used to recover the system crach or instance failure
by applying theentries in the redo log files to the datafiles.
PMON
Process
Monitor (PMON) process is used to clean up work after failed processes by
rolling back thetransactions and releasing other resources.
Database
The
database refers to disk resources, and is broken into two main structures ––
Logical structures andPhysical structures.
Logical
Structures
Oracle database is divided into smaller logical
units to manage, store, and retrieve data effeciently. Thelogical units are
tablespace, segment, extent, and data block. Figure 3 will illustrate the
relationshipsbetween those units
Figure 3. The
relationships between the Oracle logical structures
Tablespace
A
Tablespace is a grouping logical database objects. A database must have one or
more tablespaces. Inthe Figure 3, we have three tablespaces –– SYSTEM
tablespace, Tablespace 1, and Tablespace 2.Tablespace is composed by one or
more datafiles.
Segment
A
Tablespace is further broken into segments. A segment is used to stores same
type of objects. That is,every table in the database will store into a specific
segment (named Data Segment) and every index inthe database will also store in
its own segment (named Index Segment). The other segment types areTemporary
Segment and Rollback Segment.
Extent
A
segment is further broken into extents. An extent consists of one or more data
block. When thedatabase object is enlarged, an extent will be allocated. Unlike
a tablespace or a segment, an extentcannot be named.
Data
Block
A data
block is the smallest unit of storage in the Oracle database. The data block
size is a specific numberof bytes within tablespace and it has the same number
of bytes.
Physical
Structures
The
physical structures are structures of an Oracle database (in this case the disk
files) that are notdirectly manipulated by users. The physical structure
consists of datafiles, redo log files, and control files.
Datafiles
A
datafile is a file that correspondens with a tablespace. One datafile can be
used by one tablespace, butone tablespace can has more than one datafiles.
Redo
Log Files
Redo log
files are the files that store the redo entries generated by DML statements. It
can be used forrecovery processes.
Control
Files
Control
files are used to store information about physical structure of database, such
as datafiles size andlocation, redo log files location, etc
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন