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