ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

In this post I will try to show how efficiently we can avoid error ORA-12514. My suggestion is whenever you get this error forget about tnsnames.ora and other stuff related to it. Start fixing problem step by step.

Problem Description:
------------------------------------------------
Whenever you try to connect to database the following error comes.
-bash-3.00$ sqlplus shaik/a:1522/dba

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jun 24 06:35:02 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Cause of The problem:
-----------------------------------------
The services named issued with the connection identifier has not been registered yet.

Solution of The Problem:
--------------------------------------------
After getting above error forget any tnsnames.ora file. Issue lsnrctl status command on the server to which you try to connect like,
-bash-3.00$ lsnrctl status

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 07:17:56

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date 24-JUN-2008 07:01:52
Uptime 0 days 0 hr. 16 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC2)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=moon)(PORT=1522)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dbase" has 1 instance(s).
Instance dbase, status READY, has 1 handler(s) for this service...
Service "dbaseXDB" has 1 instance(s).
Instance "dbase", status READY, has 1 handler(s) for this service...
Service "dbase_XPT" has 1 instance(s).
Instance "dbase", status READY, has 1 handler(s) for this service...
The command completed successfully

Now closely look at the bolded items above , it is host, port, service and optionally instance.
Now use it in the connection descriptor as follows.

sqlplus user_id/password@host:port/service

Like here,
$sqlplus shaik/a@moon:1522/dbase
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 24 07:21:19 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

This method of connecting to database is called Easy Connect Naming Method.