| Unable to increase SGA_MAX_SIZE [message #515363] |
Mon, 11 July 2011 02:37  |
 |
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( 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 #515366 is a reply to message #515363] |
Mon, 11 July 2011 02:46  |
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 |
John Watson
Messages: 8964
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 #515420 is a reply to message #515385] |
Mon, 11 July 2011 08:28 |
mkounalis
Messages: 147
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 |
|
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 |
John Watson
Messages: 8964
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"?)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
