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.
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.
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.
Dig Deeper on SAP development and programming languages
Related Q&A from Farooq Ali
How to manually add files to an SAP transport queue
An administrator wants to learn the procedure for manually adding files to a transport queue. Continue Reading
Changing SAP spool servers for all the printers in your QA system
Employing menu settings in Tcode (SPAD) can change SAP spool servers for your QA system. Learn how in this expert tip. Continue Reading
Changing a customer message priority level after it's sent
I mistakenly sent a customer message with the wrong priority level. How can I change the priority level of the message sent? Continue Reading