Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › TNS数据包解析
发新帖
kingtigers

3

积分

0

好友

0

主题

TNS数据包解析

1#
发表于 2012-1-11 16:20:00 | 查看: 8840| 回复: 4
请问下大家谁有分析过TNS的查询请求包(TTICode=0x03,func=0x5E).我花了3天时间,把ojdbc.jar包反编译出来,再编译回去,分析TNS请求包,发现和sqlplus的请求包有很大的出入.不同的客户端对同一个数据库服务器发起同一个查询,查询包都不一样,而数据库都可以解析的出来,这到底怎么一回事呢
2#
发表于 2012-1-11 16:27:07
sqlplus 是基于OCI的，ojdbc.jar 里是 oracle jdbc driver的驱动
它们的封装是不同的，但本质上都会有sql*net 的请求信息， 稍后我会回复更多信息。

回复 只看该作者 道具 举报

3#
发表于 2012-1-12 19:56:34
jdbc Packet 包的描述

http://www.oracledatabase12g.com ... ss-description.html
  1. package oracle.net.ns;

  3. import java.io.*;
  4. import oracle.net.nl.RepConversion;

  6. // Referenced classes of package oracle.net.ns:
  7. //            NetException, NetInputStream, SQLnetDef, SessionAtts

  9. public class Packet
  10. implements SQLnetDef
  11. {

  13. public Packet(Packet packet)
  14. {
  15. this(packet.sAtts);
  16. length = packet.length;
  17. type = packet.type;
  18. flags = packet.flags;
  19. dataLen = packet.dataLen;
  20. dataOff = packet.dataOff;
  21. buffer = packet.buffer;
  22. }

  24. public Packet(SessionAtts sessionatts)
  25. {
  26. header = new byte[8];
  27. sAtts = sessionatts;
  28. sdu = sessionatts.getSDU();
  29. tdu = sessionatts.getTDU();
  30. }

  32. public Packet(SessionAtts sessionatts, int i)
  33. {
  34. this(sessionatts);
  35. createBuffer(i);
  36. }

  38. public Packet(SessionAtts sessionatts, int i, int j, int k)
  39. {
  40. this(sessionatts);
  41. createBuffer(i, j, k);
  42. }

  44. protected void createBuffer(int i)
  45. {
  46. buffer = new byte[i];
  47. buffer[0] = (byte)(i / 256);
  48. buffer[1] = (byte)(i % 256);
  49. }

  51. protected void createBuffer(int i, int j, int k)
  52. {
  53. buffer = new byte[i];
  54. buffer[0] = (byte)(i / 256);
  55. buffer[1] = (byte)(i % 256);
  56. buffer[5] = (byte)k;
  57. buffer[4] = (byte)j;
  58. }

  60. protected void dump(byte abyte0[], int i, int j)
  61. {
  62. int k = 0;
  63. System.out.println("Packet dump");
  64. System.out.println("buffer.length=" + abyte0.length);
  65. System.out.println("offset       =" + i);
  66. System.out.println("len          =" + j);
  67. for(int l = i; l < j; l +=  8 )
  68. {
  69. System.out.print("|");
  70. for(int i1 = 0; i1 < 8 && k < j - 1; i1++)
  71. {
  72. k = l + i1;
  73. RepConversion.printInHex(abyte0[k]);
  74. System.out.print(" ");
  75. }

  77. System.out.println("|");
  78. }

  80. System.out.println("finish dump");
  81. }

  83. protected void extractData()
  84. throws IOException, NetException
  85. {
  86. if(dataLen <= 0)
  87. data = new String();
  88. else
  89. if(length > dataOff)
  90. {
  91. data = new String(buffer, 0, dataOff, dataLen);
  92. } else
  93. {
  94. byte abyte0[] = new byte[dataLen];
  95. if(sAtts.nsInputStream.read(abyte0) < 0)
  96. throw new NetException(0);
  97. data = new String(abyte0, 0);
  98. }
  99. }

  101. protected String getData()
  102. {
  103. return data;
  104. }

  106. protected void receive()
  107. throws IOException, NetException
  108. {
  109. int i;
  110. for(i = 0; i < header.length;)
  111. try
  112. {
  113. if((i += sAtts.ntInputStream.read(header, i, header.length - i)) <= 0)
  114. throw new NetException(0);
  115. }
  116. catch(InterruptedIOException _ex)
  117. {
  118. throw new NetException(504);
  119. }

  121. length = header[0] & 0xff;
  122. length <<= 8;
  123. length |= header[1] & 0xff;
  124. type = header[4];
  125. flags = header[5];
  126. if(type > 19)
  127. throw new NetException(204);
  128. if(length > 32767 || length > sdu)
  129. throw new NetException(203);
  130. if(length < 8   )
  131. throw new NetException(207);
  132. buffer[5] = (byte)flags;
  133. buffer[4] = (byte)type;
  134. while(i < length)
  135. try
  136. {
  137. if((i += sAtts.ntInputStream.read(buffer, i, length - i)) <= 0)
  138. throw new NetException(0);
  139. }
  140. catch(InterruptedIOException _ex) { }
  141. }

  143. protected void send()
  144. throws IOException
  145. {
  146. synchronized(sAtts.ntOutputStream)
  147. {
  148. sAtts.ntOutputStream.write(buffer, 0, buffer.length);
  149. }
  150. }

  152. private int buffer2send;
  153. protected int sdu;
  154. protected int tdu;
  155. protected int length;
  156. public int type;
  157. protected int flags;
  158. protected int dataLen;
  159. protected int dataOff;
  160. protected String data;
  161. protected byte buffer[];
  162. protected byte header[];
  163. public SessionAtts sAtts;
  164. }
