一次访问问题排查-涉及TNS-03505、ORA-12154、TNS-12560、动态注册、防火墙、tnsping跟踪等

建了一个库，想通过Oracle Net访问，需要配置监听器和tnsnames.ora，接下来碰到一系列的问题。。。 1. 添加监听器配置，listener.ora文件默认包括： LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))     )   ) ADR_BASE_LISTENER = /opt/app/ora11g 为了新建监听器名称，添加如下： DCSOPEN =   (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = 172.101.19.57)(Port = 1521))) 或 DCSOPEN =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = dcsopen2Node)(PORT = 1521))     )   ) ADR_BASE_DCSOPEN = /opt/app/ora11g 或 DCSOPEN =   (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = 172.101.19.57)(Port = 1521))) SID_LIST_DCSOPEN =   (SID_LIST =     (SID_DESC =       (SID_NAME = PLSExtProc)       (ORACLE_HOME = /opt/app/ora11g/product/11.2.0/dcsopen)       (PROGRAM = extproc)     )     (SID_DESC =       (GLOBAL_DBNAME = dcsopen)       (ORACLE_HOME = /opt/app/ora11g/product/11.2.0/dcsopen)       (SID_NAME = dcsopen)     )   ) ADR_BASE_DCSOPEN = /opt/app/ora11g 或 DCSOPEN =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS_LIST =         (ADDRESS = (PROTOCOL = TCP)(HOST = 172.101.19.57)(PORT = 1521))       )       (ADDRESS_LIST =         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))       )     )   ) SID_LIST_DCSOPEN =   (SID_LIST =     (SID_DESC =       (SID_NAME = PLSExtProc)       (ORACLE_HOME = /opt/app/ora11g/product/11.2.0/dcsopen)       (PROGRAM = extproc)     )   ) ADR_BASE_DCSOPEN = /opt/app/ora11g 2. 添加本机的tnsnames.ora文件配置： dcsopen =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 172.101.19.57)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = dcsopen)     )   ) 3. 使用tnsping dcsopen测试，报错： ora11g>tnsping dcsopen TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-JAN-2015 00:38:55 Copyright (c) 1997, 2009, Oracle.  All rights reserved. Used parameter files: /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/sqlnet.ora TNS-03505: Failed to resolve name 4. 使用sqlplus登录测试，报错： ora11g>sqlplus dcsopen/dcsopen1@dcsopen SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 13 23:11:00 2010 Copyright (c) 1982, 2005, Oracle.  All rights reserved. ERROR: ORA-12154: TNS:could not resolve the connect identifier specified Enter user-name: 5. 检查监听器状态， ora11g>lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-JAN-2015 01:55:32 Copyright (c) 1991, 2009, Oracle.  All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date                07-JAN-2015 20:19:09 Uptime                    0 days 5 hr. 36 min. 23 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/listener.ora Listener Log File         /opt/app/ora11g/diag/tnslsnr/dcsopen2Node/listener/alert/log.xml Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dcsopen2Node)(PORT=1521))) ... 并未看到dcsopen的配置。 6. 开启trace，查看tnsping失败的原因： 创建sqlnet.ora文件： # sqlnet.ora Network Configuration File: /opt/oracle/102/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) Trace_level_client=16 Trace_directory_client=/opt/app/ora11g/product/11.2.0/dcsopen/network/admin Trace_unique_client=on Trace_timestamp_client=on Diag_adr_enabled=off tnsping.trace_directory=/opt/app/ora11g/product/11.2.0/dcsopen/network/admin tnsping.trace_level=admin 执行tnsping报错后，查看tnsping.trc文件： tail: tnsping.trc: file truncated TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-JAN-2015 19:21:31 Copyright (c) 1997, 2009, Oracle.  All rights reserved. --- TRACE CONFIGURATION INFORMATION FOLLOWS --- New trace stream is /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/tnsping.trc New trace level is 6 --- TRACE CONFIGURATION INFORMATION ENDS --- --- PARAMETER SOURCE INFORMATION FOLLOWS --- Attempted load of system pfile source /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/sqlnet.ora Parameter source loaded successfully -> PARAMETER TABLE LOAD RESULTS FOLLOW <- Successful parameter table load -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-   Diag_adr_enabled = off   tnsping.trace_level = admin   Trace_level_client = 16   NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)   tnsping.trace_directory = /opt/app/ora11g/product/11.2.0/dcsopen/network/admin   Trace_unique_client = on   Trace_directory_client = /opt/app/ora11g/product/11.2.0/dcsopen/network/admin   Trace_timestamp_client = on --- 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 /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/tnsnames.ora nnftmlf_make_system_addrfile: exit nnftboot: exit nnftrne: entry nnftrne: Original name: dcsopen nnfttran: entry nnfgrne: Query unsuccessful, skipping to next adapter nnfgrne: Switching to EZCONNECT adapter nnfhboot: entry nnfhboot: exit snlinGetAddrInfo: entry snlinGetAddrInfo: getaddrinfo() failed with error -3 snlinGetAddrInfo: exit nnfgrne: Query unsuccessful, skipping to next adapter nnfgrne: exit nnfun2a: address for name "dcsopen" not found nnfun2awanm: Getting the path of sqlnet.ora nnfun2awanm: Getting the path of local and system tnsnames.ora nnfun2awanm: exit nlse_term_audit: entry nlse_term_audit: exit 可以看到其中出现的一些错误： construction of local names file failed Query unsuccessful, skipping to next adapter getaddrinfo() failed with error -3 address for name "dcsopen" not found 直观看，没有识别出dcsopen监听。 后来查询MOS有篇文章(Client Connections Fail With TNS-12154 / ORA-12154 (文档 ID 1150680.1)) 说这个情况的原因可能是：This means Oracle Net is unable to read the file correctly or entry inside the file. 解决方法是：Rebuild the TNSNAMES.ORA file, using the GUI Net Manager tool, is the recommend solution. This will ensure there are no mistakes in the net admin file, for example, brackets, tab, spacing, etc.   Also ensure the tnsnames.ora file can be read by the oracle user. 6. 于是先使用图形化netca创建dcsopen的监听项错误依旧，经过一系列测试，确定了文件listener.ora： # listener.ora Network Configuration File: /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/listener.ora # Generated by Oracle configuration tools. DCSOPEN =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS_LIST =         (ADDRESS = (PROTOCOL = TCP)(HOST = 172.101.19.57)(PORT = 1521))       )       (ADDRESS_LIST =         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))       )     )   ) SID_LIST_DCSOPEN =   (SID_LIST =     (SID_DESC =       (SID_NAME = PLSExtProc)       (ORACLE_HOME = /opt/app/ora11g/product/11.2.0/dcsopen)       (PROGRAM = extproc)     )   ) ADR_BASE_DCSOPEN = /opt/app/ora11g 其中： (1) 参考eygle的经验，使用动态注册服务。 (指当实例启动后，由后台进程PMON在监听器中注册数据库服务信息。动态注册机制下，原来监听器中的SID_LIST部分将不再需要。) (2) 上面之所以还有一个SID_LIST，这是缺省的PLSExtProc是为外部存储过程调用而配置。一个简单的监听器配置如上所述。 启动监听，提示： ora11g>lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-JAN-2015 01:55:32 Copyright (c) 1991, 2009, Oracle.  All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date                07-JAN-2015 20:19:09 Uptime                    0 days 5 hr. 36 min. 23 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/listener.ora Listener Log File         /opt/app/ora11g/diag/tnslsnr/dcsopen2Node/listener/alert/log.xml Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dcsopen2Node)(PORT=1521))) Services Summary... Service "dcsopen" has 1 instance(s).   Instance "dcsopen", status READY, has 1 handler(s) for this service... Service "dcsopenXDB" has 1 instance(s).   Instance "dcsopen", status READY, has 1 handler(s) for this service... The command completed successfully 本机测试tnsping正常了。 7. 接着，使用netca新增tnsnames.ora文件： # tnsnames.ora Network Configuration File: /opt/app/ora11g/product/11.2.0/dcsopen/network/admin/tnsnames.ora # Generated by Oracle configuration tools. DCSOPEN =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 172.101.19.57)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = dcsopen)     )   ) 本机测试sqlplus ...@dcsopen正常了。 8. 从另外一台机器访问这个数据库dcsopen，修改tnsnames.ora文件，执行tnsping dcsopen报错： ora10g@localhost.localdomain$tnsping dcsopen TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 08-JAN-2015 00:51:37 Copyright (c) 1997, 2005, Oracle.  All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.101.19.57)(PORT = 1521))) (CONNECT_DATA = (service_name = dcsopen))) TNS-12560: TNS:protocol adapter error 执行sqlplus ...@dcsopen报错： SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 8 00:58:14 2015 Copyright (c) 1982, 2005, Oracle.  All rights reserved. ERROR: ORA-12560: TNS:protocol adapter error 于是先看看端口是否开通，执行telnet报错： ora10g@localhost.localdomain$telnet 172.101.19.57 1521 Trying 172.101.19.57... telnet: connect to address 172.101.19.57: No route to host 如果端口未开，实际报错： ora10g@localhost.localdomain$telnet 172.27.19.56 1521 Trying 172.27.19.56... telnet: connect to address 172.27.19.56: Connection refused 是不是防火墙的问题？？？ 从数据库服务器关闭防火墙： [root@dcsopen2Node ~]# service iptables stop iptables: Flushing firewall rules: [  OK  ] iptables: Setting chains to policy ACCEPT: nat mangle filter [  OK  ] iptables: Unloading modules: [  OK  ] 再从远程机器执行： ora10g@localhost.localdomain$telnet 172.101.19.571521 Trying 172.101.19.57... Connected to 172.101.19.57. Escape character is '^]'. 说明端口已开，更重要的是，明确了，就是防火墙问题。于是参考，将/etc/sysconfig/iptables文件新增一行，表示允许1521端口访问： [root@dcsopen2Node sysconfig]# vi iptables # Firewall configuration written by system-config-firewall # Manual customization of this file is not recommended. *filter :INPUT ACCEPT [0:0] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [0:0] -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT -A INPUT -p icmp -j ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited COMMIT 启动防火墙： [root@dcsopen2Node sysconfig]# service iptables start iptables: Applying firewall rules: [  OK  ] 或service iptables restart 从远程机访问： ora10g@localhost.localdomain$sqlplus dcsopen/dcsopen1@dcsopen SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 8 01:11:12 2015 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

