Pfile Vs Spfile

PFILE :
PFILE is a text file which can be modified by text editor. Oracle server required PFILE to start an oracle Instance.

SPFILE :
SPFILE is a binary file which can not be modified by text editor. It is created from PFILE.

When the Oracle instance start, first it looks to the $ORACLE_HOME/dbs   (UNIX, Linux) or  ORACLE_HOME/database (Windows) directory for the following files (in this order): 
      1.  spfileSID.ora                          (SPFILE)
      2.  Default SPFILE                       (SPFILE)  
      3.  initSID.ora                              (PFILE) 
      4.  Default PFILE                          (PFILE) 

SPFILE advantages

1.      No need to restart the database in order to have a parameter changed and the new value stored in the initialization file
2.      Reduce human errors: Parameters are checked before changes are accepted
3.      An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)

How could I switch from SPFILE to PFILE and vice-versa?

Switch from SPFILE to PFILE:
1)      CREATE PFILE FROM SPFILE;
2)      Backup and delete SPFILE
      3)   Restart the instance

Switch from PFILE to SPFILE:
     1)  CREATE SPFILE FROM PFILE=’Location of the PFILE’;
     2)  Restart the instance (the PFILE will be in the same directory but will not be used. SPFILE will be used instead)


This could be done in order to have a backup in the other format or to change the initialization file for the database instance. 

            CREATE PFILE FROM SPFILE;
            CREATE SPFILE FROM PFILE='/oradata/initORCL.ora';
            CREATE SPFILE = '/oradata/spfileORCL.ora' FROM PFILE = '/oradata/initORCL.ora' ;   

Comments

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

Register Archive log file manually in standby Database

How to check UNDO tablespace usage and who is using more undo