RE: Tracing multi-tier apps

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Wed, 5 Jul 2006 11:24:55 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF68139E@EXCNYSM0A1AJ.nysemail.nyenet>

Michael,

One simple way to trace connection issues is to review the listener.log file. I normally have logging to this file turned off. But if you are having connection issues, then turning this on and then parsing the log file might be a first step to see if the connect request even gets to your server. This would help diagnose network issues.

Secondly, look at logging all connections to the database. I have a simple schema in all of our databases that gets populated by a database trigger. Every database connection is caught and a record is created in this table. Info I catch is server where the connection is coming from, program name, os user, oracle user and data/time of the connection.

These are simple first-step debugging tools to see if a connection is being made.

Hope these help.

Tom

This transmission may contain confidential, proprietary, or privileged information which is intended solely for use by the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, copying or distribution of this transmission or its attachments is strictly prohibited. In addition, unauthorized access to this transmission may violate federal or State law, including the Electronic Communications Privacy Act of 1985. If you have received this transmission in error, please notify the sender immediately by return e-mail and delete the transmission and its attachments.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Ray Sent: Wednesday, July 05, 2006 11:08 AM
To: oracle-l_at_freelists.org
Subject: Tracing multi-tier apps

I'm having difficulty visualizing how tracing would work when dealing with compiled applications. Using some command-line interface I can figure out, but how would I enabled it outside an application?

We've been having some "connection issues" on the production floor and I

would like to track down the cause. There are 2 different cases:

A Visual Basic 6 exe that logs in to the database via a dll (OO4O wrapper). This one I think can be solved but not without recompiling every app I would want to trace.
PLCs on the floor that communicate TCPIP to a (Rockwell Software?) RSSQL server (OPC server which ties a "tag" in the PLC to a field, procedure, etc in the database), which then translates that request and does the communication with Oracle via ODBC I think.

Clients are 8i/9i and database is now 10g. Some perceive there may be an

increase in these issues since the 10g upgrade, but it cannot be quantified.

It seems a long time ago I did some SQL*Net tracing in 8i by setting a parameter or two in the sqlnet.ora (?) file. I'm not sure if that will give me all I need (I also recall it didn't trace reliably), but it won't help much with case #2 anyway, which is the more pressing one. Perhaps a network (packet) sniffer is the only way to solve it?

Sincerely,
Michael Ray
Topshot Systems LLC
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Wed Jul 05 2006 - 10:24:55 CDT