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:
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.
Next
Back to DBAsupport.com
