Andrew Fraser DBA

Change many oracle database parameters using a temporary pfile/init.ora from spfile

Spfiles are better than pfiles (init.ora’s) for most purposes, since they allow parameters to be changed with “alter system set parameter” SQL commands.

However if you want to change a whole load of parameters in one go, it is easier to temporarily switch back to the old pfile/init.ora method to allow you to edit all the non default parameters in a single text file.

First create and edit the temporary pfile/init.ora:

create pfile from spfile ;
host vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora

Then temporarily switch the database over to using that new pfile (and note that it is best to take a backup copy of the spfile before removing it):

host cp -p $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora.`date +%d%m%Y`
host rm $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
shutdown immediate
startup

Then switch back to using an spfile, but now an spfile that contains exactly what is in your temporary pfile/init.ora:

create spfile from pfile ;
host rm $ORACLE_HOME/dbs/init$ORACLE_SID.ora
shutdown immediate
startup

The above is for single instance databases – RAC databases would require the same done on each node individually, with srvctl used instead of SQL for shutdown/startup. Also for RAC (and, more unusually, on single instance also) the spfile will be in a different directory, not in $ORACLE_HOME/dbs. Use this SQL command to see the fully qualified name of the spfile:

sho parameter spfile