How to Trace the Network Packets Exchanged Between JDBC and the RDBMS (Doc ID 1050942.1)
Applies to:Oracle Net Services - Version 11.2.0.3 and laterJDBC - Version 11.2.0.3.0 and later Oracle Database - Enterprise Edition - Version 11.2.0.3 and later Information in this document applies to any platform. GoalThis note discusses how to trace what is sent and received between the Oracle JDBC Driver release 11.2 and up and the Oracle database. SolutionIn this 6 minute video, you will learn about JDBC logging and tracing A Quick Video on How to Enable Oracle JDBC Logging & Packet Tracing(Doc ID 2975841.1) Direct Link
From release 11.2, the JDBC Thin Driver includes a network trace capability. Hence this note is applicable to JDBC 11.2 and above versions. This capability allows you to trace the network packets that the driver exchanges with the server. To generate network level trace, one need the following - Oracle debugging JDBC driver (e-g ojdbc6_g.jar, ojdbc7_g.jar, ojdbc8_g.jar) - Specify the logging properties and point the location at the run time (e-g Logging.properties) - Enable the logging flags (e-g java -Doracle.jdbc.Trace=true -Djava.util.logging.config.file=<location>\Logging.properties NTraceSample)
If you already have the logging properties. Add the following line into your config file in addition to the other configuration information: oracle.net.ns.level = [OFF/SEVERE/WARNING/INFO/CONFIG/FINE/FINER/FINEST/ALL] For example: .level=SEVERE # Following line enables the Network packet tracing oracle.jdbc.diagnostics.DemultiplexingLogHandler.pattern = sqlnet_%s.log
For JDBC logging one has to use ojdbcX_g.jar file, Example ojdbc5_g.jar - Same as ojdbcX.jar, except that classes were compiled with "javac -g" and contain tracing code.
Jan 19, 2010 9:25:35 PM oracle.net.ns.Packet send TRACE_20: Debug: 00 D1 00 00 01 00 00 00 |........| 01 36 01 2C 0E 41 20 00 |.6.,.A..| 7F FF 4F 98 00 00 00 01 |..O.....| 00 97 00 3A 00 00 00 00 |...:....| 01 01 00 00 00 00 00 00 |........| 00 00 00 00 00 00 00 00 |........| 00 00 00 00 00 00 00 00 |........| 00 00 28 44 45 53 43 52 |..(DESCR| 49 50 54 49 4F 4E 3D 28 |IPTION=(| 43 4F 4E 4E 45 43 54 5F |CONNECT_| 44 41 54 41 3D 28 53 49 |DATA=(SI| 44 3D XX XX XX XX XX XX |D=<SID>)(| 43 49 44 3D 28 50 52 4F |CID=(PRO| 47 52 41 4D 3D 4A 44 42 |GRAM=JDB| 43 20 54 68 69 6E 20 43 |C.Thin.C| 6C 69 65 6E 74 29 28 48 |lient)(H| 4F 53 54 3D 5F 5F 6A 64 |OST=__jd| 62 63 5F 5F 29 28 55 53 |bc__)(US| 45 52 3D XX XX XX XX XX |ER=<USER>| XX XX 29 29 29 28 41 44 |)))(AD| 44 52 45 53 53 3D 28 50 |DRESS=(P| 52 4F 54 4F 43 4F 4C 3D |ROTOCOL=| 74 63 70 29 28 48 4F 53 |tcp)(HOS| 54 3D XX XX XX XX XX XX |T=<HOST>| 6F 73 74 29 28 50 4F 52 |(POR| 54 3D 31 35 XX XX XX XX |T=<PORT>))| 29 |) |
Jan 19, 2010 9:25:35 PM oracle.net.ns.Packet receive TRACE_20: Debug: type=6, length=185, flags=0 00 B9 00 00 06 00 00 00 |........| 00 00 01 06 00 49 42 4D |........| 50 43 2F 57 49 4E 5F 4E |PC/WIN_N| 54 2D 38 2E 31 2E 30 00 |T-8.1.0.| B2 00 01 00 00 00 64 00 |......d.| 00 00 60 01 24 0F 05 0B |..`.$...| 0C 03 0C 0C 05 04 05 0D |........| 06 09 07 08 05 05 05 05 |........| 05 0F 05 05 05 05 05 0A |........| 05 05 05 05 05 04 05 06 |........| 07 08 08 23 47 23 23 08 |...#G##.| 11 23 08 11 41 B0 23 00 |.#..A.#.| 83 00 B2 07 D0 03 00 00 |........| 00 00 00 00 00 00 00 00 |........| 00 00 00 00 00 00 00 00 |........| 00 00 00 00 00 00 00 00 |........| 00 00 00 00 00 00 00 00 |........| 00 00 00 25 06 01 01 01 |...%....| 0D 01 01 05 01 01 01 01 |........| 01 01 01 7F FF 03 09 03 |........| 03 01 00 7F 01 1F FF 01 |........| 03 01 01 3F 01 01 05 00 |...?....| 01 07 02 01 00 01 18 00 |........| 01 |. |
It was observed that the JDBC packet level trace ( oracle.net.ns.level = ALL) will not show the DCD probes, whether using the the TCP KEEPALIVE socket option or the TNS packets ( USE_NS_PROBES_FOR_DCD=true ). Switch to JDBC/OCI and use the Oracle Net client trace to see the DCD probes. - While using earlier versions of the JDBC Thin Driver than release 11.2, you must use the JNETtrace utility to trace the network packets (Document:793415.1 How to Perform the Equivalent of SQL*Net Client Tracing with Oracle JDBC Thin Driver). - If your application has a single connection, it is easy enough to sift through the network log output. If your application has many connections, it becomes exceedingly difficult to separate the network packets for each connection. To aid in this situation, the Oracle JDBC jar files include oracle.jdbc.diagnostics.DemultiplexingLogHandler. This subclass of java.util.logging.FileHandler routes different log messages to different files. When used with oracle.net.ns logging, the packets for each connection will be written to a different file For more information on this, please refer to the technical brief: Oracle JDBC Logging using java.util.logging Section "JavaNet Logging" Note : Only the network packet log messages can be demultiplexed. The other JDBC logging messages do not have the information required to demultiplex them so there is no benefit of passing them through the demultiplexer.
ReferencesNOTE:793415.1 - How to Perform the Equivalent of SQL*Net Client Tracing with Oracle JDBC Thin Driver Releases Prior to 11.2NOTE:1584305.1 - Net Trace Log Is Not Generated After Following Steps In Document 1050942.1 How to Trace the Network Packets Exchanged Between JDBC and the RDBMS in Release 11.2 |  |
Didn't find what you are looking for?
Comments
Post a Comment