Lesson 13

This module walked through the files, memory and processes, and lifecycle that make an Oracle database durable and recoverable. Here’s the distilled picture you should carry forward:

Core file types (what they do)

1. Initialization parameter file (spfile.ora or init.ora): tells the instance how to start (locations, memory targets, etc.).
2. Control files: small binary files with the database identity and physical map (datafiles, redo, checkpoints, archive status). Multiplex these.
3. Datafiles: hold segments (tables, indexes, undo, etc.) grouped by tablespaces (the logical placement layer).
4. Online redo log files: sequential record of changes; written by LGWR; organized as groups (optionally with multiple members per group).
5. Archived redo log files: copies of full online logs (in ARCHIVELOG mode) used to roll a restored backup forward to a chosen time/SCN. Typically land in the Fast Recovery Area (FRA).

How it flows (why it works)

• Changes are made in memory (buffer cache); redo is written to online logs before datafiles are updated (write-ahead logging).
• Checkpoints bound recovery work by ensuring dirty buffers up to an SCN are written out.
• With ARCHIVELOG enabled, each closed online log is archived by ARCn; this enables point-in-time and media recovery.

Tablespaces (placement and flexibility)

Objects live in segments inside tablespaces; tablespaces own one or more datafiles. A segment may span multiple datafiles of its tablespace as it grows. This indirection lets you add space, spread I/O, and scope backup/restore by tablespace.

Modern memory guidance (avoid legacy parameters)

• Do not use historical cache parameters like db_block_buffers (deprecated). They were used to size the buffer cache in very old releases.
• Preferred: Automatic memory features.
  • MEMORY_TARGET/MEMORY_MAX_TARGET (Automatic Memory Management, AMM) — single knob for SGA+PGA (where supported).
  • Or use SGA_TARGET/ SGA_MAX_SIZE with component sizes like DB_CACHE_SIZE, plus PGA_AGGREGATE_TARGET for PGA (Automatic Shared Memory Management).
• Block size: DB_BLOCK_SIZE defines the default block size at database creation; bigfile tablespaces and ASM work well at common sizes (e.g., 8K). Multiple block sizes are niche—keep it simple unless you have a proven need.

What you should be able to do now

• Explain how control files, datafiles, and redo (online/archived) fit together for durability and recovery.
• Map objects → tablespaces → datafiles; add datafiles and size/monitor growth.
• Read redo layout (groups/members), interpret log switches, and monitor archive health/FRA usage.
• Choose modern memory settings and recognize deprecated parameters.

1. ARCHIVELOG mode: database mode where each full online redo log is archived before reuse, enabling media and point-in-time recovery.
2. Archived redo log: the archived copy of a closed online redo log group, typically stored in the FRA.
3. Background processes: instance daemons such as LGWR (redo), DBWR (dirty buffers), CKPT (checkpoint signaling), ARCn (archiving), SMON/PMON (maintenance).
4. Checkpoint: event and metadata indicating that modified blocks up to a given SCN have been written to datafiles; recorded in control file and datafile headers.
5. Commit: marks a transaction durable; LGWR flushes redo for the transaction and writes a commit record.
6. Database buffer cache: SGA area caching data blocks; reduces physical I/O and allows batched writes.
7. Instance: the SGA plus background processes that operate against a set of database files; clustered RAC uses multiple instances for one database.
8. Log switch: LGWR moves from one redo group to the next; in ARCHIVELOG mode, reuse requires successful archiving and sufficient checkpoint progress.
9. Multiplexing: placing multiple members in each redo group (on different devices/ASM failure groups) or multiple control file copies to avoid single-file loss.
10. Redo log group / member: a group is the unit LGWR writes to; each member is one physical file containing identical redo for that group.
11. System tablespace: contains the data dictionary; always online when the database is open. SYSAUX offloads many components from SYSTEM.
12. Tablespace: logical container of segments; owns one or more datafiles (or tempfiles).
13. Fast Recovery Area (FRA): managed location for archived logs, flashback logs, and backups; monitor capacity to prevent archiver stalls.