cancel
Showing results for 
Search instead for 
Did you mean: 

ORACLE JDBC DataSource - connection closed

Former Member
0 Kudos

Hello,

We are using WAS 6.40

I have built a few DataSource using Visual Admin, which connect to different ORACLE databases.

I get a "V" icon next to the DataSource, which means they are OK, and I have succeeded to query the databases from Visual Admin.

After a period of time of not accessing the DataSource, from some reason, all the connections are closing.

How do I know this? - the "V" icon still appears, but when I try to access a DataSource using SQL Query, I get the following Java error. When I try to access it again, the DataSource "wakes up" and start working until next time.

I have tried to change the properties of the DataSource , but nothing helped.

I must say that I have other DataSource to SQLSERVER with no problem.

The error I get :

=========================================================

java.rmi.RemoteException: com.sap.engine.services.dbpool.exceptions.BaseRemoteException: SQL statement(s) cannot be executed over DataSource "IECORACLE_Hp19aPortalProd". If you are using an Open SQL DataSource, you must register your tables using JDDI before you request them. Reason: com.sap.engine.services.dbpool.exceptions.BaseSQLException: ResourceException in method ConnectionFactoryImpl.getConnection(): com.sap.engine.services.connector.exceptions.BaseResourceException: Error in ResourceSet.addAndEnlist("com.sap.engine.services.connector.resource.impl.HandleWrapper@15bd82f[com.sap.engine.services.dbpool.cci.ConnectionHandle@1b65c26](delisted:false) -> com.sap.engine.services.connector.resource.impl.MCEventHandler@5583bd --> not associated, true").

at com.sap.engine.services.dbpool.deploy.DataSourceManagerImpl.executeFromAppThread(DataSourceManagerImpl.java:1130)

at com.sap.engine.services.dbpool.deploy.DataSourceManagerImpl.executeInitStatements(DataSourceManagerImpl.java:511)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:324)

at com.sap.pj.jmx.introspect.DefaultMBeanInvoker.invoke(DefaultMBeanInvoker.java:58)

at com.sap.pj.jmx.mbeaninfo.AdditionalInfoProviderMBean.invoke(AdditionalInfoProviderMBean.java:289)

at com.sap.pj.jmx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:944)

at com.sap.pj.jmx.server.interceptor.MBeanServerWrapperInterceptor.invoke(MBeanServerWrapperInterceptor.java:288)

at com.sap.engine.services.jmx.CompletionInterceptor.invoke(CompletionInterceptor.java:400)

at com.sap.pj.jmx.server.interceptor.BasicMBeanServerInterceptor.invoke(BasicMBeanServerInterceptor.java:277)

at com.sap.jmx.provider.ProviderInterceptor.invoke(ProviderInterceptor.java:258)

at com.sap.engine.services.jmx.RedirectInterceptor.invoke(RedirectInterceptor.java:340)

at com.sap.pj.jmx.server.interceptor.MBeanServerInterceptorChain.invoke(MBeanServerInterceptorChain.java:330)

at com.sap.engine.services.jmx.MBeanServerSecurityWrapper.invoke(MBeanServerSecurityWrapper.java:287)

at com.sap.engine.services.jmx.MBeanServerInvoker.invokeMbs(MBeanServerInvoker.java:157)

at com.sap.engine.services.jmx.ClusterInterceptor.invokeMbs(ClusterInterceptor.java:220)

at com.sap.engine.services.jmx.ClusterInterceptor.invoke(ClusterInterceptor.java:803)

at com.sap.engine.services.jmx.MBeanServerInterceptorInvoker.invokeMbs(MBeanServerInterceptorInvoker.java:102)

at com.sap.engine.services.jmx.connector.p4.P4ConnectorServerImpl.invokeMbs(P4ConnectorServerImpl.java:61)

at com.sap.engine.services.jmx.connector.p4.P4ConnectorServerImplp4_Skel.dispatch(P4ConnectorServerImplp4_Skel.java:64)

at com.sap.engine.services.rmi_p4.DispatchImpl._runInternal(DispatchImpl.java:304)

at com.sap.engine.services.rmi_p4.DispatchImpl._run(DispatchImpl.java:193)

at com.sap.engine.services.rmi_p4.server.P4SessionProcessor.request(P4SessionProcessor.java:122)

at com.sap.engine.core.service630.context.cluster.session.ApplicationSessionMessageListener.process(ApplicationSessionMessageListener.java:33)

at com.sap.engine.core.cluster.impl6.session.MessageRunner.run(MessageRunner.java:41)

at com.sap.engine.core.thread.impl3.ActionObject.run(ActionObject.java:37)

