dedicated server process results in ORA-24777 error

[ckokotsis]

I am using JBOSS v3.2.4, Hibernate 2.1.3 and Oracle9i and when my databse query returns an 'ORA-24777: use of non-migratable database link not allowed'.

Basically we have a postcode table that has been set up as a DBLink table to point to a central database. When we try and execute a query against the postcode table we get an ORA-24777 error.

Researching into this Oracle provide the follwoing documentation:

ORA-24777 use of non-migratable database link not allowed
Cause: The transaction, which needs to be migratable between sessions, tried to access a remote database from a non-multithreaded server process.

Action: Perform the work in the local database or open a connection to the remote database from the client. If the multithreaded server option is installed, connect to the Oracle instance through the dispatcher.

Creating and maintaining seperate connections to different databases is not a valid option at the moment. Therefore, does anyone know how to configure JBOSS to use a migrateable server process as opposed to what appears to be a the current dedicated server process?


Thanks,

cK

[vabrol]

Were you able to resolve this proble?
I am getting the same thing with similar setup but no luck.
appreciate any input

Thanks

[pieter_degraeuwe]

Is there already a solution for this ?

[cvollrath]

I had the same problem while accessing an Oracle table through a DB link and I finally found a solution with a little help from an Oracle expert.
The Oracle DB (on the DB server) must be configured to run as a shared server instead of dedicated server (see init.ora, sqlnet.ora).

The use of a shared server can also be forced in the datasource definition by by including a full connect string as the jdbc url. This must the include then string "(SERVER=SHARED)"

Maybe this might help,

tof (Christof Vollrath)

[ajeeth]

Hi Christof,

With regards to the following solution :

"The use of a shared server can also be forced in the datasource definition by by including a full connect string as the jdbc url. This must the include then string (SERVER=SHARED) "

Our connection string looks like this :
jdbc:oracle:thin:@company105:1576:pprod

Could you please let me know, what needs to be modified in the above URL to enforce SERVER=SHARED ?

[arfrabe]

Hi,

here is an example

jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = company105)(PORT = 1576)))(CONNECT_DATA =(SERVICE_NAME = pprod)(SERVER=shared)))

[kukumalu]

Hi,

Is that i changed the oracle-xa-ds.xml in jboss? .Anyone can help me to change it to <shared-server>? I have tried this -->

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=3.244.4.165)(PORT=1522)))(SERVICE_NAME=ehub2)(SERVER=shared)))

Below is my datasource-property...
<xa-datasource-property name="URL">jdbc:oracle:thin:@3.244.4.165:1522:ehub2</xa-datasource-property>

[kukumalu]

Hi,
I think i miss it the CONNECT_DATA, i did tired for it,however still doesn't work.
Anyone can help?

xa-datasource-property name="URL">jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 3.244.4.165)(PORT = 1522)))(CONNECT_DATA =(SERVICE_NAME = ehub2)(SERVER=shared)))</xa-datasource-property

[h2kinfosys]

Hello All,
I set the parameters in Oracle as per the instructions and my oracle-xa-ds.xml and oracle-ds.xml files has below. Still I am getting the exception. Any input on this ?

<connection-url>jdbc:oracle:thin:@(description=(address_list=(address=(protocol=tcp)(host=mana-jb)(port=1521)))(connect_data=(server=shared)(service_name=rrm)))</connection-url>


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

