ORA-12154: TNS could not resolve the connect identifier specified

ORA-12154: TNS could not resolve the connect identifier specified

ORA 12154: TNS Listener does not currently know of service requested

Solution Checklist:

ORA 12154 looks a generic & very much known but getting cause for this error is the trick to resolve it quickly. Below 8 point checklist will ensure all basic's are followed & 9th point will be the final one which will take you closure to the resolution

1. Verify if you have compatible client version with RDBMS db version

10g, 11g or higher oracle version client are compatible with 9i or higher database versions but 8i or lower oracle databases need specific versions of oracle clients only. 

When 32 bit oracle client software is installed on 64 bit windows server it appends its own characters/braces() in connection description and results in a exception.Such issue can be tackled by installing 32 bit client in any other drive like d: , e: but not inside c: drive program files (64)

2. Client Server has only single oracle client binary installed

Mostly seen in windows machines previous oracle clients are not cleanly removed & reflecting multiple client in the same server. Need to remove all old client along with their directory structures correctly if not done by a clean removal method & have a reboot of the system before proceeding with new client installation 

3. tnsnames.ora file contains proper TNS Entry formatting

Having correct TNS entry is the key to resolving ORA-12154 error, make it as simple as below

mydb =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP) (HOST = hostname) (PORT = 1527))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = mydb)

    )

  )

If you suspect there are any missing braces or unknown issue in formatting just use below single liner

mydb=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT = 1527)))(CONNECT_DATA = (SERVICE_NAME = mydb)))

and you can easily test its resolution  through TNS alias or using description itself as below

Yes this single liner will work , I am not keen to show any example snaps here as I believe you will test yourself instead of assuming it from my snaps

tnsping "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT = 1527)))(CONNECT_DATA = (SERVICE_NAME = mydb)))“

4. Host which you are trying to connect is pingable & added in /etc/hosts

Coming from point 3 one of the basic check should be the resolution of hostname specified in HOST clause of TNS entry which can be an IP address or domain/DNS , LDAP or /etc/hosts resolvable entry

Whichever it is make sure it is the correct one

5. telnet Host Port is working

Checking correctness of hostname specified in HOST, we just have ping & telnet.

If it is pinging then check telnet to DB port on which listener is listening

telnet <IP> <Port>

In most of the cases ping & telnet seems to be working but error does not disappear , don’t lose the battle yet still few more points to be checked

6. DB service/Listener you are trying to connect is up & running on hosted db server

Yes this check looks obvious but do not skip it, may be listener is up on your DB server or although it is up but running with wrong IP (may be cluster IP) or hostname. So verify & compare TNS entry of the server against the client address and confirm its resolution to yourself

7. tnsping <TNS_Alias> is working from client

tnsping is a great tool to troubleshoot oracle net relevant issues , verify if it resolves through correct address description & within stipulated time

8. sqlplus <user>@<TNS_Alias>/<password> is working

Using sqlplus will isolate the issue if ORA-12154 is faced only in third party tools or application . sqlplus is an inbuilt oracle application development tool. Traversing it via tns alias will allow to replicate the issue & can be tested/fixed by yourself only rather than asking user to replicate it for you.

9. Troubleshoot using tracing 

If above all basic check has not helped then use tracing for sqlplus session by following the link, which will surely help to get the cause & can be fixed accordingly 

Before enabling tracing for the session make sure you are able to replicate the error which will get capture in the trace file along with the error around it

ORA-12545: Connect failed because target host or object does not exist

Let me know if all above doesn't help !!

Also Refer ...

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