Oracle : pfile and spfile

2009-06-22T08:27:00.000-07:00

This story is about Oracle, The database.

Long long ago, From the era of Dinosaurs, Oracle was using parameter file (short name pfile) to initialize parameters at the time of database instance startup. At OS level you can find them as init.ora or initSID.ora. If both files are present, initSID.ora has precedence.

{::Start of flash back sceen::

“database” and “instance” are two guys. In simple words, database guy is collection of OS level files such as Data files, control files and redo-log files and Instance guy is interface to operate and work with these files and other database objects. Instance guy has two major organs in his body, SGA (System/Shared Global Area) and PGA (Program/Private Global Area).In your lower tone, you can say; Instance is the software (and memory) that Oracle uses to manipulate the data in the database. In order for the instance to be able to manipulate that data, the instance must open the database. A database can be opened (or mounted) by more than one instance; however, an instance can open at most one database.

::End of flash back sceen::}

Pfile (parameter file) is a text file, can be viewed and edited by a simple text editor like Notepad/vi. If you will made changes in pfile, those changes will only come in affect, when instance will be restarted, because oracle reads the pfile at the time of instance startup.

If you want to change dynamic parameters without restarting the instance; that can be done with ALTER SYSTEM or ALTER SESSION statement, but those changes will be lost, depending on the lifetime of instance or session, whichever statement used for the modification.

When all Dinosaurs dead, oracle came up with concept of spfile (Server Parameter file) one of the major feature of Oracle 9i. Spfile is a binary file; the values can be changed in spfile with help of ALTER SYSTEM SET/RESET command. Though you need to restart the instance if the parameter is not dynamic in order to changes to come in effect, now benefit is, all the changes of parameters will be persistent and remain same even after instance restart.At OS level you will find them as spfileSID.ora

One can view parameter values using one of the following methods (regardless if they were set via PFILE or SPFILE):

The "SHOW PARAMETERS" command from SQL*Plus (i.e.: SHOW PARAMETERS timed_statistics)

V$PARAMETER view - display the currently in effect parameter values

V$PARAMETER2 view - display the currently in effect parameter values, but "List Values" are shown in multiple rows

V$SPPARAMETER view - display the current contents of the server parameter file.

Platform Default Name Default Location

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

UNIX and Linux spfile$ORACLE_SID.ora $ORACLE_HOME/dbs or the same location as the datafiles

Windows spfile%ORACLE_SID%.ora %ORACLE_HOME%\database

Example to create spfile from pfile lets say the file is /oracle/dbs/init.ora. In this example no SPFILE name is specified, so the file is created with the platform-specific default name and default location.

CREATE SPFILE FROM PFILE='/oracle/dbs/init.ora';

Another example, which follows, illustrates creating a server parameter file and supplying a name.

CREATE SPFILE='/oracle/dbs/exmpl_spfile.ora'

FROM PFILE='/oracle/dbs/exmp_init.ora';

Those people, who assume themselves as computer geek, please do not edit spfile through text editor; doing so will corrupt the file. You will not be able to start your instance, and if the instance is running, it could fail.

Check if your running database is using pfile or spfile; run any of the below command at SQL.

SQL>show parameter spfile;

NAME TYPE VALUE

———————————— ———– ——————————

spfile string

Above, the database is not using spfile so null string value. If spfile is in use, you will get output in below format for same command

NAME TYPE VALUE

—————————— ———– ——————————

spfile string %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA

Or you can use below command.

SQL> SELECT name, value FROM v$parameter

2 WHERE name = 'spfile';

NAME VALUE

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

spfile %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA

If spfile is already in use, the command will throw error ORA-32002: cannot create SPFILE already being used by the instance. If every things goes well and spfile got created then by default at next startup of instance spfile will be used.

Here is one small issue for, who loves to leave foot-marks or would like to embed their ideas

# To use automatic undo, I did below settings and my name is Peter

undo_management = 'auto'

The comment specified above in pfile would be omitted at the time of spfile creation.

undo_management = 'auto' # This is Peter's idea to set this value for automatic undo

On the other hand, this comment would be added to the spfile and will be exported into pfile at the time of spfile export.

The default behavior of the STARTUP command has changed since Oracle9i; the command now searches for the following files in this order:

Try to use the spfile${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)
Try to use the spfile.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)
Try to use the init${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)

To override this search sequence and use PFILE option for STARTUP command:

SQL> STARTUP PFILE='/ORACLE//myFolder/jackNjill.ora';

If you are searching for "STARTUP SPFILE=" command, then please don't waste your time, there is no such command.

Lets say you are very "innovative" kind of person and you give a damn about your running database, so being such a fearless person you decided to change the location of spfile and enjoy the show.

Below is the lyrics for your song:

Move your spfile to the location where you want to place it, say /u01/app/oracle/mydir/spfileORCL.ora

In $ORACLE_HOME/dbs create pfile init.ora and enter only one line:

spfile=/u01/app/oracle/mydir/spfileORCL.ora

Place it in default location else give pfile option while starting

STARTUP PFILE=/ora/init.ora

Bounce your database.

Blame game: If something bad happens due to this ultra-innovative idea, then it will be only you to be blamed!

One night in one of your bad dreams you saw that people are using your spfile as "notebook and pencil" you woke-up and decided to take backup of spfile in text file, so that you can keep it under your pillow.

Below is the command to create pfile

SQL>create pfile='/some/path/init.ora' from spfile;

CREATE PFILE [= 'pfile_name' ] FROM { SPFILE [= 'spfile_name'] | MEMORY } ;

After your bad dream you tried a lot to go to sleep again, but all went in vain, so you decide to play pfile-spfile game, here are the rules:

To add parameters to the pfile or to the spfile. In case you want to add parameters to the spfile, just export the file using the statement given below. This will create a pfile with all the initialization parameters from the current spfile of the database. Edit the pfile, restart the database using this pfile and then recreate the spfile, after which you will have to restart the database using the spfile.

What the guy who got the idea about spfile told to his Boss in order to convince him:

Boss... boss... SPFILEs will provide following advantages over PFILEs:

An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)
Reduce human errors. The SPFILE is maintained by the server. Parameters are checked before changes are accepted.
Eliminate configuration problems (no need to have a local PFILE if you want to start Oracle from a remote machine)
Easy to find - stored in a central location

#~#-#~#-#~#-#~#-#~#-#~#-#~#-#~#-#~#-#~#-#~#-#~#-#~#-#~#-#~#-#~#-#~#-#~#

Your comments are welcome for correction or some more information.