Search code examples
oracletnsnamesora-12545

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

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.

Solution

  • We recently had a similar issue with our application. The application would sometimes connect to the Oracle RAC and sometimes it would complain with ORA-12545. In short the issue was that the server had a real name while we were using virtual IP addresses in TNSNAMES.ORA. Once we added the mapping of the server name to IP address via system32\drivers\etc\hosts file everything started to work properly.

    I have written a bit more in my blog <shameless advert>http://dcarapic.blogspot.com/2009/04/intermittent-ora-12545-error.html</shameless advert>