What is the difference between SPFILE and PFILE in Oracle?
One reader asks about the difference between SPFILE and PFILE and advice for setting Oracle 10G parameters for SAP ECC6. Read an expert's take here.
We have SAP ECC6 on Oracle10G. I need to change some Oracle parameters recommended by SAP Early Watch Alert. I need to know where I should set these parameters (SPFILE or PFILE). I also need to know the difference between these two files (since I am new to Oracle 10G). What are their advantages? How can I set parameters in SPFIE and PFILE?
I will answer the second part of your question first (the difference between SPFILE and PFILE). Oracle provides two different types of parameter files that you can use, PFILE and SPFILE.
THE PFILE
Until Oracle 8i, we were using a text file called the PFILE (parameter file) for setting the database initialization parameters. This PFILE is read at instance startup time to get specific instance characteristics. The PFILE is text based, and can be edited in an editor like vi on UNIX or Notepad on Windows. Any changes that were made in PFILE would only take effect when the database is restarted
Since SAP/Oracle recommends using SPFILE over PFILE, I will provide some details on SPFILE to answer your question in more detail.
THE SPFILE
In Oracle9i, a new feature called SPFILE (server parameter file) was introduced. SPFILE is a binary file that contains the same information as the old PFILE. SPFILE permits dynamic changes without requiring you to restart that instance.
By default, if you do not specify PFILE in your STARTUP command, Oracle will use server parameter file (SPFILE). If you choose to use the traditional text initialization parameter file (PFILE), you must specify the PFILE clause when issuing the STARTUP command.
The SPFILE is different from the PFILE in that it can not be directly edited. This is because it has a header and footer that contain binary values. Since you can not change a SPFILE directly, Oracle allows you to manage the SPFILE via the ALTER SYSTEM command.
When you execute the ALTER SYSTEM command, the parameter change is validated immediately, which helps avoid errors associated with entering an incorrect parameter name or an invalid value. In addition, the ALTER SYSTEM allows you to specify whether you want to update the parameter immediately, update just the SPFILE, or both. All these features help avoid human error associated with manually updating a PFILE.
The following example shows how to change the current value of SESSIONS and store the value in the SPFILE.
SQL> ALTER SYSTEM SET SESSIONS=200 SCOPE=SPFILE; The new part in this command is the parameter SCOPE. You have the following options for this.
SCOPE = SPFILE
(For both static and dynamic parameters, changes are recorded in the spfile, and will take effect in the next restart.)
SCOPE = MEMORY
(For dynamic parameters, changes are applied in memory only. No static parameter change is allowed.)
SCOPE = BOTH
For dynamic parameters, the change is applied in both the server parameter file (SPFILE) and memory. No static parameter change is allowed.)
For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.
Now for the first part of your question. Since you are using Oracle 10G, you have to use the ALTER SYSTEM command to change the Oracle parameters as recommended by the SAP Early Watch Alert.