at java.security.AccessController.doPrivileged(Native Method)

at com.sap.engine.core.thread.impl3.SingleThread.execute(SingleThread.java:100)

at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:170)

Caused by: com.sap.engine.services.dbpool.exceptions.BaseSQLException: ResourceException in method ConnectionFactoryImpl.getConnection(): com.sap.engine.services.connector.exceptions.BaseResourceException: Error in ResourceSet.addAndEnlist("com.sap.engine.services.connector.resource.impl.HandleWrapper@15bd82f[com.sap.engine.services.dbpool.cci.ConnectionHandle@1b65c26](delisted:false) -> com.sap.engine.services.connector.resource.impl.MCEventHandler@5583bd --> not associated, true").

at com.sap.engine.services.dbpool.cci.ConnectionFactoryImpl.getConnection(ConnectionFactoryImpl.java:59)

at com.sap.engine.services.dbpool.deploy.DBInitializer.run(DBInitializer.java:62)

... 4 more

Caused by: com.sap.engine.services.connector.exceptions.BaseResourceException: Error in ResourceSet.addAndEnlist("com.sap.engine.services.connector.resource.impl.HandleWrapper@15bd82f[com.sap.engine.services.dbpool.cci.ConnectionHandle@1b65c26](delisted:false) -> com.sap.engine.services.connector.resource.impl.MCEventHandler@5583bd --> not associated, true").

at com.sap.engine.services.connector.jca.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:241)

at com.sap.engine.services.dbpool.cci.ConnectionFactoryImpl.getConnection(ConnectionFactoryImpl.java:51)

... 5 more

Caused by: com.sap.engine.services.ts.exceptions.BaseSystemException: Exception in LocalTransaction.begin.

at com.sap.engine.services.ts.jta.impl.TransactionImpl.enlistLocalResource(TransactionImpl.java:942)

at com.sap.engine.services.connector.resource.impl.ResourceSetImpl.addAndEnlist(ResourceSetImpl.java:260)

at com.sap.engine.services.connector.jca.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:238)

... 6 more

Caused by: com.sap.engine.services.dbpool.exceptions.BaseResourceException: SQLException thrown by the physical connection: java.sql.SQLException: Connection Closed

at com.sap.engine.services.dbpool.spi.LocalTXManagedConnectionImpl.throwBaseResourceException(LocalTXManagedConnectionImpl.java:86)

at com.sap.engine.services.dbpool.spi.LocalTXManagedConnectionImpl.begin(LocalTXManagedConnectionImpl.java:403)

at com.sap.engine.services.ts.jta.impl.TransactionImpl.enlistLocalResource(TransactionImpl.java:932)

... 8 more

Caused by: java.sql.SQLException: Connection Closed

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)

at oracle.jdbc.driver.OracleConnection.setAutoCommit(OracleConnection.java:1283)

at com.sap.engine.services.dbpool.spi.LocalTXManagedConnectionImpl.begin(LocalTXManagedConnectionImpl.java:397)

... 9 more

at com.sap.engine.services.dbpool.exceptions.BaseRemoteException.writeReplace(BaseRemoteException.java:176)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:324)

at java.io.ObjectStreamClass.invokeWriteReplace(ObjectStreamClass.java:896)

at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1011)

at java.io.ObjectOutputStream.defaultWriteFields(ObjectOutputStream.java:1332)

at java.io.ObjectOutputStream.defaultWriteObject(ObjectOutputStream.java:367)

at javax.management.MBeanException.writeObject(MBeanException.java:126)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:324)

at java.io.ObjectStreamClass.invokeWriteObject(ObjectStreamClass.java:809)

at java.io.ObjectOutputStream.writeSerialData(ObjectOutputStream.java:1296)

at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1247)

at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1052)

at java.io.ObjectOutputStream.defaultWriteFields(ObjectOutputStream.java:1332)

at java.io.ObjectOutputStream.defaultWriteObject(ObjectOutputStream.java:367)

at com.sap.exception.BaseExceptionInfo.writeObject(BaseExceptionInfo.java:964)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:324)

at java.io.ObjectStreamClass.invokeWriteObject(ObjectStreamClass.java:809)

at java.io.ObjectOutputStream.writeSerialData(ObjectOutputStream.java:1296)

at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1247)

at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1052)

at java.io.ObjectOutputStream.defaultWriteFields(ObjectOutputStream.java:1332)

at java.io.ObjectOutputStream.writeSerialData(ObjectOutputStream.java:1304)

at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1247)

at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1052)

at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:278)

