Monday, March 21, 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