A P FILE is a traditional text based init.ora parameter file. Typically this resides on the server in the $ORACLE_BASE/admin/SID/p file directory, with a symbolic link pointing to it from the $ORACLE_HOME/dbs directory.
A SP FILE, Server Parameter File, is a server managed binary file that Oracle uses to hold persistent initialization parameters. If a parameter is changed using the ALTER SYSTEM SET command Oracle will apply this parameter change to the current SP FILE.Oracle first look for a SP FILE and after that after a P FILE
SP FILE s provide the following advantages over P FILE s:
- An SP FILE can be backed-up with R MAN (R MAN cannot backup P FILE s).
- No need to restart the database in order to have a parameter changed and the new value stored in the initialization file
- Reduce human errors. The SP FILE is maintained by the server. Parameters are checked before changes are accepted.
- Easy to find – stored in a central location.
What is the difference between a P FILE and SP FILE:
A P FILE is a static, client-side text file that must be updated with a standard text editor like “notepad” or “vi”. This file normally reside on the server, however, you need a local copy if you want to start Oracle from a remote machine. DBA’s commonly refer to this file as the INIT.ORA file.
An SP FILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the “ALTER SYSTEM SET” command. This means you no longer need a local copy of the p file to start the database from a remote machine. Editing an SP FILE will corrupt it, and you will not be able to start your database anymore.
- SCOPE = SP FILE: ALTER SYSTEM changes are recorded in the sp file, to be given effect in the next restart.
- SCOPE = MEMORY: ALTER SYSTEM changes are applied in memory only. No static parameter change is allowed.
- SCOPE = BOTH: The ALTER SYSTEM change is applied in both the server parameter file and memory. No static parameter change is allowed.
- When you want to change the parameter only for the running instance and not for the subsequent instance you can change it only in memory. ;
SQL> alter system set cpu_count=4 scope=MEMORY; System altered. <ul> <li>When you want to change the parameter for subsequent instances only</li> </ul> SQL> alter system set cpu_count=4 scope=SPFILE; System altered.
- When you want to change the parameter for running and subsequent instances (if issys_modifiableis not false);
SQL> alter system set cpu_count=4 scope=BOTH;
Converting SP FILE to P FILE and vice-versa:
SQL> CREATE PFILE FROM SPFILE;
SQL> CREATE SPFILE = ‘$ORACLE_HOME/dbs/spfiletest.ora’ FROM PFILE = ‘$ORACLE_HOME/dbs/inittest.ora’;
SQL> CREATE P FILE FROM SP FILE = ‘test.ora’;
SQL> CREATE P FILE = ‘$ORACLE_HOME/dbs/my_pfile.ora’ FROM SP FILE;
SQL> CREATE P FILE = ‘$ORACLE_HOME/dbs/my_pfile.ora’ FROM SP FILE = ‘$ORACLE_HOME/dbs/my_sp file.ora’;
SQL> CREATE SP FILE FROM P FILE = ‘$ORACLE_HOME/dbs/my_p file.ora’;
SQL> CREATE SPFILE = ‘$ORACLE_HOME/dbs/my_spfile.ora’ FROM P FILE = ‘$ORACLE_HOME/dbs/my_p file.ora’;