Unix for the DBA: Handling Shared Memory

The Problem

There are some good introductions, but special questions are usually missing, like for instance  handling the Shared Memory and the kernel parameters. A professional DBA cannot rely on system administrators, but should rather advise them in setting up Unix - Systems for Oracle databases. A crucial question is the portion of Shared Memory and how to increase it.
The SGA and the PGA of an Oracle database are Shared Memory, usually several GB up to 100 GB and more. If you run several databases on a Unix server, you will definitely need more Shared Memory than the default.

1. get MemTotal

Write a little script mem.sh
#!/bin/ksh
typeset -F2 mem
mem=$(grep MemTotal /proc/meminfo | awk '{print $2}')
let "mem = $mem / 1024 / 1024"
echo "MemTotal = $mem GB"

and run it:
$> ./mem.sh
MemTotal = 15,33 GB


2. get Shared Memory
The ipcs -lm shows e.g
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 3145728
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1

We have 8 GB Shared Memory out of 15,33 MemTotal. We need 5 databases, hence more Shared Memory to avoid
ORA-27123: unable to attach to shared memory segment 
at startup time. We try to get through with 14 GB.

3. Increase Shared Memory

You find these parameters in /etc/sysctl.conf. As root or per sudo first make copy, and look at the line

kernel.shmall=2097152 # value in pagesize

Get the pagesize with 

$> getconf PAGESIZE

$> 4096

Short calculation :

max total shared memory = kernel.shmall * 4096 = 8G

 
In order to get 14 GB Shared Memory we must change the line to kernel.shmall=3670016.  As root or per sudo type

$> sysctl -p 

 

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 3670016

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048586

kernel.shmmax = 3221225472

and control the effect with

$> ipcs -lm

------ Shared Memory Limits --------

max number of segments = 4096

max seg size (kbytes) = 3145728

max total shared memory (kbytes) = 14680064

min seg size (bytes) = 1 

Now try the new configuration.