Working with the Oracle SPFILE

Let’s take a look at one of my early articles in the IOUG Tip Booklet. It is still relevant today…

In Oracle 9i, the spfile was introduced as a new method to store the initialization parameters. The use of the spfile helped to simplify the remote startup of a database and added options for the busy DBA. In this article, we will look at the spfile and how it is managed on UNIX/Linux and Windows.

What is a spfile

The server parameter file (spfile) is a binary version of the parameter file (pfile) used to set database parameters. This file, like the pfile, is read at instance startup but unlike the pfile, it can be updated through alter system commands or OEM. This gives you the ability to make sure that parameter changes made to the database now are persistent through the next database restart or will only be set at the next database restart.

 

The spfile is a binary file and should not be edited with a text editor. Oracle only supports changes to the spfile through the database to protect the file’s integrity. If you look at the file using cat (UNIX/Linux), type (Windows), or even in an editing tool, you will see the ASCII text but you won’t see the binary header and footer. Using a text editor can remove or damage the file header and/or footer and corrupt the spfile.

 

Location and order of files for startup

The spfile is stored in the same location as the pfile.

Unix/Linux: $ORACLE_HOME/dbs

Windows: %ORACLE_HOME%\database

 

The spfile filenames are also similar to the pfile. The spfile will be named spfileSID.ora while the pfile is named initSID.ora. You can also create a generic initialization called spfile.ora that can be read by multiple instances if you wanted to uniformly set parameters for several non-RAC instances on a machine.

 

When the database starts, it will look in the default location under the Oracle home (above) for the initialization files in the following order:

 

1. spfileSID.ora
2. spfile.ora
3. initSID.ora

 

If you don’t want your spfile under the Oracle home, you can change the location of the spfile similar to the way you change the location of the pfile. While the database is down, you can move the spfile to another directory (e.g. $ORA_ADMIN/$ORACLE_SID/pfile). Then you create an initSID.ora in the default location and include the one line according to your platform:

 

Unix/Linux: spfile=$ORA_ADMIN/$ORACLE_SID/pfile/spfileSID.ora

Windows: spfile=%ORA_ADMIN%/%ORACLE_SID%/pfile/spfileSID.ora

 

When the database is restarted, it will not find the spfileSID.ora or the spfile.ora in the default directory and will read the initSID.ora and will be pointed to the correct spfile.

 

Am I using a spfile?

How do I tell if I am using a spfile? From within SQL*Plus, you can query v$parameter looking for the name of “spfile” or you can just type “show parameter spfile”, Examples of both are shown in Figure 1.

 

Figure 1

How do I check the values in a spfile?

Since you can alter initialization parameters while the database is running, the values for a parameter in memory can be different than what is in the spfile. To check the value in the spfile, you query the dynamic view v$spparameter. This view serves a similar function to the v$parameter view, but there are differences in the columns.

 

In Figure 2, you can see that the current value of undo_retention is 900 and we have changed the value to 1200 in the spfile.

 

Figure 2

 

In Figure 3, you can see from a query of v$parameter that the value is still 900. But if you look at the query from v$spparameter, the value is 1200. This will be the new value the next time that the database is started.

 

Figure 3

Recovering from parameter issues

We have all done it. You set a database parameter and go to start the database but the database won’t start because of problems with a parameter. When you are dealing with a pfile, it’s an easy fix. Since the spfile is a binary file and we should not edit it, how do we get around this issue?

 

In some cases, you can start the instances in nomount mode and change the parameter with an “alter system” setting the scope to the spfile. You can then stop and restart the instance. There are times that you cannot even start the instance in nomount mode, and that is when you will have to recreate the spfile.

 

Recreate the spfile

For this example, we will change the parameter db_recovery_file_dest to a non-existing directory so that the instance won’t start. Figure 4 shows the current value for the parameter and that we have changed it in the spfile.

Figure 4

 

In Figure 5, you can see that we verify the value, shutdown and attempt to restart the database.

 

Figure 5

Now we will have to recreate the spfile. If you are on a UNIX/Linux system, you can use the strings command on the spfile and redirect the output to a new text file.

 

$ strings $ORA_ADMIN/$ORACLE_SID/pfile/spfileSID.ora > $ORA_ADMIN/$ORACLE_SID/pfile/new_pfileSID.ora

 

If you are on Windows, then you do not have access to strings or an equivalent. To get the text version, you will have to use a text editor like WordPad to read the spfile, strip out the header and footer and save it to a new text file. Figure 6 shows the binary spfile in WordPad with the header and footer included. You can save this file as %ORA_ADMIN%/%ORACLE_SID%/pfile/new_pfileSID.ora.

 

Figure 6

Now you can start the database in mount mode, specifying the new text parameter file that you just created, recreate the spfile from the pfile and shut down the instance (Figure 7).

 

Figure 7

Finally, you can restart the database and as we see in Figure 8, verify that we are using the recreated spfile.

 

Figure 8

In Conclusion

As we have demonstrated, working with the spfile is not difficult. When you first look at it, it may be difficult to understand the real benefit of the spfile. But when you look at some of new capabilities, including automated RMAN backups, you can begin to see how it can help the busy DBA.