JBoss-4.2.3.GA with Oracle11G

[sumanthreddybandi]

Hi,

I am not able to deploy oracle11g datasource in jboss-4.2.3.GA server. I am really stuck with this. Could you please help me?

My oracle-ds.xml contents are:

     <no-tx-datasource>

         <jndi-name>DefaultDS</jndi-name>

         <connection-url>jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=xx.yyy.zz.ww) (PORT=1521))

                                      (CONNECT_DATA=(SERVER = DEDICATED)(SID = ds_abc)))   </connection-url>

         <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>

         <user-name>test*****</user-name>

         <password>*******</password>

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

         <min-pool-size>2</min-pool-size>

         <max-pool-size>15</max-pool-size>

         <blocking-timeout-millis>600000</blocking-timeout-millis>

         <metadata>

              <type-mapping>Oracle11g</type-mapping>

         </metadata>

     </no-tx-datasource>

and i am getting this exception:

     23:01:58.256 DEBUG  [txtimer.DatabasePersistencePolicy] Starting jboss.ejb:service=EJBTimerService,persistencePolicy=database

     23:01:58.260 ERROR  [txtimer.GeneralPurposeDatabasePersistencePlugin] Cannot create timer table

     java.lang.IllegalStateException: Cannot obtain type mapping from: jboss.jdbc:datasource=DefaultDS,service=metadata

             at org.jboss.ejb.txtimer.GeneralPurposeDatabasePersistencePlugin.createTableIfNotExists(GeneralPurposeDatabasePersistencePlugin.java:137)

             at org.jboss.ejb.txtimer.DatabasePersistencePolicy.startService(DatabasePersistencePolicy.java:104)

             at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289)

If i change type-mapping to "Oracle9i" in oracle-ds.xml then i am getting the following exception:

00:13:24.375 WARN   [connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null

org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLRecoverableException: IO Error: Connection reset)

        at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:190)

        at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(InternalManagedConnectionPool.java:619)

        at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:264)

        at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:575)

        at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:347)

        .......................

Caused by: java.sql.SQLRecoverableException: IO Error: Connection reset

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

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

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

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

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

        at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:172)

        ... 120 more

Caused by: java.net.SocketException: Connection reset

        at java.net.SocketInputStream.read(SocketInputStream.java:168)

        at oracle.net.ns.Packet.receive(Packet.java:286)

        at oracle.net.ns.DataPacket.receive(DataPacket.java:100)

        at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:227)

        at oracle.net.ns.NetInputStream.read(NetInputStream.java:172)

[andey]

Hi,

- The "java.sql.SQLRecoverableException: Closed Connection" errors, since these are raised by the Oracle driver, they indicate that some event outside of the JBoss JVM has closed the connection. The connection has been closed or terminated by something outside of the JBoss process.

- This could be a database server initiated timeout or some error condition on the database server. But the source for this error will likely be found outside of JBoss.

- This is a known type of error indicating some problem with your Oracle database or occasionally with the network.

Root Cause

==========

Connectivity to the database has been interrupted due to factors external to JBoss which may include (but are not limited to) the following:

  - Network failures

  - Database shutdown

  - Database failure

  - Connection timeouts configured on the database server

  - Firewall related timeouts

  - The fact that restarting JBoss might fix the connections suggests that you may have invalid connections left in your pool after the network issue.

- Examine network, database and other related service logs for the time period identified by the errors recorded in the JBoss log.

Resolution

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

- Engage the system, network and DBA teams to identify and resolve the root cause.

- Make sure you should enable datasource fault tolerance for JBoss EAP connection pools.

- Fault tolerance will not prevent errors but is meant to increase the likelihood that JBoss can recover

- Fault tolerance is not meant to address long duration events such as database shutdown or lengthy network outages

[mayerw01]

>>> java.lang.IllegalStateException: Cannot obtain type mapping from: jboss.jdbc:datasource=DefaultDS,service=metadata <<<

As this message indicates the entry 'Oracle11g' cannot be found in the standardjbosscmp-jdbc.xml GC: standardjbosscmp-jdbc.xml - GrepCode Source

