Database Course Lecture 6 Outline 2024/2025

Study Notes

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

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

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

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

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

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

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.