A server parameter file is initially built from a traditional text initialization parameter file using the CREATE SPFILE statement. It is a binary file that cannot be browsed or edited using a text editor. Oracle provides other interfaces for viewing and modifying parameter settings.

 At system startup, the default behavior of the STARTUP command is to read a server parameter file to obtain initialization parameter settings. The STARTUP command with no PFILE clause, reads the server parameter file from an operating system specific location. If you choose to use the traditional text initialization parameter file, you must specify the PFILE clause when issuing the STARTUP command. Explicit instructions for starting an instance using a server parameter file are contained in Starting Up a Database.

 How to check if an instance was started using a PFILE or an SPFILE

Use the following query on a started instance (nomount, mount or open):

SQL> select count(*) from v$spparameter where value is not null;

If the result of this query is zero, the instance was started using a PFILE. If the result is a non-zero value, the instance was started using an SPFILE.
Explanation:When an instance has been started using a server side parameter file (SPFILE), which is a new feature in Oracle Server 9.0.1, the v$spparameter contains NOT NULL values for several parameters. When an instance has been started using the 'classic' PFILE, this view only contains NULL values.

Creating Server Parameter Files (SPFILE)

The server parameter file must initially be created from a traditional text initialization parameter file. It must be created prior to its use in the STARTUP command. The CREATE SPFILE statement is used to create a server parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this statement.

The following example creates a server parameter file from initialization parameter file /disk01/oracle/dbs/init.ora. In this example no SPFILE name is specified, so the file is created in a platform-specific default location and is named spfile$ORACLE_SID.ora.

CREATE SPFILE FROM PFILE='/disk01/app/oracle/product/10.0.1/dbs/init.ora';

 Another example, below, illustrates creating a server parameter file and supplying a name.

  CREATE SPFILE='/disk01/app/oracle/product/10.0.1/dbs/test_spfile.ora'
  FROM PFILE='/disk01/app/oracle/product/10.0.1/dbs/init.ora';

 The server parameter file is always created on the machine running the database server. If a server parameter file of the same name already exists on the server, it is overwritten with the new information.

Oracle recommends that you allow the database server to default the name and location of the server parameter file. This will ease administration of your database. For example, the STARTUP command assumes this default location to read the parameter file.

 When the server parameter file is created from the initialization parameter file, comments specified on the same lines as a parameter setting in the initialization parameter file are maintained in the server parameter file. All other comments are ignored.

 The CREATE SPFILE statement can be executed before or after instance startup. However, if the instance has been started using a server parameter file, an error is raised if you attempt to recreate the same server parameter file that is currently being used by the instance.

 Setting or Changing Initialization Parameter Values

 Use the SET clause of the ALTER SYSTEM statement to set or change initialization parameter values. Additionally, the SCOPE clause specifies the scope of a change as described in the following table:

 Scope/Description

 SCOPE = SPFILE

The change is applied in the server parameter file only. The effect is as follows:
For dynamic parameters, the change is effective at the next startup and is persistent.
For static parameters, the behavior is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.

 SCOPE = MEMORY

The change is applied in memory only. The effect is as follows:
For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.

For static parameters, this specification is not allowed.

 SCOPE = BOTH

The change is applied in both the server parameter file and memory. The effect is as follows:
For dynamic parameters, the effect is immediate and persistent.
For static parameters, this specification is not allowed.

Exporting Server Parameter Files (SPFILE)

You can export a server parameter file to create a traditional text initialization parameter file. Reasons for doing this include:

Creating backups of the server parameter file

For diagnostic purposes, listing all of the parameter values currently used by an instance. This is analogous to the SQL*Plus SHOW PARAMETERS command or selecting from the V$PARAMETER or V$PARAMETER2 views.

Modifying of the server parameter file by first exporting it, editing the output file, and then recreating it.

The exported file can also be used to start up an instance using the PFILE option.

The CREATE PFILE statement is used to export a server parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this statement. The exported file is created on the database server machine. It contains any comments associated with the parameter in the same line as the parameter setting.

The following example creates a text initialization parameter file from the server parameter file:

CREATE PFILE FROM SPFILE;

 Because no names were specified for the files, a platform-specific name is used for the initialization parameter file, and it is created from the platform-specific default server parameter file.

The following example creates a text initialization parameter file from a server parameter file where the names of the files are specified:

  CREATE PFILE='/disk01/oracle/dbs/tv_init.ora'
  FROM SPFILE='/disk01/oracle/dbs/tv_spfile.ora';

 Errors and Recovery for the Server Parameter Files (SPFILE)

 If an error occurs while reading the server parameter file (during startup or an export operation), or while writing the server parameter file during its creation, the operation terminates with an error reported to the user.

 If an error occurs while reading or writing the server parameter file during a parameter update, the error is reported in the alert file and all subsequent parameter updates to the server parameter file are ignored. At this point, you have the following options:

 Shutdown the instance, recover the server parameter file, then restart the instance

Continue to run without caring that subsequent parameter updates will not be persistent