Reprinted with Permission by Quest Software May 2002

The SPFILE
Venu Gopal, ORAMASTERS Inc.

Purpose

The purpose of this article is to help you become familiar with one of Oracle9i's new features, the server parameter file - aka SPFILE.

Audience

The primary audience of this article is DBAs working on previous versions of Oracle prior to Ver.9i and interested in using this new feature. Some test cases have also been explored throughout the article to familiarize you with this feature.

So what's it all about

For centuries, Oracle has been maintaining a text file called the pfile to store the database initialization parameters, which would be read at the time of instance startup to get the characteristics of the instance that has to be created by Oracle for the specified database. Any changes made to this pfile would only take effect when you restart the database; however, parameters that were dynamically alterable could be changed using the appropriate ALTER SYSTEM or ALTER SESSION statement, which would take effect immediately. Nevertheless, it should be mentioned that these parameters would need to be set every time you restarted the instance, because parameters altered in this process would be lasting for the lifetime of the instance or the session depending on the type of the statement issued. To overcome his limitation Oracle has come up with a new feature called the spfile (server parameter file). The spfile can be thought of as a control file that is used by Oracle to store the initialization parameters. Oracle writes to and reads from this binary file. The spfile is a server-side initialization parameter file; parameters stored in this file are persistent across database startups. This makes all the changes made to the instance using the ALTER SYSTEM statement persistent. So, goodbye to manual edits to the pfile. However, Oracle requires that you start an instance for the first time using the pfile and then create the spfile, which is discussed later in the article. Oracle strongly recommends that you create the spfile as a dynamic means of storing the initialization parameters.

Creating the spfile

By default a database would be working on a pfile, so the spfile must be created from the pfile at the SQL prompt. However, the database starts using the spfile only when you later restart the database using the spfile created. The spfile is created using the CREATE SPFILE statement; this requires the SYSDBA or SYSOPER privileges.

SQL> CREATE SPFILE FROM PFILE;

This is the simplest form of the CREATE SPFILE statement. It creates a spfile in the default directory (O/S dependant, usually $ORACLE_HOME/dbs/ on unix platforms) from the pfile located in the default directory. The file is named spfile$ORACLE_SID.ora. If a spfile already exists in the destination directory, it is over written, however if it's in use by the instance, then it returns the following error:

SQL> CREATE SPFILE FROM PFILE;
CREATE SPFILE FROM PFILE
*
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance

The following CREATE SPFILE creates a spfile in a non-default location from a pfile located in a non-default location; if the path in the SPFILE or PFILE clause is not specified, Oracle uses the default directory as the path.

SQL> CREATE SPFILE='d:\spfile_mydb.ora' FROM
  2  PFILE='d:\oemrep\admin\initOEMREP.ora';

Bug: After an spfile is created when you shutdown the database to startup again, you'll encounter an error, which makes you log in again and then use the STARTUP command. Look at the test scenario below:

SQL> CREATE SPFILE='d:\spfile_mydb.ora' FROM  
  2  PFILE='d:\oemrep\admin\initOEMREP.ora';

File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-03113: end-of-file on communication channel

Version: This bug exists in

Oracle9i Enterprise Edition Release 9.0.1.1.1 -
Production version.

Workaround: All you have to do is reconnect as SYSDBA and use the STARTUP command, until a patch is available.

Making use of the spfile

Now that the spfile is ready, we'll have to make the database use it. It can be done in various ways as discussed below. The default behavior of the STARTUP command has changed in Oracle9i; the command now searches for the following files in this order:

Search for the spfile$ORACLE_SID.ora file in the default location,
If it does not exist, it looks for the spfile.ora
If this file is not found either, then it searches for the pfile by name init$ORACLE_SID.ora.

Starting the database with pfile/spfile

As discussed earlier, by default Oracle starts looking for the spfile in the default location. If you want to override this and start your database with a pfile you have two options for this - either you delete the spfile and startup your database (pfile should be located in the default path), or you use the PFILE option of the STARTUP command as follows:

SQL> startup pfile='d:\spfile_mydb.ora';

You cannot start the database by specifying spfile in place of pfile in the previous command - Oracle does not allow this. Optionally, you can create a pfile containing SPFILE parameter in it that will be pointing to the location of the spfile as explained below.

A new initialization parameter called the SPFILE was introduced in Oracle9i. This is similar to the IFILE parameter that is used as a pointer to a non-default pfile. The SPFILE parameter is used to specify the path of spfile located in a non-default location. This is useful when your spfile is not located in the default location. This can also be used in conjunction with other parameters in the pfile, i.e. you may specify the location of the spfile and add some more parameters to the pfile. However, the pfile is read sequentially; if you duplicate a parameter in the spfile and the pfile, then the parameter value that is read last will take precedence over others. Look at the following scenario:

