9 Replies Latest reply on Mar 21, 2003 1:09 PM by David Jencks

    JBoss 3.2.0RC2 & Oracle9iR2 connection pooling

    Andrey Demchenko Newbie

      Hi ALL!
      I've configured Jboss for Oracle connection pool. Pool works fine, but when I make shutdown/startup for Oracle DB
      Jboss do not reconnect to Oracle DBMS (not establish new connections and not invalidate old).
      When I try to get connection from datasource I can see error "Not connected to Oracle". Restarting Jboss solves this problem.
      How can I configure oracle-service.xml that Jboss may reestablish Oracle connections after DB shutdown/startup?
      Jboss 3.2.0RC3, jdk 1.4.1_02, Oracle 9i, Linux RedHat 7.3
      The same problem in JBoss3.0.6
      May be problem it this
      (org/jboss/resource/adapter/jdbc/LocalManagedConnection.java)

      /**
      * Describe connectionError method here.
      *
      * @param e a SQLException value
      * @todo Figure out when connectionError should be called, and uncomment it.
      */
      void connectionError(SQLException e)
      {
      /* I'm commenting this out for now until I have a better understanding of
      just when
      such an error event should be propagated. Should this method be uncom
      mented, many
      methods that rely on eg integrity checks by provoking a SQLException "
      duplicate key"
      will fail.

      ConnectionEvent ce = new ConnectionEvent(this, ConnectionEvent.CONNECTION_
      ERROR_OCCURRED, e);
      Collection copy = null;
      synchronized(cels)
      {
      copy = new ArrayList(cels);
      }
      for (Iterator i = copy.iterator(); i.hasNext(); )
      {
      ConnectionEventListener cel = (ConnectionEventListener)i.next();
      cel.connectionErrorOccurred(ce);
      }
      */
      }



      --
      Best regards,
      Andrey Demchenko.

        • 1. Re: JBoss 3.2.0RC2 & Oracle9iR2 connection pooling
          David Jencks Master

          As you have noticed, the best solution is to implement an Oracle specific error handler. If you would like to contribute this, I will enthusiastically commit it.

          Otherwise, I suggest using the flush operation on the pool when the db goes down.

          • 2. Re: JBoss 3.2.0RC2 & Oracle9iR2 connection pooling
            Andrey Demchenko Newbie

            Ok. I will try to correct this problem and inform you when done.

            --
            Best regards,
            Andrey Demchenko.

            • 3. Re: JBoss 3.2.0RC2 & Oracle9iR2 connection pooling
              Andrey Demchenko Newbie

              The code bellow solves my problem
              (correct connector/src/main/org/jboss/resource/adapter/jdbc/BaseWrapperConnection.jpp)
              [I] void connectionError(SQLException e)
              {

              String error_text = (e.getMessage()).toUpperCase();

              /* Check oracle specific errors for broadcasting connectionerror
              ORA-00600 - Internal oracle error
              ORA-00028 - session has been killed
              ORA-01014 - Oracle shutdown in progres
              ORA-01033 - Oracle initialization or shutdown in progress
              TNS- - Net8 messages
              socket - for control socket error
              */
              if ((error_text.indexOf("ORA-00600") > -1) || (error_text.indexOf("ORA-00028") > -1) ||
              (error_text.indexOf("ORA-01014") > -1) || (error_text.indexOf("ORA-01033") > -1) ||
              (error_text.indexOf("ORA-01034") > -1) || (error_text.indexOf("TNS-") > -1) ||
              (error_text.indexOf("SOCKET") > -1) || (error_text.indexOf("BROKEN PIPE") > -1))
              {
              broadcastConnectionError(e);
              }
              }
              [/I]

              I do not test all errors, but when database shutdown/start
              old connections are destroing (only when try to get connection from datasource) and new connetions establishes.

              --
              Best regards,
              Andrey Demchenko.

              • 4. Re: JBoss 3.2.0RC2 & Oracle9iR2 connection pooling
                David Jencks Master

                Thanks!

                I've made a generic framework for this and put your code into an Oracle specific class. You specify the class name of the ExceptionSorter in the deployment descriptor config property ExceptionSorterClassName. The one I made for your code is org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter.

                I haven't thought yet how to modify the xsl so this can be used with the *-ds.xml configuration.

                Could you please check that this works as you expect? i will then port to jb4 and probably 3.0.x.

                • 5. Re: JBoss 3.2.0RC2 & Oracle9iR2 connection pooling
                  Andrey Demchenko Newbie

                  Server works 4 days without problems. I have added check for 3 additional Oracle errors.

                  [I] void connectionError(SQLException e)
                  {
                  String error_text = (e.getMessage()).toUpperCase();

                  /* Check oracle specific errors for broadcasting connectionerror
                  ORA-00600 - Internal oracle error
                  ORA-00028 - session has been killed
                  ORA-01014 - Oracle shutdown in progres
                  ORA-01033 - Oracle initialization or shutdown in progress
                  ORA-03111 - break received on communication channel
                  ORA-03113 - end-of-file on communication channel
                  ORA-03114 - not connected to ORACLE
                  TNS- - Net8 messages
                  socket - for control socket error
                  */
                  if ((error_text.indexOf("ORA-00600") > -1) || (error_text.indexOf("ORA-00028") > -1) ||
                  (error_text.indexOf("ORA-01014") > -1) || (error_text.indexOf("ORA-01033") > -1) ||
                  (error_text.indexOf("ORA-01034") > -1) || (error_text.indexOf("TNS-") > -1) ||
                  (error_text.indexOf("ORA-03111") > -1) || (error_text.indexOf("ORA-03113") > -1) ||
                  (error_text.indexOf("ORA-03114") > -1) ||
                  (error_text.indexOf("SOCKET") > -1) || (error_text.indexOf("BROKEN PIPE") > -1))
                  {
                  broadcastConnectionError(e);
                  }
                  }
                  [/I]

                  --
                  Best regards,
                  Andrey Demchenko.

                  • 6. Re: JBoss 3.2.0RC2 & Oracle9iR2 connection pooling
                    David Jencks Master

                    I've added these three more errors and tried again to commit the version I came up with. (maybe I forgot to actually commit??)

                    There is no documentation for ORA-01034.

                    Assuming that this time the commit worked, could you please check that my version works properly with Oracle?

                    thanks
                    david jencks

                    • 7. Re: JBoss 3.2.0RC2 & Oracle9iR2 connection pooling
                      Andrey Demchenko Newbie

                      ORA-01034 ORACLE not available (may be Oracle not started)
                      I will try to checkout jboss from CVS and test it.

                      --
                      Best regards,
                      Andrey Demchenko.

                      • 8. Re: JBoss 3.2.0RC2 & Oracle9iR2 connection pooling
                        Andrey Demchenko Newbie

                        Hi,
                        I have been tested JBoss 3.2.0RC4 for OracleExceptionSorter
                        and unfortunately has some troubles (Pool not started).
                        1) I set this in oracle-service.xml
                        [I] <config-property name="ExceptionSorterClassName" type="java.lang.String">org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</config-property>[/I]
                        2) And this is a system.log
                        ===========================
                        [I]
                        2003-03-20 10:34:23,736 DEBUG [org.jboss.resource.connectionmanager.RARDeployment] setting property: ExceptionSorterClassName to value org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
                        2003-03-20 10:34:23,737 WARN [org.jboss.resource.connectionmanager.RARDeployment] The class 'class org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory' has no setter for config property 'ExceptionSorterClassName'
                        2003-03-20 10:34:23,737 ERROR [org.jboss.resource.connectionmanager.RARDeployment] Starting failed
                        java.lang.IllegalArgumentException: The class 'class org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory' has no setter for config property 'ExceptionSorterClassName'
                        at org.jboss.resource.connectionmanager.RARDeployment.setManagedConnectionFactoryAttribute(RARDeployment.java:584)
                        at org.jboss.resource.connectionmanager.RARDeployment.setMcfProperties(RARDeployment.java:732)
                        at org.jboss.resource.connectionmanager.RARDeployment.startService(RARDeployment.java:543)
                        at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)[\I]

                        ===========================
                        Can you fix this?

                        --
                        Best regards,
                        Andrey Demchenko.

                        • 9. Re: JBoss 3.2.0RC2 & Oracle9iR2 connection pooling
                          David Jencks Master

                          OK, I think I finally fixed this (this time I actually tested it a little bit).

                          In your oracle-ds.xml (or oracle-xa-ds.xml) include

                          <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.DummyExceptionSorter</exception-sorter-class-name>


                          (you'll need to update your 3.2 source and recompile all of jboss 3.2)

                          Thanks
                          david jencks