Oracle Distilled

Connectivity errors are common questions on Oracle related forums and in many cases they are highly misunderstood by the poster of the questions and some responders.

This aim of this document is to show a method for diagnosis and resolution to a scenario in which an ORA-12545 is raised during the connection to a database.

Problem

You attempt a connection to database through SQL*Plus and you receive the following error.

[oracle@ora2 ]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 30 10:29:27 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect hr@proddb
Enter password:
ERROR:
ORA-12545: Connect failed because target host or object does not exist

SQL>

Diagnosis process

If you are unfamiliar with the error use the oerr utility to get more information on the error.

[oracle@ora2 ]$ oerr ora 12545
12545, 00000, "Connect failed because target host or object does not exist"
// *Cause: The address specified is not valid, or the program being
// connected to does not exist.
// *Action: Ensure the ADDRESS parameters have been entered correctly; the
// most likely incorrect parameter is the node name.  Ensure that the
// executable for the server exists (perhaps "oracle" is missing.)
// If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
// host name to a numeric IP address and try again.
[oracle@ora2 ]$

Nowhere in the error description above is any mention of this problem being related to the listener. This error is due to the inability of TNS to contact the host in the ADDRESS parameter for the database entry. This could be due to a typo in the host name or IP address. You do not have to take my word for it though. If you set up Oracle Net Services tracing on the client you should see something similar to the following.

(3086055104) niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=pr0d)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=proddb)(CID=(PROGRAM=sqlplus)(HOST=ora2.localdomain)(USER=oracle))))
(3086055104) nscall: connecting...
(3086055104) snlinGetAddrInfo: getaddrinfo() failed with error -3
(3086055104) nlad_expand_hst: GetAddrInfo call failed
(3086055104) nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pr0d)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=proddb)(CID=(PROGRAM=sqlplus)(HOST=ora2.localdomain)(USER=oracle))))
(3086055104) snlinGetAddrInfo: getaddrinfo() failed with error -2
(3086055104) nttbnd2addr: looking up IP addr for host: pr0d
(3086055104) snlinGetAddrInfo: getaddrinfo() failed with error -3
(3086055104) nttbnd2addr:  *** hostname lookup failure! ***
(3086055104) nserror: nsres: id=0, op=77, ns=12545, ns2=12560; nt[0]=515, nt[1]=111, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
(3086055104) nioqper:  error from nscall
(3086055104) nioqper:    ns main err code: 12545
(3086055104) nioqper:    ns (2)  err code: 12560
(3086055104) nioqper:    nt main err code: 515
(3086055104) nioqper:    nt (2)  err code: 111
(3086055104) nioqper:    nt OS   err code: 0
(3086055104) niqme: reporting NS-12545 error as ORA-12545
(3086055104) niotns: Couldn't connect, returning 12545

Here we see that the call to getaddrinf() failed with error. There was also a failure in attempting to get the IP address of pr0d. So here the trace shows that we were not even able to contact the host server let alone the listener.

Resolution

In the trace we can see the typo in the name of the host. The HOST= should be prod not pr0d. Changing that in the ORACLE_HOME/network/admin/tnsname.ora should resolve the problem. A trace is not necessary to resolve this issue successfully. The output from the oerr utility gave us the correct direction. We could have easily looked at the ORACLE_HOME/network/admin/tnsnames.ora file and seen that the host name was incorrect.

The ORA-12545 is not an indication that the network is down or that the host file or DNS has an incorrect address for the host in question. In those cases an ORA-12543 will be given.