Scared of shutdown abort?

Identifying Which Instance Owns Which Shared memory and Semaphore Segments

Sometimes it happens that after a crash or after an abnormal shutdown the oracle database instance does not release the shared memory or semaphores.

And if you do not know how to find out the unreleased shared memory or semaphores, this how you do it.

How to find which instance owns which shared memory and semaphores

On a Unix machine give the following commands

table1

$ ipcs -b

IPC status from /dev/mem as of Mon Dec 23 11:47:25 IST 2002

T ID KEY MODE OWNER GROUP QBYTES

Message Queues:

q 0 0x4107001c --rw-rw---- root printq 4194304

T ID KEY MODE OWNER GROUP SEGSZ

Shared Memory:

m 11 0x00001515 --rw-rw-rw- root system 16

m 12 0x435dce60 --rw-rw-rw- root system 8024

m 13 0x0d01c353 --rw-rw-rw- root system 1440

m 179306510 0xffffffff D-rw------- ias9 dba 1052672

m 20709394 0xec8bc6b4 --rw-r----- ora8i732 dba 328286208

m 39452692 0x62a7d5a4 --rw-r----- oracle9i dba 520896512

m 64487446 0x076d8fb0 --rw-r----- oracle9i dba 437010432

m 56492055 0x02fa8d7c --rw-r----- ora8i732 dba 293888000

m 28442648 0xbe0bab6c --rw-r----- oracle9i dba 520896512

m 4325402 0xc4d72a50 --rw-r----- oracle9i dba 470073344

m 262171 0xffffffff D-rw-r----- oracle9i dba 470073344

m 19005482 0x493f947c --rw-r----- oracle9i dba 486850560

T ID KEY MODE OWNER GROUP NSEMS

Semaphores:

s 393216 00000000 --ra-ra-ra- imnadm imnadm 4

s 1 0x6201c1b8 --ra-r--r-- root system 1

s 11 0x580038a1 --ra-ra-ra- root system 1

The above table shows the output of the ipcs –b command. This displays the shared memory and the semaphores.

Now how to know which one to kill?

Say the instance ORCL has not released the memory or semaphores.

Do the following

$ export ORACLE_SID=ORCL

$ svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

Oracle8i Release 8.1.7.0.0 - Production

JServer Release 8.1.7.0.0 - Production

SVRMGR> connect internal

Connected.

SVRMGR> oradebug ipc

Information written to trace file.

SVRMGR> exit

Server Manager complete.

$

In case you get the error

SVRMGR> oradebug ipc

ORA-00074: no process has been specified

SVRMGR>

Then find pid of a background process.

$ ps –ef|grep smon [or pmon]

oracle9i 278798 1 0 11:57:26 - 0:00 ora_smon_FFSYSHCM

ora8i732 288438 1 0 Dec 21 - 0:02 ora_smon_CSSREP1

oracle9i 315834 1 0 11:49:58 - 0:00 ora_smon_FF4HCM

oracle8i 367370 1 0 11:47:41 - 0:00 ora_smon_PGUATOMR

ora8i732 25398 1 0 Dec 21 - 0:32 ora_smon_ORCL

This will give the pid of the instance ORCL which is 25398

SVRMGR> oradebug setospid 25398

SVRMGR> oradebug ipc

Information written to trace file.

SVRMGR> exit

Server Manager complete.

$

This will write down a trace file in your $ORACLE_BASE/admin/ORCL/udump

Extract from the trace file.

Table2

"ora_48170_orcl.trc" 78 lines, 3673 characters

/ora8i732/app/product/admin/ORCL/udump/ora_48170_orcl.trc

Oracle8i Release 8.1.7.0.0 - Production

JServer Release 8.1.7.0.0 - Production

ORACLE_HOME = /ora8i732/app/product/8.1.7

System name: AIX

Node name: tcs6m1

Release: 3

Version: 4

Machine: 000F2F7F4C00

Instance name: ORCL

Redo thread mounted by this instance: 1

Oracle process number: 8

