ORACLE - DBA Tips Corner

DBA Tips Archive for Oracle

Introduction to Oracle9i Server Parameter Files (SPFILE)

by Jeff Hunter, Sr. Database Administrator

Contents

Overview
What is a Server Parameter File?
Migrating to a Server Parameter File
Creating a Server Parameter File
The SPFILE Initialization Parameter
Using ALTER SYSTEM to Change Initialization Parameter Values
Exporting the Server Parameter File
Errors and Recovery for the Server Parameter File
Viewing Parameters Settings
Reference Note for Init.Ora Parameter "SPFILE".
Troubleshooting

Oracle has traditionally stored initialization parameters in a text initialization parameter file (init<ORACLE_SID>.ora). Starting with Oracle9i, you can choose to maintain initialization parameters in a binary server parameter file.
This article introduces the server parameter file, and explains how to manage initialization parameters using either method of storing the parameters. Most of the information in this article comes directly from the Oracle Administration Documentation Set.

What is a Server Parameter File?

A server parameter file (SPFILE) can be thought of as a repository for initialization parameters that is maintained on the machine where the Oracle database server executes. 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 eliminates the need to manually update initialization parameters to make changes effected by ALTER SYSTEM statements persistent. It also provides a basis for self tuning by the Oracle database server.
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.

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 you instance, and if the instance is running, it could crash.

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.

Migrating to a Server Parameter File

If you are currently using a traditional 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 using Oracle9i Real Application Clusters, 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 Oracle Real Application Cluster instances, are discussed in:

Create a server parameter file using the CREATE SPFILE statement. This statement reads the initialization parameter file to create a server parameter file. The database does not have to be started to issue a CREATE SPFILE statement.
Start up the instance using the newly created server parameter file.

Creating a Server Parameter File

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 /u01/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='/u01/app/oracle/product/9.0.1/dbs/init.ora';

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

CREATE SPFILE='/u01/app/oracle/product/9.0.1/dbs/test_spfile.ora' FROM PFILE='/u01/app/oracle/product/9.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.
Note: When you use the Database Configuration Assistant (DBCA) to create a database, it can automatically create a server parameter file for you.

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 server (that is, you issue a STARTUP command and do not specify a PFILE), 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.
The SPFILE parameter can also be set in a traditional parameter file to indicate the server parameter file to use. You use the SPFILE parameter to specify a server parameter file located in a nondefault location. Do not use an IFILE initialization parameter within a traditional initialization parameter file to point to a server parameter file; instead, use the SPFILE parameter.

Using ALTER SYSTEM to Change Initialization Parameter Values

The ALTER SYSTEM statement allows you to set, change, or delete (restore to default value) initialization parameter values. When the ALTER SYSTEM statement is used to alter a parameter setting in a traditional initialization parameter file, the change affects only the current instance, since there is no mechanism for automatically updating initialization parameters on disk. They must be manually updated in order to be passed to a future instance. Using a server parameter file overcomes this limitation.
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 Clause 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.

It is an error to specify SCOPE=SPFILE or SCOPE=BOTH if the server is not using 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 traditional 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.
A COMMENT clause allows a comment string to be associated with the parameter update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the server parameter file.
The following statement changes the maximum number of job queue processes allowed for the instance. It also specifies a comment, and explicitly states that the change is to be made only in memory (that is, it is not persistent across instance shutdown and startup).

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=50 COMMENT='temporary change on Nov 29' SCOPE=MEMORY;

Another example illustrates setting a complex initialization parameter that takes a list of strings. Specifically, the parameter value being set is the LOG_ARCHIVE_DEST_n initialization parameter. The case could be that either the parameter is being changed to a new value or a new archive destination is being added.

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

Note that when a value consists of a list of strings, the syntax of the ALTER SYSTEM SET statement does not support editing each element of the list of values 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.
Deleting Initialization Parameter Values
For initialization parameters whose values are string values you can restore a parameter to its default value (effectively deleting it), by using the following syntax:

ALTER SYSTEM SET parameter = '';

For numeric and boolean value parameters, you must specifically set the parameter back to its original default value.

Exporting the Server Parameter File

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='/u01/oracle/dbs/test_init.ora' FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';

Errors and Recovery for the Server Parameter File

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

Viewing Parameters Settings

You have several options for viewing parameter settings.

Method Description

SHOW PARAMETERS This SQL*Plus command displays the currently in use parameter values.

CREATE PFILE This SQL statement creates a text initialization parameter file from the binary server parameter file.

