Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 30, 2003

So You Want to Use Oracle's SPFILE

By James Koopmann

Oracle's traditional method of storing parameters in the INIT.ORA file has given way to a more robust method-- the server parameter file (SPFILE). Learn what truly works in converting and maintaining your parameters without ever having to use an editor again.

What is an SPFILE

In a nutshell, the SPFILE (server parameter file) is Oracle's new method of maintaining database parameters. The old method of editing a text based parameter file (INIT.ORA) has given way to the new method of maintaining persistent parameters. By this Oracle means that you can change a system parameter and have its value be maintained across shutdown and startup. This is a great savings from the past where you had to issue the ALTER SYSTEM command and then remember to edit the INIT.ORA parameter file. I hope that you can see the benefits to this. If nothing else, you do not have to mess with learning an editor. While I have not yet gotten Oracle to admit that they are getting rid of the old text based INIT.ORA parameter file it would seem that they are pushing us this way. But let's get real about this, if Oracle is expected to become truly self-tuning, like all databases are trying to do, do you really think it is going to allow you to create a parameter file that it can't access and make changes to from within the database engine? I don't think so either.

Do I have one

Before you rush to the store to figure out if you need to create a SPFILE, let's first determine if the database has an SPFILE and is already using it. One of my favorite, semi-new commands is the SHOW PARAMETER command. So, just issue a show command for the SPFILE and see if there is anything in the VALUE returned. If not, you are lucky in that you now get to create one.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

How to get one

In order to create an SPFILE you must be SYSDBA or SYSOPER. The Oracle command to create an SPFILE is very simple. The command will read the INIT.ORA parameter file and produce the new SPFILE. If there is nothing unusual about your installation, just issue the following command. If you are on a UNIX platform, the SPFILE will be located in $ORACLE_HOME/dbs. If you are on a WINDOWS platform, it will be located in $ORACL_HOME/database.

SQL> create spfile from pfile;

File created.

CREATE SPFILE [='spfile_name'] FROM PFILE [='pfile_name'];

If you ever want to go back to the INIT.ORA type parameter file, there is the converse command to do this. The syntax follows again but the simple version is just the following.

SQL> create pfile from spfile;

File created.

CREATE PFILE [='pfile_name'] FROM SPFILE [='spfile_name'];

Occasionally save your SPFILE

While I personally hope you never need to recover your SPFILE, if you play long enough with it you will more than likely corrupt it or loose it. In order to recover properly, you will need to startup the database some how and since you do not have an SPFILE around you will have to startup the database with the old INIT.ORA file. You can save yourself a ton of grief by occasionally exporting your SPFILE back to an INIT.ORA parameter file.

This is done by the simple CREATE PFILE FROM SPFILE command. Be aware that Oracle's documentation says that this command will list all the parameters into the INIT.ORA file you just created. It does not dump all the parameters and their values; it only lists the ones in the current SPFILE. Remember also that if you want to startup the database with this INIT.ORA file, you must either remove the SPFILE or use the PFILE option on the STARTUP command.

How to use it

Of note and caution, the SPFILE you just created is a binary file; attempts to edit it will more than likely corrupt it. I have tried and found myself in a recovery scenario. Fortunately, this was on my test box, as should yours be when you test the waters with new ideas.

Now that you have created the SPFILE, you must, unfortunately shutdown and startup the database in order for the database engine to recognize it. (Wouldn't it be nice if there were a command such as 'ALTER SYSTEM RECOGNIZE...'?) There is no reason to get rid of the old INIT.ORA parameter file. When Oracle starts up, it first looks for a SPFILE.

A word of caution, don't get scared as I did at this point. When you SHUTDOWN the database, you will have to re-connect. When you do, you may get the following.

SQL> connect sys/<password> as sysdba
ORA-12547: TNS:lost contact

At this point, you will have to reboot the system. I don't know why, but just do it and everything will be cleared up.

You can verify that you do in fact have an SPFILE that is recognized by issuing the SHOW PARAMETER command.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      ?/dbs/spfile@.ora

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM