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.
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 In 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).
SYSTEM GLOBAL AREA:
SGA is the primary memory structure.
SGA is a some part of RAM memory.
SGA internally contain so many other components.
SGA internally contain 1000+ components in that mandatory components are
--fixed area
--shared pool
--Database buffer cache
--Redo log buffer
Few mandatory components are non-standard buffer cache
--keep pool
--Recycle pool
--Stream pool
--Large pool
--Java pool
FIXED AREA:
The size of this area is fixed that is 1 MB. It is header of sga .
Oracle allocates memory to sga interms granules.
(granule entry means granules like used and free granulies)
There is no parameter to change the size of fixed area component.
SHARED POOL:
It is mandatory component inside SGA.
in the 1/3 of SGA=shared pool
To specify the size of this component SHARED_POOL_SIZE parameter in p file or spfile.
if don't specified the default value will be taken that is 28MB.
The important internal components are
--Library Cache
--Data Dictionary Cache
--Control structure.
Library Cache:
The library cache is used to stores information about the commonly used SQL and PL/SQL statements.
Query Processing will be done in Library Cache.
Internally contain below components.
--Shared Sql Area
--Private Sql Area
--PL/SQL procedures&packages.
Shared Sql Area;
This area is common to all the users.
Initially user request be placed in shared sql area of L.C.
Private Sql Area:
This area holds user specific information
like bind variables and user defined variables etc
this is indivisual to every user.
PL/SQL procedures and packages:
This area will be used when end user query invokes PL/SQL objects
like procedure ,packages etc.
Data Dictionary cache:
dictionary cache is used to stores information about object definitions in the database,
such as columns, tables, indexes, users, privileges, etc.
The row cache holds all the corrected semantics by the parcer interms of rows(records).
Control structures:
This memory used to hold the lock&latches the information.
This locks&latches are require to Control concurrent user operations in open database.
DATABASE BUFFER CACHE:
This component holding the data.which was retrieved from the database.
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.
Database buffer cache is the largest component inside a SGA.
Generally we are allocating the off-size of sga size to database buffer cache.
It is dynamic parameter dynamically we can increase the value if size is not sufficient.
The default value is 48Mb.
The Entire memory allocated to the database buffer cache will be divided into small units called buffers.
The size of buffer inside a database buffer cache will b decided by the parameter DB_BLOCK_SIZE.
This parameter can take 5 different values. They are
2K,4K,8K,16K,32K upto 9i default value of this parameter is 2K.
From 10g onwards the default value of this parameter is 8k.
Note:
OLTP environments are using blocksize 2k,4k,8k
OLAP environments are using blocksize 16k,32k
All buffers inside a database buffer cache is categorised into 4 differnet types.
--free buffers
--pinned buffers
--dirty buffers
--used buffers
FREE BUFFERS:
If buffer is not even used at least once then the buffer is said to be Free buffer.
when we start instance freshly all the buffers are inside will be fresh buffers .
To retrieve data from database to database buffer cache initially we require a free buffer inside dbbc.
PINNED BUFFERS:
If buffer is participating in a transaction currently then that buffer is said to be pinned buffer.
when we store some data into the free buffer then the buffer status
will be done to pinned buffer.
DIRTY BUFFER:
If pinned buffer has modification inside it then the buffer status will be dirty buffer.
dirty buffer is a buffer which has modified value in it.
USED BUFFER:
When end user says commit oracle(writes)flushes dirty buffer from dbbc to database.
there after dirty buffer status will done to used buffers.
A used buffer inside a dbbc can be reused if no free buffer exists for future transaction purpose.
In dbbc all 4 varients of buffer will be organized 2 lists.
**LRU LIST
**WRITE LIST
LRU(least recently used)LIST;
Initially all the buffers in exist in LRU list when
a new transaction arises to allocate a free buffer to the transaction oracle starts
scanning from the LRU end of LRU list.
Once a free buffer is allocated to transaction that buffer will be moved to the
MRU end of LRU list(pinned buffer).
In the process of scanning if it find any dirty buffers that dirty buffer will move to the write list.
the write list contains only dirty buffers.
if 1/3 portion of write list get full (or)1/3 portion of LRU list is completed to finding the
free buffer then all dirty buffers in write list will be returned back to database.
After those dirty buffers status converted to used buffer
there will be added automatically LRU end of LRU list.
REDO LOG BUFFER:
This is a mandatory component inside a SGA.
To manipulate the size of redo log buffer, you can use the LOG_BUFFER
parameter in init.ora initialization parameter file.
It is the smallest component inside a SGA .
this is used to holds redo records.for every sql operation one redo record
will be generated.
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.
Redo record is collection of one or more change vectors.
for every single change on single block oracle generates a change vector.
change vector contains just change information in a block.
when end user says commit instead of writing dirty buffer back into
their original place oracle write redo records redo log buffer to
redo log file to commit operations fastly.
This is called fast commit mechanism(FCM).
JAVA POOL:
It is an unmandatory component.
when we use java language in frontend applications we need to enable this component.
to enable this component to specify the parameter JAVA_POOL_SIZE.
STREAM POOL:
It is an unmandatory component.
we require this component when we work with oracle streams.
oracle streams is technology for replicating data one database to another database.(copying)
To enable this component we need to specify the parameter STREAM_POOL_SIZE.
Optionally we use streams pool for while performing
oracle logical backups.
LARGE POOL:
It is an unmandatory component.
It is used to relieves the burden place on the shared pool.
We require this to perform RMAN BACKUPS.
when we use shared servers.
To enable this component we need to specify parameter LARGE_POOL_SIZE.
KEEP POOL:
It is an unmandatory component.
we do not specify the component shared pool will take automatically.
very frequently accessed objects we store in keep pool
for better performence.
To enable this component we need to specify parameter DB_KEEP_CACHE_SIZE
example:create table emp(eno number)keep;
RECYCLE POOL:
It is an unmandatory component.
The behaviour of this pool exactly opposite to keep pool.
the data what we saved in that will be immediately erased.
To enable this component we need to specify parameter DB_RECYCLE_CACHE_SIZE.
PROCESS GLOBAL AREA(PGA):
When server process is initiated at server side oracle uniquely allocates some amount of memory
inside RAM the server process called Process global area.
This memory can be used only by that server process.
To pecify size of the entire PGA from 9i R1 we use parameter PGA_AGGREGATE_TARGET.
This parameter internally distribute memory among all the existing components inside pga.
Every new server-process claims 5% of memory from the PGA_AGGREGATE_TARGET
To fix maximum limit fof sga in 9i R2 oracle introduced new parameter is SGA_MAX_SIZE
Which specify maximum upper boundary limit of sga.
The components inside a PGA are
**SESSION AREA
**SORT AREA
**PRIVATE SQL AREA
**HASH AREA
**MERGE AREA
SESSION AREA(OR)USER GLOBAL AREA(uga):
Session area contain complete session information of user like
login information,terminal information etc.
SORT AREA:
This area is used to performing temporary sort operations.
below operations internally involves
thay are ORDERBY,GROUP BY,CREATE INDEX,CREATE TABLE AS SELECT.
PRIVATE SQL AREA:
IT holds query specific information and bind variable information
This Area is private to a particular user as well as particular
NOTE:if private sql area exist in sga then don't exist inside pga viceversa.
HASH AREA:
To perform on HASH join operations oracle make use of this area.
MERGE AREA:
To perform on MERGE join operations oracle make use of this area.
ORACLE BACKGROUND PROCESSES:
Every instance will have own bg process.
All bg process will run paralelly in system and they will work automatically each other.
To funcationality of each and every background process well defined in oracle.
In oracle 9i oracle automatically started 5 mandatory background process.
they are
**SMON(SYSTEM MONITOR)
**PMON(PROCESS MONITOR)
**LGWR(LOG WRITER)
**DBWR(DATABASE WRITER)
**CKPT(CHECK POINT)
In 10g extra one more mandatory background process started is RECO(recoverer)process.
In 11g,12c one more mandatory background process memory manager and listener registration process.
Every background process will contain 4 charcters in it's name.
NOTE:
All the above 8 process are mandatory background processes.
If any of the process terminated abnormally then oracle stop the instance(terminated)abrubly.
When instance is down automatically oracle closes the database.
To know all bg process running currently use command.
****************************************************************
SQL>select name from V$bgprocess where paddr<>'00';
****************************************************************
Background process are devided in two types
--mandatory
--un mandatory
un mandatory bg process will not started automatically when we start the instance.
when we enable a particular feature in oracle it is associated background process will be started.
Similarly we desiable the feature oracle automatically stops it's associated bg process.
As per 9i we have total 150+ un mandatory bg process.
in 10g 160+ bg process.
in 11g 180+ bg process.
in 12 c 200+ un mandatory bg process.
To list all mandatory&un mandatory bg process
***********************************************
SQL>select name from v$bgprocess;
******************************************
bg process are running in OS level to view
*************************************
]#ps -ef | grep smon
******************
kill the bg process;
----------------------------------------------
]#kill -9 pid(processid)
---------------------------
LGWR FUNCTIONALITY:
---------------------------------
The purpose of LGWR background process is it has to flush
to the (redorecord)data from redolog buffer to redologfile.
when any concurrent end user say commit oracle calls log writer bg process
to flush the entire content of redologbuffer to redolog files.
If 1/3rd portion of redologbuffer gets full automatically oracle calls logwriter to
move the contents of redologbuffer to redologfiles.
when we shutdown the database normal way then also oracle calls logwriter.
For every 3 seconds once calls logwriter.
When an checkpoint occurs then also oracle calls logwriter.
there only one lgwr bg process for instance.
CKPT BG PROCESS(CHECK POINT):
------------------------------------------------
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.
In database every event will be uniquely identified with an a scn number
This scn number will be begin with 1 and keep on incrementing by one for every one second.
It is used to syncronized database with the information present in controlfile.
when logswitch occurs oracle invoke checkpoint.
The checkpoint event internally ckpt bg process.
Now CKPT bg process looks into controlfile to know the previous checkpointed point and strats
the synchronization process from that point to end of redologfile.
To synchronize ckpt internally calls dbwo .
dbwo flushes all the dirtybuffers present in the dbbc backto the original place inside a database.
checkpoint event will be invoke when issue the command manually.
-----------------------------------------------------
SQL>alter system checkpoint;
------------------------------------------------------
CKPT BG PROCESS will be invoked oracle in below cases.
CASE 1:
----------
When log switch occurs
CASE2:
----------
SQL>alter system checkpoint local;
---------------------------------------------
this is used standalone database .
CASE 3:
---------------
SQL>alter tablespace tablespace_name offline;
2 types offline normal and offline temporary;
CASE 4:
----------
when this issue this command
SQL>alter tablespace tablespace_name readonly;
CASE5:
-----------
SQL>alter tablespace tablespace_name begin backup;
CASE 6:
----------
SQL>shut immediateor
SQL>shut down database normal
CASE 7:
----------
Even we drop a table or truncate table then also check point occured.
DATABASE WRITER (DBWO):
----------------------------------------
the purpose of DBWo bg process is it writes the dirtybuffer inside SGA(DBBC)back to
there original place inside the database.
we can initiate more number of dbwo bg process by specify the parameter db_writer_processs
The maximum value for this parameter 20
the naming convention of dbwo bg process is
dbwo to dbw9(0,1,2,3,4,5,6,7,8,9)
dbwa ot dbwj(a,b,c,d,e,f,g,h,i,j)
In below cases dbwo bg process called by oracle.
**when checkpoint occurs
**for every 3 seconds
**1/3 portion of write list gets full
**1/3 portion of LRU list gets full oracle calls dbwo
**when alter system checkpoint command
**alter tablespace tablespace_name offline ---normal&temporary
**alter tablespace tablespace_name readonly;
**alter tablespace tablespace_name begin backup;
SMON(SYSTEM MONITOR):
Purpose of this bg process mainly used for instance recovery.
only one smon bg process for instance.
It will wakeup every 5 seconds once it is also used for performence housekeeping activities.
like cleaning temporary segments,datadictionary tablespace etc.
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):
Basic purpose of this bg process is used for process recovery
This process will claim all the resources which were being used by
abnormally terminated userprocess.
In this process of claiming the resources it claims all the buffers
inside dbbc.
It claims the dirty buffer as well as server process.
only one pmon bg process for instance.
RECO(recoverer);
-----------------------
This additional bg process from 10g
used for recovering pending transactions in open database.
MMAN(MEMORY MANAGER):
---------------------------------------------
It is additional bg process from 11g.
This is responsible for implementing AMM feature.
when you specify memory_target,memory_max_size parameters
this bg process will be initiated.
LREG:
------------
This is additional bg process in 12c.
this bg process used for registering database with listener.
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.
There are 11 types of Segments in oracle 10g.
Table
Table Partition
Index
Index Partition
Cluster
Rollback
Deferred Rollback
Temporary
Cache
Lobsegment
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.
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 databae 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:
Database Information
Archive log history
Tablespace and datafile records
Redo threads
Database’s creation data
Database name
Current Archive information
Log records
Database Id which is unique to each Database
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.
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 In 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).
SYSTEM GLOBAL AREA:
SGA is the primary memory structure.
SGA is a some part of RAM memory.
SGA internally contain so many other components.
SGA internally contain 1000+ components in that mandatory components are
--fixed area
--shared pool
--Database buffer cache
--Redo log buffer
Few mandatory components are non-standard buffer cache
--keep pool
--Recycle pool
--Stream pool
--Large pool
--Java pool
FIXED AREA:
The size of this area is fixed that is 1 MB. It is header of sga .
Oracle allocates memory to sga interms granules.
(granule entry means granules like used and free granulies)
There is no parameter to change the size of fixed area component.
SHARED POOL:
It is mandatory component inside SGA.
in the 1/3 of SGA=shared pool
To specify the size of this component SHARED_POOL_SIZE parameter in p file or spfile.
if don't specified the default value will be taken that is 28MB.
The important internal components are
--Library Cache
--Data Dictionary Cache
--Control structure.
Library Cache:
The library cache is used to stores information about the commonly used SQL and PL/SQL statements.
Query Processing will be done in Library Cache.
Internally contain below components.
--Shared Sql Area
--Private Sql Area
--PL/SQL procedures&packages.
Shared Sql Area;
This area is common to all the users.
Initially user request be placed in shared sql area of L.C.
Private Sql Area:
This area holds user specific information
like bind variables and user defined variables etc
this is indivisual to every user.
PL/SQL procedures and packages:
This area will be used when end user query invokes PL/SQL objects
like procedure ,packages etc.
Data Dictionary cache:
dictionary cache is used to stores information about object definitions in the database,
such as columns, tables, indexes, users, privileges, etc.
The row cache holds all the corrected semantics by the parcer interms of rows(records).
Control structures:
This memory used to hold the lock&latches the information.
This locks&latches are require to Control concurrent user operations in open database.
DATABASE BUFFER CACHE:
This component holding the data.which was retrieved from the database.
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.
Database buffer cache is the largest component inside a SGA.
Generally we are allocating the off-size of sga size to database buffer cache.
It is dynamic parameter dynamically we can increase the value if size is not sufficient.
The default value is 48Mb.
The Entire memory allocated to the database buffer cache will be divided into small units called buffers.
The size of buffer inside a database buffer cache will b decided by the parameter DB_BLOCK_SIZE.
This parameter can take 5 different values. They are
2K,4K,8K,16K,32K upto 9i default value of this parameter is 2K.
From 10g onwards the default value of this parameter is 8k.
Note:
OLTP environments are using blocksize 2k,4k,8k
OLAP environments are using blocksize 16k,32k
All buffers inside a database buffer cache is categorised into 4 differnet types.
--free buffers
--pinned buffers
--dirty buffers
--used buffers
FREE BUFFERS:
If buffer is not even used at least once then the buffer is said to be Free buffer.
when we start instance freshly all the buffers are inside will be fresh buffers .
To retrieve data from database to database buffer cache initially we require a free buffer inside dbbc.
PINNED BUFFERS:
If buffer is participating in a transaction currently then that buffer is said to be pinned buffer.
when we store some data into the free buffer then the buffer status
will be done to pinned buffer.
DIRTY BUFFER:
If pinned buffer has modification inside it then the buffer status will be dirty buffer.
dirty buffer is a buffer which has modified value in it.
USED BUFFER:
When end user says commit oracle(writes)flushes dirty buffer from dbbc to database.
there after dirty buffer status will done to used buffers.
A used buffer inside a dbbc can be reused if no free buffer exists for future transaction purpose.
In dbbc all 4 varients of buffer will be organized 2 lists.
**LRU LIST
**WRITE LIST
LRU(least recently used)LIST;
Initially all the buffers in exist in LRU list when
a new transaction arises to allocate a free buffer to the transaction oracle starts
scanning from the LRU end of LRU list.
Once a free buffer is allocated to transaction that buffer will be moved to the
MRU end of LRU list(pinned buffer).
In the process of scanning if it find any dirty buffers that dirty buffer will move to the write list.
the write list contains only dirty buffers.
if 1/3 portion of write list get full (or)1/3 portion of LRU list is completed to finding the
free buffer then all dirty buffers in write list will be returned back to database.
After those dirty buffers status converted to used buffer
there will be added automatically LRU end of LRU list.
REDO LOG BUFFER:
This is a mandatory component inside a SGA.
To manipulate the size of redo log buffer, you can use the LOG_BUFFER
parameter in init.ora initialization parameter file.
It is the smallest component inside a SGA .
this is used to holds redo records.for every sql operation one redo record
will be generated.
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.
Redo record is collection of one or more change vectors.
for every single change on single block oracle generates a change vector.
change vector contains just change information in a block.
when end user says commit instead of writing dirty buffer back into
their original place oracle write redo records redo log buffer to
redo log file to commit operations fastly.
This is called fast commit mechanism(FCM).
JAVA POOL:
It is an unmandatory component.
when we use java language in frontend applications we need to enable this component.
to enable this component to specify the parameter JAVA_POOL_SIZE.
STREAM POOL:
It is an unmandatory component.
we require this component when we work with oracle streams.
oracle streams is technology for replicating data one database to another database.(copying)
To enable this component we need to specify the parameter STREAM_POOL_SIZE.
Optionally we use streams pool for while performing
oracle logical backups.
LARGE POOL:
It is an unmandatory component.
It is used to relieves the burden place on the shared pool.
We require this to perform RMAN BACKUPS.
when we use shared servers.
To enable this component we need to specify parameter LARGE_POOL_SIZE.
KEEP POOL:
It is an unmandatory component.
we do not specify the component shared pool will take automatically.
very frequently accessed objects we store in keep pool
for better performence.
To enable this component we need to specify parameter DB_KEEP_CACHE_SIZE
example:create table emp(eno number)keep;
RECYCLE POOL:
It is an unmandatory component.
The behaviour of this pool exactly opposite to keep pool.
the data what we saved in that will be immediately erased.
To enable this component we need to specify parameter DB_RECYCLE_CACHE_SIZE.
PROCESS GLOBAL AREA(PGA):
When server process is initiated at server side oracle uniquely allocates some amount of memory
inside RAM the server process called Process global area.
This memory can be used only by that server process.
To pecify size of the entire PGA from 9i R1 we use parameter PGA_AGGREGATE_TARGET.
This parameter internally distribute memory among all the existing components inside pga.
Every new server-process claims 5% of memory from the PGA_AGGREGATE_TARGET
To fix maximum limit fof sga in 9i R2 oracle introduced new parameter is SGA_MAX_SIZE
Which specify maximum upper boundary limit of sga.
The components inside a PGA are
**SESSION AREA
**SORT AREA
**PRIVATE SQL AREA
**HASH AREA
**MERGE AREA
SESSION AREA(OR)USER GLOBAL AREA(uga):
Session area contain complete session information of user like
login information,terminal information etc.
SORT AREA:
This area is used to performing temporary sort operations.
below operations internally involves
thay are ORDERBY,GROUP BY,CREATE INDEX,CREATE TABLE AS SELECT.
PRIVATE SQL AREA:
IT holds query specific information and bind variable information
This Area is private to a particular user as well as particular
NOTE:if private sql area exist in sga then don't exist inside pga viceversa.
HASH AREA:
To perform on HASH join operations oracle make use of this area.
MERGE AREA:
To perform on MERGE join operations oracle make use of this area.
ORACLE BACKGROUND PROCESSES:
Every instance will have own bg process.
All bg process will run paralelly in system and they will work automatically each other.
To funcationality of each and every background process well defined in oracle.
In oracle 9i oracle automatically started 5 mandatory background process.
they are
**SMON(SYSTEM MONITOR)
**PMON(PROCESS MONITOR)
**LGWR(LOG WRITER)
**DBWR(DATABASE WRITER)
**CKPT(CHECK POINT)
In 10g extra one more mandatory background process started is RECO(recoverer)process.
In 11g,12c one more mandatory background process memory manager and listener registration process.
Every background process will contain 4 charcters in it's name.
NOTE:
All the above 8 process are mandatory background processes.
If any of the process terminated abnormally then oracle stop the instance(terminated)abrubly.
When instance is down automatically oracle closes the database.
To know all bg process running currently use command.
****************************************************************
SQL>select name from V$bgprocess where paddr<>'00';
****************************************************************
Background process are devided in two types
--mandatory
--un mandatory
un mandatory bg process will not started automatically when we start the instance.
when we enable a particular feature in oracle it is associated background process will be started.
Similarly we desiable the feature oracle automatically stops it's associated bg process.
As per 9i we have total 150+ un mandatory bg process.
in 10g 160+ bg process.
in 11g 180+ bg process.
in 12 c 200+ un mandatory bg process.
To list all mandatory&un mandatory bg process
***********************************************
SQL>select name from v$bgprocess;
******************************************
bg process are running in OS level to view
*************************************
]#ps -ef | grep smon
******************
kill the bg process;
----------------------------------------------
]#kill -9 pid(processid)
---------------------------
LGWR FUNCTIONALITY:
---------------------------------
The purpose of LGWR background process is it has to flush
to the (redorecord)data from redolog buffer to redologfile.
when any concurrent end user say commit oracle calls log writer bg process
to flush the entire content of redologbuffer to redolog files.
If 1/3rd portion of redologbuffer gets full automatically oracle calls logwriter to
move the contents of redologbuffer to redologfiles.
when we shutdown the database normal way then also oracle calls logwriter.
For every 3 seconds once calls logwriter.
When an checkpoint occurs then also oracle calls logwriter.
there only one lgwr bg process for instance.
CKPT BG PROCESS(CHECK POINT):
------------------------------------------------
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.
In database every event will be uniquely identified with an a scn number
This scn number will be begin with 1 and keep on incrementing by one for every one second.
It is used to syncronized database with the information present in controlfile.
when logswitch occurs oracle invoke checkpoint.
The checkpoint event internally ckpt bg process.
Now CKPT bg process looks into controlfile to know the previous checkpointed point and strats
the synchronization process from that point to end of redologfile.
To synchronize ckpt internally calls dbwo .
dbwo flushes all the dirtybuffers present in the dbbc backto the original place inside a database.
checkpoint event will be invoke when issue the command manually.
-----------------------------------------------------
SQL>alter system checkpoint;
------------------------------------------------------
CKPT BG PROCESS will be invoked oracle in below cases.
CASE 1:
----------
When log switch occurs
CASE2:
----------
SQL>alter system checkpoint local;
---------------------------------------------
this is used standalone database .
CASE 3:
---------------
SQL>alter tablespace tablespace_name offline;
2 types offline normal and offline temporary;
CASE 4:
----------
when this issue this command
SQL>alter tablespace tablespace_name readonly;
CASE5:
-----------
SQL>alter tablespace tablespace_name begin backup;
CASE 6:
----------
SQL>shut immediateor
SQL>shut down database normal
CASE 7:
----------
Even we drop a table or truncate table then also check point occured.
DATABASE WRITER (DBWO):
----------------------------------------
the purpose of DBWo bg process is it writes the dirtybuffer inside SGA(DBBC)back to
there original place inside the database.
we can initiate more number of dbwo bg process by specify the parameter db_writer_processs
The maximum value for this parameter 20
the naming convention of dbwo bg process is
dbwo to dbw9(0,1,2,3,4,5,6,7,8,9)
dbwa ot dbwj(a,b,c,d,e,f,g,h,i,j)
In below cases dbwo bg process called by oracle.
**when checkpoint occurs
**for every 3 seconds
**1/3 portion of write list gets full
**1/3 portion of LRU list gets full oracle calls dbwo
**when alter system checkpoint command
**alter tablespace tablespace_name offline ---normal&temporary
**alter tablespace tablespace_name readonly;
**alter tablespace tablespace_name begin backup;
SMON(SYSTEM MONITOR):
Purpose of this bg process mainly used for instance recovery.
only one smon bg process for instance.
It will wakeup every 5 seconds once it is also used for performence housekeeping activities.
like cleaning temporary segments,datadictionary tablespace etc.
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):
Basic purpose of this bg process is used for process recovery
This process will claim all the resources which were being used by
abnormally terminated userprocess.
In this process of claiming the resources it claims all the buffers
inside dbbc.
It claims the dirty buffer as well as server process.
only one pmon bg process for instance.
RECO(recoverer);
-----------------------
This additional bg process from 10g
used for recovering pending transactions in open database.
MMAN(MEMORY MANAGER):
---------------------------------------------
It is additional bg process from 11g.
This is responsible for implementing AMM feature.
when you specify memory_target,memory_max_size parameters
this bg process will be initiated.
LREG:
------------
This is additional bg process in 12c.
this bg process used for registering database with listener.
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.
There are 11 types of Segments in oracle 10g.
Table
Table Partition
Index
Index Partition
Cluster
Rollback
Deferred Rollback
Temporary
Cache
Lobsegment
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.
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 databae 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:
Database Information
Archive log history
Tablespace and datafile records
Redo threads
Database’s creation data
Database name
Current Archive information
Log records
Database Id which is unique to each Database
instance and database itself
An instance consists of some memory structures and the background processes,
whereas a database refers to the disk resources.
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 In 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).
SYSTEM GLOBAL AREA:
SGA is the primary memory structure.
SGA is a some part of RAM memory.
SGA internally contain so many other components.
SGA internally contain 1000+ components in that mandatory components are
--fixed area
--shared pool
--Database buffer cache
--Redo log buffer
Few mandatory components are non-standard buffer cache
--keep pool
--Recycle pool
--Stream pool
--Large pool
--Java pool
FIXED AREA:
The size of this area is fixed that is 1 MB. It is header of sga .
Oracle allocates memory to sga interms granules.
(granule entry means granules like used and free granulies)
There is no parameter to change the size of fixed area component.
SHARED POOL:
It is mandatory component inside SGA.
in the 1/3 of SGA=shared pool
To specify the size of this component SHARED_POOL_SIZE parameter in p file or spfile.
if don't specified the default value will be taken that is 28MB.
The important internal components are
--Library Cache
--Data Dictionary Cache
--Control structure.
Library Cache:
The library cache is used to stores information about the commonly used SQL and PL/SQL statements.
Query Processing will be done in Library Cache.
Internally contain below components.
--Shared Sql Area
--Private Sql Area
--PL/SQL procedures&packages.
Shared Sql Area;
This area is common to all the users.
Initially user request be placed in shared sql area of L.C.
Private Sql Area:
This area holds user specific information
like bind variables and user defined variables etc
this is indivisual to every user.
PL/SQL procedures and packages:
This area will be used when end user query invokes PL/SQL objects
like procedure ,packages etc.
Data Dictionary cache:
dictionary cache is used to stores information about object definitions in the database,
such as columns, tables, indexes, users, privileges, etc.
The row cache holds all the corrected semantics by the parcer interms of rows(records).
Control structures:
This memory used to hold the lock&latches the information.
This locks&latches are require to Control concurrent user operations in open database.
DATABASE BUFFER CACHE:
This component holding the data.which was retrieved from the database.
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.
Database buffer cache is the largest component inside a SGA.
Generally we are allocating the off-size of sga size to database buffer cache.
It is dynamic parameter dynamically we can increase the value if size is not sufficient.
The default value is 48Mb.
The Entire memory allocated to the database buffer cache will be divided into small units called buffers.
The size of buffer inside a database buffer cache will b decided by the parameter DB_BLOCK_SIZE.
This parameter can take 5 different values. They are
2K,4K,8K,16K,32K upto 9i default value of this parameter is 2K.
From 10g onwards the default value of this parameter is 8k.
Note:
OLTP environments are using blocksize 2k,4k,8k
OLAP environments are using blocksize 16k,32k
All buffers inside a database buffer cache is categorised into 4 differnet types.
--free buffers
--pinned buffers
--dirty buffers
--used buffers
FREE BUFFERS:
If buffer is not even used at least once then the buffer is said to be Free buffer.
when we start instance freshly all the buffers are inside will be fresh buffers .
To retrieve data from database to database buffer cache initially we require a free buffer inside dbbc.
PINNED BUFFERS:
If buffer is participating in a transaction currently then that buffer is said to be pinned buffer.
when we store some data into the free buffer then the buffer status
will be done to pinned buffer.
DIRTY BUFFER:
If pinned buffer has modification inside it then the buffer status will be dirty buffer.
dirty buffer is a buffer which has modified value in it.
USED BUFFER:
When end user says commit oracle(writes)flushes dirty buffer from dbbc to database.
there after dirty buffer status will done to used buffers.
A used buffer inside a dbbc can be reused if no free buffer exists for future transaction purpose.
In dbbc all 4 varients of buffer will be organized 2 lists.
**LRU LIST
**WRITE LIST
LRU(least recently used)LIST;
Initially all the buffers in exist in LRU list when
a new transaction arises to allocate a free buffer to the transaction oracle starts
scanning from the LRU end of LRU list.
Once a free buffer is allocated to transaction that buffer will be moved to the
MRU end of LRU list(pinned buffer).
In the process of scanning if it find any dirty buffers that dirty buffer will move to the write list.
the write list contains only dirty buffers.
if 1/3 portion of write list get full (or)1/3 portion of LRU list is completed to finding the
free buffer then all dirty buffers in write list will be returned back to database.
After those dirty buffers status converted to used buffer
there will be added automatically LRU end of LRU list.
REDO LOG BUFFER:
This is a mandatory component inside a SGA.
To manipulate the size of redo log buffer, you can use the LOG_BUFFER
parameter in init.ora initialization parameter file.
It is the smallest component inside a SGA .
this is used to holds redo records.for every sql operation one redo record
will be generated.
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.
Redo record is collection of one or more change vectors.
for every single change on single block oracle generates a change vector.
change vector contains just change information in a block.
when end user says commit instead of writing dirty buffer back into
their original place oracle write redo records redo log buffer to
redo log file to commit operations fastly.
This is called fast commit mechanism(FCM).
JAVA POOL:
It is an unmandatory component.
when we use java language in frontend applications we need to enable this component.
to enable this component to specify the parameter JAVA_POOL_SIZE.
STREAM POOL:
It is an unmandatory component.
we require this component when we work with oracle streams.
oracle streams is technology for replicating data one database to another database.(copying)
To enable this component we need to specify the parameter STREAM_POOL_SIZE.
Optionally we use streams pool for while performing
oracle logical backups.
LARGE POOL:
It is an unmandatory component.
It is used to relieves the burden place on the shared pool.
We require this to perform RMAN BACKUPS.
when we use shared servers.
To enable this component we need to specify parameter LARGE_POOL_SIZE.
KEEP POOL:
It is an unmandatory component.
we do not specify the component shared pool will take automatically.
very frequently accessed objects we store in keep pool
for better performence.
To enable this component we need to specify parameter DB_KEEP_CACHE_SIZE
example:create table emp(eno number)keep;
RECYCLE POOL:
It is an unmandatory component.
The behaviour of this pool exactly opposite to keep pool.
the data what we saved in that will be immediately erased.
To enable this component we need to specify parameter DB_RECYCLE_CACHE_SIZE.
PROCESS GLOBAL AREA(PGA):
When server process is initiated at server side oracle uniquely allocates some amount of memory
inside RAM the server process called Process global area.
This memory can be used only by that server process.
To pecify size of the entire PGA from 9i R1 we use parameter PGA_AGGREGATE_TARGET.
This parameter internally distribute memory among all the existing components inside pga.
Every new server-process claims 5% of memory from the PGA_AGGREGATE_TARGET
To fix maximum limit fof sga in 9i R2 oracle introduced new parameter is SGA_MAX_SIZE
Which specify maximum upper boundary limit of sga.
The components inside a PGA are
**SESSION AREA
**SORT AREA
**PRIVATE SQL AREA
**HASH AREA
**MERGE AREA
SESSION AREA(OR)USER GLOBAL AREA(uga):
Session area contain complete session information of user like
login information,terminal information etc.
SORT AREA:
This area is used to performing temporary sort operations.
below operations internally involves
thay are ORDERBY,GROUP BY,CREATE INDEX,CREATE TABLE AS SELECT.
PRIVATE SQL AREA:
IT holds query specific information and bind variable information
This Area is private to a particular user as well as particular
NOTE:if private sql area exist in sga then don't exist inside pga viceversa.
HASH AREA:
To perform on HASH join operations oracle make use of this area.
MERGE AREA:
To perform on MERGE join operations oracle make use of this area.
ORACLE BACKGROUND PROCESSES:
Every instance will have own bg process.
All bg process will run paralelly in system and they will work automatically each other.
To funcationality of each and every background process well defined in oracle.
In oracle 9i oracle automatically started 5 mandatory background process.
they are
**SMON(SYSTEM MONITOR)
**PMON(PROCESS MONITOR)
**LGWR(LOG WRITER)
**DBWR(DATABASE WRITER)
**CKPT(CHECK POINT)
In 10g extra one more mandatory background process started is RECO(recoverer)process.
In 11g,12c one more mandatory background process memory manager and listener registration process.
Every background process will contain 4 charcters in it's name.
NOTE:
All the above 8 process are mandatory background processes.
If any of the process terminated abnormally then oracle stop the instance(terminated)abrubly.
When instance is down automatically oracle closes the database.
To know all bg process running currently use command.
****************************************************************
SQL>select name from V$bgprocess where paddr<>'00';
****************************************************************
Background process are devided in two types
--mandatory
--un mandatory
un mandatory bg process will not started automatically when we start the instance.
when we enable a particular feature in oracle it is associated background process will be started.
Similarly we desiable the feature oracle automatically stops it's associated bg process.
As per 9i we have total 150+ un mandatory bg process.
in 10g 160+ bg process.
in 11g 180+ bg process.
in 12 c 200+ un mandatory bg process.
To list all mandatory&un mandatory bg process
***********************************************
SQL>select name from v$bgprocess;
******************************************
bg process are running in OS level to view
*************************************
]#ps -ef | grep smon
******************
kill the bg process;
----------------------------------------------
]#kill -9 pid(processid)
---------------------------
LGWR FUNCTIONALITY:
---------------------------------
The purpose of LGWR background process is it has to flush
to the (redorecord)data from redolog buffer to redologfile.
when any concurrent end user say commit oracle calls log writer bg process
to flush the entire content of redologbuffer to redolog files.
If 1/3rd portion of redologbuffer gets full automatically oracle calls logwriter to
move the contents of redologbuffer to redologfiles.
when we shutdown the database normal way then also oracle calls logwriter.
For every 3 seconds once calls logwriter.
When an checkpoint occurs then also oracle calls logwriter.
there only one lgwr bg process for instance.
CKPT BG PROCESS(CHECK POINT):
------------------------------------------------
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.
In database every event will be uniquely identified with an a scn number
This scn number will be begin with 1 and keep on incrementing by one for every one second.
It is used to syncronized database with the information present in controlfile.
when logswitch occurs oracle invoke checkpoint.
The checkpoint event internally ckpt bg process.
Now CKPT bg process looks into controlfile to know the previous checkpointed point and strats
the synchronization process from that point to end of redologfile.
To synchronize ckpt internally calls dbwo .
dbwo flushes all the dirtybuffers present in the dbbc backto the original place inside a database.
checkpoint event will be invoke when issue the command manually.
-----------------------------------------------------
SQL>alter system checkpoint;
------------------------------------------------------
CKPT BG PROCESS will be invoked oracle in below cases.
CASE 1:
----------
When log switch occurs
CASE2:
----------
SQL>alter system checkpoint local;
---------------------------------------------
this is used standalone database .
CASE 3:
---------------
SQL>alter tablespace tablespace_name offline;
2 types offline normal and offline temporary;
CASE 4:
----------
when this issue this command
SQL>alter tablespace tablespace_name readonly;
CASE5:
-----------
SQL>alter tablespace tablespace_name begin backup;
CASE 6:
----------
SQL>shut immediateor
SQL>shut down database normal
CASE 7:
----------
Even we drop a table or truncate table then also check point occured.
DATABASE WRITER (DBWO):
----------------------------------------
the purpose of DBWo bg process is it writes the dirtybuffer inside SGA(DBBC)back to
there original place inside the database.
we can initiate more number of dbwo bg process by specify the parameter db_writer_processs
The maximum value for this parameter 20
the naming convention of dbwo bg process is
dbwo to dbw9(0,1,2,3,4,5,6,7,8,9)
dbwa ot dbwj(a,b,c,d,e,f,g,h,i,j)
In below cases dbwo bg process called by oracle.
**when checkpoint occurs
**for every 3 seconds
**1/3 portion of write list gets full
**1/3 portion of LRU list gets full oracle calls dbwo
**when alter system checkpoint command
**alter tablespace tablespace_name offline ---normal&temporary
**alter tablespace tablespace_name readonly;
**alter tablespace tablespace_name begin backup;
SMON(SYSTEM MONITOR):
Purpose of this bg process mainly used for instance recovery.
only one smon bg process for instance.
It will wakeup every 5 seconds once it is also used for performence housekeeping activities.
like cleaning temporary segments,datadictionary tablespace etc.
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):
Basic purpose of this bg process is used for process recovery
This process will claim all the resources which were being used by
abnormally terminated userprocess.
In this process of claiming the resources it claims all the buffers
inside dbbc.
It claims the dirty buffer as well as server process.
only one pmon bg process for instance.
RECO(recoverer);
-----------------------
This additional bg process from 10g
used for recovering pending transactions in open database.
MMAN(MEMORY MANAGER):
---------------------------------------------
It is additional bg process from 11g.
This is responsible for implementing AMM feature.
when you specify memory_target,memory_max_size parameters
this bg process will be initiated.
LREG:
------------
This is additional bg process in 12c.
this bg process used for registering database with listener.
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.
There are 11 types of Segments in oracle 10g.
Table
Table Partition
Index
Index Partition
Cluster
Rollback
Deferred Rollback
Temporary
Cache
Lobsegment
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.
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 databae 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:
Database Information
Archive log history
Tablespace and datafile records
Redo threads
Database’s creation data
Database name
Current Archive information
Log records
Database Id which is unique to each Database
