Oracle Parameter File-PFILE vs. SPFILE

  The oracle database configures the instance according to the values
specified in parameter file.

Several configuration parameter values can be set via these parameter files
such as:

• The name
  of the database to connect to (parameter BD_NAME)

---

SQL> show parameter db_name;



NAME                  
            TYPE        VALUE

------------------------------------ ----------- -------

db_name                
           string      orcl

SQL>

• The
  database block size(parameter BD_BLOCK|_SIZE)
• The size
  of the database buffer cache(parameter BD_CACHE_SIZE)
• The size
  of the database buffer cache (parameter BD_CACHE_SIZE)
• The
  location and name of all control files(parameter control_files).

SQL> show parameter control_files;



NAME                  
            TYPE        VALUE

------------------------------------ -----------
------------------------------

control_files                
     string      C:\APP\USER\ORADATA\ORCL\CONTR

                   
                   
     OL01.CTL, C:\APP\USER\ORADATA\

                   
                   
     ORCL\CONTROL02.CTL, C:\APP\USE

                   
                   
     R\ORADATA\ORCL\CONTROL03.CTL

SQL>

PFILE vs. SPFILE

The SPFILE is the preferred type of parameter file. In the situation where
both types of parameter files exist, the databse instance will be started with
the SPFILE.

• A PFILE is
  a static, client-side text file that must be changed with a standard text
  editor like "notepad" (windows) or "vi"(linux).
  Database Administration refers to this file as the INIT.ORA file. 
• An SPFILE
  is a persistent server-side binary file that can only be modified with the
  "ALTER SYSTEM SET" oracle command.  

Characteristics of the Initialization Parameter File(PFILE)

• It is a
  text file.
• Naming
  convention is <initSID.ora>
• Default
  location is $ORACLE_HOME/dbs (Linux), %ORACLE_HOME%database(windows)
• It is
  editable by notepad, vi etc. text editor. 
• Changes
  affect only the running instance. 
• Permanent
  changes require the PFILE to be opened, edited and a restart to the
  database. 

Suppose the value of the BD_CACHE_SIZE parameter is some value X. To change
the value of the BD_CACHE_SIZE to a new value Y, you can give the following
command:



SQL>ALTER SYSTEM SET BD_CACHE_SIZE=Y



  The above changes would come into effect and would affect the
currently running instance only. If the database is restarted, the previous
value that existed (X) in the parameter file would be used for configuring the size
of the database buffer cache.



  If you want to change permanently the size of the buffer cache to Y,
you need to open the PFILE and edit the initialization parameter to take the
value Y. To bring this  into effect, restart the database. Finally it is
said that there is no other way to change parameter values in a PFILE except
editing it with text editors.

Characteristics of the Persistent (Server) Parameter File(SPFILE) 

• It is a
  binary file. 
• Naming
  convention is <spfileSID.ora>
• Default
  location: $ORACLE_HOME/dbs (Linux), %ORACLE_HOME%/database(windows)
• Not edited
  by text editors, that's, notepad, vi. 
• Dynamic
  changes may an effect on only the currently running instance, only the
  future instances or both currently running and future instances. 

Changes of Parameters in SPFILE and Advantage over PFILE

However, to change the value of the BD_CACHE_SIZE initialization parameter
to a new value Y for the currently running instance, you can issue the
following command:

SQL>ALTER SYSTEM SET BD_CACHE_SIZE=Y SCOPE=MEMORY;

The above change would come into effect and would affect the currently
running instance only(due to scope=memory option).



Again, to change the value of the DB_CACHE_SIZE initialization parameter for
only future instances you would do the following command:

SQL>ALTER SYSTEM SET BD_CACHE_SIZE=Y SCOPE=SPFILE



The above change will not affect the currently running instance.After the
database is restarted, the new value will be in effect. This type of changes
are very useful that can't be done while using PFILE.



To change the value of the BD_CACHE_SIZE initialization parameter for the
currently running instance and for future instance, you can do the following
command.

SQL>ALTER SYSTEM SET BD_CACHE_SIZE=Y SCOPE=BOTH;



The above change would come into effect and would affect the currently
running instance as well as future instances. When the database is restarted,
the new value will be in effect.

Converting Between Parameter Files in Oracle

You can create a PFILE from an SPFILE or vice versa applying the following
commands:



SQL>CREATE PFILE FROM SPFILE;

SQL>CREATE SPFILE FROM PFILE;