Did you know that you can navigate the posts by swiping left and right?
The shared memory settings needs to be changed in case if you are getting an ORA-27102 error while using the “sga_target” settings.
SHMMAX and SHMALL are two key shared memory parameters that directly impact’s the way by which Oracle creates an SGA. Shared memory is nothing but part of Unix IPC System (Inter Process Communication) maintained by kernel where multiple processes share a single chunk of memory to communicate with each other.
While trying to create an SGA during a database startup, Oracle chooses from one of the 3 memory management models a) one-segment or b) contiguous-multi segment or c) non-contiguous multi segment. Adoption of any of these models is dependent on the size of SGA and values defined for the shared memory parameters in the linux kernel, most importantly SHMMAX.
memory_target uses a file system called /dev/shm for shared memory. sga_target uses SystemV shared memory which uses kernel parameters to determine a limit.
SHMMAX is the maximum size of a single shared memory segment set in “bytes”.
# cat /proc/sys/kernel/shmmax
536870912
SHMALL is the total size of Shared Memory Segments System wide set in “pages”.
# cat /proc/sys/kernel/shmall
1415577
The key thing to note here is the value of SHMMAX is set in “bytes” but the value of SHMMALL is set in “pages”.
As SHMALL is the total size of Shard Memory Segments System wide, it should always be less than the Physical Memory on the System and should also be less than sum of SGA’s of all the oracle databases on the server. Once this value (sum of SGA’s) hit the limit, i.e. the value of shmall, then any attempt to start a new database (or even an existing database with a resized SGA) will result in an “out of memory” error (below). This is because there won’t be any more shared memory segments that Linux can allocate for SGA.
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device.
So above can happen for two reasons. Either the value of shmall is not set to an optimal value or you have reached the threshold on this server.
Setting the value for SHMALL to optimal is straight forward. All you want to know is how much “Physical Memory” (excluding Cache/Swap) you have on the system and how much of it should be set aside for Linux Kernel and to be dedicated to Oracle Databases.
For e.g. Let say the Physical Memory of a system is 6GB, out of which you want to set aside 1GB for Linux Kernel for OS Operations and dedicate the rest of 5GB to Oracle Databases. Then here’s how you will get the value for SHMALL.
Convert this 5GB to bytes and divide by page size. Remember SHMALL should be set in “pages” not “bytes”.
So here goes the calculation.
Determine Page Size first, can be done in two ways. In my case it’s 4096 and that’s the recommended and default in most cases which you can keep the same.
# getconf PAGE_SIZE
4096
or
# cat /proc/sys/kernel/shmmni
4096
Convert 5GB into bytes and divide by page size, I used the linux calc to do the math.
# echo "( 5 * 1024 * 1024 * 1024 ) / 4096 " | bc -l
1310720.00000000000000000000
Reset shmall and load it dynamically into kernel
# echo "1310720" > /proc/sys/kernel/shmall
or
Edit the /etc/sysctl.conf file to set this values or add the following entry at the end of this file. kernel.shmall = 1310720
# sysctl –p
Verify if the value has been taken into effect.
# sysctl -a | grep shmall
kernel.shmall = 1310720
# ipcs -lm
------ Shared Memory Limits --------
max number of segments = 4096 /* SHMMNI */
max seg size (kbytes) = 524288 /* SHMMAX */
max total shared memory (kbytes) = 5242880 /* SHMALL */
min seg size (bytes) = 1
Oracle makes use of one of the 3 memory management models to create the SGA during database startup and it does this in following sequence. First Oracle attempts to use the one-segment model and if this fails, it proceeds with the next one which’s the contiguous multi-segment model and if that fails too, it goes with the last option which is the non-contiguous multi-segment model.
So during startup it looks for shmmax parameter and compares it with the initialization parameter *.sga_target. If shmmax > *.sga_target, then oracle goes with one-segment model approach where the entire SGA is created within a single shared memory segment.
But the above attempt (one-segment) fails if SGA size otherwise *.sga_target > shmmax, then Oracle proceeds with the 2nd option – contiguous multi-segment model. Contiguous allocations, as the name indicates are a set of shared memory segments which are contiguous within the memory and if it can find such a set of segments then entire SGA is created to fit in within this set.
But if cannot find a set of contiguous allocations then last of the 3 option’s is chosen – non-contiguous multi-segment allocation and in this Oracle has to grab the free memory segments fragmented between used spaces.
So let’s say if you know the max size of SGA of any database on the server stays below 1GB, you can set shmmax to 1 GB. But say if you have SGA sizes for different databases spread between 512MB to 2GB, then set shmmax to 2Gigs and so on.
Install doc for 11g recommends the value of shmmax to be set to “4GB – 1byte” or half the size of physical memory whichever is lower. The “4GB – 1byte” is related to the limitation on the 32 bit (x86) systems where the virtual address space for a user process can only be little less than 4GB. As there’s no such limitation for 64bit (x86_64) bit systems, you can define SGA’s larger than 4 Gig’s. But idea here is to let Oracle use the efficient one-segment model and for this shmmax should stay higher than SGA size of any individual database on the system.