一个开发人员反映navicat不能登录Oracle数据库，请我帮忙看看：
我查看listener的日志里有下面的记录
<msg time='2022-03-09T08:05:44.750+00:00' org_id='oracle' comp_id='tnslsnr' type='UNKNOWN' level='16' host_id='ecology-data' host_addr='192.168.99.99'> <txt>09-MAR-2022 08:05:44 * (CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(PROGRAM=C:\Program?Files\PremiumSoft\Navicat?Premium?15\navicat.exe)(HOST=LENOVO-X270)(USER=vito))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.9.49)(PORT=58419)) * establish * ORCL * 12514 </txt></msg><msg time='2022-03-09T08:05:44.750+00:00' org_id='oracle' comp_id='tnslsnr' type='UNKNOWN' level='16' host_id='ecology-data' host_addr='192.168.99.99'> <txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor </txt></msg>
查询一下TNS-12514的错误：
$ oerr tns 1251412514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"// *Cause: The listener received a request to establish a connection to a// database or other service. The connect descriptor received by the listener// specified a service name for a service (usually a database service)// that either has not yet dynamically registered with the listener or has// not been statically configured for the listener. This may be a temporary// condition such as after the listener has started, but before the database// instance has registered with the listener.// *Action: // - Wait a moment and try to connect a second time.// - Check which services are currently known by the listener by executing:// lsnrctl services <listener name>// - Check that the SERVICE_NAME parameter in the connect descriptor of the// net service name used specifies a service known by the listener.// - If an easy connect naming connect identifier was used, check that// the service name specified is a service known by the listener.// - Check for an event in the listener.log file.
显然是Oracle的服务名设置错误，orcl的服务名是Oracle数据库最常用的服务名，难道会错？仔细看看listener的状态：
$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-MAR-2022 08:03:50 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.99)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionStart Date 18-FEB-2022 02:44:00Uptime 19 days 5 hr. 19 min. 50 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /oracledata/oracle/product/11.2.0/db_1/network/admin/listener.oraListener Log File /oracledata/oracle/diag/tnslsnr/aaaa-data/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.99.99)(PORT=1521)))Services Summary...Service "orcls" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully
发现服务名居然是orcls！把navicat里面的服务名对应改过来即可。实际上使用instance名也可以进行连接。
总结：
客户端连接失败的故障很常见，通常查询listener的日志就可以找到原因。
