ITPub博客

首页 > 数据库 > Oracle > 配置tnsping跟踪来诊断Oracle Net连接

配置tnsping跟踪来诊断Oracle Net连接

原创 Oracle 作者:realkid4 时间:2014-05-14 16:58:32 0 删除 编辑

 

Oracle Net是我们使用客户端连入Oracle服务器的主要方法。从组件相关方角度看,Oracle Net连接过程涉及到很多的技术和过程,例如客户端连接方式、连接字符串、监听器行为和数据库实例。

一般我们都是借助Oracle Client(非JDBC)的OCI接口方式进行Oracle连接,配置本地连接名local name。一旦连接过程出现错误,我们的诊断策略也是从外到内,从客户端到服务器逐步诊断。

 

1Tnsping简述

 

TnspingOracle提供的诊断连接动作的重要方法。我们在客户端上使用tnsping xxx(本地连接名),可以快速的定位连接过程中错误,进行问题判断。从功能上看,tnsping能够帮助我们解决几个方面问题:

 

ü  本地Oracle Net配置文件解析:sqlnet.oratnsname.ora是我们客户端最重要的两个配置文件。由于历史的原因,Oracle基础配置文件很多是文本格式,Oracle Net三个核心配置文件尤其如此。netca等配置工具也只是一个文本文件写入读取功能。很多朋友进行配置的时候,图简单直接进行文本复制黏贴,容易引起问题故障。tnsping可以模拟连接过程,对配置文件中的名称进行预解析,如果存在格式错误,解析必定失败;

ü  服务器端监听器状态诊断:在远程连接数据库服务器的时候,监听器是一个不能回避的组件。tnsping是可以进行网络访问判断的,如果监听器没有运行、或者没有在指定的端口上运行,tnsping是可以做出判断提示信息的;

ü  注册服务有限验证:在本地连接名称中,服务名、主机名、监听器伺候端口、连接协议是连接信息的几个要素。服务名是tnsping不能验证的对象,也就是说。如果监听器注册信息(动态注册、静态注册)中不存在连接的服务名,tnsping是不会报错的;

 

C:\Documents and Settings\liuzy>tnsping chdb

 

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 12-5 -

2014 17:58:21

 

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

已使用的参数文件:

D:\app\Administrator\product\11.2.0\client_1\network\admin\sqlnet.ora

 

已使用 TNSNAMES 适配器来解析别名

尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.4.53)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = chdb)))

OK (30 毫秒)

 

对于tnsping,我们除了最后的结果信息之后,还可以使用跟踪trace方法,查看每个步骤执行情况,以及在哪个步骤出现问题。本篇就介绍如何从客户端进行tnsping过程跟踪。

 

2、配置跟踪参数

 

默认情况下,tnsping跟踪功能是关闭的。我们需要在Oracle Net配置文件sqlnet.ora中进行手工的配置。注意:配置是在客户端,我们执行tnsping命令也是在客户端进行。

 

--客户端sqlnet.ora,位置$ORACLE_HOME/network/admin

# sqlnet.ora Network Configuration File: D:\app\Administrator\product\11.2.0\client_1\network\admin\sqlnet.ora

# Generated by Oracle configuration tools.

 

# This file is actually generated by netca. But if customers choose to

# install "Software Only", this file wont exist and without the native

# authentication, they will not be able to connect to the database on NT.

 

SQLNET.AUTHENTICATION_SERVICES= (NTS)

 

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

 

TNSPING.TRACE_LEVEL=SUPPORT

TNSPING.TRACE_DIRECTORY=D:\app\Administrator\product\11.2.0\client_1\network\trace

 

最后的tnsping.trace_leveltnsping.trace_directory标注了tnsping的跟踪级别和跟踪文件存放目录。和其他Oracle跟踪操作相同,tnsping也支持不同跟踪级别粒度,可以依据我们的目的不同进行调整。

目前我们trace_level可选的参数有:offuseradminsupport。在实验中我们选择了support级别,属于比较细的跟踪粒度。

trace_directory是指定跟踪目录位置。跟踪文件名称统一为tnsping.trc

 

3、执行跟踪过程

 

使用tnsping连接一个本地连接名cogdb

 

C:\Documents and Settings\liuzy>tnsping cogdb

 

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 08-5 -

2014 12:17:43

 

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

 

已使用的参数文件:

D:\app\Administrator\product\11.2.0\client_1\network\admin\sqlnet.ora

 

已使用 TNSNAMES 适配器来解析别名

尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.3.101)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cogdb)))

OK (30 毫秒)

 

在指定目录中,我们可以看到生成的跟踪文件。

 

