Hmmm. Your Oracle Database Won’t start due to invalid parameter in spfile.. hmm..
This is a quote from appsdba.com ‘When using an SPFILE it is possible to get into the situation where the database won’t start due to an invalid parameter, and since the database won’t start it is not possible to fix the SPFILE.‘
I got into this situation this morning. Its very very frustrating but relatively straightforward to fix. Here goes.
Step 0: Check for a pfile equivalent of your spfile! If it exists then start the database using startup pfile=/path/to/pfile and issue a create spfile from pfile when the database starts and then restart your database. Bingo. Amend your spfile parameters at your leisure. If you haven’t got a pfile then read on..
Step 1: find your spfile. If you don’t know where this is you’re goose is already cooked. It usually lives in $ORACLE_HOME/dbs and is called spfile<ORACLE_SID>.ora though yours may have a different name. In my case the ORACLE_SID is TEST so my spfile is called spfileTEST.ora.
Step 2: Back up your spfile (and pfiles too).
Step 3: Open a command prompt, navigate to the directory containing your spfile and type strings spfile_name > $ORACLE_HOME/dbs/temp_pfile.ora (this creates a pfile not an spfile!!). If the spfile file is in $ORACLE_HOME/dbs then rename the spfile to something you’ll remember… IF the spfile is stored within ASM then login as the ASM owner, start asmcmd and use the cp command to copy the file to a directory your database/instance owner can read and run strings /path/to/spfile/spfile_name > $ORACLE_HOME/dbs/temp_pfile.ora.
Step 4: open temp_pfile.ora with a text editor and check its parameters. Tidy up incomplete entries and amend offending entries where appropriate. (I’d accidentally reduced one of the memory parameters from 24G to 2G preventing the instance from starting up).
Step 5: try starting up the database.. startup pfile=/path/to/file/temp_pfile.ora should do the trick. If the instance starts shut it down immediately (shutdown immediate should do the trick). If the instance doesn’t start then check your parameters and try again!
Step 6: rename temp_pfile.ora to init<ORACLE_SID>.ora. Mine is called initTEST.ora.
Step 7: startup the database again. once its started type create spfile from pfile and press enter.
Step 8: Restart the database. Your database should now start up but this time its using the newly created spfile.
Step 9: Take applause from your colleagues and count your lucky stars.
This document was typed rather hastily on a train on my way home. If you spot a mistake please let me know.