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

Introduction:

We had issue with Listener connectivity from client. Even the Listener was running, but it was throwing the following error message.

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

Troubleshooting:

1- We checked with the tnsping utility from client side, and server side, and it was all working, it was displaying the following message.

TNS Ping Utility for 32-bit Windows: Version 9.2.0.8.0 - Production on 20-APR-20
09 10:02:32

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

Used parameter files:
D:\oracle\ora92\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = TESTSERVER)(PORT = 1528))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_N
AME = TESTDB)))
OK (20 msec)

2- When we checked through sql*plus it was showing the following message.

H:\>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Apr 20 10:02:52 2009

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

Enter user-name: system@TESTDB
Enter password:
ERROR:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor

3- We checked the local_listener parameter in the database and it was not set to any of the value.

SQL> show parameter local

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string

4- Here are the listener.ora and tnsnames.ora files. The important point to note here is the port number was 1528

LISTNER.ORA

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTSERVER)(PORT = 1528))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDB)
(ORACLE_HOME = /u02/oracle/products/9.2.0)
(SID_NAME = TESTDB)
)
)

TNSNAMES.ORA

TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTSERVER)(PORT = 1528))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)
Resolution:

1- We set the LOCAL_LISTENER parameter to service name "TESTDB" and checked and it was all working.

H:\>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Apr 20 10:02:52 2009

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

Enter user-name: system@TESTDB_ora
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL>

2- Following was the value of LOCAL_LISTENER parameter

SQL> show parameter local_list

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string TESTDB