D:\app\Administrator\product\11.2.0\client_1\network\trace

tnsping.trc

 

下面就是对跟踪文件的解析。

 

4、跟踪文件解析

 

跟踪文件信息比较多,我们针对一些细节内容进行说明讨论。

 

--文件标头

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 08-5 -2014 12:17:43

 

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

 

--Trace操作过程本身信息

--- TRACE CONFIGURATION INFORMATION FOLLOWS ---

New trace stream is D:\app\Administrator\product\11.2.0\client_1\network\trace\tnsping.trc

New trace level is 16 跟踪级别

--- TRACE CONFIGURATION INFORMATION ENDS ---

--- PARAMETER SOURCE INFORMATION FOLLOWS ---

Attempted load of system pfile source D:\app\Administrator\product\11.2.0\client_1\network\admin\sqlnet.ora –Oracle NET行为动作参数

Parameter source loaded successfully

 

--参数信息(从sqlnet.ora中加载的)

 -> PARAMETER TABLE LOAD RESULTS FOLLOW <-

Successful parameter table load

 -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-

  TNSPING.TRACE_LEVEL = SUPPORT

  NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

  TNSPING.TRACE_DIRECTORY = D:\app\Administrator\product\11.2.0\client_1\network\trace

  SQLNET.AUTHENTICATION_SERVICES = (NTS)

--- PARAMETER SOURCE INFORMATION ENDS ---

--- LOG CONFIGURATION INFORMATION FOLLOWS ---

Log stream will be "standard output"

Log stream validation not requested

--- LOG CONFIGURATION INFORMATION ENDS ---

 

nlstdipi: entry

nlstdipi: exit

nnfun2awanm: entry

nnfgiinit: entry

nncpcin_maybe_init: default name server domain is [root]

nnfgiinit: Installing read path

nnfgsrsp: entry

nnfgsrsp: Obtaining path parameter from names.directory_path or native_names.directory_path –本地目录读取

nnfgsrdp: entry

nnfgsrdp: Setting path:

nnfgsrdp: checking element TNSNAMES

nnfgsrdp: checking element EZCONNECT

nnfgsrdp: Path set

nnfun2a: entry

nlolgobj: entry

nnfgrne: entry

nnfgrne: Going though read path adapters

nnfgrne: Switching to TNSNAMES adapter

nnftboot: entry

nlpaxini: entry

nlpaxini: exit

nnftmlf_make_local_addrfile: entry

nnftmlf_make_local_addrfile: construction of local names file failed

nnftmlf_make_local_addrfile: exit

nlpaxini: entry

nlpaxini: exit

nnftmlf_make_system_addrfile: entry

nnftmlf_make_system_addrfile: system names file is D:\app\Administrator\product\11.2.0\client_1\network\admin\tnsnames.ora—定位到文件

nnftmlf_make_system_addrfile: exit

nnftboot: exit

nnftrne: entry

nnftrne: Original name: cogdb –当前本次ping的对象

nnfttran: entry

nncpdpt_dump_ptable: ---列出所有的在文件tnsname名称

(篇幅原因,有省略……

nncpdpt_dump_ptable: COGDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.3.101)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cogdb)))

nncpdpt_dump_ptable: ZZWEB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.3.5)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = zzweb)))

nncpdpt_dump_ptable: ORATEST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oratest)))

nncpdpt_dump_ptable: --- END D:\app\Administrator\product\11.2.0\client_1\network\admin\tnsnames.ora TABLE ---

nnfttran: exit

nnftrne: Using tnsnames.ora address (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.3.101)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cogdb))) for name cogdb –找到了!

nnftrne: exit

nnfgrne: exit

nlolgserv: entry

nnfggav: entry

nnftgav: entry

nnftgav: exit

nnfgfrm: entry

nnftfrm: entry

nnftfrm: exit

nnfgfrm: exit

nlolgserv: exit

nlolgobj: exit

nlolfmem: entry

nlolfmem: exit

nnfun2awanm: Getting the path of sqlnet.ora

nnfun2awanm: Getting the adapter name

nnfun2awanm: exit

nscall: entry

nsmal: entry

nsmal: 216 bytes at 0x1702dc0 解析cogdb连接串

nsmal: normal exit

nscall: connecting...

nlad_expand_hst: Expanding 172.16.3.101

snlinGetAddrInfo: entry

snlinGetAddrInfo: exit

nlad_expand_hst: Already an IP address –如果本次使用的不是IP地址,上面过程就是在用hosts文件和DNS进行解析;

snlinFreeAddrInfo: entry

snlinFreeAddrInfo: exit

