Home » RDBMS Server » Server Administration » Unable to use more than 2GB memory for Oracle in Windows 2003 (Oracle 11.1.0.7, Windows 2003 R2 SP2)
Unable to use more than 2GB memory for Oracle in Windows 2003 [message #512204] Fri, 17 June 2011 09:53 Go to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
Hi all

I have a 11g database installed on a windows 2003 server. When I created the database I could not assign more than 2GB for SGA_TARGET where as I have 16GB of RAM available on the server. I created the database with SGA_TARGET as 1.5 GB and Memory_TARGET as 2GB. I could create the database successfully. Later when I again tried to increase SGA_TARGET to 6GB and MEMORY_TARGET to 8GB, I could not start the database. I got below error;

ORA-27102: out of memory

OSD-00022: additional error information

O/S-Error: (OS 8) Not enough storage is available to process this command


Quote:
I added /pae in the boot.ini
Added AWE_WINDOW_SIZE key to registry as 2000000000
Set use_indirect_data_buffers=true
added db_block_buffer=131072 (ie. 2GB DB_BLOCK_SIZE=16kb)
java_pool_size= 1000M
large_pool_size=1000M
shared_pool_size=2000M



I got again the same error

Can some one please help me?

I could not use SGA_TARGET. So is AMM not allowed with AWE?

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #512215 is a reply to message #512204] Fri, 17 June 2011 10:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I created the database with SGA_TARGET as 1.5 GB and Memory_TARGET as 2GB. I could create the database successfully.
>Later when I again tried to increase SGA_TARGET to 6GB and MEMORY_TARGET to 8GB, I could not start the database. I got below error;
>Can some one please help me?
Use values that don't throw error or join 21st Century on 64-bit platform.

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #512217 is a reply to message #512204] Fri, 17 June 2011 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * from v$version;

REMOVE db_block_buffer it is obsolete since 10 years.
Use sga_max_size, large_pool_size (I doubt you need 1G, decrease to 100M) and java_pool_size (same thing unless you use many Java classes) ONLY; remove other size parameters.

Regards
Michel

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #512440 is a reply to message #512217] Mon, 20 June 2011 06:10 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
@Black Swan

You mean I cannot use more than 2GB of RAM for my database in 32 bit OS? Is there no work arround?

@Michel

I got error with SGA_MAX_SIZE and MEMORY_TARGET. That is why I used the mentioned parameters according to the note: ID 225349.1.


Still I am having the problem

Thanks
Ridhi Sundar

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #512457 is a reply to message #512440] Mon, 20 June 2011 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you do what I mentioned?

Regards
Michel

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513195 is a reply to message #512457] Fri, 24 June 2011 05:44 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
 
SQL> select * from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production




My IP FILE:
Quote:
opaprod.__oracle_base='D:\Oracle\oc4.6'#ORACLE_BASE set from environment
*.audit_file_dest='D:\Oracle\oc4.6\admin\OPAPROD\adump'
*.audit_trail='db'
*.compatible='11.1.0.7.0'
*.control_files='D:\Oracle\oc4.6\Oradata\OPAPROD\control01.ctl','D:\Oracle\oc4.6\Oradata\OPAPROD\control02.ctl','D:\Oracle\oc4.6\Orad ata\OPAPROD\control03.ctl'
*.db_block_size=16384
*.db_domain='takesolutions.com'
*.db_name='OPAPROD'
*.db_recovery_file_dest='D:\Oracle\oc4.6\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='D:\Oracle\oc4.6'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OPAPRODXDB)'
*.log_archive_dest_1='LOCATION=E:\Archive'
*.log_archive_dest_2='LOCATION=\\172.25.3.10\Oracle\OCArch'
*.log_archive_format='ARC%S_%R.%T'
*.memory_max_target=6000M
*.memory_target=6000M
*.nls_date_format='DD-MON-RRRR'
*.open_cursors=1000
*.optimizer_features_enable='9.2.0'
*.optimizer_mode='CHOOSE'
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.remote_os_authent=TRUE
*.sessions=200
*.sga_max_size=4000M
*.sga_target=4000M
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='D:\opapps\xmltemp'


I am facing the same error:
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORA-27102: out of memory
OSD-00029: additional error information
O/S-Error: (OS 8) Not enough storage is available to process this command.
SQL>

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513196 is a reply to message #513195] Fri, 24 June 2011 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see "use_indirect_data_buffers=true"

Regards
Michel

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513198 is a reply to message #513196] Fri, 24 June 2011 06:03 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And I really doubt you will be able to use more than 2, *perhaps* 3GB with a lot of fiddling.

The best option would really be to move on to a 64 bit architecture.

[Updated on: Fri, 24 June 2011 06:03]

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513350 is a reply to message #513198] Sun, 26 June 2011 23:48 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
With below parametersQuote:
opaprod.__oracle_base='D:\Oracle\oc4.6'#ORACLE_BASE set from environment
*.audit_file_dest='D:\Oracle\oc4.6\admin\OPAPROD\adump'
*.audit_trail='db'
*.compatible='11.1.0.7.0'
*.control_files='D:\Oracle\oc4.6\Oradata\OPAPROD\control01.ctl','D:\Oracle\oc4.6\Oradata\OPAPROD\control02.ctl','D:\Oracle\oc4.6\Orad ata\OPAPROD\control03.ctl'
*.db_block_size=16384
*.db_domain='takesolutions.com'
*.db_name='OPAPROD'
*.db_recovery_file_dest='D:\Oracle\oc4.6\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='D:\Oracle\oc4.6'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OPAPRODXDB)'
*.log_archive_dest_1='LOCATION=E:\Archive'
*.log_archive_dest_2='LOCATION=\\172.25.3.10\Oracle\OCArch'
*.log_archive_format='ARC%S_%R.%T'
*.nls_date_format='DD-MON-RRRR'
*.open_cursors=1000
*.optimizer_features_enable='9.2.0'
*.optimizer_mode='CHOOSE'
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.remote_os_authent=TRUE
*.sessions=200
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='D:\opapps\xmltemp'
USE_INDIRECT_DATA_BUFFERS=TRUE
*.DB_BLOCK_BUFFERS=200000
*.JAVA_POOL_SIZE=50M
*.LARGE_POOL_SIZE=50M
*.SHARED_POOL_SIZE=1000M


I am getting error
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORA-27100: shared memory realm already exists


@ Michel

I cannot use SGA_TARGET and MEMORY_TARGET with "use_indirect_data_buffers=true". It gives below error. 
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORA-00385: cannot enable Very Large Memory with new buffer cache parameters

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513351 is a reply to message #513350] Sun, 26 June 2011 23:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
alert_SID.log file contains additional clues.

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513832 is a reply to message #512204] Wed, 29 June 2011 11:14 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
You can use more memory for certain buffers
see
http
www
pontis
biz
/resources
/articles
/oracle_on_windows.php

Migrating to 64-bit os is advisable
(*not allowed to post links if you have posted <6 messages)

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513834 is a reply to message #512204] Wed, 29 June 2011 11:30 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
@Flyby

from my prvious post you can understand, with AWE I am getting error 
ORA-27100: shared memory realm already exists


Can any one advise some adjustment in parameters to overcome this error?

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513836 is a reply to message #513834] Wed, 29 June 2011 11:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Can any one advise some adjustment in parameters to overcome this error?
typically Oracle is down

It help us to know EXACTLY what was done that generates any specific error
use COPY & PASTE so we can see what you do & how Oracle responds.

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513838 is a reply to message #513836] Wed, 29 June 2011 11:51 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
in my post above[message #513350], I have mentioned what parameters I have set and while starting DB what error I am getting. Also I have mentioned earlier to enable AWE I have set /pae switch in boot.ini and set AWE_WINDOW_SIZE key in registry as 2000000000.

Let me know if you need any more information.

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513840 is a reply to message #513838] Wed, 29 June 2011 11:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Let me know if you need any more information.
I don't need more information because I don't have any problem.

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513842 is a reply to message #513840] Wed, 29 June 2011 12:06 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
I understand this forum is not just to comment something because you want to do it rather to share some knowledge to help each other. If you can not think of a solution better do not reply.

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513843 is a reply to message #513842] Wed, 29 June 2011 12:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
 
27100, 00000, "shared memory realm already exists"
// *Cause: Tried to start duplicate instances, or tried to restart an
//         instance that had not been properly shutdown
// *Action: Use a different instance name, or cleanup the failed instance's
//          SGA

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513847 is a reply to message #513843] Wed, 29 June 2011 12:20 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
@Blackswan
I am extremely sorry for my previous post.

My instance is down. I edited the parameter file and tried to start it. It did not start and errored with ORA-27100. So there is no failed instance. Correct me if I am wrong.

How to clean the failed instance? Will a system reboot work? I think I have tried rebooting and it did not work. Still I will try it again tomorrow once I will be at work.

Thanks

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513848 is a reply to message #513847] Wed, 29 June 2011 12:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>My instance is down. I edited the parameter file and tried to start it. It did not start and errored with ORA-27100.
>So there is no failed instance. Correct me if I am wrong.
>How to clean the failed instance? Will a system reboot work? I think I have tried rebooting and it did not work.

I only know what you post.
You had a working DB & proceeded to make changes that result in DB not starting.
I suggest revert back to values that allowed DB to start.
If you insist on changing parameters, then I recommend that you only ever change 1 at a time between DB restarts.

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513873 is a reply to message #512204] Wed, 29 June 2011 16:09 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
You could startup using a parameterfile (init..ora) instead of the regular spfile.

http://www.dba-oracle.com/t_ora_27100_shared_memory_realm_already_exists.htm

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #513906 is a reply to message #513873] Thu, 30 June 2011 01:25 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
I am using IP file only to start it.

Report message to a moderator

Re: Unable to use more than 2GB memory for Oracle in Windows 2003 [message #515421 is a reply to message #513906] Mon, 11 July 2011 08:44 Go to previous message
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
Senior Member
You need to read the docs Ridhi. You can not use db_block_buffers with pae. With 32-bit windows all your apps can see only 4gb of ram natively. There is a switch in the boot.ini you can set to allow your apps to use three of the four gigabytes of ram that a process can see, otherwise you really only have two gigs of ram usable per process because the os maps the other two. As has been suggested, go back to your original settings to get the database to fire back up. The only real way you can use the memory efficiently in windows is to migrate your box to 64-bit if it is capable. Docs.oracle.com has detailed documentation on how to configure pae in windows and what your limitations are.

Report message to a moderator

Previous Topic: changing archive log destination
Next Topic: Template Creation Trouble
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Apr 28 01:11:33 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.