V$PARAMETER This view displays the currently in effect parameter values.

V$PARAMETER2 This view displays the currently in effect parameter values. It is easier to distinguish list parameter values in this view because each list parameter value appears as a row.

V$SPPARAMETER This view displays the current contents of the server parameter file. The view returns NULL values if a server parameter file is not being used by the instance.

Troubleshooting

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.

ORA-32001: write to SPFILE requested but no SPFILE specified at startup

Problem Description
-------------------
When making a change to the system parameters (eg SCOPE=BOTH or SCOPE=SPFILE) 
you get -
eg. 
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

Solution Description
--------------------
If you have created a SPFILE and would like to use it when starting the database 
via the OracleService change the init<sid>.ora file found in the OracleHome\database\
remove the following -
IFILE='c:\ora901\admin\lap901\pfile\init.ora'
and add the following -
SPFILE='c:\ora901\database\SPFILELAP901.ora'

Explanation
-----------
The OracleService points to OracleHome\database\init<sid>.ora. This by 
default will point to the IFILE in OracleHome\admin\pfile
If you want the SPFILE to be used when you start the database via the OracleService
then you must amend the IFILE found in OracleHome\database\

Database Configuration Assistant will create an SPFILE for you by default but unless
you do the above changes then you will never use the 
SPFILE( unless you start the database manually). 

Bug 1987624 has been raised to resolve this issue. This is fixed in 9.0.2

How to manipulate SPFILE's using the EM Console

PURPOSE
-------
It is often difficult to visibly determine whether changes to initialization 
parameter files using Enterprise Manager affect the current parameters for the 
instance, whether the values are stored in the SPFILE for the instance, or both.

This document should help explain how Enterprise Manager is used to manipulate 
database SPFILE's from the OEM console using the tab: 

   Instance->Configuration->General 


SCOPE & APPLICATION
-------------------
For all DBAs that need to use OEM to modify the contents of the SPFILE parameter
file and/or current instance values.


A.  USING ENTERPRISE MANAGER AND DATABASE SPFILE'S TOGETHER
B.  CHANGES TO RUNNING AND CONFIGURED PARAMETERS
B.1 RUNNING PARAMETERS
B.2 CONFIGURED PARAMETERS
C.  MAKING THE CHANGES TAKE EFFECT


A.  USING ENTERPRISE MANAGER AND DATABASE SPFILE'S TOGETHER
--------------------------------------------------------
There are a couple of prerequisites that must be met before OEM can
successfully manage an SPFILE. These are listed below:

1 - Be sure to log into the database as a user with SYSDBA privileges. If
    the user does not have SYSDBA privileges then the following message will
    be displayed in the Instance->Configuration->General tab.

       You are not logged on with SYSDBA privilege
       Only controls for dynamic parameters are editable

    When connected to the database as a non-SYSDBA user there is no way to
    make changes to static parameters for neither the current running values
    nor to parameters from an SPFILE. Only changes to static parameters
    are possible.

2 - Be sure that the database was started up using an existing SPFILE.
    If the database is started using a standard PFILE then it is not
    possible to write changes to an SPFILE. If necessary there are
    options to create an SPFILE from a PFILE and a PFILE from an SPFILE. These
    options can be accessed by right-clicking on the Instance->Configuration
    ->General tab.


B.  CHANGES TO RUNNING AND CONFIGURED PARAMETERS
--------------------------------------------
After selecting the 'All Initialization Parameters' button on the Instance->
Configuration->General tab a new window titled 'Edit Database:
Configuration' will be displayed. Near the top of this window will be 2
radio buttons for selecting whether changes to initialization parameters
should be made to the currently running instance or to the configured
database SPFILE.


B.1 RUNNING PARAMETERS
    ------------------
  Running parameters are the parameters currently in effect for the
  database instance. When making changes to dynamic parameters this is the
  equivalent of entering the SQL command:

      ALTER SYSYEM SET <parameter> = <value> SCOPE=MEMORY;

  Thus the change is made immediately and the new setting goes into effect
  without having to restart the instance.

  If a change is made to a static parameter then the change cannot be made
  until the database is restarted. When clicking on the apply button a
  dialog box will ask if the database should be restarted in order for the
  changes to be made.

  Note that in either case the changes are NOT saved to the SPFILE and thus
  the new setting will not be retained when the database is restarted using the
  SPFILE of the instance.


