So You Want to Use Oracle's SPFILE

Friday May 30th 2003 by James Koopmann
Share:

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.

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
ERROR:
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

How to change a parameter setting

The process to change a parameter in the SPFILE is much the same as you have done in the past though the ALTER SYSTEM command structure. There are a couple of options that add some functionality to make a parameter change either current, for future use or for resetting a parameter. Here is the structure of the command for setting a parameter.

Setting a Parameter

ALTERE SYSTEM SET parameter_name=parameter_value
                [COMMENT='text'] 
                [DEFFERRED] 
                [SCOPE={MEMORY,SPFILE,BOTH}] 
                [SID={'sid','*'}]

ALTER SYSTEM OPTIONS

COMMENT   Put a comment on the parameter change and store it in the SPFILE
DEFERRED   Changes the value for the parameter for sessions connecting after the statement is issued
SCOPE   Specifies when the change will take effect
  MEMORY The change takes effect immediately but will not be available after the next startup
  SPFILE The change is made in the server parameter file (SPFILE) only and will be set on the next startup
  BOTH MEMORY + SPFILE and will be available after next startup, this is the default
SID   Specify the SID of the instance where you want this value to apply
  '* ' Apply to ALL instances, this is the default

If you are not using an SPFILE, and try to issue the ALTER SYSTEM command, you will get the following error.

SQL> alter system set timed_statistics=true scope=spfile;
alter system set timed_statistics=true scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

Resetting a Parameter to its' default value

ALTERE SYSTEM RESET
ALTERE SYSTEM RESET parameter_name
                [SCOPE={MEMORY,SPFILE,BOTH}] 
                [SID={'sid','*'}]

ALTER SYSTEM RESET OPTIONS

SCOPE   Specifies when the change will take effect
  MEMORY Invalid Option even though documented by Oracle
  SPFILE The change is made in the server parameter file (SPFILE) only and will be set on the next startup
  BOTH Invalid Option even though documented by Oracle
SID   Specify the SID of the instance where you want this value to apply
  '* ' Apply to ALL instances, there is no default

What Oracle documentation says

Be forewarned, Oracle documentation says that in order to delete or restore a parameter to its default value you should use the following command for string values.

ALTER SYSTEM SET parameter='';

In addition, for numeric and Boolean values you are to set the parameter specifically to its original default value. This may work, since you are setting the value in the SPFILE, but it will still make an entry in the SPFILE. This does nothing for you. What you really want is to remove the entry from the SPFILE. Follow along with the following example and you will learn of the ONLY method that I have found that works.

Examples of resetting and setting a parameter

The example I always like to use involves putting the parameter TIMED_STATISTICS under the control of Oracle's new STATISTIC_LEVEL parameter. This is a good example because most of us will have this value set in our parameter file and in order to be completely under the control of the STATISTIC_LEVEL parameter you must remove it from the parameter file. In addition, since the instance only remembers the setting of this parameter through the parameter file (until next startup) you need to set it properly for future sessions that will connect. Here is the sequence of events to do this.

  1. Remove the entry from the SPFILE
       alter system reset timed_statistics scope=spfile sid='*';
  1. Set the parameter for future session connects
       alter system set timed_statistics=true scope=memory;

Setting a parameter that is a list of strings is not that difficult. Here is an example of setting the CONTROL_FILES parameter.

alter system set control_files='/u01/app/oracle/oradata/saigon/control01.ctl',
                               '/u01/app/oracle/oradata/saigon/control02.ctl',
                               '/u01/app/oracle/oradata/saigon/control03.ctl' scope=spfile

Conclusion

Once I figured the ins' and outs' of Oracle's new SPFILE, I truly felt that I was able to take control of my database with much more certainty. I was now able to explore the vastly improved dynamic tuning capabilities that Oracle has to offer. I would encourage all DBAs to experiment and eventually switch over to the new SPFILE. It makes a world of difference in your ability to manage parameter values.

» See All Articles by Columnist James Koopmann

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved