• 来谈谈tnsping


    目前很多Oracle的爱好者希望快速搭建一套Oracle 数据库并通过客户端开发工具进行开发和应用连接。
    但是,我们也注意到,很多用户遇到的不是安装数据库本身,而是安装之后无法远程连接到数据库。并且,遇到各种各样的TNS报错,无从下手。
    这里,我们希望通过博客的方式,来展现一下网络连接方面的基本配置和诊断方法。

    就从tnsping开始吧!

    首先,我们要了解的是: 什么是tnsping?
    可以参考官方文档的具体介绍
    http://docs.oracle.com/cd/E11882_01/network.112/e41945/connect.htm#NETAG378

    tnsping 实际上就是一个小工具,用来决定是否一个Oracle Net 网络服务(service)可以被接受。
    从某种意义上来说,tnsping 针对的 sqlnet连接,就好比为我们常用的ping 命令来检查IP,主机名网络连接情况。

    我们来做一个有趣的试验:


    先来看看以下配置信息:
    数据库所在服务器端:
    数据库安装到Linux服务器,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 环境变量 TNS_ADMIN 指向 C:appliyangproduct11.2.0client_2 etworkadmin
    在该路径下,添加 tnsnames.ora 文件
    ------
    TTORCL =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = orcl)
        )
      )

    ------
    客户端的sqlnet.ora 使用默认
    SQLNET.AUTHENTICATION_SERVICES = (NTS)
    =============


    试验一:
    首先,我们来看一下数据库服务器端的实际情况:
    通过putty 登录到Linux系统的oracle 用户,停掉数据库,并检查监听情况:
    ------
    [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...     《=========服务里面并没有ORCL,因为数据库刚刚被shutdown了!
    The command completed successfully

    ------

    在Windows 客户端, 开始 菜单=》 运行 =》 输入 cmd 命令,
    进入dos 窗口,并输入 tnsping TTORCL, 返回
    ------
    C:Usersliyang>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.
    已使用的参数文件:
    C:appliyangproduct11.2.0client_2 etworkadminsqlnet.ora
    已使用 TNSNAMES 适配器来解析别名
    尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1   《=====TNSPING 仍然解析成功,返回TTORCL的具体内容!
    68.56.11)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
    OK (20 毫秒)

    ------
    上面返回的 “尝试连接” 后面部分是sqlnet 如何解析到的TTORCL 别名,或者叫 连接标识符。该别名的解析是通过客户端的 TNS_ADMIN指定路径下的 sqlnet.ora文件中的 NAMES.DIRECTORY_PATH 参数决定的。上面的试验结果显示的是通过 TNSNAMES 适配器 来解析成功的。
    该请求可以看到,是通过 TCP 的网络协议  (PROTOCOL = TCP), 连接到了 IP地址为 192.168.56.11 上的服务器(也可以是主机名),希望连接的端口为 1521, 服务名为  (SERVICE_NAME = orcl)。
    连接20毫秒的意思是 一个预估的大概网络传输往返一次的时间。

    以上测试说明了,不论数据库是否运行,只要监听存在并在运行,tnsping 都可以成功解析!
    =============


    试验二:
    停止数据库实例,更改监听的端口为 1522 , 启动监听:
    ------
    [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                  
    《====没有数据库实例进程运行

    [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)))  
    《====端口已经更改!
    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)))   《======监听1522 正常运行
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...  《====没有监听到 ORCL的服务
    The command completed successfully

    ------
    接下来,回到客户端,执行  tnsping ttorcl
    ------
    C:Usersliyang>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.
    已使用的参数文件:
    C:appliyangproduct11.2.0client_2 etworkadminsqlnet.ora
    已使用 TNSNAMES 适配器来解析别名
    尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1   <===仍然返回成功解析和接受
    68.56.11)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
    OK (0 毫秒)
    ------
    这个试验二证明了,tnsping 即使在数据库实例停止的情况下,并没有监听1521端口,只要监听运行,tnsping就可以返回正常!
    =============


    试验三:
    那我们再来测试一下,监听停止的情况
    ------
    [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                                
    《=======监听停止
      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    
    《===没有数据库实例启动
    ------
    此时,在客户端再次执行 tnsping:
    ------
    C:Usersliyang>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.
    已使用的参数文件:
    C:appliyangproduct11.2.0client_2 etworkadminsqlnet.ora
    已使用 TNSNAMES 适配器来解析别名
    尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1
    68.56.11)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
    TNS-12541: TNS: 无监听程序                    <============解析仍然成功,但是报出 TNS-12541 的错误!
    ------
    以上测试说明了,监听停止之后,tnsping 无法被远端监听接受
    =============


    以下试验四,五开始之前,我们将启动数据库实例和监听
    ------
    [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
    ------


    试验四:
    删除本地的 tnsnames.ora,之后看一下会出现什么情况
    ------
    C:Usersliyang>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.

    已使用的参数文件:
    C:appliyangproduct11.2.0client_2 etworkadminsqlnet.ora

    TNS-03505: 无法解析名称               《=====================这次,连解析都无法成功!

    ------

    注:编辑tnsnames.ora,如果我将TTORCL 修改为 ORCL,则返回同样的结果

    ------
    C:Usersliyang>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.
    已使用的参数文件:
    C:appliyangproduct11.2.0client_2 etworkadminsqlnet.ora
    TNS-03505: 无法解析名称

    ------
    =============

    试验五:
    再来测试一个IP地址输入错误的情景
    修改tnsnames.ora文件,从192.168.56.11 改为一个不存在的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:Usersliyang>ping 192.168.56.12
    正在 Ping 192.168.56.12 具有 32 字节的数据:
    来自 192.168.56.1 的回复: 无法访问目标主机。    <====ping 显示了该IP不存在

    C:Usersliyang>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.
    已使用的参数文件:
    C:appliyangproduct11.2.0client_2 etworkadminsqlnet.ora
    已使用 TNSNAMES 适配器来解析别名
    尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1
    68.56.12)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
    TNS-12535: TNS: 操作超时            《=======结果是等了较长时间报出超时错误

    ------

    总结:
    tnsping 用于诊断各种客户端连接问题还是一个非常好的工具。
    但是我们要清楚的知道,这个工具能做什么(检查客户端TNS配置),什么不能做(检查数据库实例是否启动,监听端口是否在1521上)!

    从以上试验我们看到,tnsping的解析成功仅仅意味着本地的tnsnames.ora ,IP地址,TNS_ADMIN 变量等配置成功。
    但是,完全跟目标数据库实例连接没有任何关系!

    最后,sqlnet 跟数据库也完全是独立的两个问题。不要因为sqlnet连接方面出错,就肯定是数据库的问题。要从客户端自身先找原因。

  • 相关阅读:
    ***:做人的基本原则(看完终身受益)
    ref和out的相同和不同的讨论
    关于继承和虚函数的入门讨论
    C#中利用ArrayList来对索引器访问越界情况进行内容的扩充处理
    [C#]实现IEnumerable接口来使用foreach语句的一个实例
    浅谈MSSQL锁机制
    jQuery图片分屏加载技术插件
    SET IDENTITY_INSERT [Table] [ON|OFF]
    SQL Server DATEPART() 函数
    常用的SQL语句对数据库进行操作
  • 原文地址:https://www.cnblogs.com/wuchangsoft/p/12039640.html
Copyright © 2020-2023  润新知