Oracle architecture ppt

Oracle DB Archutecture

1. Exploring the Oracle Database Architecture

2. Objectives After completing this lesson, you should be able to: List the major architectural components of Oracle Database Explain the memory structures Describe the background processes Correlate the logical and physical storage structures

3. Oracle Database The Oracle relational database management system (RDBMS) provides an open, comprehensive, integrated approach to information management

4. Connecting to a Server Client Middle tier Server Multitier architecture shown

5. Notes only

6. Database Data files Online redo log files Control files Oracle Database Architecture: Overview Database buffer cache Shared pool Data dictionary cache Library cache PMON SMON Others Server process PGA Archived log files User process Instance RECO ARC n SGA DBW n Redo log buffer LGWR CKPT

7. Connecting to the Database Connection: Communication between a user process and an instance Session: Specific connection of a user to an instance through a user process SQL> Select … Session Connection User User process Server process Session

8. Interacting with an Oracle Database User Database buffer cache Shared pool Data dictionary cache Library cache PMON SMON Others Instance RECO ARC n SGA DBW n Redo log buffer LGWR CKPT User process Server process

9. Notes Only

10. Database Oracle Database Server Structures PMON SMON Others Data files Online redo log files Control files Instance RECO ARC n DBW n LGWR CKPT Storage structures User process Server process Memory structures Processes Database buffer cache Shared pool Data dictionary cache Library cache SGA Redo log buffer

11. Oracle Database Memory Structures DB structures  Memory - Process - Storage SGA Database buffer cache Redo log buffer Java pool Streams pool Shared pool Large pool PGA PGA PGA Background process Server process 1 Server process 2 Shared SQL area Library cache Data dictionary cache Other I/O buffer Response queue Request queue Free memory

12. Notes Only

13. Database Buffer Cache Is part of the SGA Holds copies of data blocks that are read from data files Is shared by all concurrent users PMON SMON Others Instance RECO ARC n DBW n LGWR CKPT Database buffer cache Shared pool Data dictionary cache Library cache SGA Redo log buffer

14. Redo Log Buffer Is a circular buffer in the SGA Holds information about changes made to the database Contains redo entries that have the information to redo changes made by operations such as DML and DDL PMON SMON Others Instance RECO ARC n DBWn LGWR CKPT Database buffer cache Shared pool Data dictionary cache Library cache SGA Redo log buffer

15. Shared Pool Is a portion of the SGA Contains: Library cache Shared SQL area Data dictionary cache Control structures PMON SMON Others Instance RECO ARC n DBW n LGWR CKPT Shared SQL area Library cache Data dictionary cache Other Database buffer cache Shared pool Data dictionary cache Library cache SGA Redo log buffer

16. Notes Only

17. Allocation and Reuse of Memory in the Shared Pool Server process checks the shared pool to see if a shared SQL area already exists for an identical statement. Server process allocates a private SQL area on behalf of the session. Server process Shared pool Shared SQL area Library cache Data dictionary cache Other

18. Notes Only

19. Large Pool Provides large memory allocations for: Session memory for the shared server and the Oracle XA interface I/O server processes Oracle Database backup and restore operations Large pool I/O buffer Response queue Request queue Free memory Java pool Large pool Shared pool Database buffer cache Redo log buffer Streams pool

20. Java Pool and Streams Pool Java pool memory is used in server memory for all session-specific Java code and data in the JVM. Streams pool memory is used exclusively by Oracle Streams to: Store buffered queue messages Provide memory for Oracle Streams processes Java pool Streams pool

21. Process Architecture User process Is started when a database user or a batch process connects to Oracle Database Database processes Server process: Connects to the Oracle instance and is started when a user establishes a session Background processes: Are started when an Oracle instance is started DB structures - Memory  Process - Storage PMON SMON Others Instance RECO ARC n DBW n LGWR CKPT PGA User process Server process Background processes Database buffer cache Shared pool Data dictionary cache Library cache SGA Redo log buffer

22. Process Structures Server n processes SGA Oracle background processes PMON SMON Others RECO ARC n DBW n LGWR CKPT Server Server Server Server Server Database buffer cache Shared pool Data dictionary cache Library cache SGA Redo log buffer

23. Notes Only

24. Database Writer Process (DBW n ) Writes modified (dirty) buffers in the database buffer cache to disk: Asynchronously while performing other processing Periodically to advance the checkpoint Database buffer cache Database writer process Data files DBW n

25. Notes Only

26. LogWriter Process (LGWR) Writes the redo log buffer to a redo log file on disk Writes: When a user process commits a transaction When the redo log buffer is one-third full Before a DBW n process writes modified buffers to disk Redo log buffer LogWriter process Redo log files LGWR

27. Checkpoint Process (CKPT) Records checkpoint information in Control file Each data file header Checkpoint process Data files Control file CKPT

28. System Monitor Process (SMON) Performs recovery at instance startup Cleans up unused temporary segments Instance Temporary segment System Monitor process SMON

29. Process Monitor Process (PMON) Performs process recovery when a user process fails Cleans up the database buffer cache Frees resources that are used by the user process Monitors sessions for idle session timeout Dynamically registers database services with listeners Process Monitor process Database buffer cache Failed user process User PMON

30. Recoverer Process Used with the distributed database configuration Automatically connects to other databases involved in in-doubt distributed transactions Automatically resolves all in-doubt transactions Removes any rows that correspond to in-doubt transactions Recoverer process in database A In-doubt transaction in database B RECO

31. Archiver Processes (ARC n ) Copy redo log files to a designated storage device after a log switch has occurred Can collect transaction redo data and transmit that data to standby destinations Archiver process Archive destination Copies of redo log files ARC n

32. Other Processes MMON: Performs manageability-related background tasks MMNL: Performs frequent and lightweight manageability-related tasks MMAN: Performs automatic memory management tasks CJQ0: Runs user jobs used in batch processing QMNx: Monitors the Streams Advanced Queuing message queues

33. Notes Only

34. Server Process and Database Buffer Cache Buffers: Pinned Clean Free or unused Dirty Database writer process Database buffer cache SGA Data files DBW n Server process