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
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.
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
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.
ASKER
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.
*.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.
>>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;
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;
ASKER
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.
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.
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.
ASKER
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>
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>
Go ahead and try to set the parameter. The instance should be up enough.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
ASKER
thank you
Glad to help.
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.
http://blog.tanelpoder.com/2007/08/21/oracle-11g-internals-part-1-automatic-memory-management/