In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> PFILE and SPFILE initialization files in Oracle

 PFILE and SPFILE initialization files in Oracle

Here are the main topics for this article:

• How the Oracle Instance is initialized

• SPFILE advantages

• What is my database using SPFILE or PFILE ?

• How could I switch from SPFILE to PFILE and vice-versa ?

• Changing the SPFILE parameter values

• Converting SPFILE to PFILE and vice-versa

• Viewing parameter values

How the Oracle Instance is initialized

When the Oracle instance start, first it looks to the $ORACLE_HOME/dbs (UNIX, Linux) or ORACLE_HOME/database (Windows) directory for the following files (in this order): 

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

      2.  spfile.ora                                  (SPFILE)  

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

      4.  init.ora                                      (PFILE) 

The first found file is used for the instance initialization. So, Oracle first look for a SPFILE and after that after a PFILE. PFILE is a text file, SPFILE is a binary file which cannot be modified using a text editor.  

SPFILE advantages

Here are the SPFILE advantages:

1. No need to restart the database in order to have a parameter changed and the new value stored in the initialization file

2. Reduce human errors: Parameters are checked before changes are accepted

3. An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)

What is my database using SPFILE or PFILE ?

The following select has the response:

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

             FROM sys.v_$parameter WHERE name = 'spfile';

How could I switch from SPFILE to PFILE and vice-versa ?

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 (the PFILE will be in the same directory but will not be used. SPFILE will be used instead)  

Changing the SPFILE parameter values

                 ALTER SYSTEM SET timed_statistics = TRUE

                          COMMENT = 'Changed by Paul on January 1st 2008'

                          SCOPE = BOTH

                          SID = '*'

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: update the SPFILE, the parameter will take effect with next database startup
- BOTH: affect the current instance and persist to the SPFILE. This is the default behavior if an SPFILE was used at STARTUP. 

The COMMENT parameter (optional) specifies a user remark.

The SID parameter (optional; only used with RAC) indicates the instance for which the parameter applies. (Default is *: all Instances).

Converting SPFILE to PFILE and vice-versa

This could be done in order to have a backup in the other format or to change the initialization file for the database instance. 

            CREATE PFILE FROM SPFILE;

            CREATE SPFILE FROM PFILE;

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

Viewing parameter values

1)   Using SHOW PARAMETERS:     SHOW PARAMETERS sga_max

2)   Querying the following views:

        a) V$PARAMETER: display the currently in effect parameter values

        b) $SPPARAMETER: display the current contents of the server parameter file.

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> PFILE and SPFILE initialization files in Oracle