Sunday , October 22 2017
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

Comments

comments

Check Also

Moving a database from Normal File System to ASM storage

Default storage for oracle database is File System, where database files resides on local storage. …

Leave a Reply

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