Removing Semaphore and Memory for a crashed Oracle Instance

Sometimes we run into issue where database has not released its shared memory and semaphore because of abrupt shutdown or particular instance crashed. This becomes more critical when there are multiple instances running on the same machine. Unix provides ipcs command to see the semaphores and shared memory segments used by Oracle. Normally when oracle is shutdown, semaphore and shared memory segments are released.  To check run following command from $ prompt on the unix box.

$ipcs | grep oracle

If instance is up and running or instance has gone down but memory and semaphores are not released you will see some entries there. It will have two segments. Shared memory (denoted by m) and Semaphores (denoted by s). If you are running single instance, it is very easy to release the semaphore and shard memory using following command.

$ipcrm -m id (id is the id displayed for memory in ipcs under memory section)
$ipcrm -s id (id is the id displayed for semaphore in ipcs under semaphore section)

You have to be extra careful when doing this. This is to be done only when instance is not up but memory and/or semaphore are not released. So please make sure that none of the oracle process is running at this time (which should be the case).

When there are multiple instances running on the same database server, it becomes more crucial to identify which shared memory and semaphore set needs to be killed. Here is the link to an article which explains this scenario in great detail.

http://www.oracleops-support.com/2017/12/determining-which-instance-owns-which.html

sysresv – ever heard of it ?

There is a little utility one can use for listing the operating resources ( semaphores, shared memory ) an oracle instance uses on a system. the nice thing about this utility is that you can use it even when the instance is down or crashed. this may be useful if some of the resources were not cleaned up and you need to identify which resources you may/must remove.

the documentation tells that this utility is available from 8i onwards.

"$ORACLE_HOME/bin/sysresv" command to give you the shared memory segments associated with a named instance.

$ORACLE_HOME/bin/sysresv --help
/opt/oracle/product/base/11.2.0.3/bin/sysresv: invalid option -- -
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 will be attempted to be deleted for a
   sid only if there is no currently running instance
   with that sid.

$ORACLE_HOME/bin/sysresv
IPC Resources for ORACLE_SID "DB114" :
Shared Memory:
ID KEY
2621449 0x00000000
2654218 0x00000000
2686987 0x3393b3a4
Semaphores:
ID KEY
262146 0x710dfe10
Oracle Instance alive for sid "DB114"