Home / Oracle DBA / PFILE & SPFILE

PFILE & SPFILE

P FILE

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.

SP FILE

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.

Parameter Scope

  • 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.

LAB EXAMPLES:

  • 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;
System altered.

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’;

Read More:
RAC Administration
Openfiler Installation steps
Oracle RAC Architecture
DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

Oracle 10g to 11g Enhancements (Difference between Oracle 10g and 11g)

Difference between Oracle 10g and 11g 1- Enhanced Automatic Memory Management System Oracle 9i automated …

Leave a Reply

Your email address will not be published. Required fields are marked *

DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.
close-link
GET OUR LATEST CONTENT IN YOUR INBOX

SUBSCRIBE 
Your information will never be shared
close-link
Enquiry
Submit
close-link
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.
Subscribe