Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA 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

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: