JBoss application cannot make XA connection to Oracle 11g

Question

I've been working on several JBoss applications where I have everything running on my laptop (Windows XP) including an Oracle database (11g Enterprise Edition). Everything works fine on the laptop.

Now I'm setting up a new, faster machine (running Windows 7 Professional) and the non-XA JBoss apps run fine but the apps requiring an XA connection cannot connect to Oracle. I'm pretty sure it is a problem with my new Oracle installation because I can point the apps to an external Oracle server and they work, but they fail to connect when pointing them my local Oracle database.

I've done a lot of google searches and the only information I can find is that I need to run the script $ORACLE_HOME/rdbms/admin/xaview.sql to create some XA views and I need to grant the following permissions to the user:

GRANT SELECT ON sys.dba_pending_transactions TO <user>;
GRANT SELECT ON sys.pending_trans$ TO <user>;
GRANT SELECT ON sys.dba_2pc_pending TO <user>;
GRANT EXECUTE ON sys.dbms_xa TO <user>;

But after doing all that, I still cannot get the apps to connect. This is the exception I'm getting:

org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Io exception:
The Network Adapter could not establish the connection)     at
org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory.createManagedConnection(XAMangedConnectionFactory.java:144)   at
org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventLisener(InternalManagedConnectionPool.java:577) at
org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:262)    at
org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:500) at
org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:347)   at
org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:330) at
org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:402) at
org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:849)  at
org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:90) at
org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:46)   at
org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:84)

I know this isn't really a programming question, but I was hoping someone had seen this before. Any help would be much appreciated.

Answer 1

I was able to resolve this issue by ensuring that the host name and domain name settings in the LISTENER.ORA file and in the TNSNAMES.ORA file matched. For example, if one had HOST = localhost and the other had HOST = myhost.xyz.com then it would cause problems for XA but not for normal Java JDBC connections. My theory is that some part of the XA stack needed to make an OCI connection that read the TNSNAMES.ORA file but then could not connect because of a mismatch with the Listener configuration. Normal Java JDBC connections don't care about the TNSNAMES.ORA file as they make a direct connection to the listener with their own configuration settings.

I also had to rename some folders from xxx_localhost_yyy to xxx_myhost_yyy to get the Enterprise Manager to work.

To get TOAD to work I had to install the 32 bit client as it will not work with the 64 bit client and I had to configure it's ORACLE_HOME variable in the Windows registry under HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Oracle. Apparently Wow6432Node is a special place in the Windows registry for defining values that only 32 bit applications will see.

Anyway, I'm relieved to have it all working.

Answer 2

Well, can you connect using a sql client to this database ?