Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 5 down vote accepted

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;
share|improve this answer
    
This worked perfectly. The only change I needed to do on my side was to replace ORCL with the SID of the instance in question. I set the memory_target-parameter to the same value as the memory_max_target-parameter, which might not be optimal, but it made it possible to start. –  MaxH Mar 22 '13 at 8:04
    
Yep, the names of the spfiles Oracle tries to start with are spfileSID.ora and spfile.ora, then it tries to read from pfile init.ora if no spfiles found. By the way MEMORY_MAX_TARGET may well be many times greater than MEMORY_TARGET though it cannot be modified dynamically – the instance needs to be bounced. –  Yasir Arsanukaev Mar 22 '13 at 8:12
    
Oops, it's initSID.ora actually, not init.ora. –  Yasir Arsanukaev Mar 26 '13 at 13:18

The early 11gR1 releases has this bug. They won't start if the memory_target was set to 2GB (and more). Either upgrade to 11gR2 (or 11.1.0.7) or use "classical" SGA/PGA parameters(sga_target, sga_max_size, pga_aggregate_target).

share|improve this answer
    
How this is related to "ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET" the OP notes about? –  Yasir Arsanukaev Mar 27 '13 at 6:36

Assuming you are using linux OS: have you checked size of /dev/shm filesystem? The size of this fs must be long enough to fit your SGA. If not, try remounting with '-o size=xxx'.

share|improve this answer
    
Did you ever read the cause of the failure which is identified by Oracle error number in OP (ORA-00837)? –  Yasir Arsanukaev Apr 2 '13 at 1:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.