B.2 CONFIGURED PARAMETERS
    ---------------------
  Configured parameters are parameters which are stored in the server-side
  persistent file called an SPFILE. When making changes to dynamic
  parameters this is the equivalent of entering the SQL command:

      ALTER SYSYEM SET <parameter> = <value> SCOPE=BOTH;

  In this case the current value of the parameter is changed in the SGA of
  the database instance as well as stored in the SPFILE for the instance
  so that the new setting will be in effect immediately as well as at the
  next database restart that uses the SPFILE.

  Changes made to static parameters still require that the database to be
  restarted and a dialog box will be displayed to ask if the database
  should be restarted now or not. However, in this case the change is
  stored in the SPFILE so that subsequent database restarts using the
  SPFILE will keep the changes made.

 
C. MAKING THE CHANGES TAKE EFFECT
------------------------------
After making changes the either the running or configured initialization
parameters the changes do NOT go into effect after clicking the 'Ok'
button. This only saves the changes within the OEM console and does not
execute the command on the database level. On the
Instance->Configuration->General tab make sure to click on the 'Apply'
button in order to have the SQL actually executed. After the SQL command:

    ALTER SYSYEM SET <parameter> = <value> SCOPE=<scope>;

has completed a new pop-up will display indicating that the changes have
been applied to the database.

Which Current Parameter File is Used to Start 9i Instance

WHICH CURRENT PARAMETER FILE CAN YOU USE TO START 9I INSTANCES
--------------------------------------------------------------

Before 9i version, Oracle Instances start depending on a text file
called init$ORACLE_SID.ora. This file is by default located in $ORACLE_HOME/dbs
directory.
Since 9i version, Oracle introduces 'SPFILE' which is a binary file stored on 
the database Server. Changes which are applied on the Instance parameters 
may be persistent accross all the Startup/Shutdown.

In 9i version, you can startup Instance either with spfile or init.ora file.
A 9i instance may be started by :

 - default spfile          --> spfile$ORACLE_SID.ora or spfile%ORACLE_SID%.ora 
 - default init.ora file   --> init$ORACLE_SID.ora or init%ORACLE_SID%.ora
 - specified init.ora file 
 - specified spfile

The default initialization files must be located :

 On Unix           ---> $ORACLE_HOME/dbs

 On WindowsNT/2000 ---> %ORACLE_HOME%\database


The examples below show the different cases:


1) Specifying nothing
   ------------------

sqlplus /nolog
SQL> connect / as sysdba
SQL> startup

Oracle first search if the spfile$ORACLE_SID.ora exists. 
If it does not, Oracle searchs now the spfile.ora parameter file. 
If neither spfileSID.ora nor spfile.ora exist, Oracle will take the 
init$ORACLE_SID parameter file.

If none of these files exist, you receive the following messages:

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'D:\ORA901\DATABASE\INITORA901.ORA'


2) Specifying init.ora
   -------------------

This is similar as in previous versions.

SQL> startup pfile=d:\ora901\database\initORA901.ora
ORACLE instance started.

Total System Global Area  118255568 bytes
Fixed Size                   282576 bytes
Variable Size              83886080 bytes
Database Buffers           33554432 bytes
Redo Buffers                 532480 bytes
Database mounted.
Database opened.


This option is not available with spfile.If you try to startup with specifying 
the spfile, Oracle displays the following error message:

SQL> startup spfile=d:\ora901\database\spfileORA901.ora
SP2-0714: invalid combination of STARTUP options

If you startup the database with specifying the init.ora file, the spile 
parameter is displayed as empty:

NAME                              TYPE        VALUE
--------------------------------- ----------- ------------------------------
spfile                            string


3) Specifying spfile
   -----------------

In this case, you must startup with an init.ora file in which you only specify
the spfile parameter full name:
 
   spfile=d:\ora901\database\spfiletest.ora

SQL> startup pfile=d:\ora901\database\inittest.ora
ORACLE instance started.

Total System Global Area  122449892 bytes
Fixed Size                   282596 bytes
Variable Size              88080384 bytes
Database Buffers           33554432 bytes
Redo Buffers                 532480 bytes
Database mounted.
Database opened.

Now, you can check the usage of spfile by the following command under sqlplus:

SQL> show parameter spfile

NAME                           TYPE        VALUE
------------------------------ ----------- ---------------------------------
spfile                         string      d:\ora901\database\spfiletest.ora


You can now modify the content of the specified spfile:

SQL>alter system set processes = 200 scope=spfile;

System altered.

SQL> select name, value from v$spparameter
  2  where name = 'processes';

