Explain the internal architecture of Oracle Database.
Understanding the internal architecture of an Oracle Database is a bit like looking under the hood of a high-performance sports car. It’s a complex system of memory, processes, and storage files all working in perfect synchronicity to ensure data is secure, available, and fast.
At its core, you need to understand the distinction between the Database and the Instance.
In the Oracle world, these two terms are not interchangeable:
The Instance: The "living" part. It consists of the memory structures and background processes. It exists only when the database is started.
The Database: The "physical" part. These are the actual files sitting on your disk (Data files, Control files, and Redo logs).
The Instance is where the magic happens. It’s divided into two main components:
The SGA is a group of shared memory structures. Think of it as the workspace where data is cached so it doesn't have to be read from the slow disk every time.
Database Buffer Cache: Holds copies of data blocks read from data files.
Redo Log Buffer: A circular buffer that stores changes made to the database (for recovery).
Shared Pool: Stores "parsed" SQL code and data dictionary information to save CPU cycles.
Large Pool/Java Pool: Optional areas for heavy lifting like backup/restore operations or Java stored procedures.
These are the "workers" that keep the engine running.
DBWn (Database Writer): Writes modified data from the buffer cache to the disk.
LGWR (Log Writer): Writes redo entries from the buffer to the online redo log files.
CKPT (Checkpoint): Updates the headers of data files and control files to signal that a "safe point" has been reached.
SMON (System Monitor): Performs recovery when the instance starts up and cleans up unused temporary segments.
PMON (Process Monitor): Cleans up after failed user processes and registers the instance with the listener.
If the power goes out, the Instance vanishes, but the Database remains on the disk. It consists of three critical file types:
| File Type | Purpose |
| Data Files | Contain the actual user data (tables, indexes, etc.). |
| Control Files | The "brain." They contain the metadata about the database's physical structure and state. |
| Redo Log Files | The "black box." They record all changes made to the data for crash recovery. |
To tie it all together, here is what happens when you run an UPDATE statement:
Parsing: The Shared Pool checks if you’ve run this SQL before.
Execution: The data is pulled from the disk into the Buffer Cache (if it’s not already there).
Logging: Before the data is changed on disk, the change is recorded in the Redo Log Buffer.
Commit: When you commit, LGWR immediately writes the change to the Redo Log files on disk. Your data is now "safe," even if DBWn hasn't written the actual data block to the data file yet!
Note: Oracle uses a "Write-Ahead Logging" protocol. It prioritizes writing the record of the change (Redo) over the actual data file update to ensure maximum performance without sacrificing data integrity.