Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :- ajith.narayanan@infrastack-labs.in

Wednesday, September 12, 2012

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!

1 comment:

  1. thanks for posting this scenario's , i wasted lot my time earlier by waiting for memory clean up after shutdown abort ,keep it up... :)

    ReplyDelete

Thanks for you valuable comments !