Troubleshooting / traceroute within Oracle Instant Client

oracleoracle-10g

I am troubleshooting a freshly reimaged backup server Win2000 / Oracle 10.1.0.5.0.

Here's what I did:

  1. Installed Oracle 10g by copying Basic Instant Client package and ODBC package into same directory, then running odbc_install.exe.
  2. Copied tnsnames.ora from the original Win2000 server (where the DB connection works just fine)
  3. Updated Environment variables : PATH, TNS_ADMIN and rebooted Windows

When I run the end-user application, I get an error:

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

Looking more into this, many sources (e.g. http://www.orafaq.com/wiki/ORA-12545 ) recommend using the traceroute command. But I can't seem to find the \bin directory with that command anywhere (and obviously, command is not recognized), which leads me to believe that the instant client does not have this command? I do not have the installation package for the Administrator, so how can I do more troubleshooting under Instant Client?

At an OS-level e.g. from command-line, both ping and tracert work just fine with the DB server name). Oracle's traceroute should (supposedly, as per Oracle docs) give me the full error stack (instead of a single-line error) – hence it would give me additional hint why the DB connection on a recently reimaged server does not work.

As far as I can say, both Primary and Backup server are identical (I didn't work here 10 yrs ago though). Is there perhaps a package of admin tools I could download? Or just somehow unlock it within Instant Client? Are there any Oracle logs of the DB transactions / connection attempts?

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). Of course, before the server reimage, the DB connection worked just fine, so there is clearly something else missing in the first-time configuration.

I cannot seem to find any info on command line troubleshooting under instant client. Does one really need Administrator installation to do basic command line troubleshooting?

Best Answer

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.

Related Question