Wednesday, 21 May 2014

Oracle Basic Architecture

Oracle Architecture (Basic):

Below is the typical architecture of Oracle 11g. When you fire a SQL from any of the client tools like (SQL Developer/Toad/ or even SQLPLUS) below processes gets triggered at the back end.

Background Processes: It start with the database instance and perform maintenance tasks such as performing instance recovery, cleaning up processes, and writing redo buffers to disk, and so on.

Server Processes: perform work based on a client request.

clip_image001

Credits: docs.oracle.com

Terminologies & Functionality:

1. SGA

System global area. A group of shared memory structures that contain data and control information for one Oracle database instance.

2. PGA

Program global area. A memory buffer that contains data and control information for a server process.

3. Shared pool

Portion of the SGA that contains shared memory constructs such as shared SQL areas.

4. Large pool

Optional area in the SGA that provides large memory allocations for backup and restore operations, I/O server processes, and session memory for the shared server and Oracle XA.

5. Database buffer cache

The portion of the system global area (SGA) that holds copies of data blocks. All client processes concurrently connected to the instance share access to the buffer cache.

6. Redo log buffer

Memory structure in the SGA that stores redo entries—a log of changes made to the database. The database writes the redo entries stored in the redo log buffers to an online redo log file, which is used if instance recovery is necessary.

7. Online redo log

The set of two or more online redo log files that record all changes made to Oracle Database data files and control file. When a change is made to the database, Oracle Database generates a redo record in the redo buffer. log writer (LGWR) writes the contents of the redo buffer to the online redo log.

Redo Log has set of files that protect altered database data in memory that has not been written to the data files. The redo log can consist of two parts: the online redo log and the archived redo log.

8. Shared SQL area:

An area in the shared pool that contains the parse tree and execution plan for a SQL statement. Only one shared SQL area exists for a unique statement.

9. Back Ground Processes:

A process that consolidates functions that would otherwise be handled by multiple Oracle programs running for each client process. The background processes asynchronously perform I/O and monitor other Oracle processes.

1. Mandatory Background Processes

These processes run by default in a database instance started with a minimally configured initialization parameter file

i. Process Monitor Process (PMON) :

PMON is responsible for cleaning up the database buffer cache and freeing resources that the client process was using. PMON also registers information about the instance and dispatcher processes with the Oracle Net listener

ii. System Monitor Process (SMON): is in charge of a variety of system-level cleanup duties like Performing instance recovery, Recovering terminated transactions, cleaning up unused temporary segments, Coalescing contiguous free extents.

iii. Database Writer Process (DBWn): It writes the contents of database buffers to data files. Typically database buffer is the memory area that stores copies of data blocks read from data files.

iv. Log Writer Process (LGWR) :

The log writer process (LGWR) manages the redo log buffer. LGWR writes one contiguous portion of the buffer to the online redo log.

v. Checkpoint Process (CKPT) :

The checkpoint process (CKPT) updates the control file and data file headers with checkpoint information and signals DBWn to write blocks to disk

vi. Manageability Monitor Processes (MMON and MMNL):

The manageability monitor process (MMON) performs many tasks related to the Automatic Workload Repository (AWR). For example, MMON writes when a metric violates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects.

The manageability monitor lite process (MMNL) writes statistics from the Active Session History (ASH) buffer in the SGA to disk. MMNL writes to disk when the ASH buffer is full.

vii. Recoverer Process (RECO)

In a distributed database, the Recoverer process (RECO) automatically resolves failures in distributed transactions. The RECO process of a node automatically connects to other databases involved in an in-doubt distributed transaction.

2. Optional Background Processes:

i. Archiver Processes (ARCn):

The Archiver processes (ARCn) copy online redo log files to offline storage after a redo log switch occurs. These processes can also collect transaction redo data and transmit it to standby database destinations. ARCn processes exist only when the database is in ARCHIVELOG mode and automatic archiving is enabled.

