Oracle's Database Configuration File: PFILE and SPFILE

Most Oracle documentation, and most people who write about Oracle, make the same casual assumption: when someone refers to "init.ora," it's understood that the actual file on disk may be named something else. A default installation creates a generic sample file, typically named init.ora, and a second, instance-specific file such as init<SID>.ora, for example initORCL.ora for an instance named ORCL. Both live under the instance's configuration directory rather than a single fixed path. For convenience, the rest of this lesson uses "init.ora" as the general term for a text initialization parameter file, rather than any one instance's specific filename.

Purpose

• Configuration blueprint: init.ora, formally a PFILE (parameter file), is Oracle's traditional text based configuration file for an instance. It stores a list of initialization parameters and their values, which dictate behavior at startup.
• Performance tuning: many of these parameters directly influence how the database uses memory and CPU, which affects overall performance.
• Customization: the file lets a DBA tailor an instance to a specific workload rather than relying on defaults.

Functions

1. Startup configuration: when an instance starts, the parameters in effect determine:
   • Memory allocation: parameters such as SGA_TARGET (or MEMORY_TARGET for fully unified management) and PGA_AGGREGATE_TARGET control how much memory the instance allocates to its major memory structures. Older material sometimes lists DB_BLOCK_BUFFERS alongside these, a block-count based way of sizing the buffer cache from before automatic memory management existed. It has no place in a current parameter file; if you're determining how memory should actually be sized for your block size today, see Determining Database Block Size.
   • System resources: parameters like PROCESSES and OPEN_CURSORS specify how many processes and open cursors the instance can handle at once.
   • File locations: where the instance should find its control files, and where recovery-related files such as the Fast Recovery Area should live.
   • Database behavior: logging behavior, auditing, character sets, and more.
2. Persistent settings: a plain-text PFILE is read once, at startup; a change to it doesn't take effect until the instance restarts. An SPFILE, covered below, can accept many changes while the instance keeps running.

Location

• Configuration files live under the instance's configuration directory, ORACLE_BASE_CONFIG/dbs, which on a traditional read/write Oracle home is the same as $ORACLE_HOME/dbs. On a read-only Oracle home, the software itself is immutable, and ORACLE_BASE_CONFIG is the separate, writable location shared across every Oracle home under that Oracle Base, which is where you'd actually look for or edit the live file.
• The file's name typically follows init<ORACLE_SID>.ora, so an instance with SID PROD would use initPROD.ora.

Important notes

• Oracle also supports a server parameter file, or SPFILE, a binary file serving the same fundamental purpose as init.ora, but built to be written to directly by ALTER SYSTEM, without hand-editing text and without necessarily restarting the instance. Oracle's own recommendation has been the SPFILE, not init.ora, for a long time now. init.ora's role today is a template, an edit-and-rebuild tool, and an emergency-startup fallback if the SPFILE is missing or damaged. It is still fully supported and not deprecated, it is simply less convenient for ongoing administration. See Additional Database Parameters for a closer look at how the two are actually managed day to day.
• On Real Application Clusters, Oracle expects one shared SPFILE, typically stored in ASM, rather than a separate local init.ora per node; a bootstrap init.ora on each node can simply point at that shared SPFILE's location.

Moving from a text PFILE to a server parameter file uses the CREATE SPFILE command from SQL*Plus or another Oracle command interface:

1. Connect as a privileged user (typically SYSDBA).
2. Check the current configuration. Querying V$PARAMETER for the parameter named spfile tells you whether the instance is already running from one: a null value means it started from a PFILE.
3. Create the SPFILE from the existing init.ora file:

   CREATE SPFILE FROM PFILE='path_to_init.ora';

4. Restart the instance so it picks up the new SPFILE:

   SHUTDOWN IMMEDIATE;
   STARTUP;

5. Verify the SPFILE is in use by re-checking V$PARAMETER, or by querying V$SPPARAMETER directly, which reports the values actually stored in the SPFILE.

A full example, assuming the necessary administrative rights:

-- Connect as SYSDBA
CONNECT / AS SYSDBA

-- Check if an SPFILE is already in use
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM v$parameter WHERE name = 'spfile';

-- Create an SPFILE from init.ora
CREATE SPFILE FROM PFILE='/u01/app/oracle/dbs/initORCL.ora';

-- Restart to pick it up
SHUTDOWN IMMEDIATE;
STARTUP;

-- Confirm the SPFILE is now in use
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM v$parameter WHERE name = 'spfile';

That query's use of DECODE is a good example of Oracle's older conditional syntax still working exactly as it always has. DECODE is not deprecated and needs no replacement here, though if you're writing new conditional logic today rather than adapting an existing script, the ANSI standard CASE expression is generally considered more readable and is what most current Oracle style guides recommend for anything more complex than this simple two-way check.