正常了。

总结：

1. listener.ora和tnsnames.ora配置文件最好使用netca工具创建，否则手工修改很可能出现各式问题导致无法解析读取。

2. 我的排查思路是，先确保本机可以tnsping和sqlplus ...@xxx，本机可访问了，再看远程机器。

远程访问tnsping和sqlplus报错TNS-12560: TNS:protocol adapter error，说明可能两台机器之间的连接有问题而不是监听自身的问题。

3. telnet报错是No route to host，不是Connection refused，不可轻易认为是网络端口未开。因为此时表示可以正常访问到另一台远程机器了，应该考虑到是否是防火墙的问题。此时可以通过关闭防火墙测试是否会出现这个问题来判断。

4. 会使用tnsping的trace跟踪，设置sqlnet.ora配置文件。

5. 动态注册的使用。好处：简化监听器配置、连接时failover(RAC)、运行时负载均衡(RAC)。

6. 解决过程中，有的帖子说需要设置$TNS_ADMIN环境变量，尽管这是有两个Oracle版本同机，但后来测试不配置，也可以访问，说明这不是关键问题。

解决过程中，有的帖子说sqlnet.ora中的NAME.DEFAULT_DOMAIN参数，以为从跟踪日志tnsping.trc看default name server domain is [root]，可后来没有设置，也可以访问，说明这不是关键问题。

解决过程中，对我有帮助的帖子：

http://blog.itpub.net/7199859/viewspace-374281/ http://blog.itpub.net/519536/viewspace-673794/ http://blog.sina.com.cn/s/blog_9151e7300101ksui.html http://www.cnblogs.com/chinaairforce1/archive/2009/10/22/1588103.html http://blog.csdn.net/huzia/article/details/21526043

7. 最重要的一条总结：不放弃！

辅助知识：

执行netca前需要root执行xhost +，否则提示：

java.lang.NullPointerException         at oracle.ewt.lwAWT.BufferedApplet.<init>(Unknown Source)         at oracle.net.ca.NetCA.<init>(NetCA.java:420)         at oracle.net.ca.NetCA.main(NetCA.java:406)

仍存问题：

对比如下配置，未发现不同点，奇怪：

手工创建：   dcsopen =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 172.101.19.57)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = dcsopen)     )   )

工具创建：

DCSOPEN =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 172.101.19.57)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = dcsopen)     )   )