debugModerate
Unable to start Oracle (11g1) due to bad memory configuration (ORA-00837)
Viewed 0 times
dueunablebadconfiguration00837oramemorystart11g1oracle
Problem
I decided to reduce the memory allocation of a local developer instance, and was told that the following command did the job;
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;
When connecting as SYSDBA I get the following;
("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?
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 existWhen 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?
Solution
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.
Modify your memory-related parameters in
You can then save your settings back to an spfile with the following command:
And start your database as usual:
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;Code Snippets
sql> create pfile='myinit.ora' from spfile='spfileORCL.ora';sql> startup pfile='myinit.ora';sql> create spfile='spfileORCL.ora' from pfile='myinit.ora';sql> startup;Context
StackExchange Database Administrators Q#37310, answer score: 10
Revisions (0)
No revisions yet.