You should also make sure that you use a valid jdbc driver for Oracle 11 (ojdbc6.jar)

[sumanthreddybandi]

I am using valid jdbc driver (ojdbc_6g.jar), database is Oracle 11g and my java version is 1.6

Yes i know it should match type-mapping from 'standardjbosscmp-jdbc.xml' but i don't find 11g version. So what should i use Oracle9i/Oracle11g type?

[sumanthreddybandi]

@Anup,

I will try fault tolerance solution. So should i add 'fault tolerance' in the connection string like below? Or is there anything else?

jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(FAILOVER=on)(ADDRESS=(PROTOCOL=TCP)(HOST=xx.yyy.zz.ww)(PORT=1521))(CONNECT_DATA=(SERVER = DEDICATED)(SID = ds_abc)(FAILOVER_MODE=(TYPE=select)(METHOD=basic))))

I am also engaging network and DBA teams.

- Network failures        (might be)

- Database shutdown   (it is active)

- Database failure        (am able to connect to it from sqldeveloper client.. and there is a network connectivity from server to db, it is proven from: nc -v {db_ipaddress} {port})

- Connection timeouts configured on the database server (need to check this)

- Firewall related timeouts (need to check this)

- The fact that restarting JBoss might fix the connections suggests that you may have invalid connections left in your pool after the network issue. (restarting is not fixing the issue)

[andey]

Hi @Sumanth,

Set the below:

<validate-on-match>true</validate-on-match>

<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>

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

You can find EAP 4 and 5 example datasource configurations in $JBOSS_HOME/docs/examples/jca/*-ds.xml.

See the below admin guide:

https://access.redhat.com/documentation/en-US/JBoss_Enterprise_Application_Platform/4.2/html-single/Server_Configuration_Guide/index.html#idp12632240

- Connection validation is intended specifically to handle abnormal/exceptional conditions - network interruptions, explicit force kills of sessions (by database administrators for various reasons - e.g. a query that is "running wild"), etc. It is expected that connections will be found to have been terminated externally only in very rare occasions. And in these rare conditions, connection validation would come into play. If the network encounters an exception between validation attempts, there's a chance of client obtaining a stale connection leading to the problem you have cited.

- If there's a connection in the pool that is no longer valid because the database has been restarted (or a Network issue), then the validation mechanism will destroy it. It will check other connections and if it can't find a valid one will attempt to create a new one.

Note:  The error that you see signifies that the Oracle driver is unable to make a connection to the database, and it is timing out. This is usually a network issue. 

Something is causing the validation to fail - this is caused by either an issue with the database itself or the network. JBoss is destroying the connection, and then will attempt to create a new one. If the database/network is still experiencing issues, JBoss will fail to create the connection as well.

I recommend that you check with your dba/network admin for any outages during the time you saw this issue.

[mayerw01]

Sumanth Bandi schrieb:

I am using valid jdbc driver (ojdbc_6g.jar), database is Oracle 11g and my java version is 1.6

Yes i know it should match type-mapping from 'standardjbosscmp-jdbc.xml' but i don't find 11g version. So what should i use Oracle9i/Oracle11g type?

This worked in my environment:

JBoss [Trinity] 4.2.3.GA (build: SVNTag=JBoss_4_2_3_GA date=200807181417)

Java version: 1.7.0_121,Oracle Corporation

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

OS-System: Linux 4.4.36-8-default,amd64

1. Copy ojdbc6.jar to jboss-4.2.3.GA/server/default/lib/
2. oracle-ds.xml to jboss-4.2.3.GA/server/default/deploy/
3. Restart the server

My oracle-ds.xml is rather small:

<datasources>

  <local-tx-datasource>

    <jndi-name>OracleDS</jndi-name>

    <connection-url>jdbc:oracle:thin:@{hostName}:1521:XE</connection-url>

   <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>

   <user-name>${userName}</user-name>

   <password>${password}</password>

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

  <metadata>

         <type-mapping>Oracle9i</type-mapping>

    </metadata>

  </local-tx-datasource>

</datasources>

[sumanthreddybandi]

There was no issue with the jboss or datasource configuration. Network and database teams resolved the issue.

@anup, good insight

Thank you all for the support.