Ora-12514: tns:listener does not currently know of service requested in connect descriptor

Question

Problem :

I have an application which is running locally where I am facing the following error:

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

Already I have tested the connection using TNSPing which resolved correctly also I have tried SQLPlus to try connecting but it failed with the same error as above. I used following syntax for SQLPlus:

sqlplus username/password@addressname[or host name]

I have already verified following action items:

·         TNS Listener on the server is running properly.

·         Oracle itself on the server is running properly.

Does anybody have any solution on my problem?

Answer 1

I also had the same error and the solution for it was to make sure in tnsnames.ora the SERVICE_NAME must be a valid service name in your database. To find out the valid service names in your database, you must use the following query in oracle:

select value from v$parameter where name='service_names'

Also I updated tnsnames.ora as follows:

TEST =

   (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = *<validhost>*)(PORT = *<validport>*))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = *<servicenamefromDB>*)

    )

)

After that I ran following command:

sqlplus user@TEST

And Success!! The listener is basically trying to tell you that your service_name that you are using is not the valid service according to the DB.