How to resolve error TNS:listener does not currently know of service requested in connect descriptor

This time we would like to look at one of problem in oracle, especially “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”.

The vast majority of the time, this error results from an incorrectly specified connect descriptor in the tnsnames.ora file. Let’s look at a very typical example then diagnose and fix it. After that we will dig in to how the listener comes to know of a service name.

For the first we need to analyze why the error occured by starting listener.

[oracle@vmlnx01 admin]$ lsnrctl start
 
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 18:44:49
 
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
 
Starting /ora00/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Log messages written to /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                16-MAR-2011 18:44:49
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
The listener supports no services
The command completed successfully

It said that The listener supports no services. It means that the listener doesn't have any associates service. So the solution is we need to add a service to listener.ora as example below :

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME=vlnxora1)
      (ORACLE_HOME = /ora00/app/oracle/product/10.2.0/db_1)
      (SID_NAME = vlnxora1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01.vmdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

Restart listener by command lsnrctl stop followed by lsnrctl start. Now you can try login using your oracle user e.g : sqlplus scott@vlnxora1

If you get this problem in your oracle client installation, please make sure that your service name is configured correctly. To check that, do the following steps :

1. Edit tnsnames.ora
2. Make sure that service name in connect descriptor is same with oracle database service name.
3. To check oracle database service name, login to your oracle database server and execute the query below : 

   sqlplus / as sysdba
   select value from v$parameter where name='service_names';
   show parameter service_names;

4. To change oracle service name you can use alter

   ALTER SYSTEM SET SERVICE_NAMES='application_a.your.domain','application_b.your.domain' SCOPE=BOTH;