Tuesday, March 22, 2016

RDBMS-- Listener Logs, __jdbc__, parsing the listener log file

Oracle 's listener log files include jdbc connection records, as well.
However; as for the jdbc connections, HOST parameter in the CONNECT_STRING shows __jdbc__ when the client connects to the database using the Jdbc thin driver.
So, in order to determine the the real host name of these jdbc clients, we should use the info recorded in the PROTOCOL_INFO.

Example Listener log record:

16-JUN-2015 13:55:26 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=ERMAN))(SERVICE_NAME=CLONE)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.32.234)(PORT=52172)) * establish * CLONE * 0

So, when we interpret ;

TIMESTAMP = 16-JUN-2015 13:55:26
*
CONNECT DATA= (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=ERMAN))(SERVICE_NAME=CLONE))
PROTOCOL INFO = "(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.32.234)(PORT=52172)) "

So, if we need to find count of these jdbc connections grouped by their hosts(note that, this kind of work may be needed in a migration project, for determining the dependencies/the clients or application servers using the database), we can use a linux command like below;

Example Sort (Linux bash) command regexp etc.. (parsing listener.log file, pattern may change according to the dbms version)
--------------------------------------------------------------------------

[oracleerman@demoorcl trace]$ cat listener.log |grep "__jdbc__" | awk '{print $8}' | grep ADDRESS | sed 's/^.*\((HOST.*\)/\1/g'|sed 's/.PORT=.*//g'| sort | uniq -c

225791 (HOST=10.10.32.234)
22 (HOST=10.123.36.34)
42 (HOST=10.123.36.23)
7 (HOST=10.123.36.22)
45 (HOST=10.123.36.20)
30 (HOST=10.123.36.24)
12 (HOST=10.123.36.33)
7 (HOST=10.123.36.55)
29 (HOST=10.123.36.66)
21 (HOST=10.123.36.89)

1 comment :

  1. unnecessary use of cat. Try:

    grep "__jdbc__" listener.log| awk '{print $8}' | grep ADDRESS | sed 's/^.*\((HOST.*\)/\1/g'|sed 's/.PORT=.*//g'| sort | uniq -c

    ReplyDelete

If you will ask a question, please don't comment here..

For your questions, please create an issue into my forum.

Forum Link: http://ermanarslan.blogspot.com.tr/p/forum.html

Register and create an issue in the related category.
I will support you from there.