|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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.
Creating an SPFILE from a PFILE
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:
Setting and unsetting spfile parameters
alter system set <parameter_name> = .... scope = memory|spfile|both [sid=...]
alter system reset <parameter_name> scope = memory|spfile|both [sid=...]
Recovering a lost spfile
In order to recover from a lost spfile, the DBID is required.
Default location and name of an spfile
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