Difference between PFILE and SPFILE? What are their advantages?

Difference between PFILE and SPFILE? What are their advantages?

 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.

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 cannot be directly edited. This is because it has a header and footer that contain binary values. Since you cannot 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.