create spfile from pfile tips
Oracle Tips by Burleson Consulting
Introducing the PFILE and SPFILE
Oracle provides two different types of mutually
exclusive parameter files that you can use, PFILE and SPFILE. Let’s look at the
PFILE and the SPFILE in a bit more detail.
Tip! - Making a PFILE when Oracle will not start
SQL> connect system/manager as sysdba
SQL> create pfile='C:\ora\pfile\init.ora'
Now edit the pfile in notepad to make the changes need to
allow Oracle to start.
Finally, start the database using the pfile you just
SQL startup pfile='C:\ora\pfile\init.ora';
SQL create spfile from pfile='C:\ora\pfile\init.ora'
The Oracle PFILE
As we said, the parameters are stored in either a PFILE
or an SPFILE. The PFILE is a text-based file, and the “init.ora” file has been
around for over a decade. Inside the PFILE are a number of database settings
called parameters. These parameters help the Oracle programs know how to start.
The parameters tell the Oracle programs how much memory to allocate, where to
put files related to the database and where certain database files already
As the PFILE is text based, one can edit it in an editor
like vi on UNIX or Notepad on Windows. When you have changed it, you need to
make sure you save your changes to disk before you exit the editor. Also, make
sure you save it as a plain text file, since some editors (like Microsoft Word)
can save documents in special formats that Oracle would not be able to read.
Depending on which operating system you are running on,
your PFILE is located by default in the ORACLE_HOME\database (usually the case
on Windows) or ORACLE_HOME\dbs directory for most other platforms (we talked
about where ORACLE_HOME was earlier in this book).
If you are using a PFILE, it takes on the form of
initSID.ora, meaning the file will use the ORACLE_SID you defined when you
created the database. If your SID is called testdb, the resulting PFILE should
be called inittestdb.ora
The Oracle SPFILE
The SPFILE is different from the PFILE in that it can
not be directly edited. This is because it has a header and footer that contains
binary values. Since you can not change a SPFILE directly, Oracle allows you to
manage the SPFILE via the alter system command.
That might sound a bit more complex, but it really is no
harder than manually changing a PFILE. For using an SPFILE, you can reap great
benefits. It can be backed up by RMAN (Oracle’s backup and recovery software)
every time a change is made or when the database is backed up, which means it’s
easier to recover (we will talk about RMAN a great deal in a later chapter!).
Also, SPFILES allow you to make dynamic changes to parameters that are
persistent. For example, remember that we said this database parameter change
was not persistent if we were using PFILES:
Alter system set db_recovery_file_dest_size=10g;
If we were using SPFILES the parameter would keep the
same value, even after a database restart. This means you only have to change
the parameter value in one place, and that you can forget having to change it in
the PFILE of the database.
One of the most important benefits of the SPFILE is that
Oracle has introduced many automatic tuning features into the core of the
database. Without an SPFILE, Oracle can not autotune your database.
An SPFILE uses the same formatting for its file name as
the PFILE, except the word spfile replaces init. For instance, if your
ORACLE_SID is testdb, the resulting spfile would be called spfiletestdb.ora.
Create spfile from pfile
is created using the CREATE SPFILE
statement; this requires connecting as
Connect system/manager as sysdba;
SPFILE FROM PFILE;
command creates an spfile in a
non-default location ($ORACLE_HOME/database).
However, you can fully-qualify the path
name is the “create spfile” statement:
Warning - After an spfile is created,
when you bounce the database you may
encounter an error. To get around this,
you have to reconnect as SYSDBA and use
the STARTUP command.
addition of the spfile has changed the
search path for the Oracle startup deck.
Oracle now uses the following path:
for the spfile$ORACLE_SID.ora file
in the default location,
for the spfile.ora; and
for the pfile by name
Administering the PFILE and SPFILE
As a DBA the main thing you need to worry about with the
SPFILE and PFILES are backing them up. You can use RMAN to backup an SPFILE, or
back them up yourself.
Remember that a PFILE is simply a text based file, which
means you can copy it to another directory without affecting the Oracle
instance. This is the easiest way to backup a PFILE.
To back up an SPFILE, you will first want to convert it
to a PFILE. You can do this with the following syntax.
SQL> create pfile from spfile;
This will create a PFILE named initSID.ora in your
$ORACLE_HOME/database (Windows) or $ORACLE_HOME/dbs (Linux/Unix) directory.
Note that the SID in initSID.ora will be replaced with
the SID of your database as defined during creation.
In addition, you can back up the file directly to the
preferred location with the command:
SQL> create pfile=/path/to/backup.ora from spfile;
If the time comes that you must put the SPFILE back into
place, you can do so with this command:
SQL> create spfile from pfile=/path/to/backup.ora
If your database is currently running using the SPFILE,
be sure to shut down first so Oracle can replace the file. As your SPFILE is in
use the entire time your database is running, you should never overwrite it
during normal operations
You can use the V$PARAMETER dynamic view to see the
current setting of the different database parameters. In this example, we use
the DESC SQL*Plus command to describe the V$PARAMETER view, and we then query
the V$PARAMETER view to see the value of the control_file parameter setting:
select name, value from v$parameter where name = 'control_files';
You may also use the shortcut “show parameter” command.
SQL> show parameter control_files;
The Parameter File at Startup Time
Oracle prefers the use of an SPFILE to a PFILE. When
you startup your Oracle database, Oracle will scan the contents of your
parameter directory ($ORACLE_HOME/database on Windows or the Linux directory
name $ORACLE_HOME/dbs), searching in the following order:
If the directory contains none of the above, then the
startup will fail.
Alternatively, you can tell Oracle where to find a PFILE
if you store it in a different location.
SQL> startup pfile=/path/to/pfile/inittestdb.ora
Furthermore, you can create a PFILE that contains
nothing but the following line:
By doing so, we are able to startup using a PFILE in any
location we want, but continue to use an SPFILE that can also be in a different
location. This can be very beneficial for those that wish to store their SPFILE
in a centralized location, such as a SAN. Now, let’s take a quick look at redo
This is an excerpt from the bestselling "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle
Certified Master). It’s only $19.95 when you buy it directly from the
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.