Difference between the PFILE, SPFILE and INIT.ORA

Difference between the PFILE, SPFILE and INIT.ORA, and what is the function of each?


In the "old days," Oracle kept its parameters in a text file. By default, this parameter file's name was of the form initsid.ora where "sid" is replaced by the ORACLE_SID value. So if ORACLE_SID=ORCL, the parameter file was named "initorcl.ora." When two DBAs have a conversation about the parameter file, their SIDs may be different, so they refer to this parameter file as the "INIT.ORA" file. They just remove the SID from the file name, but it is implied.

Back in the old days, many of the initialization parameters were not dynamically modifiable. If you wanted to use a different set of parameters for weekend batch processing compared to online use during the week, the DBA would have two parameter files -- two INIT.ORAs. Now the DBA could not name them the same if they were in the same directory. So the DBA might have one parameter file called "init_weekday.ora" and another called "init_weekend.ora." To change the parameter values, the DBA would shut down the database Friday evening at 6 p.m. and start it up with the weekend's parameter values. Monday morning at 5 a.m., the database was restarted with the weekday's parameter values. The PFILE parameter of the STARTUP command could instruct Oracle to start with a specific Parameter FILE (PFILE) like:

STARTUP PFILE=/home/oracle/7.3/dbs/init_weekend.ora

Oracle DBAs would refer to the initialization parameter file as either the INIT.ORA or the PFILE. They are the same thing. The PFILE (INIT.ORA) is a text file which can be edited with any text editor.

In Oracle 9i, the binary parameter file was introduced. This is called the SPFILE (Server Parameter File). The SPFILE replaces the PFILE. For the most part, you can use the PFILE or the SPFILE, but not both. The SPFILE is a binary file and is not edited with any text editor. Instead, you modify the parameter's values with the ALTER SYSTEM SET command. For information on these parameter files, please refer to the following Oracle documentation:

Initialization Parameter Files and Server Parameter Files

Managing Initialization Parameters Using a Server Parameter File

There are benefits to the SPFILE over the PFILE. Since the SPFILE was introduced in Oracle 9i, I do not use the old PFILE any more. Some Oracle functionality does not work as well if you are not using the SPFILE. The self-tuning SGA works best when using a SPFILE as Oracle can store some information abou the SGA components in the SPFILE. THe SPFILE can hold parameters for multiple Oracle instances, something that is very important in a Real Application Clusters (RAC) environment. Oracle's Recovery Manager (RMAN) can automatically back up the SPFILE's contents while it cannot back up the PFILE.