Tuesday, April 10, 2012

How to "kill Semaphore of Oracle Instance" / "Remove Shared memory of Oracle Instnace"

We had Oracle instance running , Pmon of Instance was also visible .
While connecting to Oracle database it looked Normal , But while query V$database.It throws error.

So I had take look into Tail of alert log , Instance was cleanly shutdown. On Attempt to startup it gave below error.

ORA-01081: cannot start already-running ORACLE - shut it down first

So,

  • PMON is visible,
  • Alertlog shows clean shutdown of instnace last time.
  • you cant query even v$database or v$instnace.

This whole scenario leads to conclusion that semaphore of Instance is still locked.

And here we go .

To see how many Oracle symaphores/Shared memory are running.
ipcs | grep oracle
m  916291602 0xc0a8ed2c --rw-rw----    oracle       dba
m  154730522 0xee8c7308 --rw-rw----    oracle       dba
m   34111516 0x8fe194f8 --rw-rw----    oracle       dba
m  975011880 0xa1b69514 --rw-rw----    oracle       dba
s  237666346 0xe0695054 --ra-ra----    oracle       dba
s 1828749356 0x74616ad0 --ra-ra----    oracle       dba
s 1802698797 0xf9afd36c --ra-ra----    oracle       dba
s 1491337262 0xf40cfa70 --ra-ra----    oracle       dba

To identify which symaphore is connected to which Oracle instance .

echo $ORACLE_SID
VVCBDMQ1
sysresv is command to find symaphore attached to Instance.It will also shows shared memory address.

sysresv
IPC Resources for ORACLE_SID "VVCBDMQ1" :
Shared Memory:
ID              KEY
34111516        0x8fe194f8     
Semaphores:
ID              KEY
237666346       0xe0695054
Oracle Instance not alive for sid "VVCBDMQ1"

To confirm do as below

ipcs -a | grep oracle | grep  | grep 

ipcs -a | grep oracle | grep 34111516 | grep 0x8fe194f8
ipcs -a | grep oracle | grep 237666346 | grep 0xe0695054

To clean up symaphore.

ipcrm -s 34111516   <- ID
ipcrm -M 0x8fe194f8 <- key 
Check again to confirm it.
sysresv
IPC Resources for ORACLE_SID "VVCBDMQ1" :
Shared Memory
ID              KEY
No shared memory segments used
Semaphores:
ID              KEY
No semaphore resources used
Oracle Instance not alive for sid "VVCBDMQ1"

No comments:

Post a Comment