Home > Oracle DBA > Oracle concepts – pfile vs spfile ?

Oracle concepts – pfile vs spfile ?

Today we are going to discuss about pfile and spfile…

Whenever we start Oracle instance , it internally refers to a parameter file which is either a pfile or a spfile.

With regard to Older Oracle version, this parameter file is known as pfile and the DBA use to more often call it as INIT.ORA file , but starting from Oracle 9i and up , Oracle corp. introduced another parameter file which is called spfile.

Oracle recommends that you should use spfile instead of pfile , since there are certain advantages of doing so. We will cover what are the advantages of spfile over pfile in just a moment.

The pfile can be modified via a TEXT Editor such as vi editor / gedit on Linux/Unix based systems and via notepad on Windows, whereas spfile is a binary file which can be modified only by Oracle using ALTER SYSTEM set command.

CAUTION : Don’t modify the spfile via a text editor, there are high chances of file corruption since it’s a binary file and if file corruption happens then your db wont start untill and unless you re-create spfile.

Click here to download a sample pfile.

Path for your pfile or spfile on Oracle Server

FOR UNIX / LINUX based systems
$ORACLE_HOME/dbs/

FOR WINDOWS based systems
$ORACLE_HOME/database

ORACLE_HOME is a env. variable which is holding the path for Oracle software dir.

So what are the ADVANTAGES of spfile over pfile ?

1. spfile can be backed up by RMAN, where as pfile cannot be backed up by RMAN. You have to manually copy the pfile to a backup location.
2. Less chances of human errors, if you are using spfile, since the file resides on server and when you modify any of the parameter in spfile then Oracle checks for the syntax.
3. If you need to start Oracle remotely and if you are using pfile instead of spfile then you should also have the same pfile on your local machine, where as in case of spfile you don’t need spfile file on your local machine.

So, how do you identify if you are using pfile or spfile ??
Just execute the following query

SQL> set linesize 100
SQL> set pagesize 200
SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
       FROM sys.v_$parameter WHERE name = 'spfile'

You can also use the V$SPPARAMETER view to check if you are using a PFILE or not: if the “value” column is NULL for all parameters, you are using a PFILE.

See below screen shot for more details.

pfile or spfile

pfile or spfile

You can also see through SHOW PARAMETER command

SQL> show parameter spfile

Here goes the sequence followed by Oracle when we start the instance :

1. If will try to find spfile${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)
For example, if my ORACLE_SID is : mytest , then my spfile would be

$ORACLE_HOME/dbs/spfilemytest.ora

2. If above file is not present then use the spfile.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)

3. If above file is not present , then use the init${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database(Windows).

4. If above file is not present , then use the init.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database(Windows).

CAN I CREATE PFILE FROM SPFILE OR VICE-VERSA ?
If you have a pfile, you can always create a spfile out of out or if you have a spfile you can create a pfile from it.

Assuming your SID is 'mytest' and
ORACLE_HOME is '/u01/oracle/product/10.2.0/db_1/'

SQL> create pfile from spfile
This will simply create a pfile in the
following path '/u01/oracle/product/10.2.0/db_1/dbs/initmytest.ora' file

SQL> create pfile='/u01/oracle/product/10.2.0/db_1/try.ora' from spfile
This will create a pfile into user specified dir with the name try.ora

SQL> create spfile from pfile
This will create a spfile from a pfile with the following name 'spfilemytest.ora'
at the following path $ORACLE_HOME/dbs/

SQL> create spfile ='/u01/some_dir/spfilemytest.ora' from
pfile = '/u01/some_other_dir/initmytest.ora'

This will create a spfile from a pfile with the following name 'spfilemytest.ora'
at a user defined DIR

While doing a Oracle DB startup, you can specify pfile as a parameter. 
For example
SQL> startup pfile='/some_path/initSID.ora'
This will process or read pfile to start the DB

NOTE : You cannot specify spfile with startup command, 
SQL> startup spfile='some_dir/spfile.ora'
The above SQL is not possible, but you can always call a spfile within a pfile 

and then call the same like

SQL> startup pfile='/some_dir/initSID.ora'
Contents of initSID.ora will contain only 1 parameter i.e.

SPFILE='/some_dir/spfileSID.ora'

HOW CAN I MODIFY OR ALTER A PARAMETER PRESENT IN SPFILE ?

SQL> show parameter control_file_record_keep_time
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     10



SQL> alter system set control_file_record_keep_time=12 scope=both;

System altered.



SQL> show parameter control_file_record_keep_time

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     12

Alter Parameter

The SCOPE parameter can be set to SPFILE, MEMORY or BOTH:

– MEMORY: Set for the current instance only. This is the default behaviour if a PFILE was used at STARTUP.
– SPFILE: update the SPFILE, the parameter will take effect with next database startup.
– BOTH: affect the current instance and persist to the SPFILE. This is the default behaviour if an SPFILE was used at STARTUP.

The COMMENT parameter (optional) specifies a user remark.

HOW TO BACKUP SPFILE VIA RMAN ??

RMAN (Oracle’s Recovery Manager) will backup the SPFILE with the database control file if setting “CONFIGURE CONTROLFILE AUTOBACKUP” is ON (the default is OFF). PFILEs cannot be backed-up with RMAN. Look at this example:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Use the following RMAN command to restore an SPFILE:

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
Advertisements
Categories: Oracle DBA
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: