Today I observed that we were getting following error when we were going to connect using SERVICE_NAME from (DR site) to new exadata server.

The connection was working fine with SID in tnsnames.ora to individual instances.

[oracle@XXX admin]$ sqlplus dba/XXX@PROD_CDV

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 27 09:08:38 2017

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

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

Enter user-name:

I was using following tns:-

PROD_CDV =
(description=
(load_balance=on)
(address=(protocol=tcp)(host= x.x.x.x )(port=1521))
(address=(protocol=tcp)(host= x.x.x.y )(port=1521))
(connect_data=
(service_name= PROD.world)
)
)

The ping,tnsping were absolutely fine.

I enable trace in sqlnet.ora from client site

vi $ORACLE_HOME/network/admin

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
TRACE_LEVEL_CLIENT=USER
TRACE_FILENO_CLIENT=6
TRACE_FILELEN_CLIENT=51200
TRACE_UNIQUE_CLIENT=ON
TRACE_TIMESTAMP_CLIENT=ON
TRACE_DIRECTORY_CLIENT=/home/oracle/client_trace
LOG_DIRECTORY_CLIENT=/home/oracle/client_trace
DIAG_ADR_ENABLED=OFF
ADR_BASE = /u01/app/oracle

and found below issue in trace file generated under /home/oracle/client_trace:-

(3267593728) [000001 27-NOV-2017 09:08:48:587] nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prod01vm03-vip)(PORT=1521)))
(3267593728) [000001 27-NOV-2017 09:08:48:587] snlinGetAddrInfo: getaddrinfo() failed with error -2
(3267593728) [000001 27-NOV-2017 09:08:48:587] nttbnd2addr: looking up IP addr for host: prod01vm03-vip
(3267593728) [000001 27-NOV-2017 09:08:58:608] snlinGetAddrInfo: getaddrinfo() failed with error -3
(3267593728) [000001 27-NOV-2017 09:08:58:608] nttbnd2addr: *** hostname lookup failure! ***
(3267593728) [000001 27-NOV-2017 09:08:58:608] nserror: nsres: id=0, op=77, ns=12545, ns2=12560; nt[0]=515, nt[1]=110, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0

So it seems IP is getting implicitly converted to hostname .But this IP is not in DNS in DR site.

I added below entry of exadata VIP and physical IP in /etc/hosts in all 4 DR nodes

10.1.14.145 prod01vm03.tdeprdcl.world.com prod01vm03
10.1.14.147 prod02vm03.tdeprdcl.world.com prod02vm03
10.1.14.146 prod01vm03-vip.tdeprdcl.world.com prod01vm03-vip
10.1.14.148 prod02vm03-vip.tdeprdcl.world.com prod02vm03-vip

After that connection worked fine.

Reference:-

• 454927.1 Using and Disabling the Automatic Diagnostic Repository (ADR) with Oracle Net for 11g
• 219968.1 SQL*Net & Oracle Net Services – Tracing and Logging at a Glance
• 834822.1 Oracle Net Diagnostics
• 1076022.1 Examples of Troubleshooting Slow Oracle Net Connections