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

Report message to a moderator

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: 26766
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

Report message to a moderator

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: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 
startup nomount;
alter system set ... scope=spfile;
alter database mount;
alter database open;

Regards
Michel

Report message to a moderator

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: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why are you asking about Operating System parameters when problem is with Oracle's?

Report message to a moderator

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: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You problem is just an arithmetic one, you must have (in your spfile):
SGA_MAX_SIZE <= MEMORY_TARGET

Regards
Michel

Report message to a moderator

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

Report message to a moderator

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: 26766
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

Report message to a moderator

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 messageGo to next 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

Report message to a moderator

icon12.gif  Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #677370 is a reply to message #425392] Sat, 14 September 2019 19:00 Go to previous messageGo to next message
romelboza
Messages: 2
Registered: September 2019
Junior Member
Smile Gracias, y pensar que esto me iba a ser util el 14 de Setiembre de 2019

Es muy interesante el aportar en los foros nuestros conocimientos.

Report message to a moderator

Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #677383 is a reply to message #425690] Sun, 15 September 2019 13:08 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Glad you resolved your issue. Please allow me to make a side observation ...

You earlier indicated that as part of your initial attempts, you had stopped and restarted the listener. This has no bearing at all on your problem. More generally, the listener, it's existence or non-existence, has zero, zilch, nada, nyet, null bearing on the operation of the database. The listener is nothing but a connection broker. Think of it like a telephone switchboard. If your switchboard is down, the only thing affected is the ability to receive incoming calls. It has no effect at all on elevators, HVAC, fire alarms, door locks, etc. etc. etc. The database is quite happy to operate with or without a listener.

Report message to a moderator

Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #677389 is a reply to message #677383] Mon, 16 September 2019 09:19 Go to previous messageGo to next message
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
Senior Member
Ed - this thread is 10 years old.

Report message to a moderator

Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #677395 is a reply to message #677389] Mon, 16 September 2019 12:50 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
cookiemonster wrote on Mon, 16 September 2019 09:19
Ed - this thread is 10 years old.
Oopsies!

Since it was at the top of the queue, I failed to notice that 'romelboza' had raised it from the dead.

I wonder what he was doing to even discover it, much less feel compelled to revive it just to say:

"Thank you, and to think that this was going to be useful on September 14, 2019"
"It is very interesting to contribute our knowledge in the forums."

Report message to a moderator

Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #677396 is a reply to message #677395] Mon, 16 September 2019 15:19 Go to previous messageGo to next message
romelboza
Messages: 2
Registered: September 2019
Junior Member
La Respuesta de Michel me salvo de una semana de estres


Su problema es solo aritmético, debe tener (en su archivo):
SGA_MAX_SIZE <= MEMORY_TARGET

Saludos
Michel

Report message to a moderator

Re: ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504 [message #677399 is a reply to message #677395] Tue, 17 September 2019 03:21 Go to previous message
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
Senior Member
google search can very easily pull up really old threads if you're looking for a specific error message.

Report message to a moderator

Previous Topic: Query to get the maxsize and unlimited of the TableSpace
Next Topic: is it possible to move the datafiles from non CDB architecture to CDB architecture not just copy as
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 28 06:33:49 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.