Oracle Semaphore Management in UNIX Administration Oracle UNIX/Linux Tips by Burleson Consulting

Semaphore Management in UNIX

A semaphore is a term used for a signal flag used by the Navy to communicate between ships.  In some dialects of UNIX, semaphores are used by Oracle to serialize internal Oracle processes and guarantee that one thing happens before another thing.  Oracle uses semaphores in HP/UX and Solaris to synchronize shadow processes and background processes. However, AIX UNIX does not use semaphores, and a post/wait driver is used instead to serialize tasks.

The number of semaphores for an Oracle database is normally equal to the value of the processes initialization parameter. For example, a database where processes=200 would need to have 200 UNIX semaphores allocated for the Oracle database.

When allocating semaphore in UNIX, it is critical that your UNIX kernel parameter semmns be set to at least double the high-water mark of processes for every database instance on your server.  If you fail to allocate enough semaphores by setting semmns too low, your Oracle database will fail at startup time with the message:

ORA-7279: spcre: semget error, unable to get first semaphore set

Let’s talk about setting the semmns kernel parameter. To make changes in kernel shared memory or semaphore parameters, you need to perform the following steps:

1. Shut down any running Oracle instances

2. Locate the kernel configuration file for your OS

3. Make the necessary changes using the system utilities or the vi editor. 

Today, most dialects of UNIX have specialized System Administration utilities to perform kernel management (Table 2-4)

Table 4: UNIX kernel management tools

System Default values for semaphores

The number of UNIX semaphores is determined by the value of the semmns UNIX kernel parameter. 

Viewing semaphores in HP/UX version 11

In HP/UX v 11, the command to display kernel parameters is kmtune and we can grep to see the semaphore settings.

root> kmtune|grep sem

sema                 1
semaem               16384
semmap               (SEMMNI+2)
semmni               200
semmns               800
semmnu               30
semume               10
semvmx               32767

Counting Used Semaphores

The ipcs UNIX command has a –sa option that can be used to display semaphores. The total number of semaphores is determined by summing the NSEMS column in the ipcs display.  In the example below we see that there are 4 semaphores held by the ROOT user and 475 semaphores held by the ORACLE user in 3 database instances.

root> ipcs -as|grep oracle
IPC status from /dev/kmem as of Mon Sep 10 17:25:21 2001
T  ID     KEY        MODE     OWNER  GROUP   CREATOR   CGROUP NSEMS
s  15 0x00000000 --ra-r-----  oracle  dba    oracle       dba   400

Determine what Oracle database has a semaphore set

As we have noted, when an Oracle database hangs, you may have leftover background processes, held RAM memory segment and held semaphore sets. When you have multiple instances on a UNIX server and need to release a semaphore set for an Oracle database, you must first determine which semaphore set belongs to your crippled instance.

Unfortunately, you cannot tell with the ipcs –sa command which semaphore set belongs to each Oracle database.  Here is the Oracle procedure for determining the semaphore set number for an individual Oracle database so that you can remove the semaphores with the ipcs command.

root> svrmgrl

Oracle Server Manager Release 2.3.2.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.2.3.0 - Production Release
With the distributed and replication options
PL/SQL Release 2.3.2.3.0 - Production

SVRMGR> connect internal
Connected.
SVRMGR> oradebug ipc
-------------- Shared memory --------------
Seg Id     Address   Size
24064      c1739000  28975104
Total: # of segments = 1, size = 28975104
-------------- Semaphores ----------------
Total number of semaphores = 400
Number of semaphores per set = 400
Number of semaphore sets = 1
Semaphore identifiers:   
In Oracle8i and beyond, you can use SQL*Plus to perform the same function.
root> sqlplus /nologin

SQL*Plus: Release 3.3.2.0.0 - Production on Mon Sep 10 17:27:04 2001

Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.

SQL> connect system/manager as sysdba;

Connected.

Since we cannot get the semaphore set number for the crippled database, we must determine the semaphore set using the process of elimination.  We issues the above commands for each live database on the server, and the un-claimed semaphore set will belong to the crippled instance.

Once identified, you can use the following procedure for removing the semaphore set.

Removing a semaphore set for Oracle

We start by issuing the ipcs –sb command to display details of the semaphore set.

root> ipcs -sb|grep oracle
s      67 0x00000000 --ra-r-----    oracle       dba   400 
s     223 0x00000000 --ra-r-----    oracle       dba   400
s     334 0x00000000 --ra-r-----    oracle       dba   300

Now, we can remove the semaphores from the locked-up database. In this example, we assume that set 223 is the one for the crippled database.

root> ipcrm -s 223

Now we can confirm that the semaphores are deleted.

root> ipcs -sb|grep oracle

s      67 0x00000000 --ra-r-----    oracle       dba   400 
s     334 0x00000000 --ra-r-----    oracle       dba   300

Next, let’s take a look at how we can examine UNIX system log messages.

Displaying System log Messages

In UNIX, a system failure will often precipitate an Oracle crash.  Any hardware associated with disk, CPU or RAM may cause an Oracle database crash, and the UNIX system logs can be used to identify the initial cause of the Oracle failure.

The following commands are used to display the UNIX error logs.  We need to note that you should regularly check the UNIX logs, even if there is not Oracle failure. These commands can also be useful for detecting transient disk I/O problems, memory failures, etc.

Show server log on HP/UX

In HP/UX we have a file in the /var/adm directory called syslog to hold all UNIX system messages.  In the command below we search the syslog for any lines that contain the word “error”.

root> grep –i error /var/adm/syslog/syslog.log|more

May  1 20:30:08 sprihp01 syslog: NetWorker media: (warning)
dev/rmt/c5t6d0BESTn reading: I/O error

Show server log on AIX

In the IBM AIX operating system we use the errpt command to display the contents of the system log.

root> errpt -a|more
---------------------------------------------------------------------------
LABEL:          CORE_DUMP
IDENTIFIER:     C60BB505

Date/Time:       Tue May  9 10:34:47
Sequence Number: 24908
Machine Id:      000138644C00
Node Id:         sp2k6n03
Class:           S
Type:            PERM
Resource Name:   SYSPROC

Again, checking the UNIX system logs should be a regular activity for the Oracle DBA. Next, let’s take a look at how UNIX can be monitored for performance problems.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.