4

During development I found that database have large number of lived connections by:

SELECT username, COUNT(*) FROM v$session GROUP BY username;

In order to find who actually hold connection I want to get a list of IP addresses.

During general web search and reading official docs I build query:

SELECT username, seconds_in_wait, machine, port, terminal, program, module, service_name
  FROM v$session
  WHERE type = 'USER';

where machine is most important part of select. But unfortunately machine field shows host name known by client OS.

Internet full of recommendation to use UTL_INADDR.GET_HOST_ADDRESS which is not applicable in my case. Firstly because of ORA-24247: network access denied by access control list (ACL) and secondly because client OS host name usually defined in /etc/hostname and doesn't available to DNS server in our intranet...

Any other way to retrieve IP of open session to Oracle DB (DB instance hold information about its sockets in any case...).

UPDATE

I under trusted intranet but with unknown network hierarchy.

And I try to find which applications use my tables (several app-servers, I don't know all of them). Some of them overuse connections and need to be fixed. But firstly they should be identified...

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
gavenkoa
  • 45,285
  • 19
  • 251
  • 303
  • You can query `sys_context('USERENV', 'IP_ADDRESS')`, but only within the user's session - so not helpful for what you're trying to do I think, unless you're able to add a logon trigger that logs the session info including that value to your own audit table? – Alex Poole Feb 25 '13 at 14:38
  • Hm... I hope that Oracle have some built-in solution which easy to query... – gavenkoa Feb 25 '13 at 14:46
  • whats wrong with getting privs to ACL? – tbone Feb 25 '13 at 14:47
  • Currently I found this blog article about auditing into Oracle DB: http://aprakash.wordpress.com/2009/11/06/auditing-ip/ Seems that I have no rights to make such thing on DB... – gavenkoa Feb 25 '13 at 14:47
  • this isn't about auditing, but security (network access) in general. Ask your dbas to setup for you, or there may already exist a user with this ability already. A good article here: http://www.oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1.php – tbone Feb 25 '13 at 14:53
  • @tbone OK, agree about security option. But I still in issue because `machine` filed hold **hostname** from *client OS* which is not possible to resolve by DNS server. So I need for another source for getting IP... – gavenkoa Feb 25 '13 at 14:58
  • if Oracle can't lookup the IP, not sure how you'd get it. If you really need the IPs for your situation, you might ask to have Oracle server setup with DNS properly (i believe it uses nslookup to resolve ip, again check with dbas). A last resort if dns was not allowed is to hack an etc/hosts file or create your own mapping table of machine->IP – tbone Feb 25 '13 at 16:23
  • Hm... Strange that Oracle store **user supplied untrusted** *hostnames* and don't store **real IP**. I change my `/etc/hostname` to "Super-puper" and get it in `machine` field after connecting by `sqlplus` from my host. I hope in last option, that somewhere Oracle store **real IP**... In any case thank for interest to my question!! – gavenkoa Feb 25 '13 at 16:35
  • Typical blog article with solution wrong to me: http://neeraj-dba.blogspot.com/2011/09/identify-ip-addresses-and-host-names.html http://baliyansandeep.blogspot.com/2010/01/oracle-how-to-get-ip-address-of-client.html – gavenkoa Feb 25 '13 at 16:38
  • final thought: what do you intend to do with the IP? Remember that IPs can easily be spoofed, and proxies, firewalls, etc can make this less than reliable. So, you have a session id and a user/machine, what will IP do for you that you don't already have? – tbone Feb 25 '13 at 17:12
  • @tbone I under trusted intranet but with unknown network hierarchy. And I try to find which applications use my tables (several app-servers, I don't know all of them). Some of them overuse connections and need to be fixed. But firstly they should be identified)... – gavenkoa Feb 25 '13 at 19:14
  • @gavenkoa - do all the app servers use the same credentials to connect? It might be better anyway if they had their own (they can all have the same permissions via roles), then you'd know for sure who's doing what from the username? And I'm kind of hoping they're using connection pooling, though the question maybe suggests they are not. – Alex Poole Feb 25 '13 at 19:17

5 Answers5

5

Bear in mind that the Oracle session doesn't need to know, and certainly doesn't need to trust, the client name/IP address you're coming from; it's sitting above the network transport layer, and doesn't really care if you're connected over TCP/IP or something else. (I'm not even sure if the listener has to pass the info across, or if it effectively passes a ready-made socket). As you've seen the machine is just what the client declared, like program and other fields in the v$session view; it may not bear any resemblance to anything that DNS or your server's /etc/hosts can resolve, particularly if the client is a Windows box.

What you could do is, at Unix/Linux level (since you refer to /etc/hosts, I assume you aren't on Windows), look for the port and see what address that shows; for example v$session shows my port as 50527, so if I do netstat -an | grep 50527 I see:

tcp  0  0  192.168.1.1:1521  192.168.1.23:50527  ESTABLISHED

So I can see I'm connected from 192.168.1.23. You can do that with a host command if you're running SQL*Plus on the server, but it's still a bit inconvenient. If you needed to do this regularly, and adding a logon trigger to capture it to an audit table isn't an option, and you really had to do it from within the database you could probably write a Java stored procedure to do the lookup from that port for you. But it's probably easier to write a shell script to query the port numbers from v$session and do the lookup that way round.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Do I understand you correctly that you suggest to run `netstat` on Oracle server? I just don't have access to it (DBA will be laughed when I ask him for such permission...). Thank for interest to my question! +1 – gavenkoa Feb 25 '13 at 19:09
  • @gavenkoa - yes, on the server; I take it from that that you are not running SQL*Plus on the server either. You may be able to get permission to run Java in the database to get that info, but maybe not. I'm not sure I understand what your ultimate goal is, why the number of live connections matters, or what you will do with the IPs. Maybe the DBAs can monitor this (if you provide a script!?) and send you the output you need, especially if it's a one-off investigation? – Alex Poole Feb 25 '13 at 19:15
  • @AlexPloole I try to find servers that cause connection leaking. Seems that job of DBA, not mine. Thanks again for explanation! – gavenkoa Feb 25 '13 at 19:20
2

Thanks to all for digging into my question (which is still general purpose, not exactly mine!!).

Just short answer: you can't get real IP from Oracle system tables.

For this tasks you can use general purpose utilities like netstat or lsof -p <pid-of-oracle> but only on server side!

Some help can come from v$session.port values...

One good suggestion from helpers - use good names from DB clients. They are populated to v$session table rows:

machine, terminal, program, module, service_name

so they can help to identify clients...

gavenkoa
  • 45,285
  • 19
  • 251
  • 303
2

The IP address of an incoming connection can usually be found in your Listener log. That is how I track such information when I need to.

Jesus Ramos
  • 22,940
  • 10
  • 58
  • 88
Ken Banyas
  • 21
  • 1
1

I've found my auditing script from 2001 when logon triggers didn't exist AFAIK. It parses the COMMENT$TEXT column of SYS.AUD$ where ACTION#=100 to get the IP address of the client. That column still contains Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=a.a.a.a)(PORT=p)) in our environments.

T-Gergely
  • 472
  • 5
  • 13
0

Try this in your select statements (i.e., from v$session):

utl_inaddr.get_host_address(substr(machine,instr(machine,'\')+1)) ip

Sorry, I don't have the source, but it worked for me.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Tim in NYC
  • 1