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
Next
Back to DBAsupport.com
