Oracle Database Architecture
RISHIJ RAJ SRIVASTAVA / 16 -10-2024
Agenda
1 Introduction
2 Oracle Architecture
3 Memory Management in Oracle Database
4 Background Processes in Oracle Database
5 Physical Structures in Oracle Database
6 Logical Structures in Oracle Database
7 CRUD Operations
8 Conclusion
Introduction
Oracle's architecture is designed for scalability, reliability, and performance.
1)Key Components:
- Memory Management
- Background Processes
- Physical and Logical Structures
2)Client-Server Model:
Multiple clients can connect to the server, and Oracle uses memory and background processes to manage
these connections and data efficiently.
Oracle Architecture
1. Oracle Instance: Memory structures (SGA, PGA) and background processes.
Components:
- SGA: Shared by users, optimized for performance.
- PGA: Specific to each session.
- Background Processes: Essential for managing operations.
2. Oracle Database: Actual storage for data.
Components:
- Data Files: Store user data.
- Control Files: Hold metadata about the database.
- Redo Log Files: Record transactions for recovery.
Memory Management in Oracle Database
1. System Global Area (SGA): Shared memory region for data and control information.
Key Components:
- Database Buffer Cache: Stores data blocks for faster access.
- Shared Pool: Caches SQL statements and execution plans.
- Redo Log Buffer: Holds redo entries for crash recovery.
- Java Pool: For Java objects in Oracle.
- Large Pool: Used for large memory allocations.
2. Program Global Area (PGA): Private memory for each server process.
Key Components:
- Session Information: Contains data for user sessions.
- Work Areas: For SQL operations like sorting.
Background Processes in Oracle Database
Oracle uses a number of background processes to manage tasks like writing data to disk, logging transactions, and
recovering from failures.
Key Background Processes:
- Database Writer (DBWn): Writes dirty data blocks to disk.
- Log Writer (LGWR): Writes redo log entries for recoverability.
- System Monitor (SMON): Handles crash recovery and frees temporary segments.
- Process Monitor (PMON): Cleans up after failed processes.
Additional Processes:
- Checkpoint Process (CKPT): Ensures data consistency.
- Archiver (ARCn): Archives redo log files.
- Job Queue Process (J000): Executes scheduled jobs.
Physical Structures in Oracle Database
1. Data Files: Physical files on disk that store user and application data.
- Part of tablespaces, where actual database data resides.
- Can grow automatically if auto-extend is enabled.
2. Control Files: Store metadata about the database, such as the locations of data files and redo log files.
- Database name and timestamp.
- Names and locations of data files and redo log files
- Information about backup and recovery
3. Redo Log Files: Record all changes made to data within the database for recovery purposes.
- Consist of multiple groups and each group contains at least two members for redundancy
- Used to recover a database after a system crash.
4. Parameter Files (PFILE/SPFILE): Store initialization parameters that dictate how the instance is configured.
- PFILE: Editable text file.
- SPFILE: Binary file used by the database for automatic initialization.
Logical Structures in Oracle Database
1. Tablespaces: Logical storage units that group related data.
- Types of Tablespaces: System Tablespace, User Tablespaces, Temporary Tablespace, Undo Tablespace.
2. Segments: A collection of extents allocated for a specific type of data object.
- Types of Segments: Data Segments, Index Segments, Temporary Segments, Undo Segments.
3. Extents: Continuous blocks of storage within a tablespace that are allocated to segments.
- A segment consists of one or more extents.
- Extents can grow dynamically as the segment grows.
4. Blocks: The smallest unit of storage in an Oracle Database.
- Smallest unit of storage, used for reading/writing data.
- Oracle reads and writes data in blocks.
- Block sizes typically range from 2KB to 32KB.
CRUD Operations
1. Create Operation (INSERT):
-Client Action: Sends an INSERT command.
- Server Action : Data is inserted into the database buffer cache. Redo logs are generated. The Database Writer DBWn eventually writes the changes to
data files.
2. Read Operation (SELECT):
- Client Action: Sends a SELECT command.
- Server Action : Data is retrieved from the database buffer cache if available; otherwise, it is fetched from data files.
3. Update Operation (UPDATE):
- Client Action: Sends an UPDATE command.
- Server Action : Data is modified in the buffer cache. Redo log entries are created. Data changes are written to disk by DBWn during a checkpoint
4. Delete Operation (DELETE):
- Client Action: Sends a DELETE command.
- Server Action : Rows are marked for deletion. Redo logs are generated for recovery. Changes are committed to disk.
Conclusion
- Oracle's architecture integrates memory management, background processes, and data structures to ensure smooth
operation, scalability, and performance.
- SGA and PGA manage memory efficiently to optimize resource usage.
- Processes like DBWn and LGWR handle data consistency and enable recovery.
- Data files, redo logs, and tablespaces store and organize data efficiently.
- Oracle is a reliable solution for large, complex applications.
