Oracle 的参数文件pfile与spfile

In Oracle Databases through 8i, parameters controling memory, processor usage, control file locations and other key parameters are kept in a pfile (short for parameter file).

The pfile is a static, plain text files which can be altered using a text editor, but it is only read at database startup. Any changes to the pfile will not be read until the database is restarted and any changes to a running database will not be written to the pfile.

Due to these limitations, in 9i Oracle introduced the spfile (server parameter file). The spfile cannot be edited by the DBA; instead it is updated by using ALTER SYSTEM commands from within Oracle. This allows parameter changes to be persistent across database restarts, but can leave you in a pinch if you need to change a parameter to get a database started but you need the database running to change the parameter.

A 9i (or later) database can have either a pfile or an spfile, or even both, but how can you tell which you have? If you have both, which one is being used? How do you go from one to the other? How do you get out of the chicken-and-the-egg quandary of a database that will not start up without you changing a parameter that’s in that file you can’t update unless the database is up?

Note: This information is based on an Oracle 9i installation on Solaris. Your mileage may vary. I have also chosen to ignore issues of RAC installation. In my example I have used ORADB as my SID.

Am I using a pfile or an spfile?

The first thing to check is if you have a pfile or spfile. They can be specified at startup or found in the default location. The default path for the pfile is $ORACLE_HOME/dbs/init$ORACLE_SID.ora and the default for the spfile is $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora.

If both a pfile and an spfile exist in their default location and the database is started without a pfile='/path/to/init.ora' then the spfile will be used.

Assuming your database is running you can also check the spfile parameter. Either the command SHOW PARAMETER spfile or SELECT value FROM v$parameter WHERE name='spfile'; will return the path to the spfile if you are using one. If the value of spfile is blank you are not using an spfile.

The path to the spfile will often be represented in the database by ?/dbs/spfile@.ora. This may seem cryptic, but Oracle translates ? to $ORACLE_HOME and @ to $ORACLE_SID so this string translates to the default location of the spfile for this database.

How can I create an spfile from a pfile?

As long as your pfile is in the default locations and you want your spfile in the default location, you can easily create an spfile with the command CREATE SPFILE FROM PFILE;.

If you need to be more specific about the locations you can add paths to the create command like this:

CREATE SPFILE='/u01/app/oracle/product/9.2/dbs/spfileORADB.ora'
FROM PFILE=’/u01/app/oracle/product/9.2/dbs/initORADB.ora’;

These commands should work even when the database is not running! This is important when you want to change a database to use an spfile before you start it.

How can I create a pfile from an spfile?

The commands for creating a pfile are almost identical to those for creating a spfile except you reverse the order of spfile and pfile:

If your pfile is in the default location and you want your spfile created there as well run CREATE SPFILE FROM PFILE;.

If you have, or want them in custom locations specify the paths like this:

CREATE PFILE='/u01/app/oracle/product/9.2/dbs/initORADB.ora'
FROM SPFILE=’/u01/app/oracle/product/9.2/dbs/spfileORADB.ora’;

Again, this can be done without the database running. This is useful when the database fails to start due to a parameter set in the spfile. This is also a good step to integrate into your backup procedures.

How can I see what’s in my spfile

To view the settings in the spfile we have two options: First, we can use the command above to create a pfile from the spfile. This is simple, and fairly fast, but unnecessary if the database is running.

The better way, if the database is running, is to select the parameter you want to view from the oracle view v$spparameter with a command like this:

SELECT value FROM v$spparameter WHERE name='processes';

If you try to view the spfile with a text editor it may seem like it is plain text, but beware! The spfile will not behave correctly (if it works at all) if it has been edited by a text editor.

How can I update values in my spfile?

The values in spfile are updated with the ALTER SYSTEM command, but to update the spfile we add an additional parameter of SCOPE.

ALTER SYSTEM SET processes=50 SCOPE=spfile;

This command would update the parameter processes in the spfile. Since this parameter can only be set at startup, we say SCOPE=spfile and the change will be reflected when the database is restarted. Other options for SCOPE are memory which only changes the parameter until the database is restarted, and both which changes the instance immediately and will remain in effect after the database is restarted.

How can I update values in my spfile when my database won’t start?

So your database won’t startup because of a problem in your spfile. You can’t edit it with a text editor and you can’t use ALTER SYSTEM because your database is not running. It sounds like a problem, but really isn’t. Here’s what you do:

Connect up to your database as sysdba. You should get the message Connected to an idle instance

Run the command CREATE pfile FROM spfile; specifying the location as above if necessary. You should now have a fresh version of the spfile.

Edit the pfile to update the parameter you need to update.

Run the command CREATE spfile FROM pfile; to move the changes you have just made back into the spfile.

Startup the database normally. It should read the changed spfile and start up correctly. You can optionally delete the pfile if you are done.