Once this is done, prefer making ongoing changes with ALTER SYSTEM SET parameter=value SCOPE=BOTH; rather than hand-editing a text file again. The reverse direction is just as easy when you need a readable snapshot to inspect or edit: CREATE PFILE FROM SPFILE dumps the on-disk SPFILE to text, while CREATE PFILE FROM MEMORY dumps what the instance is actually running right now, which is often more useful when diagnosing drift between the two.

Everything so far assumes a healthy instance with its SPFILE intact. This is a backup and recovery course, so it's worth spending a moment on what happens when that assumption fails, because a lost or corrupted SPFILE is not the rare, exotic failure it might sound like. It happens the same way any file loss happens: a bad disk, an accidental deletion, a botched migration.

The good news is that Oracle expects this. When control file autobackups are enabled, which this course will cover as a standard recommendation later in this module, RMAN backs up the SPFILE alongside the control file, not as a separate, easy to forget task. Recovering from a missing SPFILE looks like this: start the instance without a parameter file at all, which RMAN will do using a temporary, minimal default, then restore the real one from the autobackup, then restart normally.

CONNECT TARGET /
STARTUP FORCE NOMOUNT;
RESTORE SPFILE FROM AUTOBACKUP;
STARTUP FORCE;

If your autobackups aren't going to the Fast Recovery Area, or the database's DBID isn't unique in your recovery catalog, you may need to specify the recovery area location or the DBID explicitly for RMAN to find the right autobackup, but the shape of the recovery is the same either way: get a working parameter file back first, since almost nothing else about the instance can proceed without one. This is exactly why this lesson opened with what init.ora and the SPFILE actually are before this course gets any further into backup strategy specifically: you can't reason about protecting a file you haven't first understood.

The init.ora file, or its SPFILE equivalent, contains initialization information for your database and instance. Oracle documents several hundred parameters at this point, though only a relatively small set of them, control file locations, memory targets, redo and archive destinations, and a handful of others, actually matters for a backup and recovery plan. Most parameters ship with sensible defaults and never need to be touched. This module introduces the specific parameters that relate to backup and recovery as they come up; the wrap-up lesson for this module collects them into one place.

The figure below shows a quick-reference workflow for inspecting a database's configuration and recovery settings, six checks worth running whenever you need to understand how a database is actually set up, not just how you think it's set up.

Working through the six steps in order:

1. Confirm database identity. SHOW CON_NAME tells you which container you're actually connected to, and SHOW PARAMETER db_name and db_unique_name confirm the database's identity. DB_NAME identifies the database; DB_UNIQUE_NAME is what distinguishes multiple databases that happen to share the same DB_NAME, useful in a Data Guard configuration where a primary and standby often do exactly that.
2. Locate control files. SHOW PARAMETER control_files lists every control file location the instance actually knows about. Editing that parameter's value doesn't create or copy a control file on its own, you still have to get a copy of the file to each location yourself before the instance can use it there.
3. Tune instance recovery. SHOW PARAMETER fast_start_mttr_target and SELECT target_mttr, estimated_mttr FROM v$instance_recovery tell you what recovery time you've targeted and what the instance currently estimates. Worth remembering here: LOG_CHECKPOINT_INTERVAL, if it's still set, counts redo file blocks, not seconds, an easy detail to get backwards, and one more reason to prefer FAST_START_MTTR_TARGET over the older, static checkpoint parameters covered earlier in this course.
4. Check redo archiving. ARCHIVE LOG LIST and SELECT log_mode FROM v$database tell you whether you're actually in ARCHIVELOG mode, don't assume you are just because LOG_ARCHIVE_DEST_1 or DB_RECOVERY_FILE_DEST happens to be set. An archive destination configured but unused is a common gap, ARCHIVELOG mode has to be explicitly enabled.
5. Compare active and saved values. V$SYSTEM_PARAMETER shows what the instance is running with right now, including its DEFAULT_VALUE and whether a given setting (ISDEFAULT) is still at that default. V$SPPARAMETER, filtered to ISSPECIFIED = 'TRUE', shows only the values someone has explicitly saved into the SPFILE. The two can legitimately differ, an explicitly set value can happen to equal the default, so don't treat a match or a mismatch between them as automatically meaningful without checking which one you're actually looking at.
6. Change, persist, and protect. Use ALTER SYSTEM to manage a binary SPFILE, and pick your SCOPE deliberately: MEMORY affects only the running instance, SPFILE saves the change for the next startup without touching the current instance, and BOTH does both at once. SPFILE and BOTH both require the instance to actually be running from an SPFILE in the first place. And once your configuration is right, protect it: RMAN> BACKUP SPFILE; backs up the current parameter file alongside your regular control file autobackups, so a lost or corrupted SPFILE doesn't turn into its own separate recovery problem.

A quick memory-side companion check worth keeping in your back pocket alongside all of this: SHOW SGA from SQL*Plus gives you a fast summary of your instance's memory allocation without writing a query.
SQL*Plus (or SQLcl), SQL Developer, and Oracle Enterprise Manager remain the primary ways to view and manage these parameters today. Each tool presents the same underlying information a little differently. The next lesson is about the database control file.