Database Administration

Part 1: Core Concepts (2 Marks)

• Database Administrator (DBA): An IT professional responsible for the installation, configuration, physical design, performance optimization, security authorization, and backup/disaster recovery of enterprise database management systems.
• Oracle Database Instance: The combination of background system memory structures (SGA) and background OS processes that manage database files; an instance exists in memory and can exist without opening a database (e.g., in NOMOUNT state).
• System Global Area (SGA): A shared memory region allocated upon Oracle instance startup that contains data and control information for the database, comprising the Shared Pool, Database Buffer Cache, Redo Log Buffer, Large Pool, and Java Pool.
• Program Global Area (PGA): A non-shared private memory region allocated exclusively for each individual server and background process containing session memory, sort work areas, and cursor state.
• Database Writer (DBWn): The asynchronous background process responsible for writing modified (dirty) data blocks from the Database Buffer Cache to physical data files on persistent disk storage.
• Log Writer (LGWR): The critical background process that flushes redo log entries sequentially from the Redo Log Buffer to the online redo log files upon transaction commit (COMMIT).

---

Part 2: Structural Frameworks (10 Marks)

1. Oracle Database Architecture: Memory and Process Interaction

2. Three-Stage Oracle Instance Startup Lifecycle

1. SHUTDOWN $\rightarrow$ NOMOUNT:
   • The instance reads the initialization parameter file (spfile<SID>.ora or pfile).
   • Allocates the SGA in host RAM and spawns background processes (DBWn, LGWR, CKPT, SMON, PMON).
   • Physical database files are not yet opened; used for database creation and control file recreation.
2. NOMOUNT $\rightarrow$ MOUNT:
   • The instance locates and opens the control files specified by CONTROL_FILES parameter.
   • Reads data file and redo log locations from the control files without opening them; used for renaming files, enabling ARCHIVELOG mode, and full RMAN recovery.
3. MOUNT $\rightarrow$ OPEN:
   • Opens all online data files and redo log files.
   • Performs instance recovery via SMON if the previous shutdown was dirty (abort).
   • Database becomes accessible to normal end-user client sessions.

---

Part 3: Distinction Tables

---

Part 4: Worked Problems & Exam Traps

Problem Archetype: SGA Component Sizing and Instance Startup Troubleshooting

Question: An Oracle DBA configures an enterprise server with $64\text{ GB}$ of RAM. Calculate appropriate sizing for MEMORY_TARGET or SGA components under an OLTP workload. What error occurs if control01.ctl is corrupted during ALTER DATABASE MOUNT? Solution:

1. Memory Sizing for OLTP:
   • OS reserve: $20\% = 12.8\text{ GB}$.
   • Available for Oracle: $\approx 51.2\text{ GB}$.
   • Recommended split: SGA ($70-80\%$) and PGA ($20-30\%$ for session sorting).
   • SGA_TARGET = 36 GB:
     • Database Buffer Cache: $60\% = 21.6\text{ GB}$ (keeps hot customer data blocks in RAM).
     • Shared Pool: $25\% = 9\text{ GB}$ (caches SQL execution plans and data dictionary).
     • Redo Log Buffer: $512\text{ MB}$ to $1\text{ GB}$.
   • PGA_AGGREGATE_TARGET = 15 GB.
2. Control File Error:
   • If a multiplexed control file is inaccessible or corrupted during mount, Oracle halts instance transition and throws ORA-00205: error in identifying control file, check alert log for more info.
   • The instance remains stuck in NOMOUNT state until the DBA restores a backup control file or synchronizes the corrupted copy from a healthy multiplexed peer.

TU Exam Trap: Students often confuse the role of DBWn and LGWR during a COMMIT. When a user issues COMMIT, Oracle does NOT write data blocks to disk via DBWn (doing so would cause severe disk I/O bottlenecks). Instead, Oracle only forces LGWR to write the small redo stream to the online redo logs! This fast-commit mechanism guarantees ACID Durability while deferring expensive data block writes.