詳解tnsping

cjavahtml 2018-03-21

展開全文

By: Jason Yang | Principal Product Manager

目前很多Oracle的愛好者希望快速搭建一套Oracle 數(shù)據(jù)庫并通過客戶端開發(fā)工具進(jìn)行開發(fā)和應(yīng)用連接。
但是，我們也注意到，很多用戶遇到的不是安裝數(shù)據(jù)庫本身，而是安裝之后無法遠(yuǎn)程連接到數(shù)據(jù)庫。并且，遇到各種各樣的TNS報(bào)錯(cuò)，無從下手。
這里，我們希望通過博客的方式，來展現(xiàn)一下網(wǎng)絡(luò)連接方面的基本配置和診斷方法。

就從tnsping開始吧！

首先，我們要了解的是：什么是tnsping?
可以參考官方文檔的具體介紹
http://docs.oracle.com/cd/E11882_01/network.112/e41945/connect.htm#NETAG378

tnsping 實(shí)際上就是一個(gè)小工具，用來決定是否一個(gè)Oracle Net 網(wǎng)絡(luò)服務(wù)（service）可以被接受。
從某種意義上來說，tnsping 針對的 sqlnet連接，就好比為我們常用的ping 命令來檢查IP，主機(jī)名網(wǎng)絡(luò)連接情況。

我們來做一個(gè)有趣的試驗(yàn)：

先來看看以下配置信息:
數(shù)據(jù)庫所在服務(wù)器端：
數(shù)據(jù)庫安裝到Linux服務(wù)器，hostname = ttdev, IP 地址= 192.168.56.11, ORACLE_SID=ORCL
------
[oracle@ttdev ~]$ cat /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    )
)
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (PROGRAM = extproc)
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_2)
    )
)
ADR_BASE_LISTENER = /home/oracle/app/oracle/
------

客戶端：
在Windows ，安裝oracle 客戶端后，配置Windows 環(huán)境變量 TNS_ADMIN 指向 C:\app\liyang\product\11.2.0\client_2\network\admin
在該路徑下，添加 tnsnames.ora 文件
------
TTORCL =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
)
------
客戶端的sqlnet.ora 使用默認(rèn)
SQLNET.AUTHENTICATION_SERVICES = (NTS)
=============

試驗(yàn)一：
首先，我們來看一下數(shù)據(jù)庫服務(wù)器端的實(shí)際情況：
通過putty 登錄到Linux系統(tǒng)的oracle 用戶，停掉數(shù)據(jù)庫，并檢查監(jiān)聽情況：
------
[oracle@ttdev ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 25 18:49:23 2014
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@ttdev ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 25-MAR-2014 18:52:03
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                25-MAR-2014 18:14:27
Uptime                    0 days 0 hr. 37 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/ttdev/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1521)))
Services Summary...
Service 'PLSExtProc' has 1 instance(s).
Instance 'PLSExtProc', status UNKNOWN, has 1 handler(s) for this service...     《=========服務(wù)里面并沒有ORCL，因?yàn)閿?shù)據(jù)庫剛剛被shutdown了！
The command completed successfully
------

在Windows 客戶端，開始菜單=》運(yùn)行 =》輸入 cmd 命令，
進(jìn)入dos 窗口，并輸入 tnsping TTORCL, 返回
------
C:\Users\liyang>tnsping TTORCL
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 26-3月 -
2014 10:06:08
Copyright (c) 1997, 2010, Oracle. All rights reserved.
已使用的參數(shù)文件:
C:\app\liyang\product\11.2.0\client_2\network\admin\sqlnet.ora
已使用 TNSNAMES 適配器來解析別名
嘗試連接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1   《=====TNSPING 仍然解析成功，返回TTORCL的具體內(nèi)容！
68.56.11)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (20 毫秒)
------
上面返回的 “嘗試連接” 后面部分是sqlnet 如何解析到的TTORCL 別名，或者叫連接標(biāo)識(shí)符。該別名的解析是通過客戶端的 TNS_ADMIN指定路徑下的 sqlnet.ora文件中的 NAMES.DIRECTORY_PATH 參數(shù)決定的。上面的試驗(yàn)結(jié)果顯示的是通過 TNSNAMES 適配器來解析成功的。
該請求可以看到，是通過 TCP 的網(wǎng)絡(luò)協(xié)議 (PROTOCOL = TCP)，連接到了 IP地址為 192.168.56.11 上的服務(wù)器（也可以是主機(jī)名），希望連接的端口為 1521, 服務(wù)名為 (SERVICE_NAME = orcl)。
連接20毫秒的意思是一個(gè)預(yù)估的大概網(wǎng)絡(luò)傳輸往返一次的時(shí)間。

