What is a parameter file
When you start your database in nomount mode, the instance is aware of the location of the control files from the CONTROLFILES initialization parameter in the spfile or init.ora file. When you issue a STARTUP NOMOUNT command, Oracle reads the parameter file and starts the background processes and allocates memory structures. If the initialization parameter file is located on a client machine, transfer the file (for example, FTP).
Parameter file is a text or binary to store the database initialization parameters. The oracle instance reads the parameter file during startup which are then used to control the behavior of database instance and many other aspects as well. Such as : memory allocation (SGA and PGA), startup of optional background processes, Setting of NLS parameters etc. There are 2 types of parameter files, namely :
1. pfile (parameter file) – older way [ not recommended by oracle ]
2. spfile (server parameter file) – newer way [ recommended by oracle ]
spfile was introduced starting from oracle 9i, untill that time text based pfile was used to store database initialization parameters.
pfile V/s spfile
| pfile | spfile |
|---|---|
| Text file | Binary file |
| Parameters in pfile can be edited using any text editor | spfile can not be edited using a text editor. Instead it can only be altered using the “”ALTER SYSTEM”” command |
| Default location of pfile – $ORACLE_HOME/dbs/init[SID].ora where [SID] – is the name of the instance. | Default location of spfile – $ORACLE_HOME/dbs/spfile[SID].ora where [SID] – is the name of the instance. |
| The RMAN utility can not take backup of a pfile | The RMAN utility can take backup of a spfile. |
How to check if SPFILE or PFILE is used
Run the below command against the database you want to check :
If the query returns no rows – pfile is used. If the query returns any value with a filename(with its path) then SPFILE is used by current running instance. For example:
The above example shows that spfile used by the running instance.
How to set parameters when using spfile
The basic syntax for altering the parameters using spfile is :
The SCOPE clause specifies the scope of a change for static and dynamic parameters as described below:
| SCOPE | Allowed parameter changes | Description |
|---|---|---|
| SPFILE | static and dynamic | changes are recorded in the spfile, to be given effect in the next restart |
| MEMORY | dynamic | changes are applied in memory only |
| BOTH | dynamic | changes are applied in both the server parameter file and memory |
For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.
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 machine 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, use the following steps to migrate to a server parameter file.
If the initialization parameter file is located on a client machine, transfer the file (for example, FTP) from the client machine to the server machine.
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.Create a server parameter file in the default location using the
CREATE SPFILE FROM PFILEstatement. 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 SPFILEstatement.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.
The next example illustrates creating a server parameter file and supplying a name and location.
The next example illustrates creating a server parameter file in the default location from the current values of the initialization parameters in 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. 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 | Default Location (PFILE and SPFILE) |
|---|---|---|---|
UNIX and Linux |
|
| ORACLE_HOME |
Windows |
|
| ORACLE_HOME |
Note:
Upon startup, the instance first searches for an SPFILE namedspfileORACLE_SID.
ora, and if not found, searches for spfile.ora. Using spfile.ora enables all Real Application Cluster (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 a text initialization parameter file that points to the server parameter file. For more information, see 'Starting Up a Database'.
Storing the Server Parameter File on HARD-Enabled Storage
Starting with Release 11g, the server parameter file (SPFILE) is in a new format that is compliant with the Oracle Hardware Assisted Resilient Data (HARD) initiative. HARD defines a comprehensive set of data validation algorithms, implemented at both the software and storage hardware levels, to ensure that no corrupt data is written to permanent storage. To fully enable HARD protection for the data in your SPFILE, the SPFILE must reside on HARD-enabled storage, and compatibility for your database instance must be advanced to at least 11.0.0.
You can store the HARD-compliant SPFILE on non-HARD-enabled storage. In this case, the new SPFILE format supports only detection of corrupt SPFILE data. Storing the SPFILE on HARD-enabled storage prevents corrupt data from being written to storage in the first place.
For more information about HARD, and for a list of storage vendors that supply HARD-enabled storage systems, visit: http://www.oracle.com/technology/deploy/availability/htdocs/HARD.html.
Follow these guidelines for full HARD protection when installing or upgrading your Oracle database:
When Installing or Initially Creating a Release 11g Database
When first installing or creating a Release 11g database, the COMPATIBLE initialization parameter defaults to 11.1.0, so this requirement for a HARD-compliant server parameter file (SPFILE) is met. You must then ensure that the SPFILE is stored on HARD-enabled storage. To meet this requirement, do one of the following:
For an Oracle Real Application Clusters environment without shared storage, when DBCA prompts for the location of the SPFILE, specify a location on HARD-enabled storage.
For a single-instance installation, or for an Oracle Real Application Clusters environment with shared storage, complete these steps:
Complete the database installation with Database Configuration Assistant (DBCA).
The SPFILE is created in the default location. See Table 2-3 for information on default locations.
Do one of the following:
Using an operating system command, copy the SPFILE to HARD-enabled storage.
In SQL*Plus or another interactive environment such as SQL Developer, connect to the database as user
SYSand then submit the following command:where spfile_name is a complete path name, including file name, that points to HARD-enabled storage.
Do one of the following:
Create a text initialization parameter file (PFILE) in the default location with the following single entry:
where spfile_name is the complete path to the SPFILE on HARD-enabled storage.
On the UNIX and Linux platforms, in the default SPFILE location, create a symbolic link to the SPFILE on HARD-enabled storage.
See Table 2-3 for default name and location information for PFILEs and SPFILEs.
Shut down the database instance.
Delete the SPFILE in the default location.
Start up the database instance.
When Upgrading to Release 11g from an Earlier Database Release
When upgrading to Release 11g from an earlier database release, complete these steps to migrate your SPFILE to the HARD-compliant format and to store the SPFILE on HARD-enabled storage:
Start SQL*Plus or another interactive query application, log in to the database as user
SYSorSYSTEM, and then enter the following command:WARNING:
Advancing the compatibility level to 11.1.0 enables Release 11g features and file formats and has wide repercussions. Consult Oracle Database Upgrade Guide before proceeding.
Restart the database instance.
The database is now at compatibility level 11.1.0.
If your SPFILE is not already on HARD-enabled storage, complete the following steps:
In SQL*Plus or another interactive environment, connect to the database as user
SYSand then submit the following command:where spfile_name is a complete path name, including file name, that points to HARD-enabled storage.
Do one of the following:
Create a text initialization parameter file (PFILE) in the default location with the following single entry:
where spfile_name is the complete path to the SPFILE on HARD-enabled storage.
On the UNIX and Linux platforms, in the default SPFILE location, create a symbolic link to the SPFILE on HARD-enabled storage.
See Table 2-3 for default name and location information for PFILEs and SPFILEs.
Shut down the database instance.
Delete the SPFILE in the default location.
Start up the database instance.
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:
This is the only |
SCOPE = MEMORY | The change is applied in memory only. The effect is as follows:
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 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.
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.
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 ALTERSYSTEMRESET 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
ALTERSYSTEM commandExporting 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 PARAMETERScommand or selecting from theV$PARAMETERorV$PARAMETER2views.To modify the &spfile;server parameter file by first exporting it, editing the resulting text file, and then re-creating it using the
CREATE SPFILEstatement
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 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 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:
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:Oracle Spfile Location
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.
See Also:
Oracle Database Backup and Recovery User's GuideRecovering 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 a number of ways to recover the SPFILE:
If the instance is running, issue the following command to recreate the SPFILE from the current values of initialization parameters in 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), recreate the SPFILE from the PFILE with the following command:
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 non-default 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:
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.
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: