spfile [Oracle]

An SPFILE is a Server Parameter File.

The SPFILE file is read when an instance is started up.

Up to Oracle 8i, the initialization parameters were stored (only) in the init.ora file. If a paramter has been dyanammically changed (with alter system), the changement was lost after the next startup of the database unless it was also changed in the init.ora. SPFILEs ease this as changements made with alter system can be stored and be used for the next startup of the database.

In order to permanently store such a changement, use the alter system ... scope spfile syntax.

Either, the database uses the pfile or the spfile to determine its initialization parameters. In order to find out if the database uses an spfile, use the following command in SQL*PLUS:

show parameter spfile

If it returns null, no spfile is used, otherwise it returns the path and the file name of the spfile.

The values of the spfile can be retrieved through the v$spparameter view.

create spfile from pfile  = '/some/path/init.ora';

Or alternatively with specifying the spfile's name and path:

create spfile = '/some/path/spfile-name' from pfile = '/some/other/path/init.ora';

If a non default spfile is created, one has to create an ordinary init.ora and include the following line:

spfile=/some/path/spfile-name

alter system set <parameter_name> = .... scope = memory|spfile|both [sid=...]

alter system reset <parameter_name> scope = memory|spfile|both [sid=...]

In order to recover from a lost spfile, the DBID is required.

The default location of the SPFILE is under $ORACLE_HOME/dbs on Unix and under %ORACLE_HOME%\database under Windows.

Oracle searches first for a file named spfile$ORACLE_SID.ora, then for spfile.ora and finally for init$ORACLE_SID.ora.