You set timed_statistics=true in the pfile and timed_statistics=false in the spfile

#Contents of the pfile
SPFILE='d:\oemrep\admin\initOEMREP.ora'
TIMED_STATISTICS=TRUE

Now if you specify the SPFILE parameter at the beginning of the pfile, then TIMED_STATISTICS=FALSE is read first from the spfile and later it goes on reading the TIMED_STATISTICS=TRUE specified in the pfile which takes precedence.

Modifying the Contents of spfile

Modifying the contents of spfile can be done in two ways - either by using the ALTER SYSTEM statement or by exporting and importing back the spfile (which is discussed in later sections). The parameters you alter using the ALTER SYSTEM statement get permanently stored in the spfile unlike the pfile, which has to be manually edited to add the parameters.

The ALTER SYSTEM statement has a new option called the SCOPE, which can have three values, which are explained below.

Specifying SCOPE=MEMORY

Optionally, you can specify whether the change of the parameter has to be made only for the life of this instance without making it permanent in the spfile. This option is the default if you are not using an SPFILE.

SQL> SHOW PARAMETER timed_statistics
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
timed_statistics                     boolean     FALSE
 
SQL> ALTER SYSTEM SET timed_statistics=TRUE SCOPE=MEMORY;
 
System altered.
 
SQL> SHOW PARAMETER timed_statistics
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
timed_statistics                     boolean     TRUE
 
SQL> startup FORCE
ORACLE instance started.
 
Total System Global Area   63729356 bytes
Fixed Size                   282316 bytes
Variable Size              46137344 bytes
Database Buffers           16777216 bytes
Redo Buffers                 532480 bytes
Database mounted.
Database opened.
 
SQL> SHOW PARAMETER timed_statistics
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
timed_statistics                     boolean     FALSE

Specifying SCOPE=SPFILE

If you want to make a change to a parameter in the spfile without affecting the current instance, you can do so using the SCOPE=SPFILE option of the ALTER SYSTEM statement. This is useful when you want to make a change starting from the next startup and not for the current instance. Look at the following example:

SQL> SHOW PARAMETER timed_statistics
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
timed_statistics                     boolean     FALSE
 
SQL> ALTER SYSTEM SET timed_statistics=TRUE SCOPE=SPFILE;
 
System altered.
 
SQL> SHOW PARAMETER timed_statistics
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
timed_statistics                     boolean     FALSE
 
SQL> STARTUP FORCE
ORACLE instance started.
 
Total System Global Area   63729356 bytes
Fixed Size                   282316 bytes
Variable Size              46137344 bytes
Database Buffers           16777216 bytes
Redo Buffers                 532480 bytes
Database mounted.
Database opened.
 
SQL> SHOW PARAMETER timed_statistics
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
timed_statistics                     boolean     TRUE

Specifying SCOPE=BOTH

By using the SCOPE=BOTH option with the ALTER SYSTEM statement, the parameter takes affect both in the current instance and the spfile simultaneously. This is equivalent to not specifying the SCOPE option with the ALTER SYSTEM statement. But, if it is a static parameter you are trying to alter, then you will have to execute it with the SCOPE=SPFILE option which will come into effect when you restart the database. Look at the following example:

SQL> SHOW PARAMETER audit_trail
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
audit_trail                          string      NONE
 
SQL> ALTER SYSTEM SET audit_trail=DB SCOPE=BOTH;
ALTER SYSTEM SET audit_trail=DB SCOPE=BOTH
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
 
SQL> ALTER SYSTEM SET audit_trail=DB SCOPE=SPFILE;
 
System altered.

The V$SPPARAMETER view

The exact contents of the SPFILE can be obtained from the V$SPPARAMETER dynamic performance view. Let's look at it to get a feel of it:

SQL> ALTER SYSTEM SET timed_statistics=FALSE SCOPE=SPFILE;

System altered.

SQL> SELECT name, value FROM v$parameter
  2  WHERE name='timed_statistics';

NAME                 VALUE
-------------------- ---------------------
timed_statistics     TRUE

SQL> SELECT name, value FROM v$spparameter
  2  WHERE name='timed_statistics';

NAME                 VALUE
-------------------- ---------------------
timed_statistics     FALSE

Is my database using spfile?

Just in case you want to know whether you are using the spfile or the pfile, follow the procedure below. Execute any of the queries given below: This query returns NULL in the value column if you are using the pfile.

SQL> SELECT name, value FROM v$parameter
  2  WHERE name = 'spfile';
 
NAME       VALUE
---------- --------------------------------------------------
spfile     %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA

