Unable to start Oracle (11g1) due to bad memory configuration (ORA-00837)

memoryoracleoracle-11g

I decided to reduce the memory allocation of a local developer instance, and was told that the following command did the job;

alter system set memory_max_target = 512M scope = spfile;

Which I guess it did, since I no longer can start the Oracle instance in question. This is the error I get when trying to connect as a non-DBA-user;

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

When connecting as SYSDBA I get the following;

Connected to an inactive instance.
SQL>startup nomount;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL>alter system set memory_max_target = 2G scope = spfile;
ORA-01034: ORACLE not available

("Connected to an inactive instance." is my translation of a localized error message, might not be exactly correct)
So, I'm in a catch 22 situation, I can't change the memory parameters without starting up, and I can't start up.

Is there a way to change these parameters offline?

Best Answer

If you start your instance using a server parameter file (a binary version of initialization parameter file, spfile), you can extract the initialization parameters to plain-text initialization parameter file (pfile), alter them, and then start your instance with modified memory parameters.

sql> create pfile='myinit.ora' from spfile='spfileORCL.ora';

Modify your memory-related parameters in myinit.ora and start your instance using this new configuration:

sql> startup pfile='myinit.ora';

You can then save your settings back to an spfile with the following command:

sql> create spfile='spfileORCL.ora' from pfile='myinit.ora';

And start your database as usual:

sql> startup;
Related Question