Using Oracle 10g connection issues

[rafig]

Hi,
I have just added in a Database Server to one of my solutions that is talking to an Oracle 10g database using a read-only username/password to allow my solution to look up Purchase Orders from another system back-ended by Oracle.
(My solution is using SQL Server as back-end)
This is the first time I have connected to Oracle and it took me ages to get the JDBC connection configured properly as I was given a SID that I had to 'translate' into the long string from the ORA.TNS file...
This connection is actually going over some 'data pipe' using a secure connection from one hospital to another.
I was having a problem where when using Smart Client and viewing the form that shows the linked data from Oracle, sometimes error messages pop up and it seems that the connection pool is 'exhausted' and sockets are closed.
It might be that after a certain amount of time without any calls to that connection, Oracle is closing the connection? (This part of the solution may not be accessed for long periods of time)
I tried using 'Query Validation' & 'SELECT 1;', but it didn't like it (bad char), so got rid of ';' then it wanted a 'FROM' so I made it 'SELECT 1 FROM PURCHASE_ORDERS' but that didn't work either, so had to go back to 'Exception Validation'.
Is there a way I can use something like 'Query Validation' & 'SELECT 1;' to make it keep the connection 'alive'??
I have set
Max Conn Active = 50
Max Conn Idle = 10
Max Prep Stmts Idel = 100
I also didn't understand quite how to set up the Schema that is suggested in a dialog by Servoy about using the Oracle username to stop it showing system tables. When I tried creating a schema with the username it just didn't connect, so I have it set to default <none>...
If anyone that is using Oracle can advise me, I'd be very grateful

Thanks

Rafi

[Hans Nieuwenhuis]

When I tried creating a schema with the username it just didn't connect

We only use Oracle.
The schema is the oracle username in uppercase.

I attached one server from my properties file.

Code: Select all

server.2.URL=jdbc\:oracle\:thin\:@192.168.169.129\:1521\:ibis
server.2.catalog=<none>
server.2.connectionValidationType=0
server.2.driver=oracle.jdbc.driver.OracleDriver
server.2.enabled=true
server.2.maxConnectionsActive=10
server.2.maxConnectionsIdle=5
server.2.maxPreparedStatementsIdle=100
server.2.password=encrypted\:yVyqiDBtPOKSPGdXVboioi/XMl8ZdPDG
server.2.schema=SERPRO_EXAMPLE_DATA
server.2.serverName=example_data
server.2.skipSysTables=false
server.2.userName=serpro_example_data


[ROCLASI]

Hi Rafi,

I experienced some connection issues with Oracle 10g as well. But only when I connect to it over a VPN connection.
Oracle seems to drop the connection without actually closing it so the client (be it Servoy server or otherwise) still thinks the connection is alive. Actually trying to close the app (Servoy or otherwise) will hang the app. I did make sure to check that it wasn't the VPN connection dropping.
When I am on the local network then the connection is always stable. Very weird.
Perhaps this is related to what you are seeing.

Also the SELECT 1 will be fired just before a query so it's not a 'keep alive' thing persé. Unless you have a batchprocessor that does a query every x period.
(Also it's 'SELECT 1 FROM DUAL' if you want to select a function/value from no specific table)

[rafig]

Thanks for the replies guys

The schema is the oracle username in uppercase.

Thanks!
That worked (although I can see lots of other funny tables, but maybe they are always there with Oracle, or should be...)

Hi Rafi,
I experienced some connection issues with Oracle 10g as well. But only when I connect to it over a VPN connection.
Oracle seems to drop the connection without actually closing it so the client (be it Servoy server or otherwise) still thinks the connection is alive. Actually trying to close the app (Servoy or otherwise) will hang the app. I did make sure to check that it wasn't the VPN connection dropping.
When I am on the local network then the connection is always stable. Very weird.
Perhaps this is related to what you are seeing.)

I think it is something like this, as it is a special secure connection (as there is confidential info that might be transmitted)

Also the SELECT 1 will be fired just before a query so it's not a 'keep alive' thing persé. Unless you have a batchprocessor that does a query every x period.
(Also it's 'SELECT 1 FROM DUAL' if you want to select a function/value from no specific table)

Thanks, put in the SELECT 1 FROM DUAL and it was ok!
I will see if it helps matters, if not, I might have to use a batch processor to keep it alive.

Rafi

[patrick]

That worked (although I can see lots of other funny tables, but maybe they are always there with Oracle, or should be...)

Sounds like you are using a user that sees more than he should. Are you using the same user as your schema name?

[ROCLASI]

I will see if it helps matters, if not, I might have to use a batch processor to keep it alive.

Somehow I don't think this will be enough. The batchprocessor will trigger one connection, any other connection that idles for too long will then be dead in the water up until you don't have any connections left in your pool except the one you keep alive.
In any case this is treating the symptom instead of the cause. Let them fix the cause. It's a networking/Oracle issue.

[john.allen]

I don't know how to fix it but there is definitely a difference in how Oracle can be set up and NOT have this issue. Two oracle servers here set up by the same organization have the same problem with every database on them. Another set of servers set up by a different group have no issues like this. And that set of servers are behind a huge security/firewall and yet no problems. On the ones where there is a problem it lasts exactly 8 minutes 31 seconds before it reconnects!

[rafig]

Hi John,

I don't know how to fix it but there is definitely a difference in how Oracle can be set up and NOT have this issue. Two oracle servers here set up by the same organization have the same problem with every database on them. Another set of servers set up by a different group have no issues like this. And that set of servers are behind a huge security/firewall and yet no problems. On the ones where there is a problem it lasts exactly 8 minutes 31 seconds before it reconnects!

Thanks for the reply.
Maybe for the benefit of all of us that might be using Oracle connections, something could be documented regarding the different ways they are set up or configured??
Thanks

Rafi