Create an Oracle Database Instance
Two creation routes
DBCA is the supported, scriptable path and is expected in most hands-on tasks; CREATE DATABASE is the manual path that reveals what DBCA hides - writing the parameter file, starting NOMOUNT, running the CREATE DATABASE statement, then catalog.sql, catproc.sql and pupbld.sql.
Parameters: PFILE and SPFILE
A PFILE is a text file (init.ora) read at startup. An SPFILE is a binary, server-side file (spfile.ora) that ALTER SYSTEM SET ... SCOPE=SPFILE can modify. Conversion is one-way with CREATE SPFILE FROM PFILE or CREATE PFILE FROM SPFILE. You cannot re-create an SPFILE the running instance is using, and if it is damaged you must start with STARTUP PFILE=....
Startup stages
- NOMOUNT - reads the parameter file, allocates the SGA, starts background processes. Used to create a database or re-create a control file.
- MOUNT - reads the control file (datafile and redo locations, SCNs). Used for renaming files, enabling ARCHIVELOG, or restoring a control file.
- OPEN - opens datafiles and redo logs, making the data dictionary available to users.
Shutdown modes: NORMAL waits for every session to disconnect; TRANSACTIONAL waits only for running transactions; IMMEDIATE rolls back uncommitted work, disconnects sessions and checkpoints; ABORT skips the checkpoint and forces instance recovery on the next start. STARTUP FORCE = ABORT + STARTUP.
Traps: MOUNT does not touch datafiles - that is exactly why datafile renaming is done there. Also, since 12c a new database is a multitenant container database (CDB$ROOT plus PDB$SEED) unless you say otherwise.
Rule of thumb: startup order is parameter file → control file → datafiles. Whichever stage fails tells you which file class to inspect.