Database Course Lecture 6 Outline 2024/2025

Course Outline

• Database (2) course, 2024/2025
• Lecture 6
• Course outline covers weeks 1-15
• Topics include: Advanced concepts of databases; implementation of databases; safety/integrity and security of data; data backup & recovery; midterm exam; control of simultaneous treatment; distributed database systems; practical applications of database programming language implementation; practical and oral exam; final exam

Database Topics

• Week 1-3 (CH1): Advanced concepts of databases
• Week 4 (CH2): Implementation of databases
• Week 5 (CH3): Safety, integrity, and security of data
• Week 6 (CH4): Data backup and recovery
• Week 7: Midterm Exam
• Week 7-8 (CH4): Control of simultaneous treatment (concurrency control)
• Week 10-11 (CH5): Distributed database systems - recent trends of distributed data analytics, e.g., big data analytics
• Weeks 12-13 (Ch 5): Practical applications in a language of programming and implementation of databases
• Week 14: Practical Exam & Oral Exam
• Week 15: Final Exam

Oracle Database Architecture

• An Oracle database is made up of an instance and its associated databases
• The instance contains memory structures and background processes
• When the instance starts, a shared memory area (SGA) is created and background processes are started
• Databases consist of both physical and logical structures
• Physical and logical structures are separate, allowing physical storage changes without affecting logical access
• 2024/2025

Oracle Database Memory Structures

• System Global Area (SGA):
  • A group of shared memory structures
  • Contains data and control information for one Oracle Database instance
  • Shared by all server and background processes
  • Examples of data stored include cached data blocks, shared SQL areas
• Program Global Area (PGA):
  • Memory regions containing data and control information for a server or background process
  • Non-shared memory created when the server or background process starts
  • Each server process and background process has its own PGA
• Database buffer cache:
  • Part of the SGA
  • Holds copies of data blocks read from data files
  • Shared by all concurrent users
• Redo log buffer:
  • Part of the SGA
  • Circular buffer
  • Holds information about changes made to the database
  • Contains redo entries to redo changes
• Shared pool:
  • Part of the SGA
  • Contains the library cache, shared SQL area, data dictionary cache, query results, and PL/SQL function result cache
  • Buffers for parallel execution messages and control structures
  • Used by concurrent users to access the data dictionary
  • Contains SQL statement in shared SQL area
• Large pool: (optional)
  • Large memory allocations
  • Session memory for the shared server and the Oracle XA interface (used when transactions interact with more than one database)
  • I/O server processes and Oracle Database backup and restore operations

Process Architecture

• User Process:
  • Started when a user or batch process connects to the Oracle database
• Server Process:
  • Connects to the Oracle instance
  • Starts when a user establishes a session
• Background Processes:
  • Start when an Oracle instance starts

Background Processes

• Database writer process (DBWn):
  • Writes modified buffers in the database buffer cache to disk
  • Asynchronously while performing other processing
  • Periodically to advance the checkpoint
• Log writer process (LGWR):
  • Writes the redo log buffer to a redo log file on disk
  • Writes when a user commits a transaction
  • When the redo log buffer is one-third full
  • Before DBWn process writes modified buffers to disk
• System Monitor process (SMON):
  • Performs recovery at instance startup
  • Cleans up unused temporary segments
• Process Monitor process (PMON):
  • Performs process recovery when a user process fails
  • Cleans up the database buffer cache
  • Frees resources used by the user process
  • Monitors sessions for idle session timeouts
  • Registers database services with listeners
• Recoverer process (RECO):
  • Used with distributed database configuration
  • Connects to other databases involved in in-doubt distributed transactions
  • Automatically resolves in-doubt transactions
  • Removes rows that correspond to in-doubt transactions
• Archiver processes (ARCn):
  • Copies redo log files to a storage device after a log switch
  • Collects transaction redo data
  • Transmits data to standby destinations

Additional Information

• Allocation and Reuse of Memory in the Shared Pool: Server processes check the shared pool for shared SQL area; allocates a private SQL area for the session.
• Oracle Database Memory Structures: Used for various purposes (program code being run, user-shared data, and private data.)
• Questions section: presented for future reference.