Pfile and Spfile

Pfile and Spfile

Parameter File:-A parameter file is a file that contains a list of initialization parameters and a value for each parameter.

Types of Parameter File:- Oracle support below 2 types of parameter files

Server Parameter Files (SPFILE) :- This is a Binary File and it is default.

Parameter Files (PFILE)- This is a Text File.

                                    

When the Oracle instance started, first it looks for Parameter file in $ORACLE_HOME/dbs directory in below order:-

      1.  spfile${ORACLE_SID}.ora   (SPFILE = Server Parameter File)

      2.  spfile.ora                (SPFILE) 

      3.  init${ORACLE_SID}.ora     (PFILE)

      4.  init.ora                  (PFILE)

Oracle first look for a SPFILE and after that a PFILE. 

Advantages of SPFILE:-

1) No need to restart the database in order to have a parameter changed and the new value stored in the SPFILE

2) Reduce human errors Parameters are checked before changes are accepted

3) An SPFILE can be backed-up with RMAN while PFILE can’t backed-up by RMAN

By following query we can check that which file is using by database.

SELECT DECODE (value, NULL, 'PFILE', 'SPFILE') "Init File Type"

             FROM v_$parameter WHERE name = 'spfile';

Switch from SPFILE to PFILE:

     1)  CREATE PFILE FROM SPFILE;

     2)  Backup and delete SPFILE

     3)  Restart the instance

Switch from PFILE to SPFILE :

     1)  CREATE SPFILE FROM PFILE;

     2)  Restart the instance

Note :- PFILE will be in the same directory but system will start from SPFILE as it is default.

Changing the SPFILE parameter values:-

  ALTER SYSTEM SET timed_statistics = TRUE SCOPE = BOTH;

Note :- The SCOPE parameter can be set to SPFILE, MEMORY or BOTH

- MEMORY: Set for the current instance only. This is the default behavior if a PFILE was used at STARTUP.

- SPFILE: Change value in SPFILE, the parameter will take effect with next database startup

- BOTH: affect the current instance and change the value in current SPFILE. This is the default behavior if an SPFILE was used at STARTUP.

Creating SPFILE to PFILE and vice-versa:-

CREATE PFILE FROM SPFILE;

CREATE SPFILE FROM PFILE;

CREATE SPFILE = '/oradata/spfileORCL.ora' FROM PFILE = '/oradata/initORCL.ora' ;

Oracle 11g provides a new feature to create PFILE or SPFILE using the current values in the Oracle Memory (active Instance).

This will be useful if you delete the spfile or pfile by mistake.

SQL>create pfile from memory

File created.

pfile will create in the $ORACLE_HOME/dbs directory. You can specify the location while creating the pfile.

SQL> create pfile='/home/oracle/scott/initprod.ora' from memory;

File created.

SQL>

Similarly, we can create SPFILE from memory using the below mentioned command.

CREATE SPFILE FROM MEMORY;

You need to have SYSDBA or SYSOPER role to execute above statements.