ii. Job Queue Processes (CJQ0 and Jnnn):

Oracle Database uses job queue processes to run user jobs, often in batch mode.

iii. Flashback Data Archiver Process (FBDA):

It archives historical rows of tracked tables into Flashback Data Archives. When a transaction containing DML on a tracked table commits, this process stores the pre-image of the rows into the Flashback Data Archive.

iv. Space Management Coordinator Process (SMCO) :

The SMCO process coordinates the execution of various space management related tasks, such as proactive space allocation and space reclamation. SMCO dynamically spawns slave processes (Wnnn) to implement the task.

3. Slave Processes

Slave processes are background processes that perform work on behalf of other processes.

i. I/O Slave Processes:

I/O slave processes (Innn) simulate asynchronous I/O for systems and devices that do not support it. In asynchronous I/O, there is no timing requirement for transmission, enabling other processes to start before the transmission has finished.

ii. Parallel Query Slaves:

In parallel execution or parallel processing, multiple processes work together simultaneously to run a single SQL statement. By dividing the work among multiple processes, Oracle Database can run the statement more quickly. For example, four processes handle four different quarters in a year instead of one process handling all four quarters by itself.

105 comments :

  1. Thanks for sharing the information

    For more info : Oracle Certification Program

    ReplyDelete
  2. I'm so happy to find the good inofrmation On PL/SQL thanks for your effort .friends learn Oracle PLSQL e-learning By 8 years experienced trainer

    ReplyDelete
  3. Data Warehousing Training "
    Data Warehousing Online Training
    Send ur Enquiry to contact@21cssindia.com
    Introduction to Data warehousing
    Who needs Data warehouse?
    Why Data warehouse is required?
    Types of Systems" more… Online Training- Corporate Training- IT Support U Can Reach Us On +917386622889 - +919000444287 http://www.21cssindia.com/courses/data-warehousing-online-training-29.html

    ReplyDelete
  4. It's a nice blog with lot of information,thanks for sharing...
    Hi this is babu, I want to share some information for you,If you want to join any professional course visit this website
    oracle training in chennai It's a best place to learn oracle in chennai
    thanks, babu

    ReplyDelete
  5. It's a nice blog with lot of information,thanks for sharing...
    Hi this is babu, I want to share some information for you,If you want to join any professional course visit this website
    oracle training in Chennai It's a best place

    to learn oracle in chennai

    thanks, babu

    ReplyDelete
  6. Your blog is really useful for me. Thanks for sharing this informative blog. If anyone wants to get real time Oracle Training Chennai reach FITA Oracle Training Center in Chennai. They give professional and job oriented training for all students.

    ReplyDelete
  7. Your blog is really useful for me. If anyone want to become an oracle certified professional reach FITA, which offers best Oracle Training in Chennai with years of experienced professionals. For more details about oracle, sql training visit this site.

    Oracle Training in Chennai

    ReplyDelete

  8. Thanks for sharing this informative blog. FITA provides Salesforce Course in Chennai with years of experienced professionals and fully hands-on classes. Salesforce is a cloud based CRM software. Today's most of the IT industry use this software for customer relationship management. To know more details about salesforce reach FITA Academy. Rated as No.1 Salesforce Training Institutes in Chennai.

    ReplyDelete
  9. SAP Training Institute in Chennai

    Thanks for sharing this valuable information.and I gathered some information from this blog. I did SAP Training in Chennai, at FITA Academy which offer best SAP Course in Chennai with years of experienced professionals.

    SAP Institutes in Chennai

    ReplyDelete

  10. The information you posted here is useful to make my career better keep updates...If anyone want to get Cloud Training in Chennai, Please visit FITA academy located at Chennai. Rated as No.1 Cloud Computing Training Centers in Chennai

    ReplyDelete
  11. I have read your blog and i got a very useful and knowledgeable information from your blog.You have done a great job . If anyone want to get Salesforce Developer Training in Chennai, Please visit FITA academy located at Chennai Velachery. Rated as No.1 Salesforce Training Institutes in Chennai.

    ReplyDelete
  12. Oracle Training

    The information you posted here is useful to make my career better keep updates..If anyone want to become an oracle certified professional reach FITA Oracle Training Institutes in Chennai, which offers Best Oracle Training Chennai with years of experienced professionals.

    ReplyDelete

  13. Your posts is really helpful for me.Thanks for your wonderful post. I am very happy to read your post.very nice !!!
    Mysql Training in chennai | Mysql Training chennai | Mysql course in chennai | Mysql course chennai

    ReplyDelete
  14. Thanks for sharing informative article on Salesforce technology. Your article helped me a lot to understand the career prospects in cloud computing technology. Cloud Computing Training in Chennai | Cloud Computing Training

    ReplyDelete
  15. I gathered a lot of information through this article.Every example is easy to understandable and explaining the logic easily.Thanks.
    cloud computing training in chennai | cloud computing training chennai | cloud computing course in chennai | cloud computing course chennai

    ReplyDelete
  16. Hi , great faq collection, the interview questions you provided on Oracle SQL PL/SQL Technology is appreciable. thanks for sharing, Please continue blogging.

    ReplyDelete
  17. There are lots of information about latest technology and how to get trained in them, like Hadoop Training Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Hadoop Training in Chennai). By the way you are running a great blog. Thanks for sharing this (Salesforce Training in Chennai).

    ReplyDelete
  18. Hi I’m Rahul doing my final year BE in computer. I have not got placed in any campus recruitment, so planning to go for Selenium Training in Chennai. So kindly help me by guiding on where I could do Selenium Training in Chennai which also helps in placement services.

    ReplyDelete
  19. Hello Rahul, I’m Shashaa from FITA Academy. We conduct QTP Training in Chennai. the course will cover in and out of QTP, completely and make you a complete professional. So if you are interested to do QTP Training in Chennai get to us. We also do placement services for our students.

    ReplyDelete
  20. Hi, PHP is a server-side general purpose scripting language used for web development, which is used in almost all personal blogs. It is a very easy to understand language that can be easily learnt with a proper PHP Training in Chennai. You can join a course at FITA, where the best PHP Training in Chennai is taken, and excel as a web developer.

    ReplyDelete
  21. I have read your blog, it was good to read & I am getting some useful info's through your blog keep sharing... Informatica is an ETL tools helps to transform your old business leads into new vision. Learn Informatica training in chennai from corporate professionals with very good experience in informatica tool.
    Regards,
    Best Informatica Training In Chennai|Informatica training center in Chennai|Informatica training chennai

    ReplyDelete
  22. Oracle DBA Training in Chennai
    Thanks for sharing this informative blog. I did Oracle DBA Certification in Greens Technology at Adyar. This is really useful for me to make a bright career..

    ReplyDelete
  23. Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
    Websphere Training in Chennai

    ReplyDelete
  24. Data warehousing Training in Chennai
    I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly..

    ReplyDelete
  25. Selenium Training in Chennai
    Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

    ReplyDelete
  26. Oracle Training in chennai
    Thanks for sharing such a great information..Its really nice and informative..

    ReplyDelete
  27. SAP Training in Chennai
    This post is really nice and informative. The explanation given is really comprehensive and informative..

    ReplyDelete
  28. This information is impressive..I am inspired with your post writing style & how continuously you describe this topic. After reading your post,thanks
    for taking the time to discuss this, I feel happy about it and I love learning more about this topic
    Android Training In Chennai In Chennai

    ReplyDelete
  29. Pretty article! I found some useful information in your blog, it was awesome to read,thanks for sharing this great content to my vision, keep sharing..
    Unix Training In Chennai

    ReplyDelete
  30. I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
    SalesForce Training in Chennai

    ReplyDelete
  31. This comment has been removed by the author.

    ReplyDelete
  32. There are lots of information about latest technology and how to get trained in them, like Best Hadoop Training In Chennai in Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies Hadoop Training in Chennai By the way you are running a great blog. Thanks for sharing this blogs..

    ReplyDelete
  33. hai,i have to learned to lot of information about java Gain the knowledge and hands-on experience you need to successfully design, build and deploy applications with java.
    Java Training in Chennai

    ReplyDelete
  34. hybernet is a framework Tool which helps in Functional and Regression testing of an application. If you are interested in hybernet training, our real time working.
    Hibernate Training in Chennai,

    ReplyDelete
  35. Looking for real-time training institue.Get details now may if share this link visit
    Spring Training in chennai
    oraclechennai.in:

    ReplyDelete
  36. This comment has been removed by a blog administrator.

    ReplyDelete
  37. Job oriented form_reports training in Chennai is offered by our institue is mainly focused on real time and industry oriented. We provide training from beginner’s level to advanced level techniques thought by our experts.
    forms-reports Training in Chennai

    ReplyDelete
  38. This is really an awesome article. Thank you for sharing this.It is worth reading for everyone. Visit us:
    Oracle Training in Chennai

    ReplyDelete
  39. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.
    Oracle DBA Training in Chennai

    ReplyDelete
  40. great article!!!!!This is very importent information for us.I like all content and information.I have read it.You know more about this please visit again.
    Oracle RAC Training in Chennai

    ReplyDelete
  41. Wonderful tips, very helpful well explained. Your post is definitely incredible. I will refer this to my friend.
    SalesForce Training in Chennai

    ReplyDelete
  42. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.
    Java Training in Chennai

    ReplyDelete
  43. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    PHP Training in Chennai

    ReplyDelete
  44. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.Nice article i was really impressed by seeing this article, it was very interesting and it is very useful for me..
    Android Training in Chennai

    ReplyDelete
  45. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    SAP Training in Chennai

    ReplyDelete
  46. Excellent information with unique content and it is very useful to know about the information based on blogs.
    Hadoop Training in Chennai

    ReplyDelete
  47. This is amazing step by step guide!

    http://www.tekclasses.com/

    ReplyDelete
  48. Great stuff!
    http://www.tekclasses.com/

    ReplyDelete
  49. Very Clear Step By Step Guide, Thank You for sharing an important information http://www.vaysinfotech.com/

    ReplyDelete
  50. Nice post.Thanks for sharing.Watch this Video to learn basics of SQL.
    Its only for beginners.Learn basics of SQL in 1 Hour.
    SQL for Beginners.Try this.....

    ReplyDelete
  51. It’s too informative blog and I am getting conglomerations of info’s. Thanks for sharing; I would like to see your updates regularly so keep blogging. If anyone looking car just get here
    Regards,
    sas training in Chennai|sas course in Chennai|sas institutes in Chennai

    ReplyDelete
  52. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging…
    Regards,
    Oracle apps Training in Chennai|Oracle DBA Training in Chennai

    ReplyDelete
  53. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
    Informatica Training In Chennai
    Hadoop Training In Chennai
    Oracle Training In Chennai
    Pega Training In Chennai
    Hadoop Training In Chennai

    ReplyDelete
  54. Excellant content thanks for sharing the unique information and keep posting.
    Android Training in Chennai | android courses in Chennai | FITA Velachery | FITA Training

    ReplyDelete
  55. Best Java Training Institute In ChennaiThis information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..

    ReplyDelete
  56. very nice article.Thanks for sharing the post...!
    Java Online Training

    ReplyDelete
  57. Paris airport transfer - Parisairportransfer is very common in Paris that provides facilities to both the businessmen and the tourists. We provide airport transfers from London to any airport in London and also cruise transfer services at very affordable price to our valuable clients.

    Paris taxi
    Paris airport shuttle
    paris hotel transfer
    paris airport transfer
    paris shuttle
    paris car service
    paris airport service
    disneyland paris transfer
    paris airport transportation
    beauvais airport transfer
    taxi beauvais airport
    taxi cdg airport
    taxi orly airport

    ReplyDelete
  58. logon to http://informatica.vpweb.in for remote / online Informatica server access

    ReplyDelete
  59. This comment has been removed by the author.

    ReplyDelete
  60. http://mkniit.blogspot.in

    ReplyDelete

  61. Excellant content thanks for sharing the unique information and keep posting.

    Bank Exam Questions and Answers

    Group Exam Questions and Answers

    ReplyDelete

  62. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing

    dot net training in chennai

    php training in chennai

    java training in chennai

    ReplyDelete
  63. Thank you for sharing this useful story.
    I think your shared information is helpful to me and who are want update their knowledge, who want to started their career with Oracle 12c PL/SQL Online Training.

    ReplyDelete
  64. Informatica
    Very valuable information provided, folks will be getting good knowledge Informatica online training

    ReplyDelete
  65. DreamHost is one of the best web-hosting company with plans for any hosting needs.

    ReplyDelete
  66. Thank for sharing this useful information;
    It is very useful to me and who are wanted to learn or update their knowledge on Oracle PL/SQL through Oracle PL/SQL online training or offline training.

    ReplyDelete
  67. Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

    iOS Training in Chennai

    ReplyDelete
  68. it's realy nice. it's realy helpful for sql bigginers.keep it your sharing.more talking to Website for school uk

    ReplyDelete
  69. really awesome post for sql basic learners thank you for sharing useful content...

    http://www.erptree.com/course/oracle-apps-training-in-hyderabad/

    http://www.erptree.com/course/oracle-fusion-training-in-hyderabad/

    ReplyDelete
  70. very good and informative article,Thanks for sharing informative article its very useful to me..

    http://erptree.com/course/oracle-fusion-hcm-online-training-in-san-francisco-bay-area-california-usa/

    ReplyDelete
  71. Thanks for sharing the very useful info about Oracle and please keep updating........

    ReplyDelete
  72. Thanks for sharing the very useful info about SQL Server and please keep updating........

    ReplyDelete
  73. I found your blog has very interesting topics,thanks for sharing such an interesting information with us.
    http://www.calfre.com/Hyderabad/Oracle-Fusion-SCM-Online-Training/listing

    ReplyDelete

  74. Thanks for posting useful information.You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...Really it was an awesome article...very interesting to read..
    please sharing like this information......
    Android training in chennai
    Ios training in chennai

    ReplyDelete

  75. Really it was an awesome article...very interesting to read..You have provided an nice article....Thanks for sharing..
    Web Design Company
    Web Development Company

    ReplyDelete
  76. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information&its very useful to me...
    Android training in chennai
    Ios training in chennai

    ReplyDelete
  77. In these, an article using now automation tools learning beginner very useful. If you want to be learning from automation testing tools Jenkins, Maven, Ant any more automation tool reached and visit there for below web page.If you want to be learning from automation testing tools reached and visit there for below web page.
    Selenium Training in Chennai

    ReplyDelete

  78. These ways are very simple and very much useful, as a beginner level these helped me a lot thanks fore sharing these kinds of useful and knowledgeable information.
    Android App Development Company

    ReplyDelete
  79. Nice blog. Really helpful information about Oracle …. Please keep update some more…………

    ReplyDelete
  80. great and nice blog thanks sharing..I just want to say that all the information you have given here is awesome...Thank you very much for this one.
    web design Company
    web development Company
    web design Company in chennai
    web development Company in chennai
    web design Company in India
    web development Company in India

    ReplyDelete
  81. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
    iOS App Development Company
    iOS App Development Company

    ReplyDelete
  82. I wondered upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.

    Fitness SMS
    Fitness Text
    Salon SMS
    Salon Text
    Investor Relation SMS
    Investor Relation Text

    ReplyDelete
  83. it is really amazing...thanks for sharing....provide more useful information...
    Mobile app development company

    ReplyDelete