I decided to write this post after reading post "Why I still use pfiles" on Jeff Hunter's blog.
1. What is SPFILE
SPFILE is server parameter file and it was introduced in Oracle 9i. As it is binary file managed by server, it cannot be modified using text editor. When you execute STARTUP Oracle will look at first for SPFILE then for PFILE. SPFILE can be backed up by RMAN.
2. How to change values in SPFILE
As I said before SPFILE is binary file managed by Oracle so it cannot by
modified by text editor. To change values in spfile you need execute (example)
command:
alter system set parameter_name=true scope=spfile;
See the magic parameter SCOPE. This parameter has three possible options:
- SPFILE - changes settings in SPFILE only
- MEMORY - changes settings in already running instance, new values will be lost after restart
- BOTH - changes settings in SPFILE and apply settings on already running
instance. This is the default option if SCOPE is not defined in the ALTER
SYSTEM command.
Note: if you get "ORA-02095: specified initialization parameter cannot be
modified" message it means that specific parameter is static, so it needs instance restart.
To check whether parameter is static or dynamic look at documentation or query v$parameter table as in example:
select name, issys_modifiable from v$parameter where name = 'parameter_name';
If the result is FALSE then parameter is static.
Note if the result of the query is DEFFERED instead of IMMEDIATE then it is also dynamic parameter will afffect only new created sessions.
3. How to convert PFILE to SPFILE
CREATE spfile FROM pfile;
In this case SPFILE will create in $ORACLE_HOME/dbs directory.
or
CREATE spfile='/storage/config/spfileORCL.ora' FROM
pfile='/storage/config/initORCL.ora';
In this case spfile will be created in specified location.
4. How to convert SPFILE to PFILE
There are are two ways how to convert SPFILE to PFILE, quick and standard way.
At OS system level (quick):
strings spfileORCL.ora > initORCL.ora
At Oracle level (standard):
CREATE pfile FROM spfile;
or
CREATE pfile='/storage/config/initORCL.ora' FROM spfile;
5. SPFILE in RAC enviroment
SPFILE can hold configuration for all nodes in Real Application Cluster.
You can set parameter for all nodes in cluster and also you can set parameter
only for specific node cluster.
Example:
alter system set shared_pool_size=256M scope=spfile sid='*';
This command will set parameter to value same for the all of instances (nodes) in the
cluster. The entry in spfile will be looking as following:
*.shared_pool_size=268435456
alter system set shared_pool_size=256M scope=spfile sid='ORCL_NODE1';
This command will set the shared pool for specific instance. The entry in
spfile will be looking as following:
ORCL_NODE1.shared_pool_size=268435456
Note if you didn't specify the SID it is the same as you typed SID='*', so
value for this parameter will be the same for all instances in the cluster.
If you have your SPFILE placed on shared storage you can use at least two ways how to point your instance to the new location.
1. Create symlink:
ln -s /storage/config/spfileORCL.ora $ORACLE_HOME/dbs/spfileORCL_NODE1.ora
2. Create pfile which points to spfile:
echo "spfile='/storage/config/spfileORCL.ora' >
$ORACLE_HOME/dbs/initORCL_NODE1.ora"
6. Some hints
1. Comment your parameters
It is useful to use comments during change of parameters (you will help to
workmates and yourself in the future).
To do this use COMMENT parameter as following:
alter system set java_pool_size=150M
comment=' 25-11-2006 IKA - recommended by patchset' scope=spfile;
Entry will be looking as following:
*.java_pool_size=157286400# 25-11-2006 IKA - recommended by patchset
Note keep you comments short as possible, informative and easy to understand.
2. View SPFILE settings
From sqplus:
select * from v$spparameter where value is not null order by name;
From OS: You can use commands as cat, less, more or better you can use
strings spfileORCL.ora
which displays more readable output (only text).
Update:
3. Useful informations about SPFILE
For more information about SPFILE you can find on Metalink.
Note:
293698.1 - All What you Wanted To Know About SPFILE and PFILE Files
Note:
249664.1 - Pfile vs SPfile
