ODP reconnect problem
1011899May 30 2013 — edited May 31 2013Hello.
Can anybody help with resolving next ODP problem?
When some network failure occurs application through ODP can not restore connection to DB.
Database version: 10.2.0.4, ODP version can be different 10 or 11
1) Network failure
+[26.05.2013 10:17:55][Warning]: Connection lost: Data Source=SomeDB;+
Oracle.DataAccess.Client.OracleException ORA-03114: not connected to ORACLE at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
+[26.01.2013 10:17:55][Info]: Connection closed: Data Source=SomeDB;+
2) When we try restore connection and execute "select * from dual" we get next error
+[26.05.2013 10:17:55][Info]: Connection opened: Data Source=SomeDB;+
+[26.05.2013 10:19:01][Error]: Connection to database failed: Data Source=SomeDB;+
+[26.05.2013 10:19:01][Error]:+
System.Reflection.TargetInvocationException: Exception ---> Oracle.DataAccess.Client.OracleException ORA-00900: invalid SQL statement at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
+...+
+[26.05.2013 10:19:01][Error]:+
System.Reflection.TargetInvocationException: Exception ---> Oracle.DataAccess.Client.OracleException ORA-24373: invalid length specified for statement at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
+...+
+[26.05.2013 10:19:04][Error]:+
System.Reflection.TargetInvocationException: Exception ---> Oracle.DataAccess.Client.OracleException ORA-12520: TNS:listener could not find available handler for requested type of server at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
...
We disable pooling but still have same error.
Interesting, that if we try reconnect with changed connection string (cosmetic changes) to same DB - all is good, connection is restored.
How to cure this ODP behavior? Or maybe how to clear possible caching or pooling connection data or something else without restarting application which using ODP?