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 region that contains data and control information for one Oracle database instance. It includes the database buffer cache, shared pool, and redo log buffer.
Example:
The database buffer cache in the SGA stores recently accessed data blocks, reducing the need for disk I/O.
3. Background Processes
Background processes perform various tasks to support the operation of the Oracle database. These include PMON, SMON, 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.
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 actual data in tables and indexes, while control files store metadata about the database structure.
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 tables and indexes related to user data, stored in one or more data files.
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 store rows from a table, with each block containing multiple rows of data.
7. Redo Logs
Redo logs contain records of all changes made to the database. They are used for recovery 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 the database name, creation date, and the names and locations of data files and redo log files.
Example:
The control file is crucial for database recovery, as it provides the necessary information to rebuild the database in case of a failure.
9. Online Redo Logs
Online redo logs are used to record all changes made to the database. They are essential for database recovery.
Example:
When a transaction is committed, the changes are written to the online redo logs to ensure they can be recovered if needed.
10. Archived Redo Logs
Archived redo logs are copies of online redo logs that have been archived for long-term recovery purposes.
Example:
After an online redo log is filled, it is archived to a separate location, providing a historical record of changes.
11. Database Buffer Cache
The database buffer cache is a memory area that stores copies of data blocks read from data files. It improves performance by reducing disk I/O.
Example:
When a query requests data, Oracle first checks the buffer cache. If the data is found, it is returned without accessing the disk.
12. Shared Pool
The shared pool is a memory area that stores shared SQL areas, dictionary cache, and other shared memory structures.
Example:
The shared SQL area stores parsed SQL statements, allowing multiple users to share the same execution plan and reduce parsing overhead.
13. Large Pool
The large pool is an optional memory area used for large-scale operations, such as parallel execution, backups, and I/O server processes.
Example:
During a parallel query execution, the large pool provides memory for the parallel execution processes.
14. Java Pool
The Java pool is a memory area used for Java virtual machine (JVM) operations, such as storing Java bytecode and Java stored procedures.
Example:
When a Java stored procedure is executed, the Java pool provides memory for the JVM to run the procedure.
15. Streams Pool
The streams pool is a memory area used for Oracle Streams, a feature that captures, shares, and applies data changes between databases.
Example:
When using Oracle Streams to replicate data between databases, the streams pool provides memory for the replication processes.
16. PGA (Program Global Area)
The PGA is a memory area used for storing session-specific data, such as sort areas, cursor state, and stack space.
Example:
When a user session performs a sort operation, the PGA provides memory for the sort area, improving performance.
17. Automatic Storage Management (ASM)
ASM is a feature that provides a file system for Oracle databases, managing storage allocation and redundancy.
Example:
With ASM, data files and redo log files are automatically distributed across storage devices, improving performance and availability.