10 Replies Latest reply on Jul 4, 2012 7:34 AM by periklis_douvitsas

    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

        • 1. Re: dedicated server process results in ORA-24777 error
          vabrol

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

          Thanks

          • 2. Re: dedicated server process results in ORA-24777 error
            pieter_degraeuwe

            Is there already a solution for this ?

            • 3. Re: dedicated server process results in ORA-24777 error
              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)

              • 4. Re: dedicated server process results in ORA-24777 error
                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 ?

                • 5. Re: dedicated server process results in ORA-24777 error
                  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)))

                  • 6. Re: dedicated server process results in ORA-24777 error
                    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>

                    • 7. Re: dedicated server process results in ORA-24777 error
                      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

                      • 8. Re: dedicated server process results in ORA-24777 error

                        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

                        • 9. Re: dedicated server process results in ORA-24777 error

                          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

                          • 10. Re: dedicated server process results in ORA-24777 error
                            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