Recovering an Oracle database after doing something bad to your SPFILE

Well it has been over a year since the last time I broke my database, but I did it again today!  This time I increased the sga size without also increasing some other parameter, and the database would not start, and since it would not start, I could not change the parameter.  What to do?

After a bit of hacking the SPFILE to no avail, I worked out the following process:

First, log on to Oracle as an administrator:

sqlplus / as sysdba

Next, create a nice editable plain text PFILE from the “binary” SPFILE:

create pfile from spfile;
exit

Now you can edit PFILE to change whatever parameters you need to.  Once you are done, you can start up the database from the PFILE:

sqlplus /  as sysdba
startup pfile=initSID.ora

Problem solved!

About Mark Nelson

Mark Nelson is a Developer Evangelist at Oracle, focusing on microservices and messaging. Before this role, Mark was an Architect in the Enterprise Cloud-Native Java Team, the Verrazzano Enterprise Container Platform project, worked on Wercker, WebLogic and was a senior member of the A-Team since 2010, and worked in Sales Consulting at Oracle since 2006 and various roles at IBM since 1994.
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s