at com.sap.engine.services.jmx.ResponseMessage.writeBody(ResponseMessage.java:169)

at com.sap.engine.services.jmx.Message.writeMsg(Message.java:257)

at com.sap.engine.services.jmx.Message.getMsg(Message.java:453)

at com.sap.engine.services.jmx.Message.writeExternal(Message.java:505)

at java.io.ObjectOutputStream.writeExternalData(ObjectOutputStream.java:1267)

at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1245)

at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1052)

at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:278)

at com.sap.engine.services.jmx.connector.p4.P4ConnectorServerImplp4_Skel.dispatch(P4ConnectorServerImplp4_Skel.java:66)

at com.sap.engine.services.rmi_p4.DispatchImpl._runInternal(DispatchImpl.java:304)

at com.sap.engine.services.rmi_p4.DispatchImpl._run(DispatchImpl.java:193)

at com.sap.engine.services.rmi_p4.server.P4SessionProcessor.request(P4SessionProcessor.java:122)

at com.sap.engine.core.service630.context.cluster.session.ApplicationSessionMessageListener.process(ApplicationSessionMessageListener.java:33)

at com.sap.engine.core.cluster.impl6.session.MessageRunner.run(MessageRunner.java:41)

at com.sap.engine.core.thread.impl3.ActionObject.run(ActionObject.java:37)

at java.security.AccessController.doPrivileged(Native Method)

at com.sap.engine.core.thread.impl3.SingleThread.execute(SingleThread.java:100)

at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:170)

=========================================================

Any ideas?

Anyone can help?

Thanks,

Yoav

Accepted Solutions (1)

Accepted Solutions (1)

former_member184385
Active Participant
0 Kudos

Hi Yoav,

there are timeout parameters, defining a DS, which govern an automatic closing of a dormant DS after the specified interval.

Your problem may be, that the timeout is to long, and Oracle has closed the corresponding DS connection, while Netweaver is still serving such DS connection to a client. A solution for this: make the timeout parameter in NW smaller, to align with Oracle.

Regards

Gregor

Former Member
0 Kudos

Hi Gregor,

Thanks for your response.

Which parameter do you mean exactly?

It is set to :

MAX TIME TO WAIT FOR CONNECTION : 60 (default)

CONNECTION LIFETIME : 0 (defualt)

EXPIRATION : NO (defualt)

Thanks,

Yoav

former_member184385
Active Participant
0 Kudos

Hi Yoav,

instead of talking about certain parameters, I would like to include a DS template, which worked pretty well for me

>>>>>>>>>>>>>>>>>>>> begin of xml >>>>>>>>>>>>>>>>

<?xml version='1.0' encoding='UTF-8'?>

<!DOCTYPE data-sources SYSTEM 'data-sources.dtd'>

<data-sources>

<application-name>#{ds.vendor.prefix}/#{ds.app.name}</application-name>

<data-source>

<description>Connect for Sfa Datasource</description>

<data-source-name>#{ds.name}</data-source-name>

<driver-name>#{db.drv.lib}</driver-name>

<init-connections>1</init-connections>

<max-connections>25</max-connections>

<max-time-to-wait-connection>60</max-time-to-wait-connection>

<expiration-control>

<connection-lifetime>600</connection-lifetime>

<run-cleanup-thread>300</run-cleanup-thread>

</expiration-control>

<sql-engine>vendor_sql</sql-engine>

<jdbc-1.x>

<driver-class-name>#{db.drv.class}</driver-class-name>

<url>#{db.url}</url>

<user-name>#{db.usr.name}</user-name>

<password>#{db.usr.pwd}</password>

</jdbc-1.x>

</data-source>

</data-sources>

>>>>>>>>>>>>>>>>>>>>> end of XML >>>>>>>>>>>>>>>>>>>>>

You could use this file or another functioning DS as template to create a new DS. I did the same in the following tree steps:

1. Export a functioning DS, say to c:\tmp\ds1.xml

2. edit c:\tmp\ds1.xml, changing url, password, etc.

3. import/load c:\tmp\ds1.xml

Could this be also a solution for your problem?

Regards

Gregor

Former Member
0 Kudos

Hello Gregor,

By looking at your XML file, I have noticed that parameters "connection lifetime" and "cleanup thread" were not configured in my datasource.

After configuring them, the problem was solved.

I am rewarding you points.

Thanks,

Yoav

Former Member
0 Kudos

Hi Gregor Urbanek

What to put for sqlserver2000 in <sql-engine>

<sql-engine>vendor_sql</sql-engine>

here vendor_sql = ???

Thank You

Syed Saifuddin

Answers (0)