Unix process pid: 48170, image: oracle@tcs6m1 (TNS V1-V3)

*** 2002-12-23 11:46:31.286

*** SESSION ID:(7.1) 2002-12-23 11:46:31.207

Dump of unix-generic skgm context

areaflags 00000037

realmflags 0000000f

mapsize 00001000

protectsize 00001000

lcmsize 00001000

seglen 00001000

largestsize 00000000c0000000

smallestsize 0000000000400000

stacklimit 0

stackdir -1

mode 640

magic acc01ade

Handle: 2011d3d8 `/ora8i732/app/product/8.1.7ORCL'

Dump of unix-generic realm handle `/ora8i732/app/product/8.1.7ORCL', flags = 00000000

Area #0 `Fixed Size' containing Subareas 0-0

Total size 0000000000011f94 Minimum Subarea size 00000000

Area Subarea Shmid Stable Addr Actual Addr

0 0 56492055 0000000030000000 0000000030000000

Subarea size Segment size

0000000000012000 0000000011846000

Area #1 `Variable Size' containing Subareas 2-2

Total size 0000000006a99000 Minimum Subarea size 00100000

Area Subarea Shmid Stable Addr Actual Addr

1 2 56492055 000000003ad10000 000000003ad10000

Subarea size Segment size

0000000006b00000 0000000011846000

Area #2 `Database Buffers' containing Subareas 1-1

Total size 000000000acfe000 Minimum Subarea size 00002000

Area Subarea Shmid Stable Addr Actual Addr

2 1 56492055 0000000030012000 0000000030012000

Subarea size Segment size

000000000acfe000 0000000011846000

Area #3 `Redo Buffers' containing Subareas 3-3

Total size 000000000002a000 Minimum Subarea size 00000000

Area Subarea Shmid Stable Addr Actual Addr

3 3 56492055 0000000041810000 0000000041810000

Subarea size Segment size

000000000002a000 0000000011846000

Area #4 `Lock Manager' containing Subareas 5-5

Total size 0000000000002000 Minimum Subarea size 00000000

Area Subarea Shmid Stable Addr Actual Addr

4 5 56492055 0000000041842000 0000000041842000

Subarea size Segment size

0000000000002000 0000000011846000

Area #5 `Java' containing Subareas 4-4

Total size 0000000000008000 Minimum Subarea size 00000000

Area Subarea Shmid Stable Addr Actual Addr

5 4 56492055 000000004183a000 000000004183a000

Subarea size Segment size

0000000000008000 0000000011846000

Area #6 `skgm overhead' containing Subareas 6-6

Total size 0000000000002000 Minimum Subarea size 00000000

Area Subarea Shmid Stable Addr Actual Addr

6 6 56492055 0000000041844000 0000000041844000

Subarea size Segment size

0000000000002000 0000000011846000

Dump of AIX-specific shared memory info

Number of internal AIX shared memory segments 1

Segment Shmid Size Attach Addr

1 56492055 293888000 30000000

Maximum processes: = 315

Semaphore identifiers:

393216

From table 1 and table 2 you can see that 56492055 is the shared memory that is no longer required.

Use the ipcrm –m to remove the shared memory segment.

$ ipcrm –m 56492055

Similarly you can remove the semaphores

$ ipcrm –s 393216.

Or

To find the Shared Memory id and Semaphore id you can use:

$ORACLE_HOME/bin/sysresv

IPC Resources for ORACLE_SID "ORCL" :

Shared Memory:

ID KEY

56492055 0x8a85a74c

Semaphores:

ID KEY

393216 0x4bd4814c

Oracle Instance alive for sid "ORCL"

This will save lot of your valuable time when an instance crashes and the shared memory and semaphore is not released, preventing you from starting up the database again.

HTH

Regards

PS:

or simply do

ipcrm -m `ipcs -map|awk '$9==0 {print $2}'`

$9 gives NATTACH (The no of processes attached to shared memory segment).

For the killed instance this value is 0

$2 gives the shared memory key

HAPPY LEARNING!