7 Replies Latest reply: Sep 5, 2007 9:25 PM by Michael Howles RSS

pfile VS spfile

Lauran kerry
Currently Being Moderated

Hi,

Can anyone tell me what is the different between pfile and spfile ? What is their usage and advantage/disadvantage ?

 

Regards

Lauran

  • Re: pfile VS spfile
    Manas Pattnaik
    Currently Being Moderated

    Hello,

    Actually content wise both are nearly same.

    These files contain the different parameter values of Oracle.

    However the pfile can be opened in a text editor and edited whereas the spfile is a binary file.Oracle reads the value of different parameters from the spfile. If any value has to vbe changed the same has to be changed in the pfile and the binary spfile is to be created from this pfile.

    Pl award suitably.

    Regards

  • Re: pfile VS spfile
    Elmar Billen
    Currently Being Moderated

    Hi Lauran,

     

    the spfile is the "binary version" of the pfile. If you change parameters, do not edit the pfile, as it will get overwritten any time you start br-tools. See this message at the beginning of e.g. brbackup:

     

    ls3809:lopadm 51> brbackup -u /

    BR0051I BRBACKUP 7.00 (18)

    BR0055I Start of database backup: bdwbvmoo.aft 2007-09-05 08.02.42

    <b>BR0477I Oracle pfile /oracle/LOP/102_64/dbs/initLOP.ora created from spfile /oracle/LOP/102_64/dbs/spfileLOP.ora</b>

    [...]

     

    To change Oracle parameters, use brtools instead (brtools, 1=>Instance Management, 4=>Change database parameters).

     

    Best regards,

      Elmar.

  • Re: pfile VS spfile
    Lauran kerry
    Currently Being Moderated

    Hi All,

    Thanks for the info. You mean in a SAP system, there will be pfile as well as spfile ? If so, where are they located each ?

     

    Thanks

    Lauran

  • Re: pfile VS spfile
    Eric Brunelle
    Currently Being Moderated

    on windows (Ora le 10g):

     

    pfile is at D:\oracle\[SID]\102\database\init[SID].ora

    spfile is at D:\oracle\QPR\102\database\SPFILE[SID].ORA

     

    if you have both, Oracle will use SPFILE, brtools uses the pfile. if you edit the pfile, it will contain list of parameters and they will all start with a start and a dot

     

    *.

     

     

    I personnaly hate the SPFILE and prefer the pfile. I found it easier to use.

    • Re: pfile VS spfile
      Michael Howles
      Currently Being Moderated

      The one advantage with respect to an SPFILE is that you can use brtools to change parameters to the Oracle DB on the fly.

       

      I also agree that it is very annoying to have to use brtools or sqlplus to change these parameters instead of using vi or Notepad to just change the pfile flatfile.

       

      It's worth noting that while the spfile almost looks like plaintext in an editor, it does have a hash of its content in the beginning which is an integrity check to ensure that no changes are made directly to it.

       

      To see if you are using an spfile, I believe the syntax in SQLPLUS is 'SHOW PARAMETER SPFILE' and see if it returns you a result with a filename.  If you currently have an SPFILE and want to just use a PFILE, you can go to SQLPLUS and do: CREATE PFILE FROM SPFILE; and then stop Oracle, and delete/rename the SPFILE, and then start Oracle back up.  Conversely, if you want to use an SPFILE, you would do CREATE SPFILE FROM PFILE; and restart Oracle.

       

      Personally, I find that if I am changing a significant number of Oracle parameters, it's easiest to switch to PFILE from SPFILE, make my changes in the PFILE, and then re-create the SPFILE.

       

      Again, it's a bit silly, but I suppose for the of integrity of the parameters, it's a benefit.

Actions