以上測試說明了，不論數(shù)據(jù)庫是否運(yùn)行，只要監(jiān)聽存在并在運(yùn)行，tnsping 都可以成功解析！
=============

試驗(yàn)二：
停止數(shù)據(jù)庫實(shí)例，更改監(jiān)聽的端口為 1522 ，啟動(dòng)監(jiān)聽：
------
[oracle@ttdev admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 25 21:07:28 2014
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@ttdev admin]$ ps -ef | grep smon
oracle    4335 3326 0 21:08 pts/1    00:00:00 grep smon
《====沒有數(shù)據(jù)庫實(shí)例進(jìn)程運(yùn)行

[oracle@ttdev admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 25-MAR-2014 21:08:00
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1522)))
《====端口已經(jīng)更改！
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                25-MAR-2014 21:01:31
Uptime                    0 days 0 hr. 6 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/ttdev/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1522)))   《======監(jiān)聽1522 正常運(yùn)行
Services Summary...
Service 'PLSExtProc' has 1 instance(s).
Instance 'PLSExtProc', status UNKNOWN, has 1 handler(s) for this service... 《====沒有監(jiān)聽到 ORCL的服務(wù)
The command completed successfully
------
接下來，回到客戶端，執(zhí)行 tnsping ttorcl
------
C:\Users\liyang>tnsping ttorcl
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 26-3月 -
2014 12:09:48
Copyright (c) 1997, 2010, Oracle. All rights reserved.
已使用的參數(shù)文件:
C:\app\liyang\product\11.2.0\client_2\network\admin\sqlnet.ora
已使用 TNSNAMES 適配器來解析別名
嘗試連接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1   <>
68.56.11)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 毫秒)
------
這個(gè)試驗(yàn)二證明了，tnsping 即使在數(shù)據(jù)庫實(shí)例停止的情況下，并沒有監(jiān)聽1521端口，只要監(jiān)聽運(yùn)行，tnsping就可以返回正常！
=============

試驗(yàn)三：
那我們再來測試一下，監(jiān)聽停止的情況
------
[oracle@ttdev ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 25-MAR-2014 19:26:31
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1521)))
The command completed successfully

[oracle@ttdev ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 25-MAR-2014 19:26:39
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
《=======監(jiān)聽停止
TNS-00511: No listener
   Linux Error: 111: Connection refused

[oracle@ttdev ~]$ ps -ef | grep smon
oracle    3585 3326 0 19:26 pts/1    00:00:00 grep smon
《===沒有數(shù)據(jù)庫實(shí)例啟動(dòng)
------
此時(shí)，在客戶端再次執(zhí)行 tnsping:
------
C:\Users\liyang>tnsping TTORCL
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 26-3月 -
2014 10:27:55
Copyright (c) 1997, 2010, Oracle. All rights reserved.
已使用的參數(shù)文件:
C:\app\liyang\product\11.2.0\client_2\network\admin\sqlnet.ora
已使用 TNSNAMES 適配器來解析別名
嘗試連接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1
68.56.11)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
TNS-12541: TNS: 無監(jiān)聽程序                    <============解析仍然成功，但是報(bào)出 tns-12541="">
------
以上測試說明了，監(jiān)聽停止之后，tnsping 無法被遠(yuǎn)端監(jiān)聽接受
=============

