Re: problem connecting to db.

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sun, 30 Dec 2007 06:38:15 -0800 (PST)
Message-ID: <237c1bc3-6083-4eea-af62-6596e7659253@e23g2000prf.googlegroups.com>

On Dec 30, 5:44 am, "Mr. X." <no_spam_please_at_nospam_please.com> wrote:
> Thanks.
>
> As david said :
> connection 'sometimes hang', but it isn't a regular occurrence
> (He just explained what I have written, in simplier English).
> So I don't think there is mistake on tnsnames.ora, since the problem isn't a
> regular occurence.
>
> But I would add the code, as you suggested, with timeout = 30
>   (The current situation is as I did : OraCon.CursorLocation = adUseClient,
> but after OraCon.Open
>   (maybe mistakenly, but I suppose, adUseClient is the default ... ).
> )
>
> I don't know if it is Oracle or not (maybe, network),
> and BTW, the server and the client are at the same machine (the process is
> on the server).
>
> It may be problem if I do tracing, because the problem may happens not so so
> often
> (once a day, and sometimes there is not a problem for several days ...)
>
> Please give me briefly what should I do in advanced.
>
> Thanks :)

The VB6 client and the server are on the same machine, which very likely means that you are running on the Windows platform, which opens a couple possibilities. The server should have a static IP assigned to at least one enabled network card (this could be a wireless card, for example). As I stated previously, you can modify the TNSNAMES.ORA to specify the IP address of the server, rather than the computer name, which eliminates the possible 30 second delay if DNS services for name resolution are unavailable. There is also the possibility of the client/server computer having multiple TNSNAMES.ORA files - the Oracle server software using one, and the Oracle client using the other.

Look at the services on the server to verify that the Oracle Listener service is running when this problem occurs.

Some Listener tracing is enabled by default. I am not certain about the location of the file on Oracle 9.2, but on 10.2, the file is found in the folder:
  {ORACLE_HOME}\NETWORK\log

If you see something like this in the log, it probably means that the Oracle listener could not find the specified database (you may have specified an ODBC name rather than the name in the TNSNAMES.ORA, for example):

---

TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

---

If you see something like this, it could mean that the maximum number of Oracle specified (in the init.ora or spfile) sessions or processes have been reached, or that the server has reached the maxiumum per process memory limit (either 2GB or 3GB for 32 bit Windows):

---

TNS:12518 listener could not hand off client connection

---

If some other error appears in the listener.log file, you may want to check the Oracle documentation (documentation for 11g at the following address, 9i will be similar):
http://download.oracle.com/docs/cd/B28359_01/server.111/b28278/net12500.htm

You should also check for other files in the bdump (where you found the alert log) and udump folders that have a recent date. If any files exist with a time and date at roughly the same time as when your client attempted to connect, the files could provide additional information.

Try searching the server for files named sqlnet.log (the file will likely be in the same folder as the client program). The file may provide more detailed information. Information in the sqlnet.log file may look like this:

---

Fatal NI connect error 12541, connecting to:  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myServer)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=mySID)(CID=(PROGRAM=C:\Program Files \Microsoft Office\Office\msqry32.exe)(HOST=myClient)(USER=myUser))))   VERSION INFORMATION:

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

10.2.0.2.0 - Production
  Time: 03-NOV-2006 19:52:09
  Tracing not turned on.
  Tns error struct:
    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

---

If this is a production database, it probably is not a good idea to run client programs on the server.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Dec 30 2007 - 08:38:15 CST