0

I am trying to connect to a particular TNS name; in my tnsnames.ora file I can see that it is defined as being a TCP connection to foo-scan on port 39000. I can tnsping this name OK.

I can successfully telnet foo-scan 39000 but when I try to connect in e.g. sqldeveloper I get the error ora-12545: connect failed because target host or object does not exist

According to google it is likely that the listener on foo-scan is actually trying to forward my client to bar-vip, which no doubt I cannot connect to. Also according to google there is a utility called trcroute that will debug this issue and show me exactly where my client is trying to connect and what step is actually failing. But I cannot find this utility anywhere.

2
  • 1
    Hi! In the past I used jnettrace.jar. If you have oracle support access (docid 793415.1) run it on your local machine setup a connection to your cluster address and then connect to it locally from fe sqldeveloper. it will then create a logfile in that directory with what it tried to use to connect to (bar-vip in your case). It will show the 2nd step you are missing.
    – Munchi
    Apr 22 '20 at 17:47
  • @Munchi great suggestion, thanks. If you wish to compile an answer on using jnettrace I will accept it.
    – Coxy
    Apr 23 '20 at 2:51
1

ORA-12545 is typically caused by a scenario you described. The client connects to an address (e.g. SCAN address of a RAC) where its request is forwarded to another address (e.g. VIP address of a RAC node) which the client is unable to reach due to something (e.g. missing name resolution).

In this example situation trcroute would have been no help at all. It does not provide information on this even on a successful attempt and it does not care at all about services (just like tnsping), I can give it a non-existent service and it will still report success.

[oracle@o7ca2 ~]$ tnsping "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o7ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HYPER_O7CA)))"

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-APR-2020 10:48:43

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o7ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HYPER_O7CA)))
OK (10 msec)
[oracle@o7ca2 ~]$ trcroute "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o7ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HYPER_O7CA)))"

Trace Route Utility for Linux: Version 19.0.0.0.0 - Production on 22-APR-2020 10:48:51

Copyright (c) 1995, 2019, Oracle.  All rights reserved.

Route of TrcRoute:
------------------

Node: Client            Time and address of entry into node:
-------------------------------------------------------------
22-APR-2020 10:48:51 DESCRIPTION =  ADDRESS =  PROTOCOL = TCP  HOST = o7ca-scan.balazs.vm  PORT = 1521    CONNECT_DATA =  SERVER = DEDICATED   SERVICE_NAME = HYPER_O7CA

Node: Server            Time and address of entry into node:
-------------------------------------------------------------
22-APR-2020 10:48:51 ADDRESS= PROTOCOL=TCP  HOST=o7ca-scan.balazs.vm  PORT=1521

[oracle@o7ca2 ~]$ lsnrctl services listener_scan1

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-APR-2020 10:49:06

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
Services Summary...
Service "HYPERXDB" has 4 instance(s).
  Instance "HYPER1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: o7ca1, pid: 4049>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o7ca1.balazs.vm)(PORT=13707))
  Instance "HYPER2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: o7ca2, pid: 6754>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o7ca2.balazs.vm)(PORT=24163))
  Instance "HYPER3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: o7ca3, pid: 6526>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o7ca3.balazs.vm)(PORT=15639))
  Instance "HYPER4", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: o7ca4, pid: 6593>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o7ca4.balazs.vm)(PORT=22765))
Service "HYPER_O7CA" has 4 instance(s).
  Instance "HYPER1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.25)(PORT=1521))
  Instance "HYPER2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521))
  Instance "HYPER3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.27)(PORT=1521))
  Instance "HYPER4", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.28)(PORT=1521))
Service "hyper" has 4 instance(s).
  Instance "HYPER1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.25)(PORT=1521))
  Instance "HYPER2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521))
  Instance "HYPER3", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.27)(PORT=1521))
  Instance "HYPER4", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.28)(PORT=1521))
The command completed successfully
[oracle@o7ca2 ~]$ trcroute "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o7ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XXX)))"

Trace Route Utility for Linux: Version 19.0.0.0.0 - Production on 22-APR-2020 10:51:39

Copyright (c) 1995, 2019, Oracle.  All rights reserved.

Route of TrcRoute:
------------------

Node: Client            Time and address of entry into node:
-------------------------------------------------------------
22-APR-2020 10:51:39 DESCRIPTION =  ADDRESS =  PROTOCOL = TCP  HOST = o7ca-scan.balazs.vm  PORT = 1521    CONNECT_DATA =  SERVER = DEDICATED   SERVICE_NAME = XXX

Node: Server            Time and address of entry into node:
-------------------------------------------------------------
22-APR-2020 10:51:39 ADDRESS= PROTOCOL=TCP  HOST=o7ca-scan.balazs.vm  PORT=1521

[oracle@o7ca2 ~]$

Actually this is all that happens in the listener on a trcroute attempt:

22-APR-2020 10:45:21 * 12508
TNS-12508: TNS:listener could not resolve the COMMAND given

Using the TRCROUTE Utility to Test Connectivity from the Client

The listener receives and processes the TRCROUTE connect packet. It returns the information to the client by putting it into a refuse packet.

1
  • Thanks for the info. That's disappointing that even the (supposedly?) right tool for the job won't work...
    – Coxy
    Apr 22 '20 at 10:16
1

In the comments, Munchi suggested jnettrace. I gave it a shot and this seems like the best option because chances are if you're struggling with this you might not have privileges to install Wireshark.

There's an Oracle support article located here: https://support.oracle.com/knowledge/Middleware/793415_1.html
Which at the time of writing has a download link pointing here: http://www.oracle.com/technetwork/database/enterprise-edition/jnettrace.jar

This tool basically opens a port on your local machine that it forwards to the remote scan server. You try connecting to the local port and it logs the traffic back and forth. The connection will still fail, but then you can open the trace and find the address of the vip server.

Chances are you have a JRE included inside sqldeveloper. I used this to no ill effect.

cd C:\snoracle\sqldeveloper\jdk\jre\bin
java -jar jnettrace.jar <remote scan server> <remote port> <local port>
java -jar jnettrace.jar foo-scan 39000 12345

Open sqldeveloper and try to connect to the local port. screenshot of sqldeveloper

There should be a trace file created within the current working directory. Open the trace and you should see log statements like Client to server and Server to client. In my case the payload I'm interested in was in the second Server to client packet.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bar-vip)(PORT=39000))).(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=12345))(CONNECT_DATA=(CID=(PROGRAM=SQL.Developer)(HOST=jdbc)(USER=coxy))(SERVICE_NAME=FOO)(SERVER=dedicated)(INSTANCE_NAME=FOO212)))

Now I know that the database is running on bar-vip and I can make the necessary changes to routing or firewall to be able to connect to it from my client.

1
  • 1
    I was going to write it up, but you did an amazing job on describing it! High five :) hope everything works now.
    – Munchi
    Apr 24 '20 at 22:44
0

I was not able to find any installer that has trcroute. I even went so far as to install Oracle XE, but no dice.

In the end, I installed Wireshark and captured packets on the ethernet device while I tried to connect in sqldeveloper. In a response from foo-scan, the data actually contains a TNS definition string for bar-vip. Now I know what host and port the connection is actually trying to use, I was able to fix the connection.

1
  • FYI, trcroute is an operating system utility. It is not included with any Oracle installation.
    – pmdba
    Apr 22 '20 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.