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
Database files themselves are useless without the memory structures and
processes to interact with the database. Oracle defines the term instance as
the memory structure and the background processes used to access data from a
database. 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.
STSTEM 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 datafiles. That is, when user retrieves data from database, the data will
be stored in buffer cache. Its size can be manipulated via DB_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
statements among users. In the other hand, dictionary cache is
used to stores information about object definitions in the database, 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 (select, insert, update, and delete) executed by
users will generates the redo entry. What is a redo entry? It
is an information about all data changes made by users. That redo entry is
stored in redo log buffer before it is written into the redo log files. To
manipulate the size of redo log buffer, you can use the 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 shared pool. It is also used for I/O
processes. The large pool size can be set by LARGE_POOL_SIZE parameter in
init.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 by JAVA_POOL_SIZE parameter in init.ora initialization
parameter file.
Program Global Area
Although the result of SQL statement 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 processes
There are two categories of processes that run with an Oracle database.
They are mentioned below:
·
User processes
·
System processes
The following figure illustrates the relationship between user
processes, server processes, PGA, and session:
Figure 3
The first interaction with the Oracle-based application comes from the
user computer that creates a user process. The user process then communicates
with the server process on the host computer. Here, PGA is used to store
session specific information.
ORACLE BACKGROUD 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, the database writer is named DBWR. But since
some of Oracle version allows us to have more than one database 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 redo log files.
CKPT
Checkpoint (CKPT) is a process to give a signal to DBWn to writes data
in the buffer cache into datafiles. It will also updates datafiles and control
files header when log file switch occurs.
SMON
System Monitor (SMON) process is used to recover the system crash or
instance failure by applying the entries 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 the transactions and releasing other resources.
ARCH
The ARCH background process is invoked when your database is running in
ARCHIVELOG mode. If you are archiving your redo logs, the redo logs are touched
by several background processes. First, the LGWR process copies the log_buffer
contents to the online redo log files, and then the ARCH process copies the
online redo log files to the archived redo log filesystem on UNIX. The ARCH
process commonly offloads the most recent online redo log file whenever a log
switch operation occurs in Oracle.
The figure 4: shows various components of SGA, Oracle background
processes, and their interactions with control files, data files, Redo Log
files, and archived redo logs.
DATABASE
The database refers to disk resources, and is broken into two main
structures –– Logical structures and Physical structures.
Logical Structures:~
Oracle database is divided into smaller logical units to manage, store,
and retrieve data effeciently. The logical units are tablespace, segment,
extent, and data block. Figure 5 will illustrate the relationships between
those units.
Figure 5. The relationships between the Oracle logical structures
TABLESPACE
A Tablespace is a grouping logical database objects. A database must
have one or more tablespaces. In the Figure 5, we have three tablespaces ––
SYSTEM tablespace, Tablespace 1, and Tablespace 2. Tablespace is composed by
one or more datafiles.
There are three types of tablespaces in Oracle:
·
Permanent tablespaces
·
Undo tablespaces
·
temporary tablespaces
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 in the database
will also store in its own segment (named Index Segment). The other segment
types are Temporary Segment and Rollback Segment.
A segment is a container for objects (such as tables, views, packages . . . indexes). A segment consists of Extends.
A segment is a container for objects (such as tables, views, packages . . . indexes). A segment consists of Extends.
There are 11 types of Segments in oracle 10g.
1. Table
2. Table Partition
3. Index
4. Index Partition
5. Cluster
6. Rollback
7. Deferred Rollback
8. Temporary
9. Cache
10. Lobsegment
11. Lobindex
Extent
A segment is further broken into extents. An extent consists of one or more data block. When the database object is enlarged, an extent will be allocated. Unlike a tablespace or a segment, an extent cannot be named. Space for a data on a hard disk is allocated in extends.
A segment is further broken into extents. An extent consists of one or more data block. When the database object is enlarged, an extent will be allocated. Unlike a tablespace or a segment, an extent cannot be named. Space for a data on a hard disk is allocated in extends.
Data Block
A data block is the smallest unit of storage in the Oracle database. The
data block size is a specific number of 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 not
directly 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, but one tablespace can has more than one
datafiles. An Oracle database include of a number of physical files called
datafile.
REDO LOG FILES
A Redo Log is a file that
is part of an Oracle Database. When a transaction is committed the
transaction’s details in the redo log buffer is written in a redo log file.
These files contain information that helps in recovery in the event of system
failure.
The figure 6: shows three Redo Log groups. Each group consists of two
members. The first member of each Redo Log group is stored in directory D1 and
the second member is stored in directory D2.
CONTROL FILES
Control files are used to store information about physical structure of
database. The control file is absolutely crucial to database operations. It
contains the following types of information:
1. Database
Information
2. Archive log history
3. Tablespace and
datafile records
4. Redo threads
5. Database’s creation
data
6. Database name
7. Current Archive
information
8. Log records
9. Database Id which
is unique to each Database
Comments
Post a Comment