目前很多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:\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 使用默认 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:\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. 已使用的参数文件: 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的具体内容! 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:\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. 已使用的参数文件: 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 毫秒) ------ 这个试验二证明了,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:\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. 已使用的参数文件: 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: 无监听程序 <============解析仍然成功,但是报出 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:\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.
已使用的参数文件: C:\app\liyang\product\11.2.0\client_2\network\admin\sqlnet.ora
TNS-03505: 无法解析名称 《=====================这次,连解析都无法成功! ------
注:编辑tnsnames.ora,如果我将TTORCL 修改为 ORCL,则返回同样的结果 ------ 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. 已使用的参数文件: C:\app\liyang\product\11.2.0\client_2\network\admin\sqlnet.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:\Users\liyang>ping 192.168.56.12 正在 Ping 192.168.56.12 具有 32 字节的数据: 来自 192.168.56.1 的回复: 无法访问目标主机。 <====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. 已使用的参数文件: 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: 操作超时 《=======结果是等了较长时间报出超时错误 ------
总结: tnsping 用于诊断各种客户端连接问题还是一个非常好的工具。 但是我们要清楚的知道,这个工具能做什么(检查客户端TNS配置),什么不能做(检查数据库实例是否启动,监听端口是否在1521上)!
从以上试验我们看到,tnsping的解析成功仅仅意味着本地的tnsnames.ora ,IP地址,TNS_ADMIN 变量等配置成功。 但是,完全跟目标数据库实例连接没有任何关系!
最后,sqlnet 跟数据库也完全是独立的两个问题。不要因为sqlnet连接方面出错,就肯定是数据库的问题。要从客户端自身先找原因。
我们后续将推出关于客户端连接相关问题的诊断文章,敬请期待! -- Jason Yang
|