PFILE and SPFILE Management

The PFILE or “init.ora” file is a text based database parameter file that has been around as long as the Oracle database.

The SPFILE is a non-text server parameter file that replaces the PFILE from version 9i forward.  Beginning with version 10g there are significant advantages to using the SPFILE including automatic SPFILE backups by RMAN and saving of memory management pool sizes on database shutdown so those pools can be initiated to the same sizes as the database is restarted.

In 11gR2, the standard is to use a PFILE that contains one line stating that the SPFILE is in ASM.  If other parameters are listed in the PFILE, the PFILE settings will be ignored and Oracle will use the parameter values stored in the SPFILE.
SPFILE=’+<ASMPATH>/<SID>/spfile<SID>.ora’

Comparing PFILE/SPFILE parameter values to system memory.

The following query can be run to determine parameter values that have been changed in memory but not the database parameter file.  This is a good query to run anytime there is a planned shutdown of the database scheduled to avoid having problems with change parameter values in the restart.

select sp.sid, sp.name, m.isdefault as mem_default, 
       decode(sp.isspecified,'TRUE', 'FALSE','TRUE') as sp_default,
       sp.value             as sp_value,
       m.value              as mem_value,
       sp.display_value     as sp_display_value,
       m.display_value      as mem_display_value,
       sp.update_comment    as sp_update_comment,
       m.update_comment     as mem_update_comment,
       m.issys_modifiable, m.isinstance_modifiable, m.isadjusted
  from v$spparameter  sp,
       v$parameter    m
 where sp.name = m.name
   and sp.value <> m.value
 order by sp.name, sid;

Creating the pfile/spfile from database parameter memory values.

You can create a PFILE or an SPFILE from memory values using the following commands.

create spfile = '+<ASMPATH>/<SID>/spfile<SID>.ora' from memory;
create pfile = '<ORACLE_HOME>/dbs/pfile<SID>.ora' from memory;

Moving from PFILE to SPFILE – Using the same parameters for each instance.

  • On the first node/instance, create the spfile from database parameter values in memory.
SQL> create spfile = '+<ASMPATH>/<SID>/spfile<SID>.ora' from memory;
  • Edit the pfile on the node to include only a single line specifying the spfile location in ASM.
spfile='+<ASMPATH>/<SID>/spfile<SID>.ora'
  • Restart the database instance on the first node.
svrctl stop database -d <SID> -n <node_name>
svrctl start database -d <SID> -n <node_name>
  • On each additional node in the cluster, edit the PFILE located on that node as above and restart the instance on that node to use the single SPFILE stored in ASM for the database.

Moving from PFILE to SPFILE – Using the different parameters for each instance.

Repeat this process on each node in the cluster:

  • Create the spfile from database parameter values in memory.
SQL> create spfile = '+<ASMPATH>/<SID>/spfile<SID>_<instance>.ora' 
SQL> from memory;
  • Edit the pfile to include only a single line specifying the spfile location in ASM.
spfile='+<ASMPATH>/<SID>/spfile<SID>_<instance>.ora'
  • Restart the database instance.
svrctl stop database -d <SID> -n <node_name>
svrctl start database -d <SID> -n <node_name>

Note: Before moving your SPFILE file to ASM, the database controlfiles should be moved to ASM.  MOS note “468458.1 – How to Move Controlfiles to ASM” can be used to relocate your controlfiles from an RMAN backup to ASM.

Advertisements