Alternatively, use the following query, which returns NULL in the value column if you are using pfile and not spfile:

SQL> SHOW PARAMETER spfile
 
NAME    TYPE    VALUE
------- ------- ------------------------------
spfile  string  %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA

On the other hand, you may check for the count of the following query; if the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:

SQL> SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;

Exporting the spfile

Exporting the spfile to a text file is possible using the CREATE PFILE command. This might be useful when you want 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.

SQL> CREATE PFILE='d:\init.ora' FROM SPFILE;
 
File created.
 
SQL> SHUTDOWN;
Database closed.
Database dismounted.
ORACLE instance shut down.

The pfile is created at the path specified in the PFILE option. Now you edit the pfile as you wish and start the database using this file.

SQL> STARTUP PFILE='d:\init.ora'
ORACLE instance started.
 
Total System Global Area   63729356 bytes
Fixed Size                   282316 bytes
Variable Size              46137344 bytes
Database Buffers           16777216 bytes
Redo Buffers                 532480 bytes
Database mounted.
Database opened.

Create the new spfile from the pfile (it is created in the default location).

SQL> CREATE SPFILE FROM PFILE='d:\init.ora';
 
File created.

Restart the database to start using the newly created spfile.

SQL> STARTUP FORCE
ORACLE instance started.
 
Total System Global Area   63729356 bytes
Fixed Size                   282316 bytes
Variable Size              46137344 bytes
Database Buffers           16777216 bytes
Redo Buffers                 532480 bytes
Database mounted.
Database opened.

Tip: Adding comments to the spfile

When you create an spfile from a pfile, Comments, which are specified in the pfile, are ignored and not stored in the new spfile. Export an spfile to a pfile and open it, you'll see the result. However, there is a workaround here comments added on the same line of the parameter are not omitted. Look at the following example:

    #This is set to FALSE
    timed_statistics=FALSE

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

    timed_statistics=FALSE #This is set to FALSE

On the other hand, this comment would be added to the spfile and will be exported into pfile at the time of spfile export. This should explain well how to add consistent comments to your spfile.

Migrating to Oracle9i SPFILE

Migrating to spfile from a pfile consists of two steps:

Create the spfile from the existing pfile using:

SQL> CREATE SPFILE FROM PFILE='d:\init.ora';
File created.
Restart the database to start using the newly created spfile.

SQL> STARTUP
ORACLE instance started.

Total System Global Area   63729356 bytes
Fixed Size                   282316 bytes
Variable Size              46137344 bytes
Database Buffers           16777216 bytes
Redo Buffers                 532480 bytes
Database mounted.
Database opened.

Starting a remote database using a spfile

The spfile, being a server-side parameter file, cannot be placed on a client machine and make use of it. It has to be placed on the system where the RDBMS lies. However, if you intend to start a database that is using spfile remotely, then you have to place the pfile on the client's machine that has the parameter SPFILE in it, pointing to the spfile on the server. Once Oracle reads the pfile from the client-side, it searches for the spfile in the path specified in the SPFILE parameter on the server. I'm still working on this feature and will be posting updates soon.

Using the spfile on RAC

RAC (Real Application Server) in Oracle9i is the upgraded term of OPS (Oracle Parallel Server) in Oracle8i. For those who do not have an idea about parallel servers: Normally, one database has one instance associated to it. In parallel servers, one database is associated with more than one instance (i.e. instead of your 100 users connecting to one instance; 50 users will be connected to each instance in RAC setup [Load balancing]). Each instance will have a unique instance name. Remember that even though there are n instances, there is only ONE and only ONE spfile.

While changing initialization parameters, values can be set to a specific instance or to all the instances. Lets assume that we are running an RAC with two instances PROD and SALES to change the TIMED_STATISTICS parameter to true for SALES instance you do the following:

SQL> ALTER SYSTEM SET timed_statistics=TRUE SID='sales';

System altered.

To set the seething to all the instances

SQL> ALTER SYSTEM SET timed_statistics=FALSE SID='*';

System altered.

Oracle uses the following syntax to store parameter values belonging to different instances:

sales.TIMED_STATISTICS=TRUE
*.TIMED_STATISTICS=FALSE

The two values respectively correspond to the values we have set with the previous set of commands. You may optionally use the SCOPE clause to specify it to a particular instance only without storing it in the spfile.

Conclusion

I know this article might be a bit long; being my first article, I didn't know where to cut it off. I'd appreciate your feedback and comments at andemvenugopal@rediffmail.com. I will also be posting updates to the article as I receive your comments and feedback, so be sure to check back frequently.

Happy DBA'ing

Venu G.
For ORAMASTERS Inc.

The SPFILE Venu Gopal, ORAMASTERS Inc.