以下試驗(yàn)四，五開始之前，我們將啟動(dòng)數(shù)據(jù)庫實(shí)例和監(jiān)聽
------
[oracle@ttdev admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 25-MAR-2014 21:02:31
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                25-MAR-2014 21:01:31
Uptime                    0 days 0 hr. 1 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/ttdev/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1521)))
Services Summary...
Service 'PLSExtProc' has 1 instance(s).
Instance 'PLSExtProc', status UNKNOWN, has 1 handler(s) for this service...
Service 'orcl' has 1 instance(s).
Instance 'orcl', status READY, has 2 handler(s) for this service...
Service 'orclXDB' has 1 instance(s).
Instance 'orcl', status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@ttdev admin]$ ps -ef | grep smon
oracle    4148     1 0 21:01 ?        00:00:00 ora_smon_orcl
oracle    4261 3326 0 21:02 pts/1    00:00:00 grep smon
------

試驗(yàn)四：
刪除本地的 tnsnames.ora，之后看一下會(huì)出現(xiàn)什么情況
------
C:\Users\liyang>tnsping TTORCL
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 26-3月 -
2014 10:31:38

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

已使用的參數(shù)文件:
C:\app\liyang\product\11.2.0\client_2\network\admin\sqlnet.ora

TNS-03505: 無法解析名稱               《=====================這次，連解析都無法成功！
------

注：編輯tnsnames.ora，如果我將TTORCL 修改為 ORCL，則返回同樣的結(jié)果
------
C:\Users\liyang>tnsping TTORCL
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 26-3月 -
2014 10:33:36
Copyright (c) 1997, 2010, Oracle. All rights reserved.
已使用的參數(shù)文件:
C:\app\liyang\product\11.2.0\client_2\network\admin\sqlnet.ora
TNS-03505: 無法解析名稱
------
=============

試驗(yàn)五：
再來測試一個(gè)IP地址輸入錯(cuò)誤的情景
修改tnsnames.ora文件，從192.168.56.11 改為一個(gè)不存在的192.168.56.12
------
TTORCL =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.12)(PORT = 1521)) 《==更改IP
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
)
------
C:\Users\liyang>ping 192.168.56.12
正在 Ping 192.168.56.12 具有 32 字節(jié)的數(shù)據(jù):
來自 192.168.56.1 的回復(fù): 無法訪問目標(biāo)主機(jī)。    <====ping>

C:\Users\liyang>tnsping ttorcl
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 26-3月 -
2014 11:54:25
Copyright (c) 1997, 2010, Oracle. All rights reserved.
已使用的參數(shù)文件:
C:\app\liyang\product\11.2.0\client_2\network\admin\sqlnet.ora
已使用 TNSNAMES 適配器來解析別名
嘗試連接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1
68.56.12)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
TNS-12535: TNS: 操作超時(shí)            《=======結(jié)果是等了較長時(shí)間報(bào)出超時(shí)錯(cuò)誤
------

總結(jié)：
tnsping 用于診斷各種客戶端連接問題還是一個(gè)非常好的工具。
但是我們要清楚的知道，這個(gè)工具能做什么（檢查客戶端TNS配置），什么不能做（檢查數(shù)據(jù)庫實(shí)例是否啟動(dòng)，監(jiān)聽端口是否在1521上）！

從以上試驗(yàn)我們看到，tnsping的解析成功僅僅意味著本地的tnsnames.ora ，IP地址，TNS_ADMIN 變量等配置成功。
但是，完全跟目標(biāo)數(shù)據(jù)庫實(shí)例連接沒有任何關(guān)系！

最后，sqlnet 跟數(shù)據(jù)庫也完全是獨(dú)立的兩個(gè)問題。不要因?yàn)閟qlnet連接方面出錯(cuò)，就肯定是數(shù)據(jù)庫的問題。要從客戶端自身先找原因。

我們后續(xù)將推出關(guān)于客戶端連接相關(guān)問題的診斷文章，敬請期待！ -- Jason Yang

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： cjavahtml > 《數(shù)據(jù)庫》

舉報(bào)/認(rèn)領(lǐng)