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 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.
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:
1. The alert log can be looked at. If the log shows the SPFILE parameter in "System parameters with non-default value" section, then it is clear that the instance was started with the pfile internally calling the SPFILE using the parameter.
2. Check the SPFILE parameter value in V$PARAMETER view.
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.