Home » RDBMS Server » Server Administration » ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 (Linux 2.6.18-128.1.10.el5 Oracle Release 11.1.0.7.0)
ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #425154] Wed, 07 October 2009 17:03 Go to next message
conty109
Messages: 3
Registered: October 2009
Location: Australia
Junior Member
Out of 50 virtual servers, this server was shutdown since last 3 to 4 weeks. When started & attempted to startup database, it gives me this message.
Connected to an idle instance.

SQL> startup
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504.


I tried a lot to change SGA_MAX_SIZE, but not able to change any of the two parameters because the db is in idle state. I don't know how to circumvent this situation. Any suggestions or help available from you all experts please?

Thanks

Mukesh
Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #425156 is a reply to message #425154] Wed, 07 October 2009 17:43 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
sqlplus
/ as sysdba
create pfile from spfile;
exit

Now you can edit pfile to change what ever parameters to new values.

sqlplus
/ as sysdba
startup pfile=initSID.ora


Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #425179 is a reply to message #425154] Wed, 07 October 2009 23:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
startup nomount;
alter system set ... scope=spfile;
alter database mount;
alter database open;

Regards
Michel
Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #425367 is a reply to message #425154] Thu, 08 October 2009 14:25 Go to previous messageGo to next message
ursusca
Messages: 40
Registered: September 2009
Location: Toronto, ON
Member

Hello,

If I have enough of physical memory on my Linux box can I solve this problem increasing the SHMMAX value and adjusting the "memlock" parameter in the "/etc/security/limits.conf"?

[Updated on: Thu, 08 October 2009 14:29]

Report message to a moderator

Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #425369 is a reply to message #425367] Thu, 08 October 2009 14:32 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
Why are you asking about Operating System parameters when problem is with Oracle's?

Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #425370 is a reply to message #425154] Thu, 08 October 2009 14:58 Go to previous messageGo to next message
ursusca
Messages: 40
Registered: September 2009
Location: Toronto, ON
Member

Well, I am a newbie to Oracle 11g and I thougth that this error probably similar with ORA-27102 error. I was under a misapprehension. Thank you.

[Updated on: Thu, 08 October 2009 14:59]

Report message to a moderator

Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #425392 is a reply to message #425370] Thu, 08 October 2009 23:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You problem is just an arithmetic one, you must have (in your spfile):
SGA_MAX_SIZE <= MEMORY_TARGET

Regards
Michel
Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #425686 is a reply to message #425154] Sun, 11 October 2009 17:32 Go to previous messageGo to next message
conty109
Messages: 3
Registered: October 2009
Location: Australia
Junior Member
Hi Blackswan,

Thanks for your suggestion. I have created my initSID.ora file from spfile. I have edited two values but still I am getting the same error. See the problem,

I set the values as
*.memory_target=738197504
*.sga_max_size=501741824#internally adjusted

I restarted listeners and tried to start the sqlplus session. This time I get the same error, with different values, I don't know where this value is getting changed,

[oracle@achilles bin]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Oct 12 09:37:23 2009

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

Connected to an idle instance.

SQL> startup
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504.
SQL>

Can you further help me, to understand what is happening ? Finally I am still not able to start the instance yet.

Thanks for your understanding & patience.

Regards,

Conty109
Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #425687 is a reply to message #425686] Sun, 11 October 2009 17:48 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
>SQL> startup

defaults to using spfile , if/when it exists

Assume you made pfile called initMYSID.ora containing changed values then you need to do as follows:

SQL> startup pfile=initMYSID.ora
Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #425690 is a reply to message #425687] Sun, 11 October 2009 19:35 Go to previous message
conty109
Messages: 3
Registered: October 2009
Location: Australia
Junior Member
Hi Blackswan,

Thanks very much for your valuable help. Your suggested wayout worked absolutely right for this intance; My day has been saved, the instance is up & running nicely.

On the way to this troubleshooting, I gathered very special experience & knowledge from you & other.

Thanks to you & everyone.

Regards,

Conty109
Previous Topic: ASM 11g - kernel modules
Next Topic: multiByte characters insertion/selection from oracle database problems
Goto Forum:
  


Current Time: Fri Dec 02 12:22:47 CST 2016

Total time taken to generate the page: 0.11244 seconds