cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle 10g sid

SOLVED
Go to solution
tom quach_1
Super Advisor

Oracle 10g sid

Hi

if anyone knows how to get an oracle SID by using HP-UX process # as below:
PID=6314
by using
#lsof -p 6314 it does not tell much about any oracle SID
i am trying to relate Unix PID to and oracle SID but so far have not been able to.

Process Name PID PPID Pri Name ( 200% max) CPU IO Rate RSS Cnt
--------------------------------------------------------------------------------
oracleSUN2 6314 1 240 oracle 63.6/50.8 846.8 0.0/ 0.0 53.8mb 1

Thanks in advance.
Tom
14 REPLIES
Eric Antunes
Honored Contributor

Re: Oracle 10g sid

Hi Tom,

From the process name I would tell that the instance SID is "SUN2".

Best Regards,

Eric
Each and every day is a good day to learn.
Piergiacomo Perini
Trusted Contributor

Re: Oracle 10g sid

Hi Tom,

maybe (in other Oracle version is this) "spid" from "v$process" is egual to PID.

hth
regards
pg
Piergiacomo Perini
Trusted Contributor

Re: Oracle 10g sid

_again_

with spid = to >PID you can find sid (session id) if you are refering to this and not to SID (system id, if i don't go wrong).

regards
pg
Yogeeraj_1
Honored Contributor

Re: Oracle 10g sid

hi Tom,

try:
ps -ef|grep pmon

$ ps -ef|grep pmon
oracle 20327 1 0 Apr 12 ? 25:45 ora_pmon_sun2
oracle 11853 5046 0 13:22:44 pts/te 0:00 grep pmon
$

This is one easy way from which you can see that the SID=sun2

hope this helps too!
kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Patti Johnson
Respected Contributor

Re: Oracle 10g sid

Tom,

Run this query from sqlplus.
select spid, s.sid, p.serial#, s.terminal, pid, machine, s.program, osuser,
p.program
from v$session s, v$process p
where addr = paddr and (spid like
'%&&OSID%' or process like '%&OSID%');

It will prompt you for the osid and respond with the SID and additional information about the session.

Patti
Hein van den Heuvel
Honored Contributor

Re: Oracle 10g sid

pg, patti, How do you propose to connect to the right instance, if you don't know the SID?

Tom, what's behind the question?
It is is high resource consuming process?
The simple answer would seem to be to just strip 'oracle' fron the process name.
You might want to correlate that with the existance of a _pmon_ process.

fwiw,
Hein.
Oviwan
Honored Contributor

Re: Oracle 10g sid

Hey

Which PID have you?

if it's the one of the pmon process use:

$ps -ef | grep ${PMON_PID} | grep -v grep | awk '{print substr($NF,10)}'

Regards
Piergiacomo Perini
Trusted Contributor

Re: Oracle 10g sid

Hi all,

Hein you are right , i assume that Tom knows the istance name / SID ...
but if this is not so then yogeeraj's (or eric's) method is good:
SID=SUN2.

hth
regards
pg
tom quach_1
Super Advisor

Re: Oracle 10g sid

Thank you all for your replies.
Sorry for the confusing. what i meant was the
Oracle process ID (SID from v$session)and not
oracle instance.
if i have PID from Unix command by using
#ps -ef|grep ora is there a way to trace this PID to oracle process ID?

Thanks,
Tom
Sandman!
Honored Contributor

Re: Oracle 10g sid

>#ps -ef|grep ora is there a way to trace this PID to oracle process ID?

Do you mean the Oracle program/process that launched this shadow process. Since the process you are looking at is the Oracle server process aka shadow process which is forked by the Oracle listener process to service an incoming client connection. This will also be the process reported in the SPID column of the v$session table.

~hope it helps
spex
Honored Contributor
Solution

Re: Oracle 10g sid

Hi Tom,

Save the following script to a file, set the execute bit, and pass the OS pid of interest as the first argument:

#!/usr/bin/sh
if [ ${#} -lt 1 ]
then
echo "Usage: $(basename ${0}) "
exit 1
fi
# Set up Oracle environment
. oraenv
UN=myuser
PW=mypass
OSPID=${1}
sqlplus -S "${UN}/${PW}" << _EOF_
SELECT s.sid
FROM v\$session s, v\$process p
WHERE s.paddr = p.addr
AND p.spid=${OSPID};
exit;
_EOF_
exit 0

PCS
tom quach_1
Super Advisor

Re: Oracle 10g sid

Thanks Spex for the script.

this script works, However, i would like to know if these "ps" or "lsof" will work for the same purpose without using sqlplus.

Thanks,
Tom
Sandman!
Honored Contributor

Re: Oracle 10g sid

No lsof or ps won't give the internal Oracle system identifier (SID) associated with each session. That can be obtained only using sqlplus.

~hope it helps
tom quach_1
Super Advisor

Re: Oracle 10g sid

Thank you all for your help.
Spex's script works for my question.

Thanks again
Tom