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 an Architect (an "IC6") in the Fusion Middleware Central Development Team at Oracle. Mark's job is to make Fusion Middleware easy to use in the cloud and at home, for developers and operations folks, with special focus on continuous delivery, configuration management and provisioning - making it simple to manage the configuration of complex environments and applications built with Oracle Database, Fusion Middleware and Fusion Applications, on-premise and in the cloud. Before joining this team, Mark 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 )

Google+ photo

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

Connecting to %s