There is no trcroute
for the instant client, but even if you had the full administrator client, trcroute
may not return anything useful.
By the way, from the command line I can ping the destination DB (defined in tnsnames.ora) just fine, so I believe I can completely
rule out the networking (e.g. DNS/firewall/accessibility).
And that is the mistake people usually make, based on a false assumption. Just because you can ping, let's say, even tnsping
the database server, does not mean at all you will be able to connect or name resolution works properly. Ping and tnsping
checks work only up to the first entry point in a database connection, and they stop there, these tools alone are not enough for testing database connections.
Based on my experience, ORA-12545
/TNS-12545
is a result of indirect name resolution problems. Even the official description suggests trying IP addresses instead of host names:
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.
Whenever you connect through a listener, the listener may forward your request to another address that a participant does not understand => ORA-12545
.
Make sure your client and server can resolve all addresses specified in the connection string, especially the addresses that appear in the output of lsnrctl services
on the database server.
And below is an example.
My client:
[oracle@o62 ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.6.2 o62.balazs.vm o62
192.168.12.5 o6ca-scan.balazs.vm o6ca-scan
My TNS entry:
[oracle@o62 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
MINDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = o6ca-scan.balazs.vm)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mindb)
)
)
Run some checks:
[oracle@o62 ~]$ ping o6ca-scan.balazs.vm -c 3
PING o6ca-scan.balazs.vm (192.168.12.5) 56(84) bytes of data.
64 bytes from o6ca-scan.balazs.vm (192.168.12.5): icmp_seq=1 ttl=64 time=0.090 ms
64 bytes from o6ca-scan.balazs.vm (192.168.12.5): icmp_seq=2 ttl=64 time=0.111 ms
64 bytes from o6ca-scan.balazs.vm (192.168.12.5): icmp_seq=3 ttl=64 time=0.094 ms
--- o6ca-scan.balazs.vm ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 1998ms
rtt min/avg/max/mdev = 0.090/0.098/0.111/0.012 ms
[oracle@o62 ~]$ tnsping mindb
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:35:06
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o6ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mindb)))
OK (0 msec)
I can ping the host in my TNS entry, even tnsping works, so I will be able to connect, right? Well, no:
[oracle@o62 ~]$ sqlplus bp/bp@mindb
SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 7 18:36:06 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-12545: Connect failed because target host or object does not exist
So let's check what trcroute
says:
[oracle@o62 ~]$ trcroute mindb
Trace Route Utility for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:36:59
Copyright (c) 1995, 2014, Oracle. All rights reserved.
Route of TrcRoute:
------------------
Node: Client Time and address of entry into node:
-------------------------------------------------------------
07-APR-2016 18:36:59 ADDRESS= PROTOCOL=TCP HOST=o6ca-scan.balazs.vm PORT=1521
Node: Server Time and address of entry into node:
-------------------------------------------------------------
07-APR-2016 18:36:59 ADDRESS= PROTOCOL=TCP HOST=o6ca-scan.balazs.vm PORT=1521
No errors, everything should be OK, still, I can't connect. So much for trcroute
.
Now let's do some real troubleshooting, and check the server side. The address I am connecting to:
[oracle@o6ca1 ~]$ lsnrctl status listener_scan1
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:40:13
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias LISTENER_SCAN1
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 07-APR-2016 18:15:18
Uptime 0 days 0 hr. 24 min. 54 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/grid12102/network/admin/listener.ora
Listener Log File /opt/oracle/base/diag/tnslsnr/o6ca1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.5)(PORT=1521)))
Services Summary...
Service "mindb" has 2 instance(s).
Instance "mindb1", status READY, has 1 handler(s) for this service...
Instance "mindb2", status READY, has 1 handler(s) for this service...
The command completed successfully
And the services registered here:
[oracle@o6ca1 ~]$ lsnrctl services listener_scan1
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:39:37
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
Services Summary...
Service "mindb" has 2 instance(s).
Instance "mindb1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=o6ca1-vip.balazs.vm)(PORT=1521))
Instance "mindb2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=o6ca2-vip.balazs.vm)(PORT=1521))
Notice in the above output, the mindb
service has 2 instances/handlers, with different addresses. Whenever I connect to o6ca-scan.balazs.vm
, my requests will be forwarded to o6ca1-vip.balazs.vm
or o6ca2-vip.balazs.vm
. My client can't understand those addresses, but if I fix it:
[oracle@o62 ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.6.2 o62.balazs.vm o62
192.168.12.3 o6ca1-vip.balazs.vm o6ca1-vip
192.168.12.4 o6ca2-vip.balazs.vm o6ca2-vip
192.168.12.5 o6ca-scan.balazs.vm o6ca-scan
[oracle@o62 ~]$ sqlplus bp/bp@mindb
SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 7 18:45:13 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Thu Apr 07 2016 18:45:07 +02:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL>
The above is a very common mistake when using Oracle RAC. But ORA-12545
is not specific to RAC, that can happen in single instance environments as well. All you need is a shared server configuration, and name resolution problems on the database server. Same story short:
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) Services Summary... Service "exdb" has 1 instance(s). Instance "exdb", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
"D000" established:55 refused:0 current:15 max:1022 state:ready
DISPATCHER <machine: o61.balazs.vm, pid: 592>
(ADDRESS=(PROTOCOL=tcp)(HOST=o61.balazs.vm)(PORT=42571))
I have a dispatcher listening on o61.balazs.vm
. If I request a shared server connection, and the listener tries to forward the request to o61.balazs.vm
, but can not resolve that address, that will result an ORA-12545
as well.