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

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


Background:

ORA-12545 is a generic error as a result of TNS configuration issue at client.
TNS configuration issue is unable to resolve host / IP specified in TNS entry
ORA-12545 is observed in DB links & remote connections via TNS alias



It has also been noticed in remote RAC databases wherein intermittent error being thrown while connecting to particular instance listed in address list

Analysis:

Analysis can be done starting with basic TNS checks like

1. ping <DB_HOST/IP>
2. telnet <DB_HOST/IP> <PORT>
3. tnsping <Tns_Alias>
4. sqlplus <username>@<TNS_Alias>/<password>
5. TNS entry & formatting


Checks 1 to 3 found to be successful when ORA-12545 is faced, but 4 & 5 will hold you back & making them successful will resolve the error




I have faced this issue intermittently while connecting to remote RAC database having two instances.

HOST= of TNS entry drives this error will see how to capture & resolve it

Best method to identify the root cause is to enable the trace at client (which can be local DB server also)


Update $ORACLE_HOME/network/admin/sqlnet.ora file with below tracing parameters with appropriate resolvable folder location & restart the listener


$cat sqlnet.ora
trace_level_server = 16
trace_level_client = 16
trace_file_server = svr_bo
trace_directory_server = /oracle10g/app/oracle/product/10.2.0/Db_1/network/trace
trace_unique_server = on
trace_timestamp_server = on
log_file_server = svr_bo
log_directory_server = /oracle10g/app/oracle/product/10.2.0/Db_1/network/log
NOTE: In 11g due to diagnostic dest need to supply trace directory server path to something like below as per your diag path settings. In case trace files not found mine the diagnostic_dest

/oravl01/ora11g/app/oradiag_ora11g/diag/clients/user_ora11g/host_3427921560_80/trace
Below is the TNS entry involved in TNS issue


AMSDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orahostdb1a.mheb.co.in)(PORT = 1529))
(ADDRESS = (PROTOCOL = TCP)(HOST = orahostdb2b.mheb.co.in)(PORT = 1529))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = AMSDB)
)
)


It is advisable to keep /etc/hosts updated with remote database server host entries referred in tnsnames.ora


$cat "/etc/hosts" | tail -2
10.133.1.20 orahostdb1a.mheb.co.in
10.133.1.22 orahostdb2b.mheb.co.in


To get trace file with error being captured in it, re-produce the error with few attempts till error is received using sqlplus connection to remote database over TNS





$sqlplus LINK_SDAMSDB@AMSDB
password: 

SQL Plus: Release 10.2.0.5.0 - Production on Tue Mar 5 16:56:32 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
ERROR:
ORA-12545: Connect failed because target host or object does not exist

Enter user-name:



Till the time error is received on prompt there could be multiple files generated in trace location, to identify the exact trace file for analysis use grep to find trace files with "12545" error code

Once trace file with "12545" error code identified open the file & start searching for the "err" or "error" string

[05-MAR-2013 16:47:57:958] nscall: connecting...
[05-MAR-2013 16:47:57:958] nsc2addr: entry
[05-MAR-2013 16:47:57:958] nsc2addr: (ADDRESS=(PROTOCOL=TCP)(HOST=orahostdb1a)(PORT=1529))
[05-MAR-2013 16:47:57:958] nttbnd2addr: entry
[05-MAR-2013 16:47:57:958] snlinGetAddrInfo: entry
[05-MAR-2013 16:47:57:958] snlinGetAddrInfo: Invalid IP address string orahostdb1a
[05-MAR-2013 16:47:57:958] snlinFreeAddrInfo: entry
[05-MAR-2013 16:47:57:958] snlinFreeAddrInfo: exit
[05-MAR-2013 16:47:57:958] snlinGetAddrInfo: exit
[05-MAR-2013 16:47:57:958] nttbnd2addr: looking up IP addr for host: orahostdb1a
[05-MAR-2013 16:47:57:958] snlinGetAddrInfo: entry
[05-MAR-2013 16:47:57:975] snlinGetAddrInfo: Name resolution failed for orahostdb1a
[05-MAR-2013 16:47:57:975] snlinFreeAddrInfo: entry
[05-MAR-2013 16:47:57:975] snlinFreeAddrInfo: exit
[05-MAR-2013 16:47:57:975] snlinGetAddrInfo: exit
[05-MAR-2013 16:47:57:975] nttbnd2addr: *** hostname look up failure!***
[05-MAR-2013 16:47:57:976] nttbnd2addr: exit
[05-MAR-2013 16:47:57:976] nserror: entry
[05-MAR-2013 16:47:57:976] nserror: nsres: id=0, op=77, ns=12545, ns2=12560; nt[0]=515, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[05-MAR-2013 16:47:57:976] nsc2addr: error exit
[05-MAR-2013 16:47:57:976] nsmfr: entry
[05-MAR-2013 16:47:57:976] nsmfr: 193 bytes at 0x1001c8a30
[05-MAR-2013 16:47:57:976] nsmfr: normal exit
[05-MAR-2013 16:47:57:977] nsmfr: entry
[05-MAR-2013 16:47:57:977] nsmfr: 264 bytes at 0x1001b52f0
[05-MAR-2013 16:47:57:977] nsmfr: normal exit
[05-MAR-2013 16:47:57:977] nladtrm: entry
[05-MAR-2013 16:47:57:977] nladtrm: exit
[05-MAR-2013 16:47:57:977] nscall: error exit
[05-MAR-2013 16:47:57:977] nioqper: error from nscall
[05-MAR-2013 16:47:57:977] nioqper: ns main err code: 12545
[05-MAR-2013 16:47:57:977] nioqper: ns (2) err code: 12560
[05-MAR-2013 16:47:57:977] nioqper: nt main err code: 515
[05-MAR-2013 16:47:57:977] nioqper: nt (2) err code: 0
[05-MAR-2013 16:47:57:977] nioqper: nt OS err code: 0
[05-MAR-2013 16:47:57:977] niomapnserror: entry



Cause:

Here the issue found to be with host-name as nscall was searching for orahostdb1a entry & found orahostdb1a.mheb.co.in

/etc/hosts file was unable to resolve orahostdb1a host entry


Solution:

Update /etc/hosts with orahostdb1a as below so that it can be resolved by tns


$cat "/etc/hosts" | tail -2
10.133.1.20 orahostdb1a.mheb.co.in orahostdb1a
10.133.1.22 orahostdb2b.mheb.co.in orahostdb2b


Re-try logging with sqlplus over tns multiple times, error should dis-appear


Once trace file is identified, disable the client tracing by commenting the parameters as below and restart the listener to avoid trace file generation


$cat sqlnet.ora
#trace_level_server = 16
#trace_level_client = 16
#trace_file_server = svr_bo
#trace_directory_server = /oracle10g/app/oracle/product/10.2.0/Db_1/network/trace
#trace_unique_server = on
#trace_timestamp_server = on
#log_file_server = svr_bo
#log_directory_server = /oracle10g/app/oracle/product/10.2.0/Db_1/network/log
Hope You got Success !!!




Do refer below link for Network errors :ORA-12520: TNS:listener could not find available handler for requested type of server
SQL*Net message to dblink , SQL*Net message from dblink
Error connecting ASM ORA-15055: unable to connect to ASM instance Fatal NI connect error 12547 ORA-12547: TNS:lost contact