org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Io exception: End of TNS data channel)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:225)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:195)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(InternalManagedConnectionPool.java:633)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:267)
at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:622)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:404)
at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:381)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:496)
at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:941)
at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:89)
at org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:46)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:67)
at net.cox.customerprofile.data.ControllerEAOBean.getControllers(ControllerEAOBean.java:93)
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:597)
at org.jboss.aop.joinpoint.MethodInvocation.invokeTarget(MethodInvocation.java:122)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:111)
at org.jboss.ejb3.EJBContainerInvocationWrapper.invokeNext(EJBContainerInvocationWrapper.java:69)
at org.jboss.ejb3.interceptors.aop.InterceptorSequencer.invoke(InterceptorSequencer.java:73)
at org.jboss.ejb3.interceptors.aop.InterceptorSequencer.aroundInvoke(InterceptorSequencer.java:59)
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:597)
at org.jboss.aop.advice.PerJoinpointAdvice.invoke(PerJoinpointAdvice.java:174)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor.fillMethod(InvocationContextInterceptor.java:72)
at org.jboss.aop.advice.org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor_z_fillMethod_1524522201.invoke(InvocationContextInterceptor_z_fillMethod_1524522201.java)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor.setup(InvocationContextInterceptor.java:88)
at org.jboss.aop.advice.org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor_z_setup_1524522201.invoke(InvocationContextInterceptor_z_setup_1524522201.java)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.ejb3.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:62)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:56)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.ejb3.tx.NullInterceptor.invoke(NullInterceptor.java:42)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:68)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.aspects.tx.TxPolicy.invokeInNoTx(TxPolicy.java:66)
at org.jboss.ejb3.tx.TxInterceptor$NotSupported.invoke(TxInterceptor.java:114)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.ejb3.tx.NullInterceptor.invoke(NullInterceptor.java:42)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.ejb3.security.Ejb3AuthenticationInterceptorv2.invoke(Ejb3AuthenticationInterceptorv2.java:186)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:41)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.ejb3.BlockContainerShutdownInterceptor.invoke(BlockContainerShutdownInterceptor.java:67)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.aspects.currentinvocation.CurrentInvocationInterceptor.invoke(CurrentInvocationInterceptor.java:67)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
at org.jboss.ejb3.session.SessionSpecContainer.invoke(SessionSpecContainer.java:176)
at org.jboss.ejb3.session.SessionSpecContainer.invoke(SessionSpecContainer.java:216)
at org.jboss.ejb3.proxy.impl.handler.session.SessionProxyInvocationHandlerBase.invoke(SessionProxyInvocationHandlerBase.java:207)
at org.jboss.ejb3.proxy.impl.handler.session.SessionProxyInvocationHandlerBase.invoke(SessionProxyInvocationHandlerBase.java:164)
at $Proxy743.getControllers(Unknown Source)
at net.cox.web.admin.ControllerDetails.pageBeginRender(ControllerDetails.java:74)
at org.apache.tapestry.AbstractPage.firePageBeginRender(AbstractPage.java:409)
at org.apache.tapestry.AbstractPage.renderPage(AbstractPage.java:244)
at org.apache.tapestry.engine.RequestCycle.renderPage(RequestCycle.java:397)
at org.apache.tapestry.services.impl.DefaultResponseBuilder.renderResponse(DefaultResponseBuilder.java:151)
at org.apache.tapestry.services.impl.ResponseRendererImpl.renderResponse(ResponseRendererImpl.java:33)
at $ResponseRenderer_12482ed255e.renderResponse($ResponseRenderer_12482ed255e.java)
at $ResponseRenderer_12482ed255d.renderResponse($ResponseRenderer_12482ed255d.java)
at org.apache.tapestry.engine.PageService.service(PageService.java:68)
at $IEngineService_12482ed25d5.service($IEngineService_12482ed25d5.java)
at org.apache.tapestry.services.impl.EngineServiceInnerProxy.service(EngineServiceInnerProxy.java:77)
at org.apache.tapestry.services.impl.EngineServiceOuterProxy.service(EngineServiceOuterProxy.java:72)
at org.apache.tapestry.engine.AbstractEngine.service(AbstractEngine.java:241)
at org.apache.tapestry.services.impl.InvokeEngineTerminator.service(InvokeEngineTerminator.java:54)
at $WebRequestServicer_12482ed25b4.service($WebRequestServicer_12482ed25b4.java)
at $WebRequestServicer_12482ed25b3.service($WebRequestServicer_12482ed25b3.java)
at $WebRequestServicer_12482ed25b0.service($WebRequestServicer_12482ed25b0.java)
at $WebRequestServicer_12482ed25af.service($WebRequestServicer_12482ed25af.java)
at org.apache.tapestry.services.impl.WebRequestServicerPipelineBridge.service(WebRequestServicerPipelineBridge.java:61)
at $ServletRequestServicer_12482ed2596.service($ServletRequestServicer_12482ed2596.java)
at $ServletRequestServicer_12482ed2595.service($ServletRequestServicer_12482ed2595.java)
at org.apache.tapestry.request.DecodedRequestInjector.service(DecodedRequestInjector.java:55)
at $ServletRequestServicerFilter_12482ed2592.service($ServletRequestServicerFilter_12482ed2592.java)
at $ServletRequestServicerFilter_12482ed2591.service($ServletRequestServicerFilter_12482ed2591.java)
at $ServletRequestServicer_12482ed2597.service($ServletRequestServicer_12482ed2597.java)
at org.apache.tapestry.multipart.MultipartDecoderFilter.service(MultipartDecoderFilter.java:52)
at $ServletRequestServicerFilter_12482ed2590.service($ServletRequestServicerFilter_12482ed2590.java)
at $ServletRequestServicerFilter_12482ed258f.service($ServletRequestServicerFilter_12482ed258f.java)
at $ServletRequestServicer_12482ed2597.service($ServletRequestServicer_12482ed2597.java)
at org.apache.tapestry.services.impl.SetupRequestEncoding.service(SetupRequestEncoding.java:53)
at $ServletRequestServicerFilter_12482ed2594.service($ServletRequestServicerFilter_12482ed2594.java)
at $ServletRequestServicerFilter_12482ed2593.service($ServletRequestServicerFilter_12482ed2593.java)
at $ServletRequestServicer_12482ed2597.service($ServletRequestServicer_12482ed2597.java)
at $ServletRequestServicer_12482ed258a.service($ServletRequestServicer_12482ed258a.java)
at $ServletRequestServicer_12482ed2589.service($ServletRequestServicer_12482ed2589.java)
at org.apache.tapestry.ApplicationServlet.doService(ApplicationServlet.java:126)
at org.apache.tapestry.ApplicationServlet.doGet(ApplicationServlet.java:103)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:190)
at org.jboss.web.tomcat.service.session.ClusteredSessionValve.handleRequest(ClusteredSessionValve.java:135)
at org.jboss.web.tomcat.service.session.ClusteredSessionValve.invoke(ClusteredSessionValve.java:94)
at org.jboss.web.tomcat.service.session.LockingValve.invoke(LockingValve.java:62)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:92)
at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.process(SecurityContextEstablishmentValve.java:126)
at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:70)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)
at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:905)
at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:592)
at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:2036)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Io exception: End of TNS data channel
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:494)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:411)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:490)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:202)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:33)
at oracle.jdbc.driver.OracleDriver._connect_2(OracleDriver.java:465)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:207)
... 139 more

[h2kinfosys]

If we write the bellow connection URL it works fine, without providing Server=SHARED.
<connection-url>jdbc:oracle:thin:@(description=((address=(protocol=tcp)(host=mana-jb)(port=1521)))(connect_data=(SID=testdb)


I would like to know how to find out the JBOss is using Shared Server connection pool?
What are the approaches we need to test to make sure the Database connections are SHARED?

Thank you,
Mahesh

[periklis_douvitsas]

I know it's been a while but for others that reas this forum.

I think what you did wrong was SERVICE_NAME = ehub2

When you configure shared mode in the service name you have to put the value that you see in "dispatchers" in oracle parameters

So if the value in the dispatchers is :(PROTOCOL=TCP) (SERVICE=ehub2XDB) then in the service name you have to put SERVICE_NAME = ehub2XDB