Oracle 11g: ORA-00845: MEMORY_TARGET not supported on this system

Hi,

We've created a new db (DEV), 11gR1 (spfile on RH Linux using dbca), the db was working fine, yesterday we've figured it out, that it is down, and when trying to start, it is giving this error:

ORA-00845: MEMORY_TARGET not supported on this system

when checked at the alert log, these are last couple of messages, and nothing is mentioned when it went down and no errors reported.

Wed Mar 24 11:43:04 2010
QMNC started with pid=24, OS id=30102
db_recovery_file_dest_size of 30720 MB is 21.47% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Completed: ALTER DATABASE OPEN
Wed Mar 24 11:48:02 2010
Starting background process CJQ0
Wed Mar 24 11:48:03 2010
CJQ0 started with pid=36, OS id=31474
Wed Mar 24 11:48:05 2010
Starting background process SMCO
Wed Mar 24 11:48:05 2010
SMCO started with pid=37, OS id=31476

Need assistance here; first how to identify what exactly is the problem, and then fix it and obviously to make sure, not to happen again.

This is our first encounter with Oracle 11g ;-)

Best regards.
el123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
0
el123Author Commented:
thanks slightwv, this is an excellent article, but my db is down, what should I do to change the size of this parameter, which is right now:

*.memory_target=1610612736

just did a vi for the spfile,
can you please assist here, what should my steps, to bring back my db?
and to avoid this error to raise again.

thanks.
0
slightwv (䄆 Netminder) Commented:
>>just did a vi for the spfile,

Can't do this.  It's a binary file.  If you have already written it out, we might have some problems.  Not impossible problems, just problems.  Let me know if you encounter errors in the following steps.

The database doesn't have to be fully open to change init parameters.

do the following:
sqlplus /nolog
SQL> connect / as sysdba
SQL> startup nomount
SQL> alter system set memory_target=??? scope=spfile;
SQL> shutdown immediate;
SQL> startup;
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

el123Author Commented:
please suggest, what should be the size of this parameter, we have 4 dbs running on the same box, and it is a dev box or what should be the formula to set it up? so we should not encounter this again, thanks for your help.
0
slightwv (䄆 Netminder) Commented:
There's no magic formula for setting up memory allocations for databases.  Every database has it's own special needs.

I'm sure you can find posts that go into great detail and some smoke and mirrors to come up with a initial best guess but for development, I say make an educated guess to start.  Let apps/users on the system and adjust accordingly.

Best first guess:  ((Max memory on box - amount for OS and other apps so you don't swap)/number of oracle instances) * .80

Note: I suggest taking 80% or less to give you some room to grow/adjust/add additional databsaes.

If you have Grid Control set up to monitor these instances, you should get an idea of where to focus your resources pretty quick.

0
el123Author Commented:
this is output ...


oracle@vmoracle:scripts$ sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Mar 31 11:42:10 2010

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORA-00845: MEMORY_TARGET not supported on this system
SQL>
0
slightwv (䄆 Netminder) Commented:
Go ahead and try to set the parameter.  The instance should be up enough.
0
el123Author Commented:
error :-(

oracle@vmoracle:oracle$ sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Mar 31 11:49:02 2010

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORA-00845: MEMORY_TARGET not supported on this system
SQL> alter system set memory_target=406847488 scope=spfile;
alter system set memory_target=406847488 scope=spfile
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0


SQL> shutdown
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
SQL>


where as I can connect to other dbs
0
slightwv (䄆 Netminder) Commented:
I assume you adjusted /dev/shm and the MEMORY_MAX parameter is just to big for the system to handle?


>>ORA-01034: ORACLE not available

OK them.... old school:

Look in your alert log for the last successful startup.  There should be a list of non-default init.ora parameters.

Copy these out and put them in a text file somewhere, say /tmp/myTempInit.ora

edit the file to adjust the memory parameter.

then:
SQL> startup pfile='/tmp/myTempInit.ora';

if it starts up then recreate the spfile:
SQL> create spfile from pfile='/tmp/myTempInit.ora';
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
el123Author Commented:
thank you slightwv

I was able to get it started, after setting memory_target=768m half of old value, but can you please suggest any link or any good & easy place, where I can see how to size memory parameters?

Thanks again
0
slightwv (䄆 Netminder) Commented:
Sorry but no.  Like I posted up in http:#29222681, there is no silver bullet.

You pretty much have to go on experience for a starting point.  The more you know about the database and how it will be used, the better your first guess.

I'm sure if you Google around you will find tons of info out there.  I'll go out on a limb and bet most if/not all talk about tuning after the instance is up and running.

The reports in AWR/ADDM and the various advisors show nice graphs/reports on how the database can be adjusted and the estimated impact on the change.

The memory advisor is pretty decent.  Check out:
http://www.dba-oracle.com/oracle10g_tuning/t_memory_advisor.htm
0
el123Author Commented:
thank you
0
slightwv (䄆 Netminder) Commented:
Glad to help.
0
johnsmith1962Commented:
The error is caused by the swap settings on the Oracle server.  You will need to increase the amount of swap available in order to increase the memory target settings.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux Distributions

From novice to tech pro — start learning today.