Spfile and Pfile

Spfile means Server Parameter File.  Oracle reads this file first while starting oracle instance.  The sequence of files which oracle looks to start the instance are:

spfileOrcl.ora
spfile.ora
INITorcl.ora

Oracle will look for parameter file in the above sequence.  Here orcl is the oracle SID. This file is located in $ORACLE_HOME\database directory for windows and $ORACLE_HOME\dbs directory for Linux.

This is a binary file which we can't edit using a text editor.  To view this file in a text editor and take a backup of the same we can use the following command:

CREATE PFILE FROM SPFILE;

(this command will create parameter file editable version in database or dbs folder. If we want the file in our selected folder use the following command.

CREATE PFILE='c:\oracle\INITorcl.ora' FROM SPFILE;

We can start the oracle instance using this file also.  But it will be one way operation. Oracle will use this file to start its instance but from the instance we can't write anything to this file.  For two way operation we have to use spfile.  Oracle reads this spfile while starting its instance and from the commands it can save information to it also.

We can also create spfile from a pfile:

create spfile from pfile; 

(if the pfile is in database directory)

create spfile from pfile='c:\oracle\INITorcl.ora'; 

(When the pfile exists in another folder).

We can also create pfile and spfile from memory (running oracle instance);

create pfile from memory;

create spfile from memory;

(we can't create spfile from memory if we started the instance from it)

Changing a dynamic parameter (sga_target) in pfile:-

alter system set sga_target=3g;

(this change happened only in memory, to make it permanent open the initprod.ora pfile and bounce back the db)

$cd $ORACLE_HOME/dbs

vi initprod.ora

sga_target=3g

:wq

Changing a static parameter (sga_max_size) in pfile:-

The static parameter (sga_max_size) can be changed only in parameter file and the db must be bounce back.

Changing a dynamic parameter in spfile:-

alter system set sga_target = 300m;

alter system set sga_target = 500m scope=memory;

alter system set sga_target = 500m scope=both;

Changing a static parameter in spfile:-

alter system set sga_max_size = 1g scope=spfile;

alter system set sga_max_size = 500m comment='max size is 1GB' scope=spfile;