Oracle Net是我們使用客戶端連入Oracle伺服器的主要方法。從元件相關方角度看，Oracle Net連線過程涉及到很多的技術和過程，例如客戶端連線方式、連線字串、監聽器行為和資料庫例項。

一般我們都是藉助Oracle Client（非JDBC）的OCI介面方式進行Oracle連線，配置本地連線名local name。一旦連線過程出現錯誤，我們的診斷策略也是從外到內，從客戶端到伺服器逐步診斷。

1、Tnsping簡述

Tnsping是Oracle提供的診斷連線動作的重要方法。我們在客戶端上使用tnsping xxx（本地連線名），可以快速的定位連線過程中錯誤，進行問題判斷。從功能上看，tnsping能夠幫助我們解決幾個方面問題：

ü 本地Oracle Net配置檔案解析：sqlnet.ora、tnsname.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

已使用的引數檔案:

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_level和tnsping.trace_directory標註了tnsping的跟蹤級別和跟蹤檔案存放目錄。和其他Oracle跟蹤操作相同，tnsping也支援不同跟蹤級別粒度，可以依據我們的目的不同進行調整。

目前我們trace_level可選的引數有：off、user、admin和support。在實驗中我們選擇了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

已使用的引數檔案:

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

--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 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

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作為一個官方提供的診斷工具，可以很大程度上幫助我們解決問題，提高工作效率。

配置tnsping跟蹤來診斷Oracle Net連線

相關文章