SQL-Querys on Oracle not working randomly after upgrade from 12 to 14

[Former Member]

I just completed and upgrade from MII 12 to MII 14. Both instances used Oracle as the database engine though the MII 14 servers were built on new servers and the configuration, projects and data were moved. The current Oracle version is 11.2.0.3.0. The old servers are shut down and unavailable.

I am finding that seemingly at random some queries are failing to function without generating any errors in either the BLS or the MII or Oracle logs. The query may be a read or a write query but it simply fails to either return the requested data or fails to execute the insert or update. These are all part of settled code that has been in operation for over a year with no problems on MII 12.

The error is not repeatable which makes it even more difficult to diagnose. The occurance is random usually on the order of 2 to 3 failures and hour on the busy tables.

In all the cases I have been able to identify, there is another query accessing the same table in the BLS, usually, though not always, doing the oposite of the failed query and prior to the failed query. This makes it seem like there is some sort of exclusive table lock going on or that MII is not commiting and releasing the SQL session upon completion of the query. Does MII persist the database connections within a BLS? Whatever the reason, the MII 12 server didn't do it.

I have tried a lot of things to remedy the issue. I have moved the failing query to another BLS and executed it outside the base BLS. In one case I moved the offeding query to a stand alone BLS and executed it from the JavaScript on the page after the primary BLS but I still get errors.

Has anyone seen this behaviour before?

[salvatore_castro · Accepted solution]

First, please verify that the JDBC driver you were using before is either the same one you've uploaded and deployed into your new environment (MII Menu -> System Resources -> JDBC drivers) or is the right one for the version of Oracle DB you have.

For the Data Server please share the screenshot of your configuration if it's IDBC type (MII Menu -> Data Services -> Data Servers; select your connection) and if it's a Data Source type please share the resource configuration screen where you have the connection setup.

Finally, what are the errors that you are seeing when it does fail?

Sam

[Former Member]

Aha, good idea. The JDBC driver is the same as was used on the old server (ojdbc14.jar), however it is for 10g. The Oracle site shows a different driver (ojdbc6.jar) for 11g. I'll try swapping it out and see what happens.

As for the connector config, it is here:

As for errors, there are no errors in either MII or Oracle. That is the strangest part (and the most frustrating. Since it isn't repeatable I can't catch in in the BLS running manually in trace mode.

Thanks for the help.

[former_member185280]

Check that the failed queries aren't being put into the MII Data Buffer.