3 Replies Latest reply: Mar 25, 2014 11:12 AM by Joe Weinstein-Oracle RSS

    Generic datasource with RAC

    user2695214

      We have a deployment that is using weblogic 10.3.5 and oracle RAC. The customer owns and manages the oracle server so we do not have all the details.

       

      We are given the URL, username and password and put it in a generic datasource in the weblogic console. The application works for a few days and then over some time the database connection pool becomes suspended and the application stops processing.

       

      My question is related to generic vs gridlink datasources.  What is the difference and is it safe to use a generic datasource to connect to a RAC that has 3 databases in the cluster.

       

      Could this be a cause of the database pool suspension ?

       

       

      Thanks.

        • 1. Re: Generic datasource with RAC
          Joe Weinstein-Oracle

          Hi. Show me the xml file that represents the datasource, and the exceptions in the log that lead to

          the server suspending the datasource.

           

          The difference between gridlink and generic is that once configured, a gridlink datasource gets an independent

          source of information about the health of the RAC DBMS, and can use that information to load-balance and

          react to DBMS failures. A generic datasource relies on it's own testing and replacing connections, and treats

          every connection as identical. If you are using more than one RAC node, you can use a MultiDataSource to

          do load-balancing and failover among 3 generic datasources, each one configured only to connect to a

          specific RAC node. If you only use one datasource, and the URL allows/delivers connections to a mix of

          the RAC nodes, then that can cause problems. If one RAC node goes down, all the connections to that

          node will be dead. The datasource thinks all connections are identical, so if it sees X consecutive connection

          tests fail, it may assume all connections are dead, and temporarily suspend the datasource, killing the

          reset of the connections even to up-and-running RAC nodes.

          • 2. Re: Generic datasource with RAC
            user2695214

            Thanks for your response.

             

            The main concern we have is that the pool is suspended and does not recover.  I can access the DB with no problem via sqlplus and squirrel.  But the datasource gets suspended and stays suspended until we restart the application.

             

            Here is the jdbc configuration:

             

              <name>DS_NAME</name>

            <jdbc-driver-params>

            <url>jdbc:oracle:thin:@dbhostname:1521/dbapps</url>

            <driver-name>oracle.jdbc.xa.client.OracleXADataSource</driver-name>

                <properties>

                  <property>

            <name>user</name>

                    <value>database_rac_app</value>

            </property>

            </properties>

            <password-encrypted>REMOVED FROM POST</password-encrypted>

            <use-xa-data-source-interface>true</use-xa-data-source-interface>

            </jdbc-driver-params>

              <jdbc-connection-pool-params>

            <initial-capacity>30</initial-capacity>

            <max-capacity>100</max-capacity>

            <capacity-increment>1</capacity-increment>

            <shrink-frequency-seconds>0</shrink-frequency-seconds>

            <highest-num-waiters>2147483647</highest-num-waiters>

            <connection-creation-retry-frequency-seconds>10</connection-creation-retry-frequency-seconds>

            <connection-reserve-timeout-seconds>10</connection-reserve-timeout-seconds>

            <test-frequency-seconds>0</test-frequency-seconds>

                <test-connections-on-reserve>true</test-connections-on-reserve>

            <ignore-in-use-connections-enabled>true</ignore-in-use-connections-enabled>

            <inactive-connection-timeout-seconds>30</inactive-connection-timeout-seconds>

            <test-table-name>SQL SELECT 1 FROM DUAL</test-table-name>

            <login-delay-seconds>0</login-delay-seconds>

            <statement-cache-size>10</statement-cache-size>

            <statement-cache-type>LRU</statement-cache-type>

            <remove-infected-connections>true</remove-infected-connections>

            <seconds-to-trust-an-idle-pool-connection>10</seconds-to-trust-an-idle-pool-connection>

            <statement-timeout>-1</statement-timeout>

            <jdbc-xa-debug-level>10</jdbc-xa-debug-level>

            <pinned-to-thread>false</pinned-to-thread>

            <wrap-types>true</wrap-types>

            </jdbc-connection-pool-params>

            <jdbc-data-source-params>

                <jndi-name>racapp/datasource</jndi-name>

            <global-transactions-protocol>TwoPhaseCommit</global-transactions-protocol>

            <keep-conn-after-local-tx>true</keep-conn-after-local-tx>

            </jdbc-data-source-params>

            <jdbc-xa-params>

            <keep-xa-conn-till-tx-complete>true</keep-xa-conn-till-tx-complete>

            <need-tx-ctx-on-close>false</need-tx-ctx-on-close>

            <xa-end-only-once>false</xa-end-only-once>

            <keep-logical-conn-open-on-release>false</keep-logical-conn-open-on-release>

            <resource-health-monitoring>true</resource-health-monitoring>

            <recover-only-once>false</recover-only-once>

            <xa-set-transaction-timeout>true</xa-set-transaction-timeout>

            <xa-transaction-timeout>600</xa-transaction-timeout>

            <rollback-local-tx-upon-conn-close>false</rollback-local-tx-upon-conn-close>

            <xa-retry-duration-seconds>900</xa-retry-duration-seconds>

            <xa-retry-interval-seconds>60</xa-retry-interval-seconds>

            </jdbc-xa-params>

             

             

            Here are the unique exceptions I am seeing:

             

             

            Internal Exception: java.sql.SQLException: Internal error: Cannot obtain XAConnection weblogic.common.resourcepool.ResourceDisabledException: Pool DS_NAME is Suspended,

            cannot allocate resources to applications..

                    at weblogic.common.resourcepool.ResourcePoolImpl.reserveResourceInternal(ResourcePoolImpl.java:377)

                    at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:342)

                    at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:419)

                    at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:324)

                    at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(ConnectionPoolManager.java:94)

                    at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(ConnectionPoolManager.java:63)

                    at weblogic.jdbc.jta.DataSource.getXAConnectionFromPool(DataSource.java:1677)

                    at weblogic.jdbc.jta.DataSource.refreshXAConnAndEnlist(DataSource.java:1445)

                    at weblogic.jdbc.jta.DataSource.getConnection(DataSource.java:446)

                    at weblogic.jdbc.jta.DataSource.connect(DataSource.java:403)

                    at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:364)

             

             

            and this one:

             

            java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

                    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:419)

                    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:538)

                    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:228)

                    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)

                    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)

                    at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:280)

                    at oracle.jdbc.xa.client.OracleXADataSource.getPooledConnection(OracleXADataSource.java:482)

                    at oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:156)

                    at oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:101)

                    at weblogic.jdbc.common.internal.XAConnectionEnvFactory.makeConnection(XAConnectionEnvFactory.java:477)

                    at weblogic.jdbc.common.internal.XAConnectionEnvFactory.createResource(XAConnectionEnvFactory.java:177)

                    at weblogic.common.resourcepool.ResourcePoolImpl.makeResources(ResourcePoolImpl.java:1249)

                    at weblogic.common.resourcepool.ResourcePoolImpl$ResourcePoolMaintanenceTask.timerExpired(ResourcePoolImpl.java:2718)

                    at weblogic.timers.internal.TimerImpl.run(TimerImpl.java:273)

                    at weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:528)

                    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209)

                    at weblogic.work.ExecuteThread.run(ExecuteThread.java:178)

            Caused By: oracle.net.ns.NetException: The Network Adapter could not establish the connection

                    at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:375)

                    at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:422)

                    at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:686)

                    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:342)

                    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1056)

                    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:308)

                    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:538)

                    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:228)

                    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)

                    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)

                    at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:280)

                    at oracle.jdbc.xa.client.OracleXADataSource.getPooledConnection(OracleXADataSource.java:482)

                    at oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:156)

                    at oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:101)

                    at weblogic.jdbc.common.internal.XAConnectionEnvFactory.makeConnection(XAConnectionEnvFactory.java:477)

                    at weblogic.jdbc.common.internal.XAConnectionEnvFactory.createResource(XAConnectionEnvFactory.java:177)

                    at weblogic.common.resourcepool.ResourcePoolImpl.makeResources(ResourcePoolImpl.java:1249)

                    at weblogic.common.resourcepool.ResourcePoolImpl$ResourcePoolMaintanenceTask.timerExpired(ResourcePoolImpl.java:2718)

                    at weblogic.timers.internal.TimerImpl.run(TimerImpl.java:273)

                    at weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:528)

                    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209)

                    at weblogic.work.ExecuteThread.run(ExecuteThread.java:178)

            Caused By: java.net.ConnectException: Connection timed out

                    at java.net.PlainSocketImpl.socketConnect(Native Method)

                    at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:351)

                    at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:213)

                    at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:200)

                    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)

                    at java.net.Socket.connect(Socket.java:529)

                    at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:150)

                    at oracle.net.nt.ConnOption.connect(ConnOption.java:130)

                    at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:353)

                    at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:422)

                    at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:686)

                    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:342)

            • 3. Re: Generic datasource with RAC
              Joe Weinstein-Oracle

              OK, you have multiple problems.

              1 - WLS can't support XA with a generic datasource unless all connections are always to one specific RAC node.

              You need to get a URL for each specific RAC node, make a datasource for each, and make a load-balancing

              multidatasource using all those, and have your apps use the multidatasource for connections.

              2 - If WLS gets exceptions trying to make a connection to the DBMS, that is a DBMS/network problem,

              and will cause serious WLS symptoms, such as the datasource suspending itself. Every 5 seconds or so

              after that, it will try to reconnect to the DBMS, and will re-enable the datasource automatically when it can.

              3 - Are you sure that's the whole XML, with all the driver-level properties? Or is there a firewall between

              WLS and the DBMS? The 'Conneciton timed out' exception is unusual, and I would associate it with

              a driver-level connect timeout setting or perhaps a firewall etc. If yo have a firewall that may be killing

              connections if idle too long, you shouyld try setting the datasource's test frequency to something fractionally

              more frequent than the firewall's idle timeout to keep the connections looking busy enough to be spared.