NAME            VALUE
--------------- -----
processes       200

ORA-03113: End-of-File on Communication Channel at STARTUP After Creating SPFILE

1. Start the database:
SQL> STARTUP PFILE=D:\ORACLE\ORA90\DATABASE\INITV901.ORA
ORACLE instance started.

2. Check if an SPFILE exists:
SQL> SHOW PARAMETERS SPFILE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

2. Create an SPFILE:
SQL> CREATE SPFILE FROM PFILE;
File created.

3. Stop the database
SQL> SHUTDOWN IMMEDIATE
ORACLE instance shut down.

4. Restart the database:
SQL> STARTUP
ORA-03113: end-of-file on communication channel
SQL> STARTUP
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
cause: The cause of this problem is still undetermined.



fix:

Simply re-connect as SYSDBA and perform STARTUP again:

1. SQL> CONNECT / AS SYSDBA
Connected to an idle instance.

2. SQL> STARTUP
ORACLE instance started.

3. SQL> SHOW PARAMETERS SPFILE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      %ORACLE_HOME%\DATABASE\SPFILE%
                                                 ORACLE_SID%.ORA

CREATE PFILE Command Results in ORA-02236 Error

Problem Description
-------------------

You need to create a traditional parameter file from an existing spfile. When
you do so, you receive an ORA-02236 error.

For example:

    SQL> create pfile=initRel12bis.ora from spfile;
    create pfile=initRel12bis.ora from spfile
                 *
    ERROR at line 1:
    ORA-02236: invalid file name


Solution Description
--------------------

If you want create a pfile which is not the default pfile initSID.ora, you 
can specify it, but you must add single quote around it, such as:
 
    SQL> create pfile='initRel12bis.ora' from spfile;
    File created.

The pfile specified has been created, same as the spfile currently loaded
on the instance.

The default target directory will be $ORACLE_HOME/dbs on Unix. It will be 
%ORACLE_HOME%\database for WindowsNT/2000. You can choose another target 
directory if it is necessary.


Explanation
-----------

The following command works successfully:

    SQL> create pfile from spfile='spfileV900SUP.ora';
    File created.

It creates an initV900SUP.ora parameter file by default, or overwrites an 
existing one.

If you want another pfile name, you must give it explicitly, such as:

    SQL> create pfile='inittest.ora' from spfile='spfileV900SUP.ora';
    File created.

What Happens and What to Do when the SPFILE has been Manually Modified?

Problem Description
-------------------

The SPFILE has previously been manually edited when the instance was opened.

$ ls -l $ORACLE_HOME/dbs/spfileRel14.ora
-rw-rw----   1 sme   dba    907 Mar 19 09:17 /ora9i/dbs/spfileRel14.ora

a. Once you attempt to store new parameter values, you get the following error :

   SQL> alter system set processes=70 scope=spfile;
   alter system set processes=70 scope=spfile
   *
   ERROR at line 1:
   ORA-27046: file size is not a multiple of logical block size
   Additional information: 1

b. Once you attempt to start the database up whereas there is not any 
   initSID.ora parameter file anymore, you get the following error because the 
   instance expects to use the spfile by default, but it is not usable:

   SQL> startup
   ORA-01078: failure in processing system parameters 


Solution Description
--------------------

Recreate a binary SPFILE from an old init.ora, if any exists:

   SQL> create spfile from pfile='initRel14bis.ora';
   File created.

If no init.ora file exists, recreate one and then create the binary SPFILE from
it then a new usable spfileRel14.ora will be available.

$ ls  -l sp*
-rw-r-----   1 sme      dba         2560 Mar 19 15:30 spfileRel14.ora 


Note that the file size has changed, though it contains the same list of 
parameters and values. 
  

Explanation
-----------

The SPFILE is a binary file.
It can only be modified by ALTER SYSTEM sql command or recreated from an 
existing init.ora parameter file, and you must not change it directly.

Same Parameters Appear in the Init.ora and in the Embedded spfile.ora

PURPOSE
-------

When an instance starts up with an "init.ora" parameter file embedding an 
"spfile.ora" parameter file that both include the same parameters, the values
taken into account depend on the place of the reference to the spfile.

 
SCOPE & APPLICATION
-------------------

For all DBAs who create "init.ora" embedding the new SPFILE parameter.


How is the "init.ora" parameter file read
-----------------------------------------
 
At startup, it is sequentially read.

