Managing Server Parameter Files

Purpose

This module describes how to use and manage server parameter files (SPFILE).

Topics

This module discusses the following:

	Overview
	Migrating From a Text Initialization Parameter File
		Creating the Server Parameter File
		Starting the Instance Using the Server Parameter File
	Modifying the Server Parameter File
		Using the ALTER SYSTEM Command
		Exporting the Server Parameter File
	Changing Static Initialization Parameters
	Re-creating the Server Parameter File After Manual Editing
		Re-creating the Server Parameter File From a Text Initialization Parameter File
		Re-creating the Server Parameter File

Viewing Screenshots

Move your mouse over this icon to show all screenshots. You can also move your mouse over each individual icon to see only the screenshot associated with it.

Overview

Back to List of Topics

A server parameter file (also referred to as SPFILE) is server-side initialization parameter file. It is a repository for initialization parameters and is maintained on the machine where the Oracle server executes. Initialization parameters stored in a server parameter file are persistent across instance startup and shut down. You can update initialization parameters in the server parameter file with the ALTER SYSTEM command.

When you start your instance and do not specify the location, the Oracle server locates your initialization parameter file by searching in the default location for a named file in the following order:

	spfile$ORACLE_SID.ora
	spfile.ora
	init$ORACLE_SID.ora

The default location in UNIX for the server parameter file or text initialization parameter file is $ORACLE_HOME/dbs. On Windows, the default location is ORACLE_HOME/dbs.

Migrating From a Text Initialization Parameter File

Back to List of Topics

If you are currently using a text initialization parameter file (also referred to as an init.ora file), you can easily migrate to using a server parameter file.

Creating the Server Parameter File

Back to List of Topics

You can create a server parameter file (SPFILE) from a text initialization parameter file by performing the following steps:

CREATE SPFILE FROM PFILE='<pfile_name>';

Starting the Instance Using the Server Parameter File

Back to List of Topics

After you have created your server parameter file, you need to restart your instance using this file. If you have created the server parameter file with a default name, perform the following steps to start your instance using your server parameter file:

Modifying the Server Parameter File

Back to List

You can modify the values of parameters in the server parameter file by issuing the ALTER SYSTEM command or exporting the contents of the server parameter file to a text initialization parameter file.

Using the ALTER SYSTEM Command

Back to List of Topics

If you started your instance with a server parameter file, you can use the ALTER SYSTEM command to change the values of the initialization parameters in the server parameter file. Specify the SCOPE=SPFILE option to change the values in the server parameter file only. Specify SCOPE=BOTH to change values in memory and in the server parameter file. This option is valid only for dynamic parameters. SCOPE=BOTH is the default if the instance has been started with a server parameter file.

In the following example, the USER_DUMP_DEST parameter is changed in memory and in the server parameter file.

SHOW PARAMETER <parameter name>;

SELECT name, type, value, issys_modifiable
FROM v$parameter
WHERE name = '<parameter name>';

ALTER SYSTEM SET <parameter name>=<new value>;

SHOW PARAMETER <parameter name>;

Exporting the Server Parameter File

Back to List of Topics

You can export a server parameter file to create a text initialization parameter file.

CREATE PFILE='<pfile_name>' FROM SPFILE;

CREATE PFILE='<pfile_name>' FROM SPFILE='<spfile_name>'; 

CREATE SPFILE FROM PFILE='<pfile_name>';

CREATE SPFILE='<spfile_name>' FROM PFILE='<pfile_name>';

Changing Static Initialization Parameters in the Server Parameter File

Back to List of Topics

You can change the value of a static initialization parameter by using the SCOPE=SPFILE clause on the ALTER SYSTEM command as follows. SPFILE indicates that the change is made in the server parameter file only. Note: The new setting takes effect when the database instance is shut down and restarted.

SHOW PARAMETER <parameter name>;

SELECT name, type, value, issys_modifiable
FROM v$parameter
WHERE name = '<parameter name>';

ALTER SYSTEM SET <parameter name>=<new value> SCOPE=SPFILE;

Re-creating the Server Parameter File After Manual Editing

Back to List of Topics

If the server parameter file has been manually edited, you may receive an error when you attempt to change a parameter using the ALTER SYSTEM command or when you try to start the instance as shown in the examples below.

Note: Although you can open the binary server parameter file with a text editor and view its text, you should not manually edit it. If you edit the server parameter file, you will corrupt the file. You will not be able to start you instance. If the instance is running, it could fail.

Re-creating the Server Parameter File From a Text Initialization Parameter File

Back to List of Topics

If you have a text initialization parameter file, you can re-create your server parameter file from it as follows:

CREATE SPFILE FROM PFILE='<pfile_name>';

Re-creating the Server Parameter File

Back to List of Topics

If you do not have a text initialization parameter, re-create one and then re-create your server parameter file from it:

CREATE SPFILE FROM PFILE='<pfile_name>';

Move your mouse over this icon to hide all screenshots