Posted by: Monish | May 24, 2008

BASIC ORACLE ARCHITECTURE

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.

  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.

    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:

  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

Advertisements

Responses

  1. Very informative. Plz post more number of this kind of articles.
    Thanks,
    Rezaur

  2. very done monish…..keep posting these kinds of articles more….. 🙂

  3. GOOD ONE.

  4. Dear its really good specially for beginners…Continues such kind of help

  5. Great!!!!
    Maza Aagaya..
    Keep it up..

  6. Hi monish… tht was superb… thanx for sharing and keep posting… thx a lot

  7. Really good description.
    Would be thankful for more articles with figures

  8. Very informative and very easy to understand…
    thank u

  9. It was nice info for beginners

  10. It was nice for beginners

    ths

  11. It was nice info for beginners thanks

  12. 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

    • Really you havae explain oracle architecture in a very easy manner
      so that any body can understand
      tanks for keep posting in more details

  13. hey thanks very much

  14. its very easy to understand

  15. Very good article, easy to understand. Well explained.

  16. it is very-very good………………..

  17. thanks a lot….its very easy to understand.Bless you

  18. It’s very nice.Every time i confused when i read the architecture.But it gives a clear uderstanding for me

  19. Awesome good:) posted everything is in precise manner……….easy to understood……..

  20. Good one and very informative…:)

  21. thank you very much, it’s a usefull Articl

  22. 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

  23. easy understanding of oracle architecture and its basics…keep posting this type of articles ..
    thank you very much

  24. Really usefull……………….Thanks a lot 🙂

  25. Its really easy to understand …. Thank you very much

  26. Nice Artical and easy to understand 🙂

  27. Its really easy to understand …. Thank you very much

  28. great

  29. very very nice and easy explanation..thank u very much…

  30. very nice explanation.. 🙂

  31. Very well mannered and easy to understand..Good document 🙂

  32. Excellent work,even beginner can understand it very easily.

  33. thanks a lot… your article is really easy to understand for beginners.. keep doing the greAT work..

  34. Reblogged this on SHAHKAR AHMED and commented:
    great article

  35. Too Good It’s easy to understand

  36. excellent thanks u monish

  37. the best article man

  38. Very nice.. Thank u. Please post more articles.

  39. i have read the Db very nice ,add more details

  40. JazaakAllah

  41. Good one 🙂

  42. Good work…Keep it up!!!

  43. super……monish

  44. Easy to understand… user friendly..Superb.

  45. VERY NICE MONISH.. KEEP IT UP

  46. Easy to Understand…..Thanqqqq

  47. Very nice explanation which is easily understandable by anyone. Good…

  48. Very nice explanation!

  49. Great Work .

  50. Best information for new comers…… keep going more topic as like this…

  51. its very very useful to me as laymen. Thanks a lot!!!

  52. Its Awesome……Thank You Very Much…It helps me a lot

  53. very use for me and easy to understand]

  54. in this website is very useful for me and all the dba beginner.

    • Thanks!

  55. Great brother!!! Really useful.

  56. Thanks very much monish

  57. Love you brother,very useful information.Thanks for this!!

  58. Thanks brother very useful information

  59. Thanks a lot for providing very useful information.its very easy to understand

  60. Really Good……

  61. Good explanation……..

  62. 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/

  63. 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.

  64. simply explained mate.. Thanks

  65. Concise, crisp, to the point. Many thanks for the article

    • Thanks Gaurav

  66. thanks. very helpful article and explained in very easy way to understand the oracle architecture. Keep posting….

  67. I am new to this field but this helped me to understand every aspect keenly.

  68. Very helpful article.. Keep up the good work 🙂

  69. Thanks you , Its Very helpful …. 🙂

  70. Superb. You understand what we are looking for 🙂

    • Good to know that the article helped. 😃

  71. Easy to understand and very useful ……. Thanks …………….

  72. really very nice description.please, write all concept like this.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories