Oracle - Pfile and Spfile

The default location for a spfile or pfile is $ORACLE_HOME/dbs however if you are unsure as to where your spfile is located you can issue the following from SQLPLUS:

SQL> SHOW PARAMETER spfile;
NAME TYPE VALUE
-------- ------- ---------
spfile string /app/oracle/product/10.2.0.4server/db_1/dbs/spfileictst3f.ora

Create pfile from spfile

If you wish to backup your spfile, you can create a pfile from the spfile which will save the current parameter configuration.

SQL> create pfile='<pfile location>' from spfile;

or

SQL> CREATE PFILE='<pfile location>' FROM SPFILE = '<spfile location>';

Create spfile from pfile

If you want to then revert back to a saved pfile, you can overwrite your current spfile with the following:

SQL> CREATE SPFILE FROM PFILE = '<pfile location>';

or

SQL> CREATE SPFILE='<spfile location>' FROM PFILE='<pfile location>';

Check spfile parameters

To check all the spfile parameters you can issue the following:

SQL> SHOW PARAMETER;

Check pfile parameters

You can either check the pfile parameters, after starting a database up with a pfile, using the above method. Or you can go to the pfile location and either cat or view the file. To change the pfile parameters you can directly edit the pfile using vi from the command line.

Change spfile parameters

Some parameters you can save to memory, for use just within the current session; some you can save within the spfile; and some must be set at both. Parameters saved within the spfile can only be initiated following a database restart.

To save a new parameter within the spfile you issue the following within SQLPLUS:

SQL> ALTER SYSTEM SET <parameter name>='<value>' SCOPE=[SPFILE/MEMORY/BOTH];

Startup database with pfile or spfile

If you want to startup a database using either a different spfile or using a pfile you first need to shut it down. Then you should issue the following:

SQL> CONNECT sys/password AS SYSDBA
SQL> startup pfile='<pfile location>';

or

SQL> CONNECT sys/password AS SYSDBA
SQL> startup spfile='<spfile location>';