Oracle Database Components (PDF)

How Oracle Database Components Work Together: A Simple Scenario Imagine you are running an online bookstore. Your website receives an order from a customer to buy a book. Here’s how the Oracle Database architecture components work together to process this order: 1. The Instance: Memory Structures...

How Oracle Database Components Work Together: A Simple Scenario Imagine you are running an online bookstore. Your website receives an order from a customer to buy a book. Here’s how the Oracle Database architecture components work together to process this order: 1. The Instance: Memory Structures and Background Processes An Oracle instance is the brain of the database, consisting of memory (to temporarily hold data) and background processes (to manage and organize operations). Memory Structures: When the customer places an order, the system temporarily stores the customer’s information in memory: o System Global Area (SGA): ▪ Shared Pool: Stores the SQL query (e.g., "Insert new order") and checks if it's already been processed before. ▪ Database Buffer Cache: Temporarily holds the details of the book order and any other required data (like the book inventory) before writing it to permanent storage. ▪ Redo Log Buffer: Tracks every change made to the database so it can be recovered in case of failure. o Program Global Area (PGA): Handles session-specific tasks, like sorting the books by category for this customer. Background Processes: These processes ensure everything runs smoothly: o DBWn (Database Writer): Later writes the book order from memory to the database file on disk. o LGWR (Log Writer): Logs the order details into redo log files to ensure the transaction is saved. o PMON (Process Monitor): If the customer session crashes, it cleans up leftover resources.  o SMON (System Monitor): If the database crashes, it recovers unfinished tasks, like incomplete book orders. 2. Logical and Physical Storage Structures The database uses logical structures to organize the data and physical structures to save it on disk. Logical Storage: o The bookstore database has a tablespace for customer data, another for orders, and one for books. o The segments in the tablespace hold related data, like a table for book orders. o Extents and data blocks are smaller units that ensure efficient storage. Physical Storage: o The data files on disk store all customer, order, and book information. o Redo log files store the history of changes, ensuring that the book order isn’t lost if the system crashes. o Control files track where the data files are located and ensure everything stays organized. 3. Database Management Tools The DBA (Database Administrator) uses tools to set up, monitor, and maintain the system: SQL*Plus: The DBA writes the SQL query to add the order. Oracle Enterprise Manager (OEM): Monitors the performance of the database (e.g., ensuring it handles customer orders efficiently). RMAN (Recovery Manager): If something goes wrong, RMAN helps recover the database, ensuring no book orders are lost. SQL Developer: Makes it easy for developers to design the database, such as creating a table for book categories.  DBCA: Helps the DBA create the database when the online bookstore was first set up. How It All Comes Together: 1. A customer orders a book. 2. The SQL command ("Insert new order") is received and stored in the Shared Pool of the SGA. 3. The order details are temporarily saved in the Database Buffer Cache. 4. The Redo Log Buffer tracks the order to ensure recovery if something crashes. 5. The LGWR writes the redo logs to disk, securing the transaction. 6. The DBWn writes the order to the appropriate data file on disk. 7. The control file ensures the database structure is intact. 8. If the server crashes during this process, SMON and RMAN recover the database to its last safe state. By the end of this process, the customer's order is saved in the database, and everything is ready for the next transaction. Every component plays a role in making this happen seamlessly.