nlad_expand_hst: Result: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.3.101)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=cogdb))) –真实地址

nladini: entry

nladini: exit

nladget: entry

nladget: exit

nsmal: entry

nsmal: 104 bytes at 0x2042670

nsmal: normal exit

nsc2addr: entry

nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.3.101)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cogdb)))

nttbnd2addr: entry

snlinGetAddrInfo: entry

snlinGetAddrInfo: exit

nttbnd2addr: using host IP address: 172.16.3.101

snlinFreeAddrInfo: entry

snlinFreeAddrInfo: exit

nttbnd2addr: exit

nsc2addr: normal exit

nsopen: entry

nsmal: entry

nsmal: 996 bytes at 0x20428d0

nsmal: normal exit

nsopenmplx: entry

nsmal: entry

nsmal: 2120 bytes at 0x2042cc0

nsmal: normal exit

nsiorini: entry

nsbal: entry

nsbgetfl: entry

nsbgetfl: normal exit

nsmal: entry

nsmal: 84 bytes at 0x2043650

nsmal: normal exit

nsbal: normal exit

nsiorini: exit (0)

nscpxget: entry

nscpxget: normal exit

nsopenalloc_nsntx: nlhthput on mplx_ht_nsgbu:ctx=20428d0, nsntx=2042cc0

nsopenmplx: normal exit

nsopen: opening transport...

nttcon: entry

nttcon: toc = 1

nttcnp: entry

nttcnp: creating a socket. –连接开始

nttcnp: exit

nttcni: entry

nttcni: Tcp conn timeout = 60000 (ms)

nttcni: TCP Connect TO enabled. Switching to NB

nttctl: entry

nttctl: Setting connection into non-blocking mode

nttcni: trying to connect to socket 1660.

ntt2err: entry

ntt2err: exit

ntctst: size of NTTEST list is 1 - not calling poll

sntseltst: Testing for WRITE on socket 1660

sntseltst: FOUND: write request on socket 1660

nttctl: entry

nttctl: Clearing non-blocking mode

snlinGetNameInfo: entry

snlinGetNameInfo: exit

nttcni: connected on ipaddr 172.17.12.80

nttcni: exit

nttcon: NT layer TCP/IP connection has been established.

nttcon: set TCP_NODELAY on 1660

nttcon: exit

nsopen: transport is open

nsoptions: entry

nsoptions: lcl[0]=0x0, lcl[1]=0x900001, gbl[0]=0x0, gbl[1]=0x0, cha=0x0

nsoptions: Vectored IO not supported.

nsoptions: lcl[0]=0xf4ffefff, lcl[1]=0x900001, gbl[0]=0xfabf, gbl[1]=0x0

nsoptions: normal exit

nsnainit: entry

nsnainit: call

nsnainit: NA not wanted - disabling and returning

nsopen: global context check-in (to slot 0) complete

nsopen: lcl[0]=0xf4ffefff, lcl[1]=0x900001, gbl[0]=0xfabf, gbl[1]=0x0, tdu=32767, sdu=8192

nsfull_opn: entry

nsfull_opn: cid=0, opcode=65, *bl=0, *what=0, uflgs=0x0, cflgs=0x0

nsfull_opn: nsctx: state=7, flg=0x4001, mvd=0

nsbal: entry

nsbgetfl: entry

nsbgetfl: normal exit

nsmal: entry

nsmal: 84 bytes at 0x20661a8

nsmal: normal exit

nsbal: normal exit

nsbal: entry

nsbgetfl: entry

nsbgetfl: normal exit

nsmal: entry

nsmal: 84 bytes at 0x2068228

nsmal: normal exit

nsbal: normal exit

nsfull_opn: normal exit

nsopen: normal exit

nsmfr: entry

nsmfr: 104 bytes at 0x2042670

nsmfr: normal exit

nsdo: entry

nsdo: cid=0, opcode=67, *bl=29, *what=8, uflgs=0x0, cflgs=0x3

nsdo: rank=64, nsctxrnk=0

nsdo: nsctx: state=14, flg=0x4005, mvd=0

nsdo: gtn=10, gtc=10, ptn=10, ptc=8155

nscon: entry

nscon: doing connect handshake...

nscon: sending NSPTCN packet

nspsend: entry

nspsend: plen=87, type=1

nttwr: entry

nttwr: socket 1660 had bytes written=87

nttwr: exit

nspsend: packet dump

nspsend: 00 57 00 00 01 00 00 00  |.W......|

nspsend: 01 3A 01 2C 00 00 20 00  |.:.,....|

nspsend: 7F FF C6 0E 00 00 01 00  |........|

