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

On Dec 27, 5:26*am, "Mr. X." <no_spam_please (AT) nospam_please (DOT) com> wrote:
You'll need to post the connection string details and the Oracle
release (all four numbers) before anyone can assist you. What you've
posted is of little use in diagnosing your problem.


David Fitzjarrell

David Fitzjarrell wrote :
-----------------------
You'll need to post the connection string details and the Oracle
release (all four numbers) before anyone can assist you. What you've
posted is of little use in diagnosing your problem.
------------------------


O.K
connection string is :
Provider=OraOLEDB.Oracle;Data Source=***;User ID=***;Password=***

And the oracle version is Oracle 9i.
Oracle9i Release 9.2.0.6.0
JServer Release 9.2.0.6.0

On Dec 27, 8:27*am, "Mr. X." <no_spam_please (AT) nospam_please (DOT) com> wrote:
Your connection 'sometimes hang', but it isn't a regular occurrence,
so why do you blame Oracle for the issue? Have you considered this
may be a network issue? Have you considered that the alert log
entries are not associated with the connection hanging? Have you
considered anything other than Oracle being at fault?

If you're so convinced that Oracle is the culprit then I would enable
client tracing in the local sqlnet.ora file and see what the traces
tell you.

Post them after you generate them if you're still confused.


David Fitzjarrell

On Dec 27, 6:26*am, "Mr. X." <no_spam_please (AT) nospam_please (DOT) com> wrote:
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....ca345ebfabc168
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.

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

On Dec 30, 5:44*am, "Mr. X." <no_spam_please (AT) nospam_please (DOT) com> wrote:
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/B...8/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=myU ser))))
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.

Thanks ...
Some things :

I am sorry for my bad English, so in some cases I may be understood :
Client <-> Server :
I didn't mean that there is a user that running a specific application on
the server.
I call for the Server : Oracle, and the Client : my program, that runs by a
windows-schedule.
(On the machine : Windows 2000).

Also, the only message I have seen on listener.ora is
TNS-12514: TNS:listener does not currently know of service requested
in connect descriptor
(and I see it occured when running sql-plus, once for some debugging , and
the schedule-programs don't hang at that time...)

In Sqlnet.ora I have messages that related to the programs of my schedules,
the same as you described.
(long time ago, and not when my programs hang last time) - that seems to be
weird - is that related to the problem I have describe on first time ?

(the programs still hang once for 1-3 days generally - like lottery - I
don't know exactly when and which of my schedules ...).

Thanks

On Dec 30, 10:26*am, "Mr. X." <no_spam_please (AT) nospam_please (DOT) com>
wrote:
Just to list what you have stated previously:
* Oracle Server 9.2.0.6 running on Windows 2000
* VB6 client running on the same server, attempting to connect using
OraOLEDB.Oracle
* VB6 client is started using the Windows task scheduler
* Connection attempt sometimes causes the VB6 program to hang, once
every 1 to 3 days
* Alert log shows a possibly unrelated error "SMON: Parallel
transaction recovery tried" sometimes when the connection hangs
* Listener log file does not indicate a problem when the client hangs
during connection attempts
* Sqlnet.log file shows some error activity for the client program,
but nothing recorded when the client hangs during connection attempts

Are there any other files in the bdump or udump folders that are
created when the client hangs? Look in the Windows 2000 event logs,
specifically the Application event logs. Are there any event log
entries when the client hangs during connection attempts - you may see
errors like "Could not allocate memory..." Is it possible to run the
client program on another computer?

On the computer that has the Microsoft Visual Basic 6 editor, make
certain that you have installed either service pack 3 or service pack
4 for Visual Studio (98). Also, install MDAC 2.8 on the computer with
Microsoft Visual Basic 6, and on the server. Create a new setup
package for the VB6 program, and then run the setup package on the
server. Make certain that the server has Service Pack 4 for Windows
2000 installed.

When the problem occurs, execute the following in SQL*Plus to see if
you are having a problem with the maximum number of sessions:
SELECT
COUNT(*) CUR_SESSIONS,
MAX_SESSIONS,
MAX_PROCESSES
FROM
V$SESSION,
(SELECT
MAX(DECODE(NAME,'sessions',VALUE,NULL)) MAX_SESSIONS,
MAX(DECODE(NAME,'processes',VALUE,NULL)) MAX_PROCESSES
FROM
V$PARAMETER
WHERE
NAME IN ('sessions','processes'))
GROUP BY
MAX_SESSIONS,
MAX_PROCESSES;

You may see something like this:
CUR_SESSIONS MAX_SESSIONS MAX_PROCESSES
------------ ------------ -------------
209 226 210

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Thanks.
I have tried the select statement as you suggest,
and the current processes count are not near by the maximum limit (right
now, respectively : 50, 200, 180).
I think the current processes number grows on time, but I don't know how to
follow it, unless I do some code (schedule that inspect the current status
and write it to log every 5 minutes).
There are no other relative files at bdump, udump etc ...
How can I check the current service pack & MDAC 2.8, and why those
installation related to the problem I have described.

Thanks