Irfan's World

January 24, 2011

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

Filed under: Oracle 9i/10g/11g — Tags: , — Irfan @ 10:38 pm

When you connect using SQL Developer or any other third party tools that uses JDBC thin client would return this error, but remember it will not give you this error when you connect using SQL*PLUS.

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

As the error message suggests, the database server did not register itself with the database listener.  To troubleshoot this connectivity issue, we will start with looking at TNSNAMES.ORA and LISTENER.ORA files on the client machine from which you want to connect. In other words, I would say there is something like SERVICE NAME or HOST name is incorrect in the listerner.ora or tnsnames.ora file on the client machine.

Note : If you receive this error first contact your DBA to resolve it first.

The files that we are discussing here are Oracle systems files, take the backup of these files before you change any settings.  These files contain information regarding the HOST name or IP address of Oracle database server(s) in your network.  When a client machine tries to connect to the server using oracle client middle ware then you have to make sure that the client machine has correct information of the database server (HOST name or IP Address).  I repeat HOST or IP Address.

Follow these two steps to resolve this issue:

First of all, make sure that both of these files are in sync, you must use only one of the below options, it means either

   1.     Use HOST name in both the files (TNSNAMES.ORA and LISTENER.ORA)

       or

          Use IP Address of the Database Server in both the files (TNSNAMES.ORA and LISTENER.ORA)

   2. Register the database with the listener.



Step 1:

The following screen shots shows that I am using the HOST name as ‘irfan-PC’. Here is a sample of tnsnames.ora file from my client machine.

Here is a sample of listener.ora file from my client machine in that you will see the HOST name of the database server. 

Step 2:

Now connect to SQL*PLUS as DBA and issue the following command:

ALTER SYSTEM REGISTER

This command will register the database with the listener.

Now connect SQL Developer to oracle database :

Following screen shot shows some mandatory details when you connect using SQL Developer that will ensure to connect to an oracle database using SQL Developer.

Click ‘Test’ to check the connection and it should connect without issues. Hope you saved some time by resolving this error :).

Let me know if there are any other alternatives to resolve this error.

Blog at WordPress.com.