This means that if the reference to an "spfile.ora" is defined at the beginning 
of the "init.ora", any parameter value from the "spfile.ora" is overwritten 
if the same parameters are also initialized in the "init.ora" after this reference.

1. Either be careful not to duplicate the parameters in both files.
2. Or locate the SPFILE parameter at the end of the "init.ora" if you want be
   sure the "spfile" settings are choosen.

Example
-------

=======================================================================
Case 1: SPFILE=spfile.ora is defined at the end of the "init.ora"
=======================================================================
$more initV900.ora
...
undo_retention=700
spfile=/export/home1/ora900/dbs/spfile.ora 

$more /export/home1/ora900/dbs/spfile.ora
...
*.undo_retention=200

SQL> startup pfile='$ORACLE_HOME/dbs/initV900.ora'
ORACLE instance started.

Total System Global Area  139231484 bytes
Fixed Size                   278780 bytes
Variable Size              50331648 bytes
Database Buffers           88080384 bytes
Redo Buffers                 540672 bytes
Database mounted.
Database opened.
SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     200


=======================================================================
Case 2: SPFILE=spfile.ora is defined at the beginning of the "init.ora"
=======================================================================

$more initV900.ora
...
spfile=/export/home1/ora900/dbs/spfile.ora 
undo_retention=700


$more /export/home1/ora900/dbs/spfile.ora
...
*.undo_retention=200

SQL> startup pfile='$ORACLE_HOME/dbs/initV900.ora'
ORACLE instance started.

Total System Global Area  139231484 bytes
Fixed Size                   278780 bytes
Variable Size              50331648 bytes
Database Buffers           88080384 bytes
Redo Buffers                 540672 bytes
Database mounted.
Database opened.
SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     700

How to change static parameters through SPFILE parameter file

Problem Description
-------------------

You need to change a static parameter value in the SPFILE used at instance
startup, but get the following error message:

  SQL> alter system set audit_trail=DB scope=both; 
                        * 
       ERROR at line 1: 
       ORA-02095: specified initialization parameter cannot be modified          


Solution Description
--------------------

SCOPE=BOTH applies the modification at the instance level and in the spfile used 
at startup.
Use the value that applies the modification in the spfile only, provided that 
the spfile was used at startup: 

  SQL> alter system set audit_trail=DB scope=spfile;
  System altered.

If no spfile was used at startup, you would get another error message such as:

  SQL> alter system set audit_trail=DB scope=spfile;
  alter system set audit_trail=DB scope=spfile
  *
  ERROR at line 1:
  ORA-32001: write to SPFILE requested but no SPFILE specified at startup


Explanation
-----------
According to the definition of a static parameter, it cannot be modified during 
instance life, but only in the init or spfile parameter file.

Reference Note for Init.Ora Parameter "SPFILE"

Oracle9i Information

Note: Default values and ranges may differ from those shown here due to variations between releases...

Parameter type String

Syntax SPFILE = spfile_name

Default Value $ORACLE_HOME/dbs/spfile.ora

Parameter Class Static (auto-resource)

Range of Values Any valid SPFILE

Oracle9i Real Application Clusters Multiple instances should have the same value

The value of this parameter is the name of the current server parameter file (SPFILE) in use. This parameter can be defined in a client side PFILE to indicate the name of the server parameter file to use.
When the default server parameter file is used by the server, the value of SPFILE is internally set by the server.
The SPFILE resides in the ORACLE_HOMEdbs directory; however, users can place it anywhere on their machine as long as it is specified in a PFILE.

Support and Historical Notes for "SPFILE"

This parameter was introduced in Oracle9i.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Friday, 20-Jan-2006 16:42:48 EST
Page Count: 60935

SCOPE Clause	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.

Method	Description
SHOW PARAMETERS	This SQL*Plus command displays the currently in use parameter values.
CREATE PFILE	This SQL statement creates a text initialization parameter file from the binary server parameter file.
V$PARAMETER	This view displays the currently in effect parameter values.
V$PARAMETER2	This view displays the currently in effect parameter values. It is easier to distinguish list parameter values in this view because each list parameter value appears as a row.
V$SPPARAMETER	This view displays the current contents of the server parameter file. The view returns NULL values if a server parameter file is not being used by the instance.

Parameter type	String
Syntax	SPFILE = spfile_name
Default Value	$ORACLE_HOME/dbs/spfile.ora
Parameter Class	Static (auto-resource)
Range of Values	Any valid SPFILE
Oracle9i Real Application Clusters	Multiple instances should have the same value