oracle-databaseoracle11gsqlplusora-12514tnsping

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


We have an application running locally where we're experiencing the following error:

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

I've tested the connection using TNSPing which resolved correctly and I tried SQLPlus to try connecting, which failed with the same error as above. I used this syntax for SQLPlus:

sqlplus username/password@addressname[or host name]

We have verified that:

We don't know of any changes that were made to this environment. Anything else we can test?


Solution

  • I had this issue and the fix was to make sure in tnsnames.ora the SERVICE_NAME is a valid service name in your database. To find out valid service names, you can use the following query in oracle:

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

    Once I updated tnsnames.ora to:

    TEST =
       (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = *<validhost>*)(PORT = *<validport>*))
        )
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = *<servicenamefromDB>*)
        )
    )
    

    then I ran:

    sqlplus user@TEST
    

    Success! The listener is basically telling you that whatever service_name you are using isn't a valid service according to the DB.

    (*I was running sqlplus from Win7 client workstation to remote DB and blame the DBAs ;) *)