Search this blog for other interesting articles

Monday 25 July 2011

Difference between PFILE and SPFILE in Oracle


This article answers the following queries :

  • What is PFILE in Oracle?

  • What is SPFILE  in Oracle?

  • What is the use of PFILE or SPFILE in Oracle ?

  • What is the difference between PFILE and SPFILE in Oracle ?

  • What are the advantages of SPFILE compared to PFILE in Oracle ?

  • How to check whether database is  running with PFILE or SPFILE ?



Use of PFILE or SPFILE

When an Oracle instance is started, its characteristics are set by reading some initialization parameters. These parameters are specified either through PFILE or SPFILE.  (0r)

When an oracle instance is being started, it reads either PFILE or SPFILE to set its characteristics.


What is PFILE ?

A PFILE is a static, client side text file. This file normally resides on the server. However in case you would like to start oracle from a remote machine, you will need a local copy of the PFILE in that machine.

This file can be edited with a standard text editor like notepad or vi editor. This file will be commonly referred as INIT.ORA file.

What is SPFILE ?

SPFILE (Server parameter file) is a persistent server side binary file. SPFILE should only be modified through “ALTER SYSTEM SET” command . Editing an SPFILE directly will corrupt the file and the start up of the database or instance will not be possible.

As SPFILe is a server side binary file,  local copy of the PFILE is not required to start oracle from a remote machine.


Advantages of SPFILE compared to PFILE

  • A SPFILE doesnot need a local copy of  the pfile to start  oracle from a remote machine.  Thus eliminates configuration problems.

  • SPFILE  is a binary file and modications to that can only be done through ALTER SYSTEM SET command.

  • As SPFILE is maintained by the server, human errors can be eliminated as the parameters are checked before modification in SPFILE

  • It is easy  to locate SPFILE as it is stored in a central location

  • Changes to the parameters in SPFILE will take immediate effect without restart of the instance i.e Dynamic change of parameters is possible

  • SPFILE can be backed up by RMAN


Difference between PFILE and SPFILE in Oracle :


SNO
PFILE
SPFILE
1
Static, client side text file
Persistent server side binary file
2
Local copy of pfile required to start database from a remote machine
local copy is not required
3
Can be edited through notepad or vi editor
Editing directly will corrupt the file. It should be modified only through ALTER SYSTEM SET command
4
Is available in earlier versions of Oracle 9i
Available from Oracle 9i and above
5
Prone to human errors while modification
Eliminates human errors as parameters are checked before modification
6
Cannot be backed up by RMAN
Can be backed up by RMAN
7
Parameters in pfile cannot be changed dynamically and system needs to be bounced for the new changes to take effect
Dynamic change of some parameters is possible. Need not restart server for the changes to take effect



Query to findout, if database is running with PFILE or SPFILE

Goto SQL prompt and execute the following command to figure out if database is started with a PFILE or SPFILE.


SQL > SELECT DECODE(value, NULL, ‘PFILE’, ‘SPFILE’) “Init File” FROM sys.v_$parameter WHERE name = ‘spfile’;





Enter your email address:


Delivered by FeedBurner



5 comments:

Anonymous said...

it would be really good if you mention the path's where SPFILE and PFILE Located

Anonymous said...

/oracle/sid/102_64/dbs

Anonymous said...

You can also tell about creationof spfile from pfile and vice versa.

Anonymous said...

create pfile from spfile;

create spfile from pfile;

Anonymous said...

$ORACLE_HOME/dbs

Search this blog for other interesting articles

Please subscribe & activate link received to your email id to receive latest articles

Enter your email address:

Delivered by FeedBurner