Skip Headers
Oracle® Database Administrator's Guide
11g Release 2 (11.2)

Part Number E17120-11
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

Managing Initialization Parameters Using a Server Parameter File

Initialization parameters for the Oracle Database have traditionally been stored in a text initialization parameter file. For better manageability, you can choose to maintain initialization parameters in a binary server parameter file that is persistent across database startup and shutdown. This section introduces the server parameter file, and explains how to manage initialization parameters using either method of storing the parameters. The following topics are contained in this section.

What Is a Server Parameter File?

A server parameter file can be thought of as a repository for initialization parameters that is maintained on the system running the Oracle Database server. It is, by design, a server-side initialization parameter file. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup. This arrangement eliminates the need to manually update initialization parameters to make persistent any changes effected by ALTER SYSTEM statements. It also provides a basis for self-tuning by the Oracle Database server.

A server parameter file is initially built from a text initialization parameter file using the CREATE SPFILE statement. (It can also be created directly by the Database Configuration Assistant.) The server parameter file is a binary file that cannot be edited using a text editor. Oracle Database provides other interfaces for viewing and modifying parameter settings in a server parameter file.

Caution:

Although you can open the binary server parameter file with a text editor and view its text, do not manually edit it. Doing so will corrupt the file. You will not be able to start your instance, and if the instance is running, it could fail.

When you issue a STARTUP command with no PFILE clause, the Oracle instance searches an operating system–specific default location for a server parameter file from which to read initialization parameter settings. If no server parameter file is found, the instance searches for a text initialization parameter file. If a server parameter file exists but you want to override it with settings in a text initialization parameter file, you must specify the PFILE clause when issuing the STARTUP command. Instructions for starting an instance using a server parameter file are contained in "Starting Up a Database".

Migrating to a Server Parameter File

If you are currently using a text initialization parameter file, then use the following steps to migrate to a server parameter file.

  1. If the initialization parameter file is located on a client system, then transfer the file (for example, FTP) from the client system to the server system.

    Note:

    If you are migrating to a server parameter file in an Oracle Real Application Clusters environment, you must combine all of your instance-specific initialization parameter files into a single initialization parameter file. Instructions for doing this and other actions unique to using a server parameter file for instances that are part of an Oracle Real Application Clusters installation are discussed in Oracle Real Application Clusters Administration and Deployment Guide and in your platform-specific Oracle Real Application Clusters Installation Guide.
  2. Create a server parameter file in the default location using the CREATE SPFILE FROM PFILE statement. See "Creating a Server Parameter File" for instructions.

    This statement reads the text initialization parameter file to create a server parameter file. The database does not have to be started to issue a CREATE SPFILE statement.

  3. Start up or restart the instance.

    The instance finds the new SPFILE in the default location and starts up with it.

Creating a Server Parameter File

You use the CREATE SPFILE statement to create a server parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this statement.

Note:

When you use the Database Configuration Assistant to create a database, it automatically creates a server parameter file for you.

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 re-create the same server parameter file that is currently being used by the instance.

You can create a server parameter file (SPFILE) from an existing text initialization parameter file or from memory. Creating the SPFILE from memory means copying the current values of initialization parameters in the running instance to the SPFILE.

The following example creates a server parameter file from text initialization parameter file /u01/oracle/dbs/init.ora. In this example no SPFILE name is specified, so the file is created with the platform-specific default name and location shown in Table 2-3.

CREATE SPFILE FROM PFILE='/u01/oracle/dbs/init.ora';

The next example illustrates creating a server parameter file and supplying a name and location.

CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora'
       FROM PFILE='/u01/oracle/dbs/test_init.ora';

The next example illustrates creating a server parameter file in the default location from the current values of the initialization parameters in memory.

CREATE SPFILE FROM MEMORY;

Whether you use the default SPFILE name and default location or specify an SPFILE name and location, if an SPFILE of the same name already exists in the location, it is overwritten without a warning message.

When you create an SPFILE from a text initialization parameter file, comments specified on the same lines as a parameter setting in the initialization parameter file are maintained in the SPFILE. All other comments are ignored.

Oracle recommends that you allow the database to give the SPFILE the default name and store it in the default location. This eases administration of your database. For example, the STARTUP command assumes this default location to read the SPFILE.

Table 2-3 shows the default name and location for both the text initialization parameter file (PFILE) and server parameter file (SPFILE) for the UNIX, Linux, and Windows platforms, both with and without the presence of Oracle Automatic Storage Management (Oracle ASM). The table assumes that the SPFILE is a file. If it is a raw device, the default name could be a logical volume name or partition device name, and the default location could differ.

Table 2-3 PFILE and SPFILE Default Names and Locations on UNIX, LInux, and Windows

Platform PFILE Default Name SPFILE Default Name PFILE Default Location SPFILE Default Location

UNIX and Linux

initORACLE_SID.ora

spfileORACLE_SID.ora

OH/dbs or the same location as the datafilesFoot 1 

Without Oracle ASM:

OH/dbs or the same location as the datafilesFootref 1

When Oracle ASM is present:

In the same disk group as the datafilesFoot 2 

Windows

initORACLE_SID.ora

spfileORACLE_SID.ora

OH\database

Without Oracle ASM:

OH\database

When Oracle ASM is present:

In the same disk group as the datafilesFootref 2


Footnote 1 OH represents the Oracle home directory

Footnote 2 Assumes database created with DBCA

Note:

Upon startup, the instance first searches for an SPFILE named spfileORACLE_SID.ora, and if not found, searches for spfile.ora. Using spfile.ora enables all Real Application Cluster (Oracle RAC) instances to use the same server parameter file.

If neither SPFILE is found, the instance searches for the text initialization parameter file initORACLE_SID.ora.

If you create an SPFILE in a location other than the default location, you must create in the default PFILE location a "stub" PFILE that points to the server parameter file. For more information, see "Starting Up a Database".

When you create the database with DBCA when Oracle ASM is present, DBCA places the SPFILE in an Oracle ASM disk group, and also causes this stub PFILE to be created.

The SPFILE Initialization Parameter

The SPFILE initialization parameter contains the name of the current server parameter file. When the default server parameter file is used by the database—that is, you issue a STARTUP command and do not specify a PFILE parameter—the value of SPFILE is internally set by the server. The SQL*Plus command SHOW PARAMETERS SPFILE (or any other method of querying the value of a parameter) displays the name of the server parameter file that is currently in use.

Changing Initialization Parameter Values

The ALTER SYSTEM statement enables you to set, change, or restore to default the values of initialization parameters. If you are using a text initialization parameter file, the ALTER SYSTEM statement changes the value of a parameter only for the current instance, because there is no mechanism for automatically updating text initialization parameters on disk. You must update them manually to be passed to a future instance. Using a server parameter file overcomes this limitation.

There are two kinds of initialization parameters:

  • Dynamic initialization parameters can be changed for the current Oracle Database instance. The changes take effect immediately.

  • Static initialization parameters cannot be changed for the current instance. You must change these parameters in the text initialization file or server parameter file and then restart the database before changes take effect.

Setting or Changing Initialization Parameter Values

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

SCOPE Clause Description
SCOPE = SPFILE The change is applied in the server parameter file only. The effect is as follows:
  • No change is made to the current instance.

  • For both dynamic and static parameters, the change is effective at the next startup and is persistent.

This is the only SCOPE specification allowed for static parameters.

SCOPE = MEMORY The change is applied in memory only. The effect is as follows:
  • The change is made to the current instance and is effective immediately.

  • 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:
  • The change is made to the current instance and is effective immediately.

  • For dynamic parameters, the effect is persistent because the server parameter file is updated.

For static parameters, this specification is not allowed.


It is an error to specify SCOPE=SPFILE or SCOPE=BOTH if the instance did not start up with a server parameter file. The default is SCOPE=BOTH if a server parameter file was used to start up the instance, and MEMORY if a text initialization parameter file was used to start up the instance.

For dynamic parameters, you can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.

When you specify SCOPE as SPFILE or BOTH, an optional COMMENT clause lets you associate a text string with the parameter update. The comment is written to the server parameter file.

The following statement changes the maximum number of failed login attempts before the connection is dropped. It includes a comment, and explicitly states that the change is to be made only in the server parameter file.

ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS=3
                 COMMENT='Reduce from 10 for tighter security.'
                 SCOPE=SPFILE;

The next example sets a complex initialization parameter that takes a list of attributes. Specifically, the parameter value being set is the LOG_ARCHIVE_DEST_n initialization parameter. This statement could change an existing setting for this parameter or create a new archive destination.

ALTER SYSTEM 
     SET LOG_ARCHIVE_DEST_4='LOCATION=/u02/oracle/rbdb1/',MANDATORY,'REOPEN=2'
         COMMENT='Add new destimation on Nov 29'
         SCOPE=SPFILE;

