ADO connection to Oracle gets a listener error, but only on the first few attempts

From: Mark Freeman <mark_freeman_at_asc.aon.com>
Date: 6 Jun 2003 08:40:40 -0700
Message-ID: <df323d2.0306060740.4da77447@posting.google.com>

I have a VB 6.0 program that runs on a Win2k machine and connects to an Oracle 8i database on an AIX system. It works fine.

I split it up into two programs such that one invokes multiple instances of the other. The one that has multiple instances running accesses Oracle to do a query based on a command-line parameter passed by the controller program.

When I fire up the controller program, it immediately runs 8 instances of the database access program. The first two or three get an Oracle error and the rest work fine.

The controller program ensures that 8 instances of the database access program are always running -- when one instance completes and exists it spawns a new one, until it goes through the thousands required.

If I kill off the controller program, let all the access program instances complete, and then run the controller program again, I do not get the errors even though it is doing exactly the same thing as it did before.

The issue appears to be one of multiple programs attempting to make Oracle connections at the same time, but only when no connection has been made for some time.

The exact error I get is:

 Run-time error '-2147467259 (80004005)':  ORA-12541: TNS:no listener

I get the following in sqlnet.log:

---

Fatal NI connect error 12541, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=uscoluxap04.cisgi.com)(PORT=1521))(CONNECT_DATA=(SID=aoninfrp)(CID=(PROGRAM=C:\to-home\NT
LAN-Server Group\Software\FetchWMI\FetchWMI-One.exe)(HOST=COL-MFREEMANLT)(USER=mfreeman))))

  VERSION INFORMATION:

	TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
	Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version

8.1.7.0.0 - Production
  Time: 06-JUN-2003 11:22:31
  Tracing not turned on.
  Tns error struct:
    nr err code: 0
    ns main err code: 12541
    TNS-12541: TNS:no listener
    ns secondary err code: 12560
    nt main err code: 511
    TNS-00511: No listener
    nt secondary err code: 61
    nt OS err code: 0

---

Fatal NI connect error 12560, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=C:\to-home\NT
LAN-Server Group\Software\FetchWMI\FetchWMI-One.exe)(HOST=COL-MFREEMANLT)(USER=mfreeman))))

  VERSION INFORMATION:

	TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
	Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version

8.1.7.0.0 - Production
  Time: 06-JUN-2003 11:22:32
  Tracing not turned on.
  Tns error struct:
    nr err code: 0
    ns main err code: 12560
    TNS-12560: TNS:protocol adapter error     ns secondary err code: 0
    nt main err code: 530
    TNS-00530: Protocol adapter error
    nt secondary err code: 126
    nt OS err code: 0

My sqlnet.ora:

# SQLNET.ORA Network Configuration File: C:\orant\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) automatic_ipc=off

trace_level_client=16
trace_unique_client=true
trace_directory_client = c:\

The code that makes the connection (in the interests of security, actual strings have been replaced with pound signs):

    Dim cnnOracle As ADODB.Connection
    Set cnnOracle = New ADODB.Connection     cnnOracle.Open "Provider=msdaora;Data Source=##;User Id=##;Password=##;" Received on Fri Jun 06 2003 - 10:40:40 CDT