Semaphore Management
Let me talk about semaphores and how they are related to Oracle.
Semaphores are signals used by Oracle to serialize the internal Oracle processes. The number of semaphores for a database is equal to the value of the PROCESSES parameter in the INIT.ORA file. For example, a database with PROCESSES=200 will have 200 semaphores allocated for Oracle. It is critical that the Unix kernel parameter semmns be set to at least double the total number of processes for all database instances on your server. If it’s not set, your databases will fail to start, and you’ll receive the following error:
spcre: semget error, unable to get first semaphore set.
Change Kernel Parameters
It’s often necessary to make changes to kernel parameters on a Unix system in order to accommodate the needs of the Oracle database software. You should always work with your Unix system administrator to make such changes. The general process, however, is as follows:
1. Shut down any running Oracle instances.
2. Locate the kernel configuration file for your operating system.
3. Make the necessary changes using system utilities or the vi editor.
4. Reconfigure the kernel.
5. Reboot your machine.
6. Restart your Oracle instances.
Remember, kernel configuration requires a great deal of expertise. Always work with your system administrator.
Important Note:
Reconfiguring kernel parameters can have a dramatic impact on your server. The effects of a mistake can be catastrophic. Hence, you need to make sure that you fully understand kernel configuration before attempting any such changes.
There are 3 OS kernel parameters that work together to limit semaphore allocation.
When an application requests semaphores, the OS releases them in ‘sets’. Illustrated here as 2 sets:
This is controlled by SEMMNI –>OS limit on the Number of Identifiers or sets.
Each set contains a tunable number of individual semaphores.
Illustrated here as 2 semaphores per semaphore set:
This is controlled by SEMMSL –>The number of semaphores in an identifier or set.(Semaphore List)
Ultimately however, the OS can limit the total number of semaphores available from the OS. Controlled by:
SEMMNS –> The total Number of Semaphores allowed system wide.
For instance:
Let’s say SEMMNI = 100000000 and SEMMSL= 100000000 while SEMMNS=10
Even though SEMMNI is 100000000 and SEMMSL is 100000000, the max # of semaphores
available on your system will only be 10, because SEMMNS is set to 10.
Inversely:
Let’s say SEMMNI = 10 and SEMMSL = 10 while SEMMNS= 100000000000000000000000000
Because SEMMNI is 10 and SEMMSL is 10, the max # of semaphores avail on your system will only be 100 or (10 X 10), despite what SEMMNS is set too.
THIS NOTION CAN BE SUMMARIZED BY THE FOLLOWING STATEMENT:
The max # of semaphores that can be allocated on a system will be the lesser of: (semmsl * semmni) or semmns.
SEMMNI, SEMMSL & SEMMNS are the basic names for OS semaphore kernel parameters, the full name may vary depending on your OS. Consult your OS specific Oracle Install guide.
Display Values for Semaphores
The maximum allowed number of semaphores is specified by the semmns kernel parameter. In HP-UX Version 11, the command to show semaphores is kmtune. Run kmtune, and pipe the output through grep sem to filter out everything except semaphore settings. For example:
>kmtune|grep sem
sema 1
semaem 16384
semmap (SEMMNI+2)
semmni 200
semmns 800
semmnu 30
semume 10
semvmx 32767
Look at this output, find the line for semmns, and you’ll quickly see that the server has 800 semaphores.
Count Used Semaphores
You can use the -sa option of the ipcs command to display the number of used semaphores. The total number of used semaphores is determined by summing the NSEMS column, which is the far right column in the output. For example:
>ipcs -sa|grep oracle
s 221 0x0000 –ra-r—– oracle dba 200
s 223 0x0000 –ra-r—– oracle dba 200
s 1024 0x0000 –ra-r—– oracle dba 100
s 225 0x0000 –ra-r—– oracle dba 75
From this output, you can determine the following:
• There are four instances running for each line of output.
• There are 575 (200+200+100+75) semaphores held by the Oracle user for those four database instances.
The output from the ipcs -sa command will always display one line per instance. From there, it’s just a matter of summing the rightmost column to get the total number of semaphores that are being used.
Determine the Semaphore Sets Held by an Instance
When you need to remove a semaphore set for a crashed instance, you cannot tell using the ipcs -sa command just which semaphore sets are associated with which instances. You can, however, get this information by using Server Manager’s oradebug command. Here’s an example:
>sqlplus / as sysdba
SQL>oradebug ipc
Shared memory information written to trace file.
Server Manager writes oradebug output to a trace file; look in your udump directory for a .trc file. The contents will appear as follows:
—————— Semaphores ——————
Total number of semaphores = 100
Number of semaphores per set = 100
Number of semaphore sets = 1
Semaphore identifiers: 14825
The IDs of the semaphore sets used by the instance are listed under the “Semaphore identifiers” heading. In this case, the instance is using just one semaphore set: ID 14825.
Remove a Held Semaphore After a Crash
When an Oracle instance crashes, background processes are killed, but the memory for the SGA region is sometimes still held by the server. The ipcs command in the following example will identify those semaphores that are being used by Oracle instances:
>ipcs -sa|grep oracle
s 221 0x0000 –ra-r—– oracle dba 200
s 223 0x0000 –ra-r—– oracle dba 200
s 1024 0x0000 –ra-r—– oracle dba 100
s 225 0x0000 –ra-r—– oracle dba 75
Now you have to determine which semaphore set is associated with the crashed instanceyou do not want to delete the wrong set. If you have only one instance on your server, you’ll have only one Oracle semaphore set to choose from. If you have multiple instances, use the oradebug command described earlier to determine the semaphore sets used by each of the surviving instances. Then, through the process of elimination, you can determine the set associated with the crashed instance. Once you identify the sets of semaphores that you wish to release, you can issue the ipcrm -s command to release them.
The following example releases semaphore sets 221 and 223:
>ipcrm -s 221
>ipcrm -s 223
>ipcs -sa|grep oracle
s 1024 0x0000 –ra-r—– oracle dba 100
s 225 0x0000 –ra-r—– oracle dba 75