Using ODBC or Oracle Trace to find the cause of the error?

Question

I have a third-party Windows service that monitors / controls the hardware and updates the Oracle database. Their services sometimes report a row / column error in the database "bad", but they do not give a database error, and their services must be restarted, and everything is in order. The current suspicion is that some of our applications / services that read / write to the same tables / rows are interfering - for example, some kind of lock / lock. I suspect there is some kind of leak in their system, as it happens once a week, but our systems never need to be restarted.

I tried to get the DBA to start tracing in Oracle (10g), but this helped our applications not access the Oracle database. Our systems access Oracle in .NET using either the Oracle ODP client or the Microsoft client (older programs), and on the same server (or web applications or services), or on other management workstations. Third-party services connect to Oracle through ODBC on this server. I also tried to start the ODBC trace (since it will only be activity from a third-party service), but received nothing in the trace file.

So, I'm trying to find a way to get the ODBC trace working, or what I need to see so that the Oracle trace does not kill my server.

I am looking for the inexhaustible error returned by Oracle to the thrid-party service, so I can say that we are somehow interfering with their access to the data.

Answer 1

If a block in the database is corrupted "Bad", it should appear in the warning logs as error ORA-01578. I would look for the archive log for the ORA error, and then compare this with the timestamp on the reported client error. This suggests the definition of "bad." It would be better if accurate error messages were sent.

Tracing a blanket in a database is a difficult task, as it will affect the performance of your entire application. And leave it for a whole week, perhaps impossible. I also found in one case (I don’t remember the exact circumstance) where the inclusion of tracing fixed an error.

One of the methods I've used in the past is to add a sql statement to change the session and enable sqltrace. This is due to the possibility of changing the code in any way. Depending on the application, this may or may not be possible.

Another method would be to work with the database administrator to identify the session and enable SQL tracing for that session. In addition, if you can identify offensive sql statements and parameter values, you can replicate the problem outside of the service.

I found that most ORMs avoid transmitting an ORA error. However, it is usually registered at the application server level with a corresponding ORM error.

I used this method and variations of this method to fix errors when starting the application. Hope this is helpful.