Unreliable Oracle connection (intermittent error "Connect failed because target host or object does not exist")

Question

We recently changed physical DB's, new servers, new locations, same database schema and data and since the change over, whenever we try to connect directly to the DB in our own desktop type applications about half of the time we get this error:

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach[0]
ORA-12545: Connect failed because target host or object does not exist

The rest of the time, it connects right away without any issues. Our applications that establish connections via JDBC don't seem to have any issues but we do when going through something that does a tnsnames.ora lookup (or that's my hunch at least). TNSPING works 100% but using an oracle executable like SQLLDDR fails at least 50% of the time. Here's an anonymized snppet of our TSNNAMES file and a TNSPING output:

DB_CONNECTION =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 1.2.3.4)(PORT = 1521))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = MY.URL.COM)
  )
)

And the TNSPING:

C:\>TNSPING DB_CONNECTION
TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\oracle\ora92\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 1.2.3.4)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = MY.URL.COM)))
OK (200 msec)

I have the SID of the DB, which is what the JDBC connections rely on but adding it to the tnsnames.ora doesn't improve the odds of making a connection. I don't know enough about how the tnsnames file is being used by Oracle to intelligently resolve this issue. What can I try?

Edit

The new DB's may be two load balanced databases which may be part of the problem.

Answer 1

Ah, gotta love the intermittent problems :) (In the following I have had to replace the underscore character as I don't know the escape char for this wiki language)

Are you always using the same OS user to connect and to do the tnsping?

Check for environment variables TNS[underscore]ADMIN pointing to different tnsnames.ora locations. Do a scan of the client for duplicate tnsnames.ora; either in locations pointed to by $TNS[underscore]ADMIN or in different $ORACLE[underscore]HOMES (e.g. if you have 2 Oracle client installs).

Likewise check that you are using the same $ORACLE[underscore]HOME and $PATH environment variables for all connection attempts and tnsping. (E.g. always the same OS user or each user has the same values)

I see that the tnsping output reports version 9.2 so this is not likely, but in 11g the DB registers with the listener which can take a minute or so. (maybe also true with 10g). Attempts to connect prior to this will not find the target.

Another unlikely possibility - is a service with the same service name still advertised on the old host server? Remove it if at all possible.

After that I would start to look at the network itself. Is a ping of the server always successful and quick? If you're using the hostname rather than IP in the tnsnames.ora, does the hostname reliably resolve to the correct IP (nslookup). Is there a local firewall and if so is it behaving?

Regards Karl

Answer 2

If you are using Oracle 10g, you may use an Easy Connect handler instead:

//servername/instancename

,

//1.2.3.4/my.url.com

in your case.

It's immune to lots of TNSNAMES issues.