Identifying Shared Memory Segments

Posted at Monday, June 27, 2005
I was working on a database server that had an inadequate amount of swap space configured and I tried to start an instance that would not be accomodated, considering the remaining host resources. My startup command hung and I had to kill the session from another terminal. The problem was that there was not enough host resources to properly clean-up my process, including the shared memory segments allocated therein. Oracle tells me that this is the behavior with the -9 option, to the kill command as well: that it won't deallocate the shared memory segment, etc. In other words, there was a shared memory segment assigned to an instance that I in essence had killed. To release the hold on host resources, particularly reservable swap, we need to be able to identify and remove these resource assignments.

There are a couple of methods to do this, but I prefer identifying all the shared memory segments associated with known instances and then removing the remaining shared memory segments via the process of elimination.

Method 1 (Process of elimination):

1. Identify all running database instances on the host:

$ ps -ef | grep pmon


2. Source each instance one-by-one:

$ . oraenv testdb


3. Run the sysresv command and annotate the shared memory segment information:

$ sysresv

IPC Resources for ORACLE_SID "testdb" :
Shared Memory:
ID KEY
210958 0xdd8c0854
Semaphores:
ID KEY
33013805 0xaf2e3088
Oracle Instance alive for sid "testdb"


4. Confirm that information in #3 is accurate:

$ ipcs -a | grep oracle | grep 210958 | grep 0xdd8c0854
m 210958 0xdd8c0854 --rw-r----- oracle dba oracle dba 9 191799296 17403 17554 11:11:00 11:11:00 11:09:43

$ sqlplus "/ as sysdba"

SQL> select count(*) from v$session;

COUNT(*)
----------
10


As you can see the shared memory segment is shown as active via ipcs, it shows 9 attached processes: I confirm this (I can do so since I am using dedicated connections only) by selecting all from v$session via sqlplus, it returns 10, which is the 9 attached processes plus my new dedicated connection from sqlplus, equaling 10.

5. Issue the ipcrm -s <semaphore id> for each row returned from ipcs -a | grep oracle, which does not correspond to that which you verified in #4

Method 2 (Directly identify the orphans):

1. Identify the active shared memory segments owned by oracle that have no attached processes; assume that this is the subset to remove:

ipcs -am | grep no-entry | grep oracle | awk '{ tmp = length($1); print substr($1, 2,tmp);}'


2. Remove all the semaphore ids returned from #1 via the ipcrm -s <sempahore id> command

Method 3 (Process of elmination alternative):

1. See Method 1, Step 1

2. See Method 1. Step 2

3. Run the oradebug ipc command:

$ sqlplus "/ as sysdba"

SQL> oradebug ipc
Information written to trace file.


Oracle gives the following query to identify the name of the trace file, under user_dump_dest, where this information is written:

SELECT instance_name || '_ora_' || p.spid || '.trc'
FROM v$instance i, v$process p, v$session s, v$parameter p
WHERE s.paddr = p.addr
AND s.sid IN ( SELECT DISTINCT sid FROM v$mystat )
AND p.name = 'user_dump_dest';


Obtain the semaphore information by reading this file:

$ more tracefile


From this output obtain the shmid for each instance

4. See Method 1. Step 4, except in this case, grep for the shmid only

5. See Method 1. Step 5, except in this case, issue the ipcrm -m <shmid> for each row in ipcs -m | grep oracle, not identified in #4

You may want to reference Metalink Article 68281.1.

Labels: