Oracle Database Architecture and Instance Management
This domain tests whether you can describe an Oracle instance's memory and process architecture precisely enough to predict how a configuration change behaves. Expect questions on SGA components, the PGA, background processes, and the boundary between an instance and a database.
Instance versus database
An instance is memory plus background processes. A database is the set of physical files: datafiles, the control file, and online redo logs. One instance normally opens one database; the instance can exist without the database being open (nomount), which is why parameter and file errors surface at different startup stages.
Memory structures
- SGA: shared pool (library cache, data dictionary cache, result cache), database buffer cache, redo log buffer, large pool, Java pool, fixed SGA.
- PGA: private per-server-process memory holding sort and hash areas plus session state; sized by PGA_AGGREGATE_TARGET.
- Automatic Shared Memory Management resizes SGA components through MMAN when SGA_TARGET is set. Automatic Memory Management uses MEMORY_TARGET and manages SGA plus PGA together.
Background processes
DBWn writes dirty buffers to datafiles. LGWR writes redo to the current online redo log on commit, every three seconds, and when the buffer hits one third full. CKPT updates file headers and signals DBWn. SMON performs instance and transaction recovery. PMON cleans up failed processes and registers services with the listener. MMON and MMNL drive AWR statistics. ARCn archives redo in ARCHIVELOG mode.
Worked example
A candidate sets SGA_TARGET=4G while SGA_MAX_SIZE is 2G, and startup fails with ORA-00823. Reasoning: SGA_TARGET can never exceed SGA_MAX_SIZE, and SGA_MAX_SIZE is fixed for the life of the instance. The fix is to raise SGA_MAX_SIZE in the SPFILE and restart before raising SGA_TARGET.
Traps that cost marks
- Believing DBWn writes at commit; durability comes from LGWR.
- Confusing SGA_MAX_SIZE with MEMORY_MAX_TARGET.
- Treating the PGA as part of the SGA.