Removing the semaphores of a ‘KILL’ed oracle instance

There are times when i had performed a ‘shutdown immediate’, the database just hangs and doesn’t go any further displaying the message “Shutting down in progress”.  Its used to be test databases and i used to kill the pmon from the OS level. If we kill the oracle process from the OS level, there are chances that the semaphores there were used by the killed oracle instance might stay on to trouble you the next time you issue ‘startup’.  A friend of mine had faced this problem(few yrs back 🙂 ) and below is what he did

================================================================

This is the initial error

 

ORA-27102: out of memory

SVR4 Error: 22: Invalid argument

SQL>

 

Tue Aug  1 03:10:14 2006

Starting ORACLE instance (normal)

Tue Aug  1 03:10:14 2006

WARNING: EINVAL creating segment of size 0x000000004bc00000

fix shm parameters in /etc/system or equivalent

Tue Aug  1 03:12:49 2006

Starting ORACLE instance (normal)

Tue Aug  1 03:12:49 2006

WARNING: EINVAL creating segment of size 0x000000004bc00000

fix shm parameters in /etc/system or equivalent

 

so problem with shm parameters

-bash-3.00$ ipcs -sb

IPC status from <running system> as of Tue Aug  1 03:30:52 GMT+4 2006

T         ID      KEY        MODE        OWNER    GROUP NSEMS

Semaphores:

s   33554452   0xac5420   –ra-r—–  orakdev      dba   504

s        103   0x6d96508  –ra-r—–  orakdev      dba   504
-bash-3.00$ ipcs -b

IPC status from <running system> as of Tue Aug  1 03:42:55 GMT+4 2006

T         ID      KEY        MODE        OWNER    GROUP QBYTES

Message Queues:

T         ID      KEY        MODE        OWNER    GROUP      SEGSZ

Shared Memory:

m   16777269   0x5e9d71c0 –rw-r—–  orakdev      dba 1270874112

m         51   0x626417f8 –rw-r—–  orakdev      dba 1270874112

T         ID      KEY        MODE        OWNER    GROUP NSEMS

Semaphores:

s   33554452   0xac5420   –ra-r—–  orakdev      dba   504

s        103   0x6d96508  –ra-r—–  orakdev      dba   504

 

ORACLE_SID

ORACLE_HOME should be set

SQL> oradebug setmypid

Statement processed.

SQL> oradebug ipc

Information written to trace file.

SQL> sho parameter dump

bash-3.00$

bash-3.00$ cat devt_ora_11784.trc

Dump file /d02/oracle/kdevdb/9.2.0/admin/DEVT_pundevdb/udump/devt_ora_11784.trc

Oracle9i Enterprise Edition Release 9.2.0.6.0 – Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 – Production

ORACLE_HOME = /d02/oracle/kdevdb/9.2.0

System name:    SunOS

Node name:      pundevdb

Release:        5.10

Version:        Generic_118822-26

Machine:        sun4u

Instance name: DEVT

Redo thread mounted by this instance: 1

Oracle process number: 11

Unix process pid: 11784, image: oracle@pundevdb (TNS V1-V3)

 

*** 2006-08-01 03:47:28.384

*** SESSION ID:(12.26) 2006-08-01 03:47:28.382

Dump of unix-generic skgm context

areaflags            00000037

realmflags           0000000f

mapsize              00002000

protectsize          00002000

lcmsize              00002000

seglen               00400000

largestsize  00000000f8000000

smallestsize 0000000001000000

stacklimit           ff47ee17

stackdir                   -1

mode                      640

magic                acc01ade

Handle:               3181300 `/d02/oracle/kdevdb/9.2.0DEVT’

Dump of unix-generic realm handle `/d02/oracle/kdevdb/9.2.0DEVT’, flags = 00000000

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

  Total size 000000000006f9bc Minimum Subarea size 00000000

   Area  Subarea    Shmid      Stable Addr      Actual Addr

      0        0 16777269 0000000010000000 0000000010000000

                              Subarea size     Segment size

                          0000000000070000 000000004bc00000

 Area #1 `Variable Size’ containing Subareas 1-1

  Total size 000000004a000000 Minimum Subarea size 01000000

   Area  Subarea    Shmid      Stable Addr      Actual Addr

      1        1 16777269 0000000010070000 0000000010070000

                              Subarea size     Segment size

                          000000004af90000 000000004bc00000

 Area #2 `Redo Buffers’ containing Subareas 2-2

  Total size 0000000000a26000 Minimum Subarea size 00000000

   Area  Subarea    Shmid      Stable Addr      Actual Addr

      2        2 16777269 000000005b000000 000000005b000000

                              Subarea size     Segment size

                          0000000000a26000 000000004bc00000

 Area #3 `skgm overhead’ containing Subareas 3-3

  Total size 0000000000002000 Minimum Subarea size 00000000

   Area  Subarea    Shmid      Stable Addr      Actual Addr

      3        3 16777269 000000005ba26000 000000005ba26000

                              Subarea size     Segment size

                          0000000000002000 000000004bc00000

