ORA-12514: TNS:listener could not resolve SERVICE_

应用不能连接数据库了。应用日志里发现如下错误：

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect。

解决办法如下：

$ tnsping arsprd.emea.net

TNS Ping Utility for Solaris: Version 9.2.0.7.0 - Production on 22-JUN-2010 04:07:53

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
/global/oracle/orahome1/product/9.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.224.200)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ARSPRD.EMEA.NET)))
OK (0 msec)

注意：SERVICE_NAME = ARSPRD.EMEA.NET。
$ sqlplus

SQL*Plus: Release 9.2.0.7.0 - Production on Tue Jun 22 04:08:12 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor

$ lsnrctl

LSNRCTL for Solaris: Version 9.2.0.7.0 - Production on 22-JUN-2010 04:08:54

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 9.2.0.7.0 - Production
Start Date                22-JUN-2010 04:07:36
Uptime                    0 days 0 hr. 1 min. 20 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /global/oracle/orahome1/product/9.2.0/network/admin/listener.ora
Listener Log File         /global/oracle/orahome1/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Orc01ud-lon01)(PORT=1521)))
Services Summary...
Service "ARSPRD.sevenspace.net" has 1 instance(s).
  Instance "ARSPRD", status READY, has 1 handler(s) for this service...
The command completed successfully

注意：在linster里：Service "ARSPRD.sevenspace.net"

修改listener.ora or tnsnames.ora文件其中一个，让其与另一个文件里的配置一致就可以了.

这里的listener是动态注册的，服务名可以在初始化参数里修改。

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Tue Jun 22 04:06:56 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mts_service                          string      ARSPRD
service_names                        string      ARSPRD
SQL> alter system set service_names='ARSPRD.EMEA.NET';

System altered.

SQL>

这时再连接就可以了。