Overview of Oracle Database Architecture
Key Concepts
1. Instance
An Oracle instance consists of the memory structures and background processes necessary to manage database files. It includes the System Global Area (SGA) and the background processes.
Example:
When you start an Oracle database, an instance is created, which includes the SGA and background processes like PMON (Process Monitor) and SMON (System Monitor).
2. System Global Area (SGA)
The SGA is a shared memory area that contains data and control information for one Oracle database instance. It includes the database buffer cache, redo log buffer, and shared pool.
Example:
The database buffer cache in the SGA stores frequently accessed data blocks, reducing the need for disk I/O operations.
3. Background Processes
Background processes perform various tasks to support the database instance. These include PMON (Process Monitor), SMON (System Monitor), DBWn (Database Writer), LGWR (Log Writer), and CKPT (Checkpoint).
Example:
The LGWR process writes redo entries from the redo log buffer to the redo log files, ensuring data integrity in case of a crash.
4. Database Files
Database files include data files, control files, and redo log files. These files store the actual data, control information, and transaction logs, respectively.
Example:
Data files store the tables and indexes, control files maintain the database's structural information, and redo log files record all changes made to the database.
5. Tablespaces
Tablespaces are logical storage units within the database that group related logical structures. Each tablespace consists of one or more data files.
Example:
A tablespace named "USERS" might contain all user-related data, while another tablespace named "SYSTEM" contains system-related data.
6. Data Blocks
Data blocks are the smallest units of storage in Oracle. They are the physical structures on disk that store actual data.
Example:
A data block might contain rows from a table, and multiple data blocks make up a data file.
7. Redo Log Files
Redo log files record all changes made to the database. They are crucial for recovery operations, ensuring data consistency in case of a failure.
Example:
If a transaction updates a row in a table, the changes are first written to the redo log buffer and then to the redo log files.
8. Control Files
Control files contain metadata about the database, including its name, creation date, and the locations of data files and redo log files.
Example:
The control file is essential for starting the database instance, as it provides the necessary information to locate and manage database files.
9. Archive Log Mode
Archive log mode is a configuration that allows Oracle to save redo log files before they are overwritten. This is crucial for point-in-time recovery.
Example:
In archive log mode, the LGWR process archives redo log files to a specified location, ensuring that all changes are saved for recovery purposes.
10. Shared Pool
The shared pool is a component of the SGA that stores shared SQL areas, dictionary cache, and other control structures. It improves performance by caching frequently used data.
Example:
When a SQL statement is executed, its parsed representation is stored in the shared pool, allowing subsequent executions to reuse the parsed code.
11. Database Buffer Cache
The database buffer cache is a component of the SGA that stores copies of data blocks read from data files. It reduces disk I/O by keeping frequently accessed data in memory.
Example:
When a query requests data from a table, Oracle checks the buffer cache first. If the data is found, it is returned without accessing the disk.
12. Redo Log Buffer
The redo log buffer is a circular buffer in the SGA that temporarily stores redo entries before they are written to the redo log files. It ensures that changes are recorded efficiently.
Example:
When a transaction is committed, the redo entries are first written to the redo log buffer and then asynchronously to the redo log files.
13. PMON (Process Monitor)
The PMON process monitors other background processes and performs recovery operations if a process fails. It also cleans up resources used by terminated user processes.
Example:
If a user process crashes, PMON will detect the failure, release the resources held by the process, and restart any necessary background processes.
14. SMON (System Monitor)
The SMON process performs system-level recovery operations, such as rolling forward committed transactions and rolling back uncommitted transactions during instance recovery.
Example:
After a crash, SMON performs instance recovery by applying redo entries from the redo log files to bring the database to a consistent state.
15. DBWn (Database Writer)
The DBWn process writes dirty buffers (modified data blocks) from the database buffer cache to data files. It ensures that data is persisted to disk.
Example:
When a data block is modified, it is marked as dirty. DBWn periodically writes these dirty blocks to data files to free up space in the buffer cache.
16. LGWR (Log Writer)
The LGWR process writes redo entries from the redo log buffer to the redo log files. It ensures that all changes are recorded for recovery purposes.
Example:
When a transaction is committed, LGWR writes the redo entries to the redo log files, ensuring that the changes are safely recorded.
17. CKPT (Checkpoint)
The CKPT process updates the control file and data file headers with the latest checkpoint information. It helps in reducing the time required for instance recovery.
Example:
During a checkpoint, CKPT updates the control file and data file headers to reflect the current state of the database, reducing the amount of redo log entries needed for recovery.