复制代码

回复 只看该作者 道具 举报

4#
发表于 2012-1-12 20:00:50
  1. SQL*NET PACKET STRUCTURE: NS PACKET HEADER

  3. Problem Description:
  4. ====================

  6. This entry contains information about the SQL*Net packet structure and the NS
  7. packet headers (protocol independent layer).

  9. These packet types appear in the SQL*Net trace. The help explain how the
  10. client and server are synchronized and what they are doing.

  12. Search words:
  13. packets tracing file trc tns sqlnet

  15. Solution Description:
  16. =====================

  18. ================
  19. NS PACKET HEADER
  20. ================
  21. 0 1 2 3
  22. 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7
  23. —————————————————————–
  24. | NSPHDLEN | NSPHDPSM |
  25. |—————————————————————|
  26. | NSPHDTYP | reserved | NSPHDHSM |
  27. —————————————————————–
  28. NSPHDLEN: Packet length – number of bytes in the entire packet.
  29. NSPHDPSM: Packet checksum – the 16-bit ones complement of the 16-bit
  30. ones complement sum of the entire packet.
  31. NSPHDTYP: Packet type
  32. Code Decimal HEX Description
  33. ———– ——- ——- ——————————
  34. NSPTCN 01 1 connect
  35. NSPTAC 02 2 accept
  36. NSPTAK 03 3 acknowledge
  37. NSPTRF 04 4 refuse
  38. NSPTRD 05 5 redirect
  39. NSPTDA 06 6 data
  40. NSPTNL 07 7 “null” – empty data, no flags
  41. NSPTAB 09 9 abort
  42. NSPTRS 11 B re-send
  43. NSPTMK 12 C marker
  44. NSPTAT 13 D attention
  45. NSPTCNL 14 E control information
  46. NSPTHI 19 12 highest legal packet type

  48. NSPHDHSM: Header checksum – the 16-bit ones complement of the 16-bit
  49. ones complement sum of the packet header.
  50. NSPSIZHD: size of packet header

  52. =================
  53. NS CONNECT PACKET
  54. =================
  55. 0 1 2 3
  56. 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7
  57. —————————————————————–
  58. | NSPHDLEN | NSPHDPSM |
  59. |——————————————————- ——-|
  60. | NSPTCN (1) | reserved | NSPHDHSM |
  61. |—————————————————————|
  62. | NSPCNVSN | NSPCNLOV |
  63. |—————————————————————|
  64. | NSPCNOPT | NSPCNSDU |
  65. |—————————————————————|
  66. | NSPCNTDU | NSPCNNTC |
  67. |—————————————————————|
  68. | NSPCNTNA | NSPCNONE |
  69. |—————————————————————|
  70. | NSPCNLEN | NSPCNOFF |
  71. |—————————————————————|
  72. | NSPCNMXC |
  73. |—————————————————————|
  74. | NSPCNFL0 | NSPCNFL1 |//////////////////////////////|
  75. |—————————————————————|
  76. | (expansion) |
  77. |—————————————————————|
  78. | NSPCNDAT, NSPCNV3DAT, NSPCNV2DAT (data) |
  79. —————————————————————–

  81. NSPCNVSN Packet version number.
  82. NSPCNLOV Lowest version number that sender can be compatible with.
  83. NSPCNOPT Global service options supported (see NSGBLXXXX in nsi.h).
  84. NSPCNSDU My SDU (session data unit) size.
  85. NSPCNTDU Maximum TDU (transport data unit) size for NT driver.
  86. NSPCNNTC NT protocol characteristics (see NTCXXX in ti.h).
  87. NSPCNTNA Line turnaround value (=> max consecutive packet sends
  88. allowed before a packet recv must occur).
  89. NSPCNONE The value ’1′ in my hardware byte order
  90. NSPCNLEN Length of connect data (not necessarily in this packet).
  91. NSPCNOFF Offset to start of connect data.
  92. NSPCNMXC Maximum connect data that can be received.
  93. NSPCNFL0 Connect flags (from nsinfflg).
  94. NSPCNFL1 Connect flags (from nsinfflg).

  96. **** The following are added for Oracle Trace functionality (expansion)
  97. NSPCNCF1 cross facility item 1
  98. NSPCNCF2 cross facility item 2
  99. NSPCNCID unique connection id
  100. **** End of update for Oracle trace

  102. NSPCNDAT start of connect data (V3.6 packet)
  103. NSPCNV3DAT start of connect data (V3 packet)
  104. NSPCNV2DAT start of connect data, V2 packet

  106. ================
  107. NS ACCEPT PACKET
  108. ================
  109. 0 1 2 3
  110. 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7
  111. —————————————————————–
  112. | NSPHDLEN | NSPHDPSM |
  113. |—————————————————————|
  114. | NSPTAC (2) | reserved | NSPHDHSM |
  115. |—————————————————————|
  116. | NSPACVSN | NSPACOPT |
  117. |—————————————————————|
  118. | NSPACSDU | NSPACTDU |
  119. |—————————————————————|
  120. | NSPACONE | NSPACLEN |
  121. |—————————————————————|
  122. | NSPACOFF | NSPACFL0 | NSPACFL1 |
  123. |—————————————————————|
  124. | (expansion) |
  125. |—————————————————————|
  126. | NSPACDAT, NSPACV2DAT (data) |
  127. —————————————————————–

  129. NSPACVSN Version that this connection is to run at.
  130. NSPACOPT Global service options in place for this connection.
  131. NSPACSDU SDU (session data unit) size for this connection.
  132. NSPACTDU Maximum TDU (transport data unit) for this connection.
  133. NSPACONE The value ’1′ in my hardware byte order
  134. NSPACLEN Length of connect data (not necessarily in this packet).
  135. NSPACOFF Offset to start of connect data.
  136. NSPACFL0 Connect flags (from nsctxinf).
  137. NSPACFL1 Connect flags (from nsctxinf).
  138. NSPACDAT Start of connect data (V3/V3.6 packet)
  139. NSPACV2DAT Start of connect data, V2 packet

  141. ================
  142. NS RESEND PACKET
  143. ================
  144. 0 1 2 3
  145. 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7
  146. —————————————————————–
  147. | NSPHDLEN | NSPHDPSM |
  148. |—————————————————————|
  149. | NSPTRS (11) | reserved | NSPHDHSM |
  150. —————————————————————–

  152. ================
  153. NS REFUSE PACKET
  154. ================
  155. 0 1 2 3
  156. 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7
  157. —————————————————————–
  158. | NSPHDLEN | NSPHDPSM |
  159. |—————————————————————|
  160. | NSPTRF (4) | reserved | NSPHDHSM |
  161. |—————————————————————|
  162. | NSPRFURS | NSPRFSRS | NSPRFLEN |
  163. |—————————————————————|
  164. | NSPRFDAT (data) |
  165. —————————————————————–

  167. NSPRFURS User (application) reason for refusal.
  168. NSPRFSRS System (NS) reason for refusal.
  169. NSPRFLEN Length of refuse data (not necessarily in this packet).
  170. NSPRFDAT Start of connect data

  172. ==================
  173. NS REDIRECT PACKET
  174. ==================
  175. 0 1 2 3
  176. 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7
  177. —————————————————————–
  178. | NSPHDLEN | NSPHDPSM |
  179. |—————————————————————|
  180. | NSPTRD (5) | reserved | NSPHDHSM |
  181. |—————————————————————|
  182. | NSPRDLEN | NSPRDDAT (data) |
  183. |——————————— |
  184. | |
  185. —————————————————————–

  187. NSPRDLEN Length of redirect data (not necessarily in this packet).
  188. NSPRDDAT Start of connect data

  190. ==============
  191. NS DATA PACKET
  192. ==============
  193. 0 1 2 3
  194. 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7
  195. —————————————————————–
  196. | NSPHDLEN | NSPHDPSM |
  197. |———————————————————————-|
  198. | NSPTDA (6) | reserved | NSPHDHSM |
  199. |———————————————————————-|
  200. | NSPDAFLG | NSPDADAT (data) |
  201. |———————————————————————-|

  203. NSPDAFLG flags:
  204. Code Value Description
  205. ———– ———– ——————————
  206. NSPDAFZER 0
  207. NSPDAFTKN 1 the hdx (send) token
  208. NSPDAFRCF 2 request confirmation
  209. NSPDAFCFM 4 confirmation
  210. NSPDAFRSV 8 RESERVED (for hdx flag)
  211. NSPDAFMOR 20 more data to come
  212. NSPDAFEOF 40 “end of file”
  213. NSPDAFIMM 80 do immediate confirmation
  214. NSPDAFRTS 100 request-to-send
  215. NSPDAFRNT 200 request NT trailer
  216. NSPDADAT start of data

  218. ================
  219. NS MARKER PACKET
  220. ================
  221. 0 1 2 3
  222. 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7
  223. —————————————————————–
  224. | NSPHDLEN | NSPHDPSM |
  225. |—————————————————————|
  226. | NSPTMK (12) | reserved | NSPHDHSM |
  227. |—————————————————————|
  228. | NSPMKTYP | NSPMKODT | NSPMKDAT |
  229. —————————————————————-

  231. NSPMKTYP marker type
  232. Code Value Description
  233. ———– ———– ——————————
  234. NSPMKTD0 0 data marker – 0 data bytes
  235. NSPMKTD1 1 data marker – 1 data byte
  236. NSPMKTAT 2 attention marker => ATTENTION packet
  237. NSPMKODT old (pre-V3.05) data byte
  238. NSPMKDAT data byte – For V3.05 we want NSPMKDAT to be the same
  239. as
  240. NSPDADAT – this fixes a bug to do with reading a
  241. marker via
  242. the NSBRECV interface where the MARKER packet
  243. co-resides in
  244. the same SDU buffer as a another packet (“leftover”
  245. condition).

  247. ===================
  248. NS ATTENTION PACKET
  249. ===================
  250. (there isn’t one, it’s a special case of a MARKER packet, see above)

  252. ===============
  253. NS ABORT PACKET
  254. ===============

  256. 0 1 2 3
  257. 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7
  258. —————————————————————–
  259. | NSPHDLEN | NSPHDPSM |
  260. |—————————————————————|
  261. | NSPTAB (9) | reserved | NSPHDHSM |
  262. |—————————————————————|
  263. | NSPABURS | NSPABSRS | NSPABDAT (data) |
  264. |——————————— |
  265. | |
  266. —————————————————————–

  268. NSPABURS User (application) reason for abort.
  269. NSPABSRS System (NS) reason for abort.
  270. NSPABDAT Start of abort data

  272. =================
  273. NS CONTROL PACKET
  274. =================
  275. 0 1 2 3
  276. 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7
  277. —————————————————————–
  278. | NSPHDLEN | NSPHDPSM |
  279. |—————————————————————|
  280. | NSPTCNL (14) | reserved | NSPHDHSM |
  281. |—————————————————————|
  282. | NSPTCNLCMD | data |
  283. |——————————— |
  284. | |
  285. —————————————————————–

  287. * The control command passed. Initially the only command would be for
  288. Oracle trace.

  290. NSPCTLCMD valid commands are:

  292. Code Value Description
  293. ———– ———– ————————–
  294. NSPCTL_OT 1 Oracle trace command
复制代码

回复 只看该作者 道具 举报

5#
发表于 2012-1-12 20:07:28
你可以参考这三个note:

How to Perform the Equivalent of SQL*Net Client Tracing with Oracle JDBC Thin Driver [ID 793415.1]
How to Trace the Network Packets Exchanged Between JDBC and the RDBMS in Release 11.2 [ID 1050942.1]
How To Generate Sqlnet trace with Sql*Developer ? [ID 1390063.1]

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-24 01:44 , Processed in 0.049716 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569