Mapping a process in a remote server to a oracle session

Something we have probably needed many times is to find in a remote server the program that is running a session against our database.
Perhaps and for any reason,  we need to know exactly which concrete process in the remote server is accessing our database (from the
operating system perspective).
Let's say is a sqlplus prompt, but when we log on the remote system, we see that there are many sqlplus processes runnning. Which
is the right one?


Ok, here you are a little trick to discover it. Of course, you need access to both the database server as the server running the program.
(this works only on UNIX).


First, we get the process id of the oracle server process of the session in our database server:

Let's say that the offending session has an sid of 48.

SQL> select p.spid from v$session ses, v$process p
  2  where
  3  ses.paddr = p.addr and
  4  ses.sid = 48;

SPID
------------------------
9592

SQL>

Next, let's check the network data related to the process in our database server with netstat:

[oracle@dbserver ~]$ netstat -putan | grep 9592
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 ::ffff:192.68.252.174:1522  ::ffff:192.25.7.19:27882    ESTABLISHED 9592/oracleLAB
[oracle@dbserver ~]$

The output shows that process 9592 is accesses locally by means of port 1522 to the IP address 192.68.252.174 and comes from remote IP 192.25.7.19 through port 27882

Now, we access the remote server and run netstat there (we do not need root privileges to run netstat) and we do a grep to the port number we got in the previous step.:


[PRE] [asserver].root:/root > netstat -putan | grep 27882
tcp        0      0 192.25.7.19:27882           192.68.252.174:1522         ESTABLISHED 12431/java
[PRE] [asserver].root:/root >


We see that the result matches what we see in the previous point. In this server a java process with pid 121431 establishes a TCP connection from local IP 192.25.7.19 through
port 27882 to a remote server's IP 192.68.252.174 through port 1522.


Now, we only need to check the process using ps:

[PRE] [asserver].root:/root > ps -ef |grep 12431
weblogic 12431 12381  0 Nov28 ?        00:12:16 /opt/java_jdk/bin/java -server -Xms1024m -Xmx1024m -XX:MaxPermSize=256m -Dweblogic.Name=WL_0102 -Djava.security.policy=/opt/bea/weblogic/server/lib/weblogic.policy -verbose:gc -Xloggc:/logs/WLH_0102/GC/gc_28112014074536.log -DEntorno=WEBL -Djava.awt.headless=true -Djava.net.preferIPv4Stack=true -Djava.net.preferIPv6Addresses=false -Dweblogic.ProductionModeEnabled=true -da -Dplatform.home=/opt/bea/weblogic10.3 -Dwls.home=/opt/bea/weblogic10.3/server -Dweblogic.home=/opt/bea/weblogic10.3/server -Dweblogic.management.discover=false -Dweblogic.management.server=http://SVHASR03.lacaixa.es:7001 -Dwlw.iterativeDev=false -Dwlw.testConsole=false -Dwlw.logErrorsToConsole=false -Dweblogic.ext.dirs=/opt/bea/patch_wls1032/profiles/default/sysext_manifest_classpath -Djava.security.egd=file:/dev/./urandom -Dweblogic.wsee.skip.async.response=true weblogic.Server
root     29975 29723  0 14:21 pts/1    00:00:00 grep 12431
PRE] [asserver].root:/root >

And voilá!, we see that the offending process is a weblogic server process, whose data can be seen at the command line in this case...


Happy troubleshooting!