Friday, October 19, 2012

0 pfile vs spfile

What is pfile and spfile?

pfile is the parameter file for oracle instance. Usually refer to init.ora. It is the text file that store the oracle server instance parameters and can be changed by text editor such as vi or notepad++.  The common issue for using pfile is that the file is store on the disk some where. the default location is under $ORACLE_HOME/dba (Linux) $ORACLE_HOME/database (win) and there is no parameter validation for the changes. You will only receive the error when you try to restart the data server. Very like the editing the sybase configure file. ( However, when update the sybase configure, the changes does reflect to the cfg file )

From Oracle 9i, spfile was born. It stands for server parameter file. The file is binary format and only can be change by using alter system parameter command. 

When oracle instance start up, it search for the spfile by default, if no spfile file is found, it is search for pfile.if spfile is in the search directory ($ORACLE_HOME/dba( Linux) $ORACLE_HOME/database ( win)), but you want to use the pfile in stead. you must use

startup pfile='full path of the pfile'

another advantage for the spfile is now we can use RMAN command to backup the spfile as part of backup process.

Create spfile

Below examples show three different ways to create the spfile.

CREATE SPFILE FROM pfile='/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/init.ora';
CREATE SPFILE=''/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/test_spfile.ora'
       FROM PFILE=''/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/init.ora;

Check whether system is using pfile/spfile

show parameter spfile

when use pfile, the value is emptyimage

When use spfile


SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" 
       FROM sys.v_$parameter WHERE name = 'spfile';

When use pfile


When use spfile


select * from V$SPPARAMETER

when use pfile, the value columns are all null


When use spfile


Convert between spfile and pfile

If the instance already running as pfile.

  • create the pfile
  • shutdown database. ( EX: shutdown immediate)
  • startup pfile=’’”

If the instance already run as pfile

  • create the spfile
  • shutdown database. ( EX: shutdown immediate)

if the spfile is in the default location, the simply startup will use the spfile. if you put the spfile in some other location. you have to create the pfile with single line:

SPFILE’=full path of the spfile

  • startup pfile=’’”

Check the pfile/spfile location

show parameter spfile

will show the spfile location if oracle instance is using spfile. however, if the oracle is using the pfile, I am not sure where to find it.  By default, the pfile is under $ORACLE_HOME/dba ( Linux) $ORACLE_HOME/database with init${ORACLE_SID}.ora

pfile/spfile lookup location order


Show parameter

Below table shows different way to get the parameter and its value.

show parameters show parameters in effect at current session
show spparameters show parameters in spfile
V$PARAMETER View that show parameters in effect at current session
V$PARAMETER2 View that show parameters in effect at current session but for list of the value show multiple lines
V$SYSTEM_PARAMETER View that show parameters in effect at current instance
V$SYSTEM_PARAMETER2 View that show parameters in effect at current instance but for list of the value show multiple lines
V$SPPARAMETER show the current content of the spfile. The view returns FALSE values in the ISSPECIFIED column if an SPFILE is not being used.
x$ksppi The table contains a record for all documented and undocumented parameters. DO NOT touch it unless you know what you are doing.  The table only contains the name in the ksppinm column. The value is store in the x$ksppcv table
x$ksppcv The table contains  all documented and undocumented parameters’s value
select n.indx, n.ksppinm,v.ksppstvl
from x$ksppi n, x$ksppcv v
where n.indx=v.indx


Determine the parameter dynamic or static

select name, ISSYS_MODIFIABLE from v$parameter;

If the ISSYS_MODIFIABLE is IMMEDIATE, then it can be change at runtime. otherwise , we have to save it in pfile/spfile and bounce the instance.


set parameter for spfile

alter system set PARAMETER = XX scope=[MEMORY|SPFILE|BOTH]
  • MEMORY: The change only apply at memory. once instance bounce, it is gone.
  • SPFILE: The change only apply at spfile for both dynamic and static parameter.
  • Both: The change apply for both but only dynamic effective right away.

Below example show the change for DB_CACHE_SIZE in memory, the setting does not retain after server bounce.

show parameter db_cache_size;
alter system set db_cache_size=100M scope=memory;
show parameter db_cache_size;
shutdown immediate
show parameter db_cache_size;


This example show the change on the spfile does not effective right away but get change after instance bounce.

show parameter db_cache_size;
alter system set db_cache_size=100M scope=spfile;
show parameter db_cache_size;
shutdown immediate
show parameter db_cache_size;


Below example shows trying to modify the static parameter LOCK_SGA in memory or BOTH. we will get the ORA-02095: specified initialization parameter cannot be modified

In this case, have to use scope=spfile


set parameter for pfile

Since there is no spfile. the scope=spfile|both wont work . if we try to use it, we will get ORA-32001: write to SPFILE requested but no SPFILE is in use.

Alter system set command does not work properly with pfile. Just edit the pfile and bounce the instance.

Backup spfile

RMAN  will backup the SPFILE with the database control file as part backup. PFILEs cannot be backed-up with RMAN.  The option default is off.


Use the following RMAN command to restore an SPFILE.


If server is using spfile and server is down. How do I read the parameter files to figure out the location of the parameter etc ..?

Even for the binary file, you still can read it

  • Linux: strings -a spfileora10g.ora
  • Win: write spfileora10g.ora

Connect to the listener with nomount

Startup nomount
show parameter control


What if spfile is corrupt somehow

Use one of below approach to fix it

  1. If the spfile is part of RMAN backup. restore it from RMAN.
  2. As alternatively , use the string/write command to covert the binary file to text file. then fix the data in pfile, start from pfile and create spfile from pfile.
  3. Create the init.ora and start from scrach.



Post a Comment


SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates