Beware of crashed Oracle Instance Semaphores and Memory!!

 Author: Adewri

Page: 1 2

Shared memory and semaphores are amongst the most important resources for an Oracle instance. In normal situations the semaphore and shared memory segments are released when oracle is shutdown. However being database administrator we mostly encounter the scenario where Oracle instance crashes abruptly and its shared memory and semaphore are not released. The situation becomes critical if you have many multiple instances running on the same machine as it is unclear which shared memory and semaphore sets to kill. If you are able to identify which shared memory and semaphore segments are owned by a particular instance then it can help you in recovery situations where the database instance has not released its shared memory and semaphores on database shutdown.

Identifying semaphores and shared memory segments:

The ipcs command can help you identify the semaphores and shared memory segments used by Oracle. You can run following command from $ prompt on the UNIX box. You will see some entries if your instance is up and running or the instance has gone but memory and semaphores are not released.

$ipcs | grep oracle

You will see two segments. The segment that represents shared memory is denoted by m whereas the segment that represents semaphores is denoted by s.

Releasing semaphores and shared memory segments for single instance:

If you are running single instance, it is very easy to release the semaphore and shard memory. You can run below commands for it.

$ipcrm -m id

In the above command id will be the id displayed for memory in ipcs under memory section.

$ipcrm -s id

In the above command id will be the id displayed for semaphore in ipcs under semaphore section.

$ ipcrm –m 76172057
$ ipcrm –s 505178

You can use below code to find the shared memory id and semaphore id.

$ORACLE_HOME/bin/sysresv
IPC Resources for ORACLE_SID "MYINSTANCE" :
Shared Memory:
ID KEY
56492055 0x8a85a74c
Semaphores:
ID KEY
393216 0x4bd4814c
Oracle Instance alive for sid "MYINSTANCE"

This will save your time and prevent you from starting up the database again when an instance crashes and the shared memory and semaphore are not released. However you must be careful that no other Oracle process should run at that time. This is to be done only when instance is not up but memory and/or semaphore are not released. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Inside Oracle 11g Virtual Columns!
   Amazing Tips for ORACLE_HOME Issues: Must See!!
   Oracle Security Guide: Is your Database Secure?
   Oracle – SQL: Performance Boost with Collections