What is Spfile and pfile ? How to create spfile from pfile and vice-versa?

 

Spfile refers to Server Parameter file(spfile$ORACLE_SID.ora). Server parameter files are binary files that exist only on the server and are called from client locations to start up the database. Since spfile is a binary file we cannot edit the file directly rather we need to use ALTER SYSTEM SET parameter to modify/update the parameters.

You must have the SYSDBA or the SYSOPER system privilege to create spfile. You can execute this statement before or after instance startup. However, if you have already started an instance using spfile_name, you cannot specify the same spfile_name in this statement.

CREATE SPFILE='$PATH/spfile_name' FROM PFILE='$PATH/pfile_name';

Pfile refers to Parameter file(init$ORACLE_SID.ora). Pfile is a text file created from spfile, used to get a list of the current parameter setting being used by the database. We can easily edit the pfile using editor like vi and applied to the database by creating spfile from the pfile.

You must have the SYSDBA or the SYSOPER system privilege to create pfile. We can execute this statement either before or after instance startup.

CREATE PFILE='$PATH/pfile_name' FROM SPFILE='$PATH/spfile_name';

A simple pfile will just have a path to the spfile

oracle@node1# cat init$ORACLE_SID.ora
SPFILE=/$PATH/spfile$ORACLE_SID.ora

Both the spfile and pfile are located in the following path by default:

for windows : $ORACLE_HOME/database
for LINUX : $ORACLE_HOME/dbs

During database startup, Oracle scans the above mentioned directories in the following order:

  • spfile$ORACLE_SID.ora
  • spfile.ora
  • init$ORACLE_SID.ora
  • init.ora

If oracle is unable to find these files, then the startup will fail.

We can also govern the DB start by manually defining the PFILE path:

SQL> startup pfile='$PATH/init$ORACLE_SID.ora';

Following command can be used to find the current parameter setting / value

SQL > SHOW PARAMETERS

To display all parameters with the text SGA

SQL > SHOW PARAMETERS SGA

 

SQL> show parameters sga
NAME TYPE VALUE
------------------- ----------- ----------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 39G
sga_target big integer 39G

We can also use v$parameter to find the current parameter setting / value

SQL> select name,value from v$parameter where name='sga_target';
NAME VALUE
-------------------- ------------------------------
sga_target 41875931136

V$SYSTEM_PARAMETER is the view which shows instance level parameters.A new session inherits parameter values from the instance-wide values.

SQL> select name,value from v$system_parameter where name = 'sga_target';
NAME VALUE
-------------------- ------------------------------
sga_target 41875931136

v$parameter view – session based / local setting
v$system_parameter view – instance based / global setting

Dynamic Parameters :

Some initialization parameters are dynamic, that is, they can be modified using the ALTER SESSION (valid for that session) or ALTER SYSTEM statements while an instance is running and doesnot require a instance restart.

Leave a Reply