Dump of Solaris-specific skgm context

sharedmmu 00000001

shareddec        0

used region        0: start 0000000020000000 length 000000003c000000

Maximum processes:               = 500

Number of semaphores per set:    = 504

Semaphores key overhead per set: = 4

User Semaphores per set:         = 500

Number of semaphore sets:        = 1

Semaphore identifiers:           = 1

Semaphore List=

33554452

————– system semaphore information ————-

IPC status from <running system> as of Tue Aug  1 03:47:28 GMT+4 2006

T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP NSEMS   OTIME    CTIME

Semaphores:

s   33554452   0xac5420   –ra-r—–  orakdev      dba  orakdev      dba   504  3:47:22  3:30:07

s        103   0x6d96508  –ra-r—–  orakdev      dba  orakdev      dba   504  8:09:22  5:33:06

bash-3.00$

 

 

Delete from memory

ipcrm -m 33554452   

 

Also from Semophore

ipcrm -s 103

 

 

bash-3.00$ ipcs -b

IPC status from <running system> as of Tue Aug  1 03:52:10 GMT+4 2006

T         ID      KEY        MODE        OWNER    GROUP QBYTES

Message Queues:

T         ID      KEY        MODE        OWNER    GROUP      SEGSZ

Shared Memory:

m   16777269   0x5e9d71c0 –rw-r—–  orakdev      dba 1270874112

m         51   0x626417f8 –rw-r—–  orakdev      dba 1270874112

T         ID      KEY        MODE        OWNER    GROUP NSEMS

Semaphores:

s   33554452   0xac5420   –ra-r—–  orakdev      dba   504

s        103   0x6d96508  –ra-r—–  orakdev      dba   504

bash-3.00$

 

 

ipcrm -m 16777269  

 

(If i try to start here will give me

SQL*Plus: Release 9.2.0.6.0 – Production on Tue Aug 1 03:54:21 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

ORA-27102: out of memory

SVR4 Error: 22: Invalid argument

Disconnected

)

 

ipcrm -s 33554452  

 

 

Now should be fine as

 

bash-3.00$ ipcs -b

IPC status from <running system> as of Tue Aug  1 03:55:27 GMT+4 2006

T         ID      KEY        MODE        OWNER    GROUP QBYTES

Message Queues:

T         ID      KEY        MODE        OWNER    GROUP      SEGSZ

Shared Memory:

m         51   0x626417f8 –rw-r—–  orakdev      dba 1270874112

T         ID      KEY        MODE        OWNER    GROUP NSEMS

Semaphores:

s        103   0x6d96508  –ra-r—–  orakdev      dba   504

bash-3.00$

bash-3.00$

gives nothing

 

on starting the DB starts OK .

 
 

SVRMGR>oradebug help

   HELP          [command]                 Describe one or all commands

   SETMYPID                                Debug current process

   SETOSPID      <ospid>                   Set OS pid of process to debug

   SETORAPID     <orapid> [‘force’]        Set Oracle pid of process to debug

   DUMP          <dump_name> <level>       Invoke named dump

   DUMPSGA       [bytes]                   Dump fixed SGA

   DUMPLIST                                Print a list of available dumps

   EVENT         <text>                    Set trace event in process

   SESSION_EVENT <text>                    Set trace event in session

   DUMPVAR       <p|s|uga> <name> [level]  Print/dump a fixed PGA/SGA/UGA

variable

   SETVAR        <p|s|uga> <name> <value>  Modify a fixed PGA/SGA/UGA variable

   PEEK          <addr> <len> [level]      Print/Dump memory

   POKE          <addr> <len> <value>      Modify memory

   WAKEUP        <orapid>                  Wake up Oracle process

   SUSPEND                                 Suspend execution

   RESUME                                  Resume execution

   FLUSH                                   Flush pending writes to trace file

   TRACEFILE_NAME                          Get name of trace file

   CORE                                    Dump core without crashing process

   IPC                                     Dump ipc information

   UNLIMIT                                 Unlimit the size of the trace file

   PROCSTAT                                Dump process statistics

   CALL          <func> [arg1] … [argn]  Invoke function with arguments

 
 

 SETOSPID      <ospid>                   Set OS pid of process to debug  (like pmon id(99% crashes the system if do this but if it is already crashed and you want to see then use this ) )

 oradebug ipc

================================================================

 

 

 

 

Advertisements

2 thoughts on “Removing the semaphores of a ‘KILL’ed oracle instance

  1. Pingback: ipc oracle

  2. Pingback: Index « My confrontations with oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s