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

2 comments:

  1. Great article, congratulations. You helped me to fix an issue that was working since 1 day!

    Regards.

    ReplyDelete
  2. SO HELPFULL THANX AJAY

    ReplyDelete

My Popular Posts