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.
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 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 statemen 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:
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 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, 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.
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
=================
Readers are leaders. You can only master the craft if you constantly study and keep up the hands-on practice. Following are some of the great books for your reference.
Please use the below links to grab your copy. Thank you! 🙂
Expert Oracle Database Architecture (by Thomas Kyte and Darl Kuhn) –> https://amzn.to/2EZ8eUE
Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions –> https://amzn.to/2F17qPh
Oracle Database 12c DBA Handbook (Oracle Press) –> https://amzn.to/2F0tJoh
RMAN Recipes for Oracle Database 12c-A Problem-Solution Approach –> https://amzn.to/2EYY3ja
Oracle Database 12c Performance Tuning Recipes –> https://amzn.to/2F01uGj
Oracle RMAN 11g Backup and Recovery (Oracle Press) –> https://amzn.to/2NQ3OT9
OCA/OCP Oracle Database 12c All-in-One Exam Guide (Exams 1Z0-061, 1Z0-062, & 1Z0-063) –> https://amzn.to/2F0jTD0
OCA/OCP Oracle Database 11g All-in-One Exam Guide with CD-ROM –> https://amzn.to/2SPPJWJ
Expert Oracle RAC Performance Diagnostics and Tuning –> https://amzn.to/2NTXxWr
Pro Oracle Database 11g Administration (Expert’s Voice in Oracle) –> https://amzn.to/2NRj0iD
Monish's DBA Blog 
Very informative. Plz post more number of this kind of articles.
Thanks,
Rezaur
By: C.R.Rahman on May 27, 2008
at 10:39 AM
very done monish…..keep posting these kinds of articles more….. 🙂
By: Hina Mehta on May 31, 2008
at 1:29 PM
GOOD ONE.
By: MURUGESAN on August 7, 2008
at 6:26 PM
Dear its really good specially for beginners…Continues such kind of help
By: Muhammad IQBAL on August 7, 2008
at 9:27 PM
Great!!!!
Maza Aagaya..
Keep it up..
By: Rohit Sharma on August 10, 2008
at 8:54 AM
Hi monish… tht was superb… thanx for sharing and keep posting… thx a lot
By: Vivek on September 30, 2008
at 11:00 AM
Really good description.
Would be thankful for more articles with figures
By: Ramakrishna Raju on July 1, 2009
at 10:01 AM
Very informative and very easy to understand…
thank u
By: lucky on June 24, 2010
at 5:01 AM
It was nice info for beginners
By: devi on November 19, 2010
at 2:24 PM
It was nice for beginners
ths
By: devi on November 19, 2010
at 2:25 PM
It was nice info for beginners thanks
By: devi on November 19, 2010
at 2:26 PM
Really Great Article I have read on DB Architecture. Very much relative, to-the-point, easy to Understand , Informative, Supportive…
No more I can say..
Thanks and keep Posting
Regards
Chandra Prakash
9999323758
By: CHANDRA PRAKASH on April 13, 2011
at 2:45 PM
Really you havae explain oracle architecture in a very easy manner
so that any body can understand
tanks for keep posting in more details
By: Pravin Rajaram on June 16, 2012
at 11:50 AM
hey thanks very much
By: oracle dba architecture on August 16, 2012
at 1:54 AM
its very easy to understand
By: preeti on August 29, 2012
at 6:54 PM
Very good article, easy to understand. Well explained.
By: Oracle on April 2, 2013
at 6:11 PM
it is very-very good………………..
By: Narayan on April 17, 2013
at 12:42 PM
thanks a lot….its very easy to understand.Bless you
By: bebe on May 1, 2013
at 9:28 PM
It’s very nice.Every time i confused when i read the architecture.But it gives a clear uderstanding for me
By: aneela on June 7, 2013
at 2:17 PM
Awesome good:) posted everything is in precise manner……….easy to understood……..
By: saru on July 30, 2013
at 4:49 PM
Good one and very informative…:)
By: Pawan on August 19, 2013
at 12:55 PM
thank you very much, it’s a usefull Articl
By: subzero on September 9, 2013
at 6:27 PM
Great beat ! I wish to apprentice while you amend your site, how can i subscribe for a weblog site?
The account helped me a applicable deal. I were a little bit acquainted of this your broarcast offered bright clear concept
By: casino sverige on September 13, 2013
at 10:06 PM
easy understanding of oracle architecture and its basics…keep posting this type of articles ..
thank you very much
By: ilahi pasha b on September 23, 2013
at 3:34 PM
Really usefull……………….Thanks a lot 🙂
By: Sunny on October 1, 2013
at 11:41 PM
Its really easy to understand …. Thank you very much
By: Giri on October 29, 2013
at 6:46 PM
Nice Artical and easy to understand 🙂
By: Nidhika on November 6, 2013
at 2:22 PM
Its really easy to understand …. Thank you very much
By: praful on November 25, 2013
at 5:19 PM
great
By: ARINDAM on December 22, 2013
at 9:47 PM
very very nice and easy explanation..thank u very much…
By: Chithu on January 27, 2014
at 10:20 AM
very nice explanation.. 🙂
By: Pooja Ghag on February 11, 2014
at 2:24 PM
Very well mannered and easy to understand..Good document 🙂
By: Vrushali P on February 28, 2014
at 6:08 PM
Excellent work,even beginner can understand it very easily.
By: Sravan on March 24, 2014
at 5:49 AM
thanks a lot… your article is really easy to understand for beginners.. keep doing the greAT work..
By: sameer khan on March 24, 2014
at 12:44 PM
Reblogged this on SHAHKAR AHMED and commented:
great article
By: shahkar ahmed on April 13, 2014
at 11:41 PM
Too Good It’s easy to understand
By: Deepak Joshi on April 21, 2014
at 10:10 PM
excellent thanks u monish
By: santosh on May 8, 2014
at 5:09 PM
the best article man
By: tarun on July 10, 2014
at 10:46 PM
Very nice.. Thank u. Please post more articles.
By: saravanan on July 29, 2014
at 5:29 PM
i have read the Db very nice ,add more details
By: Gsuriyan on September 8, 2014
at 12:59 PM
JazaakAllah
By: razi on October 14, 2014
at 10:01 PM
Good one 🙂
By: G@NE$HA on November 30, 2014
at 2:46 PM
Good work…Keep it up!!!
By: HITESH BAJPAI on July 29, 2015
at 8:16 PM
super……monish
By: sai on August 28, 2015
at 10:45 AM
Easy to understand… user friendly..Superb.
By: Arvind pandey on September 4, 2015
at 9:42 AM
VERY NICE MONISH.. KEEP IT UP
By: fahed on September 6, 2015
at 12:37 AM
Easy to Understand…..Thanqqqq
By: Komal on September 17, 2015
at 12:13 PM
Very nice explanation which is easily understandable by anyone. Good…
By: Jigar Thakker on October 12, 2015
at 3:36 AM
Very nice explanation!
By: Venkanna on November 27, 2015
at 2:25 PM
Great Work .
By: christin on December 16, 2015
at 2:59 PM
Best information for new comers…… keep going more topic as like this…
By: sandip chavan on December 22, 2015
at 7:06 PM
its very very useful to me as laymen. Thanks a lot!!!
By: lahu on April 15, 2016
at 7:50 PM
Its Awesome……Thank You Very Much…It helps me a lot
By: Wasif on May 2, 2016
at 3:47 PM
very use for me and easy to understand]
By: mahavishnu on June 1, 2016
at 4:34 PM
in this website is very useful for me and all the dba beginner.
By: karthi on June 2, 2016
at 11:39 AM
Thanks!
By: Monish on June 2, 2016
at 1:34 PM
Great brother!!! Really useful.
By: nishikant on July 6, 2016
at 9:33 PM
Thanks very much monish
By: Manish Kumar on September 24, 2016
at 12:10 AM
Love you brother,very useful information.Thanks for this!!
By: THE BK MEN on October 4, 2016
at 12:34 AM
Thanks brother very useful information
By: THE BK MEN on October 4, 2016
at 12:36 AM
Thanks a lot for providing very useful information.its very easy to understand
By: rn on November 28, 2016
at 12:19 PM
Really Good……
By: Devanand on December 27, 2016
at 4:59 PM
Good explanation……..
By: Pramod on January 6, 2017
at 11:05 PM
Very good explanation about the oracle. The instance you presented really good and very well understand by me.
http://www.erptree.com/course/oracle-fusion-tutorial/
By: oraclefusionfinancialsonlinetraining on January 18, 2017
at 12:22 PM
Hi,
your article so informative and i have cleared all of my doubts.
your way of explanation is awesome thank you for sharing useful information.
By: oraclefusionfinancialsonlinetraining on January 24, 2017
at 5:15 PM
simply explained mate.. Thanks
By: Ashu Raina on February 6, 2017
at 9:06 PM
Concise, crisp, to the point. Many thanks for the article
By: GAURAV MISHRA on March 18, 2017
at 12:06 PM
Thanks Gaurav
By: Monish on April 11, 2017
at 10:39 AM
thanks. very helpful article and explained in very easy way to understand the oracle architecture. Keep posting….
By: Rahul Shaw on March 29, 2017
at 4:17 PM
I am new to this field but this helped me to understand every aspect keenly.
By: jyothi on April 8, 2017
at 3:56 PM
Very helpful article.. Keep up the good work 🙂
By: Ganesh on May 22, 2017
at 11:46 AM
Thanks you , Its Very helpful …. 🙂
By: Bala on June 21, 2017
at 1:41 PM
Superb. You understand what we are looking for 🙂
By: Santosh Dubey on July 8, 2017
at 3:16 PM
Good to know that the article helped. 😃
By: Monish on July 11, 2017
at 10:01 PM
Easy to understand and very useful ……. Thanks …………….
By: Satyaranjan Sahoo on September 21, 2017
at 1:47 AM
really very nice description.please, write all concept like this.
By: Ghanashyam Sahoo on December 3, 2017
at 11:38 PM
hi sahoo
please tell me draw and neetly dscribe the oracle architecture along individual subtittels
By: lakshmi on January 4, 2018
at 5:48 PM
Really Informative..Thanks a lot
By: nivetha on February 2, 2018
at 2:57 PM
give me a post about how to install oracle argus 7.0.3
By: pradeep on February 27, 2018
at 12:14 PM
very much informative, thanks a lot
By: Ram on May 30, 2018
at 8:25 PM
Very Informative
By: Martin Ebenezer on June 12, 2018
at 12:45 PM
Hello admin, i’ve been reading your website for some time
and I really like coming back here. I can see that you
probably don’t make money on your website. I know one interesting method of earning money, I think you
will like it. Search google for: dracko’s tricks
By: DewittBiggie on July 6, 2018
at 3:17 AM
Hlw Monish, it is very helpful article for everyone… Specially for beginners.. 😄😄
By: Savita kumari on August 3, 2018
at 12:09 AM
Thanks !
By: Monish on March 12, 2019
at 9:37 PM