Oracle pfile vs spfile: Difference and Comparison

advertisements

_____________________________________________________________________________________________________________________

Pfile/Init file

1.   It is used for database startup

2.   It is also called INIT file

3.   It is a text file which contains all the main parameters for the database startup

4.   This can be edited using notepad or Vi editor

5.   The general format for the pfile is INIT.ora. But it can be in different names.

6.   During the database startup, the Oracle search for the pfile in default location which is ORACLE_HOME/dbs or ORACLE_HOME/database

7.   If your pfile is in different directory you can specify the file name using following format

SQL> startup pfile='c:\orcl\initorcl.ora';

8.   we can create pfile from memory using following command

 Create pfile='/home/oracle/st/initeORCL.ora' from memory;

9.   We can create pfile from spfile using following command

 create pfile='/home/oracle/st/initORCL.ora' from spfile;

10.               Pfile is not a dynamic file which means none of the parameter value changes will not get updated to the pfile automatically/dynamically.

11.               Suppose, if you have spfile and pfile is present in the default location of the database (ORACLE_HOME/dbs or ORACLE_HOME/database), the pfile gets second priority than the spfile.

 

Spfile

1.   Spfile is used for database startup

2.   This also called as server parameter file

3.   it is a binary file

4.   it is a dynamic file

5.   The general format for the pfile is SPfile.ora. But it can be in different names.

6.   During the database startup, the Oracle search for the pfile in default location which is Unix:ORACLE_HOME/dbs or Windows:ORACLE_HOME/database

7.   It is not recommended / supposed to edit the file which is being currently used by the database.

8.   SPFILE can be edited by alter system command.

9.   If your pfile is in different directory you can specify the file name using following format

SQL> startup pfile='c:\orcl\spfileorcl.ora';

10.               we can create pfile from pfile using following command

SQL> create spfile='/home/oracle/st/spfileorcl.ora' from pfile='/home/oracle/st/initorcl.ora';

11.               we can create pfile from memory using following command

SQL> create spfile='/home/oracle/st/spfileorcl.ora' from memory;

12.               You can verify whether/confirm the database is using following database parameter value. If the value of the parameter is null means it is using pfile

SQL> show parameter spfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------

spfile                               string      /u00/app/oracle/product/11.2.0

.3/db_1/dbs/spfileorcl.ora

13.               Spfile can be backed up by the RMAN tool with the database backup so that recovery is also easy.

14.               Oracle has many auto tuning features as well as dynamic parameters which can be handled only if your database is using spfile

Suppose, if you have spfile and pfile is present in the default location of the database (ORACLE_HOME/dbs or ORACLE_HOME/database), the spfile gets first priority.

_____________________________________________________________________________________________________________________

Website Stats