About

Martin Klier

usn-it.de

Oracle Database with SGA of or larger than 16GB

Hi,

using large SGA’s requires 64bit Oracle RDBMS, of course. But furthermore, some OS settings are to be done. SUSE walks it’s own lane, quite as usual, so I will provide two ways for doing without ORA-27102.

SUSE with orarun package in place, edit /etc/sysconfig/oracle and change:

SET_ORACLE_KERNEL_PARAMETERS="yes"
SHMMAX=34359738368
SHMMNI=4096
SHMALL=8388608
SHM_USE_BIGPAGES=1
SEMMSL=2000
SEMMNS=500000
SEMOPM=100
SEMMNI=256
IP_LOCAL_PORT_RANGE="1024 65000"
RMEM_MAX=1048576
RMEM_DEFAULT=1048576
WMEM_MAX=1048576
WMEM_DEFAULT=1048576
FILE_MAX_KERNEL=131072
FILE_MAX_SHELL=65536
PROCESSES_MAX_SHELL=65536
MAX_CORE_FILE_SIZE_SHELL=unlimited
VM_MAPPED_RATIO=100
AIO_MAX_SIZE=262144

Generic enterprise Linux, using /etc/sysctl.conf

kernel.sysrq = 1
kernel.core_uses_pid = 1
kernel.shmmni = 4096
kernel.shmall = 8388608
kernel.shmmax =34359738368
net.core.rmem_default=1048576
net.core.wmem_default=1048576
net.core.rmem_max=1048576
net.core.wmem_max=1048576
kernel.sem = 2000 500000 100 250
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

Finally, you can change the SGA size to 16GB:

SQL> alter system set sga_max_size=16G scope=spfile;

SQL> alter system set sga_target=16G scope=spfile;

The changes will become active at next instance restart, but first you have to activate the kernel parameter settings (on SUSE by re-running of /etc/init.d/oracle start, otherwise execute sysctl -p). If not, you will see some ORA-27102 soon 🙂

Have Fun
Usn

German Federal Constitutional Court: Confidentiality and Integrity of IT systems
The Speed-up loop

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.