Re: problem connecting to db.

On Dec 27, 6:26 am, "Mr. X." <no_spam_please_at_nospam_please.com> wrote:
> Hello,
> In my VB 6.0 code, I have tried to connect to oracle-database,
> but program just hang (even there is no exception !!!)
> Code is :
>
> In alert.log there is a massage (it sometimes occurs when the above hang,
> but not always when the program hangs) :
> SMON: Parallel transaction recovery tried
>
> The code in brief:
> --------------------
>
> Public OraCon As New ADODB.Connection
> Public mainConnectionStr As String
> ...
>
> public sub connectToDB
>   On Error GoTo connectToDB_err
>   mainConnectionStr = ...
>   OraCon.ConnectionString = mainConnectionStr
>   Call OraCon.Open ' ***** here programs hang sometimes ******
>                                 ' ***** also, when hang - not always there
> is a line on alert.log, but sometimes there is. ******
>   goto end_proc
> connectToDB_err:
> ' ****** when the above error, this code is never reachable *******
>   msgBox "" & err.number & "," & err.description
> end_proc:
> end sub
>
> What may be the cause for the above problem ?
>
> Thanks :)

Just a couple suggestions. Before the Call OraCon.Open line (note that the word Call is unnecessary), add the following:   OraCon.ConnectionTimeout = 30
  OraCon.CursorLocation = adUseClient

Have you checked the listener log file on the database server? The log file may be very large, so you may either want to examine just the tail of the log, or copy it to another computer for viewing. It is possible that the computer is trying to find the database using DNS or something similar. Make certain that the TNSNAMES.ORA file on the client is correct, and that the connection string that you are specifying in VB6 matches that in the TNSNAMES.ORA file. For example, if the TNSNAMES.ORA on the client contains the following: LT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = AIRFORCE_1)(PORT = 1521))     )
    (CONNECT_DATA =
      (SERVICE_NAME = MYDB1)
    )
  )

In this case, the VB6 connection string should specify LT, and not MYDB1. In the above, the Oracle client would be looking for a server named AIRFORCE_1. If the DNS server is down, the client would likely experience at least a 30 second delay before the computer used another method to resolve the IP address of the AIRFORCE_1 server - it might be a good idea to see what would happen if you specify the server's IP address in the TNSNAMES.ORA file on the client.

There is a Usenet posting from 2004 that implies that the error in the alert log may be related to the initialization parameters used by the Oracle instance:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/86ca345ebfabc168 If possible, post the list of initialization parameters used by the database instance.

If you do not have physical access to the server, I suggest running a Wireshark/Ethereal trace on the client while attempting to connect to the database. If you see a DNS lookup request, and then a 30+ second pause, that likely means that the DNS server was unavailable to resolve the database server name - check the status of the internal DNS server. If you see a response from the DNS server, and then a connection request to Oracle, but no response back from the server hosting the Oracle database, then the problem is likely in/on the Oracle server or the network segment between the server and the client. A couple weeks ago I saw a similar problem, where the limit set for processes in the initialization parameters was hit - in some cases the clients would hang when attempting to connect to the database, and in others the clients would be told that the connection request failed. The listener log file showed that it could not hand off the connection requests to the database instance.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Dec 27 2007 - 11:11:49 CST