Linux/Oracle -- Find Client IP connected to a Shadow process directly from OS

As known; when you use Oracle Database on Unix/Linux OS, clients are connected to server processes called shadow processes. These shadow processes work on behalf of the clients and make database operations.. (there are shared servers, too. Finding the client ip in a shared server is another story)

The connection between the client computers and these shadow processes are  based on the Tcp sockets.
So if we trace a shadow process id all the way down from the pid to related socket, we can gather the remote ip , actually the ip address that belongs to the client.

By working on this info, we can understand who is connected to our database without even connecting to Oracle Instance..
This is also very helpful for Linux admins in diagnosing the performance of a Linux System that runs a critical Oracle Database.


Lets see,
First we identify the process that we want to find the associated client's remote ip address..

We can use top or ps for this...

top ->
2758 oraprod   18   0 55.2g  46m  34m R 100.0  0.0   6:03.32 oraclePROD (LOCAL=NO)

so it seems like the process with pid(Linux process id) 2758, consumes Cpu resources aggresively..

Lets go to /proc file system, and see the file descriptors of the process.. Note that the sockets have file descriptors, too..

cd /proc/2758/fd
ls -al |grep socket
lrwx------ 1 oraerman oinstall 64 Dec 27 11:31 12 -> socket:[1764624]

So we have found the socket number , it is 1764624..

Now, we ll go to /proc/net to find out the socket information..
We use the file tcp as it s a tcp socket;

cd /proc/net;
cat tcp | grep 1764624
 498: 9000050A:0623 8A00050A:3780 01 00000000:00000000 00:00000000 00000000   500        0 1764624 3 ffff81128e130c80 203 40 1 8 100

So we have gathered the socket information.
The information gathered here is in the format below..

46: 010310AC:9C4C 030310AC:1770 01 

   |      |      |      |      |   |--> connection state

   |      |      |      |      |------> remote TCP port number

   |      |      |      |-------------> remote IPv4 address

   |      |      |--------------------> local TCP port number

   |      |---------------------------> local IPv4 address

   |----------------------------------> number of entry

The remote IPV4 address in here is the ip address of the connected client. This is what we want to find. Also, there are port state and etc. information , but we dont need those...

498: 9000050A:0623 8A00050A:3780 01 00000000:00000000 00:00000000 00000000   500        0 1764624 3 ffff81128e130c80 203 40 1 8 100   

So the Ip adress in Hex is : 8A00050A 

This is Hex in Little endian form.. So we need to reverse it before converting to the real ip address.

8E00050A ---reverse----> 0A05008A 

So if we convert 0A05008A to ip address, we find 10.5.0.138, which is the related client's real ip address!

---------------------

Note that; "Hex to Ip" conversion is  as follows:

0A . 05 . 00 . 8A -> 10 . 5. 0 . 138

I wil try to extend this OS information gathering method and try to implement these kind of methods programatically.