Finding Oracle allocated semaphores in Linux...

As I always say, there is always something to know/learn.

Having some issues with high usage of semaphores in our environment and digging more found this nice utility from Oracle (yeah its been there since 8.1 but today I came to know about it) called "sysresv".

If you have access to metalink then here is the ID#123322.1

BTW, you can still get all semaphores allocated by oracle with a simple command:
ipcs -s |grep oracle --This will list out all semaphores allocated for all instances on the server.

sysresv utility gives the list of semaphores only for the instance that you pointed to...

Once you get the semaphore id (semid) try below to get more info on that semid like process id (pid):
ipcs -si semid

This will give detailed info like below:


Semaphore Array semid=331382805
uid=80   gid=80  cuid=80         cgid=80
mode=0660, access_perms=0660
nsems = 128
otime = Tue Feb  5 16:45:46 2013
ctime = Tue Feb  5 16:45:46 2013
semnum     value      ncount     zcount     pid
0                 1          0             0          1315
1             18380      0             0          1315
2             18703      0             0          1315

From the above try to ps -ef |grep pid and see if this shows any OS process that is related to Oracle (in our case). If ps -ef does not return anything then that is a dead process and time clear that semaphore allocation using ipcrm -s semid.

Here is the info about this nice utility if you do not have metalink access:


SYSRESV Utility [ID 123322.1]

The sysresv utility included with Oracle 8.1.5 and above provides instance
status (and OS resources used) for specified ORACLE_SIDs.  This utility is
especially useful when multiple instances are running.  OS resources can be
removed using this utility if the specified instance is detected to be dead.

This utility may be useful when an instance has crashed or was aborted
and memory and semaphores related to this instance were not cleaned up
automatically.  This utility is also helpful in determining which instance
is running.

The sysresv utility, located in $O_H/bin, can be used from locations other
than $O_H/bin.

Point your environment to the instance of interest before using sysresv.

Usage:
------

sysresv:
usage   : sysresv [-if] [-d ] [-l sid1 ...]
          -i : Prompt before removing ipc resources for each sid
          -f : Remove ipc resources silently, oevrrides -i option
          -d : List ipc resources for each sid if on
          -l sid1 .. : apply sysresv to each sid
Default : sysresv -d on -l $ORACLE_SID
Note    : ipc resources are attempted to be deleted for a
          sid only if there is no currently running instance
          with that sid.

 
Examples:
---------

o  Instance is not running:

   /u02/app/oracle/product/8.1.7> sysresv

   IPC Resources for ORACLE_SID "R817" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "R817"


o  Instance is running:

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory:
   ID              KEY
   16437           0xe4efa8dc
   Semaphores:
   ID              KEY
   12320802        0x09d48346
   Oracle Instance alive for sid "X816"


o  Attempting to remove memory and semphores using sysresv when Oracle
   detects an instance is running:

   /u03/app/oracle/product/8.1.6> sysresv -f

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory:
   ID              KEY
   16437           0xe4efa8dc
   Semaphores:
   ID              KEY
   12320802        0x09d48346
   Oracle Instance alive for sid "X816"
   SYSRESV-005: Warning
           Instance maybe alive - aborting remove for sid "X816"


o  Removing IPC resources:

   [Sysresv shows memory and semaphores exist but Oracle determines the
    instance is not alive.  Cleanup is needed.]

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory:
   ID              KEY
   16837           0xe4efa8dc
   Semaphores:
   ID              KEY
   12714018        0x09d48346
   Oracle Instance not alive for sid "X816"


o  Removing IPC resources using sysresv:

   /u03/app/oracle/product/8.1.6> sysresv -i

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "X816"
   Remove ipc resources for sid "X816" (y/n)?y
   Done removing ipc resources for sid "X816"
   /u03/app/oracle/product/8.1.6


   Verify the resources were removed:

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "X816"

   
o  If you need to remove memory segments, and Oracle detects the
   instance is alive through sysresv:

   % ipcrm -m

   Where is the memory id shown in the sysresv output.

   Example:
   % ipcrm -m 16437

   If you need to remove semaphores, and Oracle detects the
   instance is alive through sysresv:

   % ipcrm -s

   where is the semaphore id shown in the sysresv output.

   Example:
   % ipcrm -s 12320802