nspsend: 00 1D 00 3A 00 00 00 00  |...:....|

nspsend: 00 00 00 00 00 00 00 00  |........|

nspsend: 00 00 00 00 00 00 00 00  |........|

nspsend: 00 00 00 00 00 00 00 00  |........|

nspsend: 00 00 28 43 4F 4E 4E 45  |..(CONNE|

nspsend: 43 54 5F 44 41 54 41 3D  |CT_DATA=|

nspsend: 28 43 4F 4D 4D 41 4E 44  |(COMMAND|

nspsend: 3D 70 69 6E 67 29 29     |=ping)) |

nspsend: 87 bytes to transport –发出87 bytes

nspsend: normal exit

nscon: exit (0)

nsdo: nsctxrnk=0

nsdo: normal exit

nsdo: entry

nsdo: cid=0, opcode=68, *bl=1024, *what=9, uflgs=0x2000, cflgs=0x3

nsdo: rank=64, nsctxrnk=0

nsdo: nsctx: state=2, flg=0x4005, mvd=0

nsdo: gtn=10, gtc=10, ptn=10, ptc=8155

nscon: entry

nscon: recving a packet

nsprecv: entry

nsprecv: reading from transport...

nttrd: entry

nttrd: socket 1660 had bytes read=73 –读取到73 bytes的回信

nttrd: exit

nsprecv: 73 bytes from transport

nsprecv: tlen=73, plen=73, type=4

nsprecv: packet dump

nsprecv: 00 49 00 00 04 00 00 00  |.I......|

nsprecv: 22 00 00 3D 28 44 45 53  |"..=(DES|

nsprecv: 43 52 49 50 54 49 4F 4E  |CRIPTION|

nsprecv: 3D 28 54 4D 50 3D 29 28  |=(TMP=)(|

nsprecv: 56 53 4E 4E 55 4D 3D 31  |VSNNUM=1|

nsprecv: 36 39 38 37 30 33 33 36  |69870336|

nsprecv: 29 28 45 52 52 3D 30 29  |)(ERR=0)|

nsprecv: 28 41 4C 49 41 53 3D 4C  |(ALIAS=L|

nsprecv: 49 53 54 45 4E 45 52 29  |ISTENER)|

nsprecv: 29                       |)       |

nsprecv: normal exit

nscon: got NSPTRF packet

nscon: got 61 bytes connect data

nscon: exit (0)

nsdo: nsctxrnk=0

nsdo: normal exit

nscall: refused

nsclose: entry

nsvntx_dei: entry

nsvntx_dei: exit

nstimarmed: entry

nstimarmed: no timer allocated

nstimarmed: normal exit

nttctl: entry

nttctl: entry

nsfull_cls: entry

nsfull_cls: cid=0, opcode=65, *bl=0, *what=0, uflgs=0x0, cflgs=0x440

nsfull_cls: nsctx: state=3, flg=0x4001, mvd=0

nsbfr: entry

nsbaddfl: entry

nsbaddfl: normal exit

nsbfr: normal exit

nsbfr: entry

nsbaddfl: entry

nsbaddfl: normal exit

nsbfr: normal exit

nsfull_cls: normal exit

nsiocancel: entry

nsiofrrg: entry

nsiofrrg: cur = 204360c

nsbfr: entry

nsbaddfl: entry

nsbaddfl: normal exit

nsbfr: normal exit

nsiofrrg: exit

nsiocancel: exit

nsclose: closing transport

nttdisc: entry

nttdisc: Closed socket 1660

nttdisc: exit

nsclose: global context check-out (from slot 0) complete

nsnadisc: entry

nsnadisc: no native services in use - returning

nsvntx_dei: entry

nsvntx_dei: exit

nsopenfree_nsntx: nlhthdel from mplx_ht_nsgbu, ctx=20428d0 nsntx=2042cc0

nsiocancel: entry

nsiofrrg: entry

nsiofrrg: exit

nsiocancel: exit

nsmfr: entry

nsmfr: 2120 bytes at 0x2042cc0

nsmfr: normal exit

nsmfr: entry

nsmfr: 996 bytes at 0x20428d0

nsmfr: normal exit

nsclose: normal exit

nscall: error exit

nsdisc: entry

nsclose: entry

nsclose: normal exit

nsdisc: exit (0)

nlse_term_audit: entry

nlse_term_audit: exit

 

5、结论

 

Oracle Net是一个复杂的过程,涉及到客户端、服务器、监听器和网络等诸多组件对象。Tnsping作为一个官方提供的诊断工具,可以很大程度上帮助我们解决问题,提高工作效率。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-1161228/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7813989