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
- On Windows, a PFILE is typically placed in a folder of your choice and edited with Notepad; the commands are the same.
- Paths in the examples are generic.