Guenadi N Jilevski's Oracle BLOG


Oracle pfile and spfile

Oracle pfile and spfile

There are two types of initialization parameter files in oracle:

1)Parameter Files (PFILES)
2)Server Parameter Files (SPFILES).

Comparison Between Pfile and Spfile:

1. Pfile is the text file that can be edited using a text editor. Spfile is binary file that cannot be edited directly by text editor. If you edit spfile it will corrupt.

2. When changes are made to the PFILE, the instance must be shut down and restarted before it takes effect. Most changes to the SPFILE can be made dynamically, while the instance is open and running.

3. Pfile is by default named as init{instance_name}.ora. Spfile is by default named as spfile{instance_name}.ora where instance_name will be replaced by your database name. By default spfile is located under $ORACLE_HOME/dbs in unix and windows and pfile will be located under $ORACLE_HOME\database location on windows.

4. Pfile can be created from an SPFILE using the create pfile from spfile command.Spfile can be created from a PFILE using the create spfile from pfile command.

5. In order to modify any parameter value in pfile you must open the pfile with any text editor and manually edit it. And in order to modify any parameter value inside spfile you must issue ALTER SYSTEM SET with SCOPE=SPFILE or SCOPE=BOTH. Pfile can never be edited with ALTER SYSTEM SET.

6. Spfile can be backed up by RMAN. pfile can’t be backed up by RMAN.

December 7, 2007 Posted by | oracle | Leave a comment