Files - Expert Oracle Database Architecture - page 80

Server Parameter Files (SPFILEs)
SPFILEs represent a fundamental change in the way Oracle accesses and maintains parameter settings for the
instance. An SPFILE eliminates the two serious issues associated with legacy parameter files:
• It stops the proliferation of parameter files. An SPFILE is always stored on the database server;
the SPFILE must exist on the server machine itself and can't be located on the client machine.
This makes it practical to have a single source of “truth” with regard to parameter settings.

• It removes the need (in fact, it removes the ability) to manually maintain parameter files outside
of the database using a text editor. The ALTER SYSTEM command lets you write values directly
into the SPFILE . Administrators no longer have to find and maintain all of the parameter files
by hand.

The naming convention for this file by default is

$ORACLE_HOME/dbs/spfile$ORACLE_SID.ora (UNIX/Linux environment variable)
%ORACLE_HOME/database/spfile%ORACLE_SID%.ora (Windows environment variable)

I strongly recommend using the default location; doing otherwise defeats the simplicity SPFILEs represent. When
an SPFILE is in its default location, everything is more or less done for you. Moving the SPFILE to a nondefault location
means you have to tell Oracle where to find the SPFILE , leading to the original problems of legacy parameter files all
over again!

Converting to SPFILEs
Suppose you have a database that is using a legacy parameter file. The move to an SPFILE is quite simple—you use the
CREATE SPFILE command.

■ You can also use a “reverse” command to create a parameter file ( PFILE ) from an SPFILE . i'll explain shortly
why you might want to do that.

Note

So, assuming you have an init.ora parameter file and that init.ora parameter file is in the default location on
the server, you simply issue the CREATE SPFILE command and restart your server instance:

EODA@ORA12CR1> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- -------
spfile string
EODA@ORA12CR1> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-01031: insufficient privileges