Database Creation, Startup/Shutdown, and Configuration
This domain tests how a database is created and how it moves through startup and shutdown states, including which file is read at each stage and which failures map to which stage.
Creation and parameter files
You create a database with DBCA or a CREATE DATABASE script, optionally using Oracle Managed Files through DB_CREATE_FILE_DEST. A PFILE is a text file you can edit; an SPFILE is binary, server-side, and changed with ALTER SYSTEM SET ... SCOPE=MEMORY|SPFILE|BOTH. CREATE SPFILE FROM PFILE converts in either direction; startup order is SPFILE first, then PFILE.
Startup stages
NOMOUNT reads the parameter file, allocates the SGA, starts background processes, and opens the alert log. MOUNT then reads the control file named by control_files, which supplies the datafile and redo log names. OPEN opens datafiles and online redo logs and makes the database available. ALTER DATABASE MOUNT and ALTER DATABASE OPEN move forward one stage at a time.
Shutdown modes
NORMAL waits for all sessions to disconnect. TRANSACTIONAL allows no new transactions, waits for active ones to finish, then disconnects sessions. IMMEDIATE rolls back uncommitted transactions, disconnects users, checkpoints, and closes cleanly. ABORT performs no checkpoint and requires instance recovery at the next startup; it is the fallback when IMMEDIATE hangs.
Worked example
STARTUP fails with ORA-00205: error in identifying control file. Reasoning: the instance reached MOUNT, since NOMOUNT does not read the control file, so the control_files path is wrong or a file is missing. Start in NOMOUNT, check SHOW PARAMETER control_files, and if one multiplexed copy is intact, restore the missing member with ALTER SYSTEM SET control_files before mounting.
Traps that cost marks
- Believing NOMOUNT reads the control file.
- Thinking ABORT loses committed data; it does not, but it forces instance recovery.
- Assuming a clean IMMEDIATE shutdown still needs instance recovery on the next start.