Lesson 4	Database Initialization File
Objective	Start to write the Initialization File for the Oracle Database

Oracle 23ai Database Initialization File

An Oracle database instance (memory + background processes) cannot start until it reads initialization parameters. These parameters define the instance’s operating environment: memory sizing, process limits, file locations, and the multitenant configuration required to mount and open the database. During STARTUP NOMOUNT, Oracle uses the initialization parameters to allocate the SGA, set up PGA-related structures, and start background processes. Only after that can Oracle locate and read the control files to proceed to MOUNT and then OPEN.

PFILE vs SPFILE in Oracle 23ai

Oracle 23ai continues to support two initialization-parameter file types:

PFILE (parameter file) — a plain-text file, typically named init<ORACLE_SID>.ora. It is human-editable and commonly used for initial configuration, troubleshooting, or as a bootstrap file when an SPFILE is unavailable.
SPFILE (server parameter file) — a binary file, typically named spfile<ORACLE_SID>.ora. This is the preferred standard in modern Oracle because persistent changes can be made with ALTER SYSTEM ... SCOPE=SPFILE (or BOTH), without manually editing a text file. :contentReference[oaicite:0]{index=0}

Operationally, most environments run day-to-day using an SPFILE, while keeping a known-good PFILE available for recovery scenarios (for example, to start the instance when the SPFILE is missing or corrupt).

Where Oracle Looks for Initialization Files

The default location varies by platform and configuration, but the common patterns remain:

Linux/UNIX: PFILE/SPFILE commonly reside under $ORACLE_HOME/dbs (for example, $ORACLE_HOME/dbs/initCOIN.ora or $ORACLE_HOME/dbs/spfileCOIN.ora).
Windows: PFILE/SPFILE commonly reside under the Oracle home “database” directory (for example, %ORACLE_HOME%\database\initCOIN.ora or %ORACLE_HOME%\database\spfileCOIN.ora).

Regardless of where the file resides, you can always start explicitly with a known file:

-- SQL*Plus
STARTUP NOMOUNT PFILE='/full/path/to/initCOIN.ora';

Oracle 23ai Multitenant Context

Oracle 23ai is documented as an “Oracle AI Database” and is deeply aligned with the multitenant architecture: a CDB contains PDBs, and the instance-level initialization file governs the CDB instance. :contentReference[oaicite:1]{index=1} When you create a database using SQL (rather than DBCA), Oracle’s multitenant documentation continues to reference the ENABLE_PLUGGABLE_DATABASE initialization parameter for CDB creation workflows. :contentReference[oaicite:2]{index=2}

Key takeaway for this lesson: you write the initialization file for the instance (the CDB instance). PDB-level settings are managed through SQL once the instance is up, not via separate “PDB init.ora files” in normal practice.

Legacy Parameters: bdump, udump, init.ora Naming

Older init files often contained parameters such as background_dump_dest (bdump) and user_dump_dest (udump). These parameters were deprecated beginning in Oracle Database 11g and replaced by diagnostic_dest, which points to the Automatic Diagnostic Repository (ADR). :contentReference[oaicite:3]{index=3} In Oracle 23ai-era guidance, you should prefer diagnostic_dest and let Oracle manage trace/alert locations under the ADR structure.

Project Walkthrough: Start Writing initCOIN.ora

This module’s project database is named COIN, and the training goal is to begin building a working PFILE named initCOIN.ora. Even if your environment ultimately runs from an SPFILE, creating a clean PFILE teaches you what Oracle needs at startup and how parameter scoping works.

Step 1: Create the pfile directory for COIN

If you are following an OFA-style project layout for training, you can keep your project PFILE under an admin directory tree. Create the directories if they do not exist.

# Linux/UNIX example
mkdir -p $ORACLE_BASE/admin/COIN/pfile

:: Windows example (Command Prompt)
mkdir C:\oracle\admin\COIN
mkdir C:\oracle\admin\COIN\pfile

Note: many real-world systems will use the Oracle Home default locations instead. The key is consistency and ensuring the Oracle software owner account has appropriate read permissions.

Step 2: Create a blank initCOIN.ora

Use a plain-text editor and save a new file named initCOIN.ora. If you are using a GUI editor, this is effectively:

File->Save As

Save the file into your chosen PFILE directory (for example, $ORACLE_BASE/admin/COIN/pfile or the default Oracle location).

Step 3: Add a minimal, modern starter parameter set

The following starter parameters are intentionally minimal, modernized for Oracle 23ai conventions, and avoid deprecated bdump/udump settings. Treat file paths as examples; adjust for your environment (filesystem vs ASM, Oracle base locations, and naming standards).

# initCOIN.ora (PFILE) - starter example

db_name='COIN'
db_unique_name='COIN'

# Memory (choose one approach appropriate for your environment)
memory_target=2G

# Control files (example filesystem paths; ASM environments will differ)
control_files=(
  '/u01/app/oracle/oradata/COIN/control01.ctl',
  '/u02/app/oracle/oradata/COIN/control02.ctl'
)

# Diagnostics (ADR root)
diagnostic_dest='/u01/app/oracle'

# Multitenant enablement is referenced in Oracle multitenant creation workflows
enable_pluggable_database=TRUE

In a production build, you would typically include additional parameters (process limits, undo tablespace, character set decisions at CREATE DATABASE time, redo sizing decisions, archive log configuration, and so on). For this lesson, the objective is to start writing a clean file that can be used to bootstrap an instance to NOMOUNT.

Optional Pattern: Split initCOIN.ora and configCOIN.ora using IFILE

Some environments separate “site-wide” settings from “database-specific” settings. Oracle supports this via the IFILE directive: the primary init file includes another parameter file. This pattern is still useful for standardization, but ensure the included file also avoids deprecated bdump/udump parameters.

Example: keep initCOIN.ora small and include a COIN-specific config file.

# initCOIN.ora
ifile='/u01/app/oracle/admin/COIN/pfile/configCOIN.ora'

# configCOIN.ora
db_name='COIN'
db_unique_name='COIN'
memory_target=2G
control_files=(
  '/u01/app/oracle/oradata/COIN/control01.ctl',
  '/u02/app/oracle/oradata/COIN/control02.ctl'
)
diagnostic_dest='/u01/app/oracle'
enable_pluggable_database=TRUE

Step 4: Start with the PFILE, then create an SPFILE

Once your PFILE is valid, the standard next step is to start the instance using the PFILE and then generate an SPFILE for ongoing administration.

-- SQL*Plus
CONNECT / AS SYSDBA;

STARTUP NOMOUNT PFILE='/u01/app/oracle/admin/COIN/pfile/initCOIN.ora';

-- After validating parameters, persist them into an SPFILE
CREATE SPFILE FROM PFILE='/u01/app/oracle/admin/COIN/pfile/initCOIN.ora';

SHUTDOWN IMMEDIATE;
STARTUP;

This sequence gives you the best of both worlds: the transparency and editability of a PFILE during early setup, and the operational convenience of an SPFILE for long-term management. :contentReference[oaicite:4]{index=4}