Oracle's SPFILE - Part 1

August 17, 2004

Oracle requires an initialization file to define the attributes and characteristics of the starting instance and the connecting sessions there of. We are aware of the Initialization parameter file (also referred as init.ora file or PFILE). This file holds the setup parameters that define the attribute of the instance that is being started. Administrators can control, tune and optimize an instance by setting and modifying the initialization parameters in this file.

Some parameters can be dynamically modified to affect the present instance, while others require the instance to be brought down so that changes can take affect. This remains the same when using PFILE or SPFILE. A simple search on the net will reveal a lot of information regarding PFILE and SPFILE.

Parameter files are by default situated in $ORACLE_HOME/dbs (on UNIX) or %ORACLE_HOME%\database (on Windows) directory.

SPFILE

SPFILE stands for Server Parameter File. It is an extension of the initialization parameter storage mechanism, which allows some additional advantages as compared to the simple text based init.ora file. This feature aims at advancing towards Oracle's self-tuning capabilities. As the name states, the file is present on the server side and is not required on the client to start the instance.

• It is a binary file and the use of editors to modify it is not supported. The only way of changing parameter values is by using the ALTER SYSTEM SET/RESET command.

• If using the PFILE option, parameter changes done with the ALTER SYSTEM command need to be manually changed in the init.ora file to reflect the changes on future startups. With SPFILE, the ALTER SYSTEM command can update the binary file as well. This allows the changes to be persistent across startups.

• Since it is binary, it is prone to becoming corrupt if things go wrong. As a backup strategy, create copies of the SPFILE and convert it to a text-based PFILE for safe keeping.

• The ISSYS_MODIFIABLE column in V$PARAMETER tells us whether the parameters are static or dynamic. Static parameters require the instance to be restarted while dynamic parameters can take effect immediately upon being changed.

  SQL> select distinct issys_modifiable from v$parameter;
   
  ISSYS_MODIFIABLE
  ---------------------------
  DEFERRED
  FALSE
  IMMEDIATE
  

  If the ISSYS_MODIFIABLE value is set to FALSE for a parameter, it means that the parameter cannot change its value in the lifetime of the instance; the database needs to be restarted for changes to take effect. A parameter set to IMMEDATE value means that it is dynamic and can be set to change the present active instance as well as future database restarts. A parameter set to DEFERRED is also dynamic, but changes only affect subsequent sessions, currently active sessions will not be affected and retain the old parameter value.

• The PFILE is required from the client from where the instance is being started. The SPFILE is not required to be from the client from where the instance is started. This comes in handy when we are trying to start the database remotely.

• If you have a standby database, the primary database parameter changes are not propagated to the standby. This needs to be done manually and SPFILE does not aid in this.

• If no SPFILE exists and one needs to be created, Oracle requires that the instance should be started at least once with the PFILE and then the SPFILE created. SPFILE is the default mode in new releases.

• In a multi-instance Real Application cluster system, a single copy of the SPFILE can be used for all instances. The SPFILE maintains different format styles to support both the common values for all instances as well as specific values for individual instances.

• The default behavior of the STARTUP command is changed to look for an SPFILE first, when no PFILE option is explicitly specified.

• The contents of SPFILE can be obtained from V$SPPARAMETER view. The parameters having ISSPECIFIED column set to TRUE are the ones present in the binary file.

• You only need to be connected as SYSDBA/SYSOPER to create the SPFILE or PFILE. The database need not be started. This option is useful in case the SPFILE has been corrupted and you need to rebuild it from a PFILE.

Which SPFILE on startup

If you have multiple SPFILEs, it will be confusing to identify which file is linked to which instance. By default, the SPFILE name is spfile<sid>.ora. This information can be identified by looking at the below logs/views:

ALTER SYSTEM command for SPFILE

SPFILE can be modified by using the ALTER SYSTEM command. This command allows us to set and reset the parameter values.

alter system set <parameter>=<value> 
                   scope=<memory/spfile/both> 
                   comment=<'comments'> 
                   deferred 
                   sid=<sid, *>

The SCOPE clause of the command decides how the changes will take effect. This option can have the following values:

MEMORY - changes are active for the current instance only and are lost on restart.

SPFILE - changes are stored in the SPFILE and are activated on the next startup, the presently active instance is not affected. Static parameters need this option to change their values.

BOTH - changes are effective immediately for the present instance and are stored in SPFILE for future startups. This is the default.

COMMENT is optional and can be specified to store remarks or audit information.

DEFERRED option is used to set parameter values for future connecting sessions. Currently active sessions are not affected and they retain the old parameter value. The option is required for parameters that have the ISSSYS_MODIFIABLE column value in V$PARAMETER set to 'DEFERRED'. It is optional if the ISSYS_MODIFIABLE value is set to 'IMMEDIATE'. For static parameters, this cannot be specified.

The SID clause is valid for Real Application Clusters only. Setting this to a specific SID value changes the parameter value for that particular instance only. Setting this to '*' will affect all instances on the cluster--this is the default if the instance was started using the SPFILE. If the instance was started using PFILE then Oracle assumes the current instance as default.

On starting the instance

When an instance is started, if SPFILE is present, it will be used; otherwise, Oracle searches for a default PFILE. If a PFILE is explicitly specified in the STARTUP command Oracle uses it.

SQL> startup pfile=initdb1.ora

In the above case, the PFILE can also have an SPFILE parameter set so that a different SPFILE can be used to start the instance rather than the default one.

There is no option to specify an SPFILE manually while starting the database. This is identified and picked up by Oracle either by using the SPFILE parameter in PFILE or by searching the default paths.

In the next part of this series, we will cover (with examples) the various ways of working with SPFILE and some common issues encountered.

» See All Articles by Columnist Amar Kumar Padhi