When a value consists of a list of parameters, you cannot edit individual attributes by the position or ordinal number. You must specify the complete list of values each time the parameter is updated, and the new list completely replaces the old list.

Clearing Initialization Parameter Values

You can use the ALTER SYSTEM RESET command to clear (remove) the setting of any initialization parameter in the SPFILE that was used to start the instance. Neither SCOPE=MEMORY nor SCOPE=BOTH are allowed. The SCOPE = SPFILE clause is not required, but can be included.

You may want to clear a parameter in the SPFILE so that upon the next database startup a default value is used.

See Also:

Oracle Database SQL Language Reference for information about the ALTER SYSTEM command

Exporting the Server Parameter File

You can use the CREATE PFILE statement to export a server parameter file (SPFILE) to a text initialization parameter file. Doing so might be necessary for several reasons:

  • 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.

  • To modify the &spfile;server parameter file by first exporting it, editing the resulting text file, and then re-creating it using the CREATE SPFILE statement

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

You must have the SYSDBA or the SYSOPER system privilege to execute the CREATE PFILE statement. The exported file is created on the database server system. 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 SPFILE:

CREATE PFILE FROM SPFILE;

Because no names were specified for the files, the database creates an initialization parameter file with a platform-specific name, 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, but in this example the names of the files are specified:

CREATE PFILE='/u01/oracle/dbs/test_init.ora'
       FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';

Note:

An alternative is to create a PFILE from the current values of the initialization parameters in memory. The following is an example of the required command:
CREATE PFILE='/u01/oracle/dbs/test_init.ora' FROM MEMORY;

Backing Up the Server Parameter File

You can create a backup of your server parameter file (SPFILE) by exporting it, as described in "Exporting the Server Parameter File". If the backup and recovery strategy for your database is implemented using Recovery Manager (RMAN), then you can use RMAN to create a backup of the SPFILE. The SPFILE is backed up automatically by RMAN when you back up your database, but RMAN also enables you to specifically create a backup of the currently active SPFILE.

Recovering a Lost or Damaged Server Parameter File

If your server parameter file (SPFILE) becomes lost or corrupted, the current instance may fail, or the next attempt at starting the database instance may fail. There are several ways to recover the SPFILE:

  • If the instance is running, issue the following command to re-create the SPFILE from the current values of initialization parameters in memory:

    CREATE SPFILE FROM MEMORY;
    

    This command creates the SPFILE with the default name and in the default location. You can also create the SPFILE with a new name or in a specified location. See "Creating a Server Parameter File" for examples.

  • If you have a valid text initialization parameter file (PFILE), re-create the SPFILE from the PFILE with the following command:

    CREATE SPFILE FROM PFILE;
    

    This command assumes that the PFILE is in the default location and has the default name. See "Creating a Server Parameter File" for the command syntax to use when the PFILE is not in the default location or has a nondefault name.

  • Restore the SPFILE from backup.

    See "Backing Up the Server Parameter File" for more information.

  • If none of the previous methods are possible in your situation, perform these steps:

    1. Create a text initialization parameter file (PFILE) from the parameter value listings in the alert log.

      When an instance starts up, the initialization parameters used for startup are written to the alert log. You can copy and paste this section from the text version of the alert log (without XML tags) into a new PFILE.

      See "Viewing the Alert Log" for more information.

    2. Create the SPFILE from the PFILE.

      See "Creating a Server Parameter File" for instructions.

Read/Write Errors During a Parameter Update

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

  • Shut down the instance, recover the server parameter file and described earlier in this section, and then restart the instance.

  • Continue to run the database if you do not care that subsequent parameter updates will not be persistent.

Viewing Parameter Settings

You can view parameter settings in several ways, as shown in the following table.

Method Description
SHOW PARAMETERS This SQL*Plus command displays the values of initialization parameters in effect for the current session.
SHOW SPPARAMETERS This SQL*Plus command displays the values of initialization parameters in the server parameter file (SPFILE).
CREATE PFILE This SQL statement creates a text initialization parameter file (PFILE) from the SPFILE or from the current in-memory settings. You can then view the PFILE with any text editor.
V$PARAMETER This view displays the values of initialization parameters in effect for the current session.
V$PARAMETER2 This view displays the values of initialization parameters in effect for the current session. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row.
V$SYSTEM_PARAMETER This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values.
V$SYSTEM_PARAMETER2 This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row.
V$SPPARAMETER This view displays the current contents of the SPFILE. The view returns FALSE values in the ISSPECIFIED column if an SPFILE is not being used by the instance.

See Also:

Oracle Database Reference for a complete description of views