LLI Wiki — My Digital Knowledge Base

Oracle: PFILE and SPFILE, creation and startup with a temporary file

Differences between PFILE and SPFILE, converting both ways, and fixing a parameter that prevents startup (ORA-00093).

An Oracle instance reads its initialization parameters from either a binary server parameter file (SPFILE) or a text parameter file (PFILE). This page explains the difference and the usual PFILE/SPFILE operations, including how to start an instance that refuses to boot because of a bad parameter.

PFILE and SPFILE

SPFILE PFILE
Format Binary, server side Text file
Default name spfile<SID>.ora init<SID>.ora (in $ORACLE_HOME/dbs)
Edited by Oracle only (ALTER SYSTEM), never by hand: manual edits corrupt it Any text editor
Changes take effect Dynamically, and persist (SCOPE=SPFILE/MEMORY/BOTH) At the next startup

At startup Oracle looks for spfile<SID>.ora, then spfile.ora, then init<SID>.ora, in that order.

Locate the parameter file in use

show parameter spfile;

Check whether a parameter can be changed dynamically:

select name, value, issys_modifiable from sys.v$parameter where name = 'sga_max_size';

Create a PFILE from the SPFILE

Always make a backup before changing parameters.

create pfile='/tmp/pfile_backup.ora' from spfile;

-- default location and name ($ORACLE_HOME/dbs/init<SID>.ora)
create pfile from spfile;

-- from the current values in memory
create pfile='/tmp/pfile_memory.ora' from memory;

Create an SPFILE from a PFILE

create spfile from pfile='/tmp/pfile_backup.ora';

-- with an explicit name
create spfile='$ORACLE_HOME/dbs/spfileORCL.ora' from pfile='$ORACLE_HOME/dbs/initORCL.ora';

Both commands work whether the instance is started or not.

Start the instance with a temporary parameter file

Edit a PFILE, start with it to test, then save the result as the new SPFILE once the instance runs correctly:

sqlplus / as sysdba
startup pfile='/tmp/pfile_backup.ora';

-- once validated, make it permanent
create spfile from pfile='/tmp/pfile_backup.ora';

To only mount the database: startup mount pfile='/tmp/pfile_backup.ora';

Example: instance does not start (ORA-00093)

An invalid memory parameter can prevent the instance from starting, for example:

SQL> startup nomount
ORA-00093: pga_aggregate_target must be between 10 m and 4096g-1

Since the SPFILE cannot be edited by hand, go through a PFILE:

-- 1. create a PFILE from the SPFILE (works even if the instance is down)
create pfile='/tmp/pfile_fix.ora' from spfile;
# 2. correct the value in the text file
vim /tmp/pfile_fix.ora
-- 3. start with the corrected file
startup pfile='/tmp/pfile_fix.ora';

-- 4. keep the configuration
create spfile from pfile='/tmp/pfile_fix.ora';

When the instance is running, the same parameter can be changed in the SPFILE only (effective at next restart):

alter system set pga_aggregate_target=10G scope=spfile;

Reference

Notes

Mastodon LLIMastodon SocialFosstodon