Home » RDBMS Server » Server Administration » Unable to increase SGA_MAX_SIZE (RHEL 4 U5 ; ORACLE 10G R2)
Unable to increase SGA_MAX_SIZE [message #515363] Mon, 11 July 2011 02:37 Go to next message
arindam_msc
Messages: 3
Registered: July 2011
Location: Ranchi
Junior Member
We are using 32 bit rhel 4 u5.Our database version Oracle 10g R2.
My Physical Memory(RAM)=16gb
SGA_MAX_SIZE=2G
SGA_TARGET=2G
kernel.shmmax=2G

Now I want to change SGA_MAX_SIZE to 6 GB
BUT ORA 27102 error occured.

HERE I have given the details procedure:

1st I have checked the physical memory in 3 different way:

[root@localhost ~]# grep MemTotal /proc/meminfo
MemTotal: 16616904 kB

[root@localhost ~]# cat /proc/meminfo
MemTotal: 16616904 kB
MemFree: 503600 kB
Buffers: 15888 kB
Cached: 13029856 kB
SwapCached: 77756 kB
Active: 10259180 kB
Inactive: 4136432 kB
HighTotal: 15853196 kB
HighFree: 261632 kB
LowTotal: 763708 kB
LowFree: 241968 kB
SwapTotal: 4192924 kB
SwapFree: 3990780 kB
Dirty: 60 kB
Writeback: 0 kB
Mapped: 3420668 kB
Slab: 142612 kB
CommitLimit: 12501376 kB
Committed_AS: 11176928 kB
PageTables: 1547716 kB
VmallocTotal: 106488 kB
VmallocUsed: 3944 kB
VmallocChunk: 102236 kB
HugePages_Total: 0
HugePages_Free: 0

[root@localhost ~]# top
top - 12:58:53 up 14 days, 3:02, 2 users, load average: 1.42, 1.73, 1.54
Tasks: 619 total, 3 running, 616 sleeping, 0 stopped, 0 zombie
Cpu(s): 8.6% us, 3.5% sy, 0.0% ni, 83.9% id, 4.0% wa, 0.0% hi, 0.0% si
Mem: 16616904k total, 15806776k used, 810128k free, 20092k buffers
Swap: 4192924k total, 99064k used, 4093860k free, 13361148k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4112 oracle 15 0 2142m 207m 205m S 34 1.3 0:01.02 oracle
4594 root 15 0 49732 13m 4836 S 5 0.1 10:03.43 X
4092 oracle 15 0 2141m 62m 60m S 5 0.4 0:00.15 oracle
4121 oracle 15 0 2142m 44m 42m S 5 0.3 0:00.14 oracle
4104 oracle 15 0 2140m 51m 50m S 3 0.3 0:00.10 oracle
4117 oracle 15 0 2141m 42m 41m S 3 0.3 0:00.09 oracle
4119 oracle 15 0 2140m 37m 36m S 3 0.2 0:00.08 oracle
4102 oracle 15 0 2141m 37m 35m S 2 0.2 0:00.07 oracle
4108 oracle 15 0 2140m 27m 26m S 1 0.2 0:00.04 oracle
5568 oracle 16 0 42940 6776 4312 S 1 0.0 53:12.17 tnslsnr
4115 oracle 15 0 2140m 15m 14m S 1 0.1 0:00.03 oracle
4125 oracle 15 0 2141m 20m 19m S 1 0.1 0:00.03 oracle
30185 oracle 15 0 2141m 88m 87m S 1 0.5 0:00.43 oracle
4090 oracle 15 0 2140m 15m 13m S 1 0.1 0:00.02 oracle
4094 oracle 16 0 2141m 19m 18m S 1 0.1 0:00.02 oracle
4096 oracle 16 0 2140m 15m 13m S 1 0.1 0:00.02 oracle
4106 oracle 15 0 2140m 18m 17m S 1 0.1 0:00.02 oracle
4110 oracle 15 0 2140m 14m 13m S 1 0.1 0:00.02 oracle
4123 oracle 15 0 2140m 15m 14m S 1 0.1 0:00.02 oracle
[root@localhost ~]#


Now I have checked sysctl.conf file & change shmmax=6G

[root@localhost ~]# gedit /etc/sysctl.conf


# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(Cool and
# sysctl.conf(5) for more details.
# Controls IP packet forwarding


net.ipv4.ip_forward = 0


# Controls source route verification


net.ipv4.conf.default.rp_filter = 1


# Do not accept source routing


net.ipv4.conf.default.accept_source_route = 0


# Controls the System Request debugging functionality of the kernel


kernel.sysrq = 0


# Controls whether core dumps will append the PID to the core filename.

# Useful for debugging multi-threaded applications.


kernel.core_uses_pid = 1



# Oracle configuration parameter


kernel.shmall = 2097152

#kernel.shmmax = 2147483648


kernel.shmmax = 6442450944

kernel.shmmni = 4096
kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.core.rmem_max = 262144

net.core.rmem_default = 262144

net.core.wmem_max = 262144

net.core.wmem_default = 262144

net.ipv4.ip_local_port_range = 1024 65000

Now shutdown database & restart the OS.


[root@localhost ~]# su - oracle


[oracle@localhost ~]$ sqlplus / as sysdba



SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 7 17:21:19 2011

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "SYS"

SQL> show sga
Total System Global Area 2147483648 bytes
Fixed Size 1220460 bytes
Variable Size 385876116 bytes
Database Buffers 1744830464 bytes
Redo Buffers 15556608 bytes

SQL> show parameter sga_max_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 2G

SQL> show parameter sga_target;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 2G

SQL> alter system set sga_max_size=5120M scope=spfile;
System altered.

SQL> shut

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

ORA-27102: out of memory

Now I have replaced my previous spfile.Then...

SQL> startup

ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 1220460 bytes
Variable Size 385876116 bytes
Database Buffers 1744830464 bytes
Redo Buffers 15556608 bytes
Database mounted.
Database opened.

SQL> alter system set sga_max_size=3000M scope=spfile;

System altered.

SQL> shut
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

ORA-27102: out of memory
Linux Error: 12: Cannot allocate memory
Additional information: 1
Additional information: 1409036

SQL>

HOW CAN I SOLVE THIS PROBLEM?
Re: Unable to increase SGA_MAX_SIZE [message #515365 is a reply to message #515363] Mon, 11 July 2011 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-27102: out of memory
 *Cause: Out of memory
 *Action: Consult the trace file for details


Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

[Updated on: Mon, 11 July 2011 02:45]

Report message to a moderator

Re: Unable to increase SGA_MAX_SIZE [message #515366 is a reply to message #515363] Mon, 11 July 2011 02:46 Go to previous messageGo to next message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member
>Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

This is a 32bit Oracle install, not a 64bit install. The OS would also be 32bit.

You'd need to configure VLM to address >4GB


Hemant K Chitale
Re: Unable to increase SGA_MAX_SIZE [message #515367 is a reply to message #515363] Mon, 11 July 2011 02:59 Go to previous messageGo to next message
John Watson
Messages: 4091
Registered: January 2010
Location: Global Village
Senior Member
Hi - with 32bit Linux, your technique can't work. I think you'll need to use the Linux PAE kernel, and then configure your SGA with
use_indirect_data_buffers=true
db_block_buffers=something-big
rather than using automatic shared memory management.

[update: typo]

[Updated on: Mon, 11 July 2011 03:00]

Report message to a moderator

Re: Unable to increase SGA_MAX_SIZE [message #515381 is a reply to message #515367] Mon, 11 July 2011 04:44 Go to previous messageGo to next message
arindam_msc
Messages: 3
Registered: July 2011
Location: Ranchi
Junior Member
Thank u for your quick replay
Physical Address Extension (PAE) is a feature to allow 32 bit processors to access larger than 4G RAM.
But now I want to change SGA_MAX_SIZE from 2048M to just 3000M.
Then is it necessary to use PAE KERNEL?
Re: Unable to increase SGA_MAX_SIZE [message #515385 is a reply to message #515381] Mon, 11 July 2011 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hmmm, why don't you try it?
You tried for 5120M, change it to 3000M and see (and post) what happens.

Regards
Michel
Re: Unable to increase SGA_MAX_SIZE [message #515420 is a reply to message #515385] Mon, 11 July 2011 08:28 Go to previous messageGo to next message
mkounalis
Messages: 146
Registered: October 2009
Location: Dallas, TX
Senior Member
With a 32-bit os, each process can only see 4gb of ram natively. The os will map some of that ram to itself. I have had struggles getting sga's over 1.8 gb no matter what you set sga_max to. Remember also that all processes including connection threads need to fit into that 4gb footprint. Also - you really can only increase your buffer cache by implementing pae - not the sga. If the machine is 64-bit capable you are much better off implementing 64-bit os and oracle to utilize the 16gb of ram you have in the box.
Re: Unable to increase SGA_MAX_SIZE [message #515543 is a reply to message #515385] Tue, 12 July 2011 03:03 Go to previous messageGo to next message
arindam_msc
Messages: 3
Registered: July 2011
Location: Ranchi
Junior Member
very sorry for not to implement your advice.Actually I did not understand properly whatever u said.

1st should I enter use_indirect_data_buffers =true in pfile?
2nd DB_BLOCK_BUFFERS=something big means?

our os linux RHEL 4 U5

I have checked that the range of DB_BLOCK_BUFFERS is 50 to an operating system-specific maximum

IF I enter this parameter in pfile,then what about db_block_size which already in my pfile with value 8k.

should i close the sga_target parameter after entering use_indirect_data_buffers =true in pfile?

I am requesting u to help me to understand the matter properly.
please,please help me.

[Updated on: Tue, 12 July 2011 03:07]

Report message to a moderator

Re: Unable to increase SGA_MAX_SIZE [message #515564 is a reply to message #515543] Tue, 12 July 2011 04:13 Go to previous message
John Watson
Messages: 4091
Registered: January 2010
Location: Global Village
Senior Member
Quote:
1st should I enter use_indirect_data_buffers =true in pfile?
Yes

Quote:
2nd DB_BLOCK_BUFFERS=something big means?
Do some arithmetic. You know your block size, divide that into the size you want your buffer cache to be.

Quote:
should i close the sga_target parameter after entering use_indirect_data_buffers =true in pfile?
Yes (if by "close" you mean "remove"?)
Previous Topic: Template Creation Trouble
Next Topic: how to set initial_rsrc_consumer_group
Goto Forum:
  


Current Time: Sun Apr 20 16:17:35 CDT 2014

Total time taken to generate the page: 0.11747 seconds