Don’t modify your spfile (unless you are sure)

I have seen it twice in the past week, so I need to get the word out.  You need to test parameter changes BEFORE modifying your spfile.  In at least one of the cases I have seen in the past week, the DBA created a backup of their spfile before modifying it.  This is smart, but they did not realize they could use this backup copy for testing changes.

The environment was an Oracle 11g release 2 ( three-node RAC system.  The DBA needed to change a non-modifiable (non-dynamic) parameter and restart the database to make the change permanent.  They created a copy of the spfile, which is always a GREAT idea before modifying it, brought down the three instances, but was unable to bring them back up due to a problem with the parameter setting.  Now the spfile is bad and the database won’t start.  Now they need to recreate the spfile with the backup and start the process over.

What they could have done is make the parameter changes to the spfile backup and try to start one more of the instances using it instead of the spfile. In that case, if the parameter setting is bad, the spfile is unaffected and corrections can be made more swiftly with a regular editor.

In order to start the instance with the backup spfile (pfile), this command could have been used while connected to one of the instances through SQL*Plus as SYSDBA:

startup pfile=<path_and_name_of_pfile>

Once the instance starts with the parameter change, you can shut it down, modify the spfile by either changing the parameter with an ‘alter system’ command or simply replacing the spfile with the contents of the modified pfile with the ‘create spfile from pfile’ command and you are done.