Tweaking Oracle XE for XA Transactions in JBoss 6

I’m currently testing an application in Oracle 10g XE environment and getting familiar with working with Oracle alongside JBoss AS 6 to support XA transactions. After configuring my XA datasource as per JBoss’s documentation, I started up the JBoss server only to come up with the ff exception.

ORA-12516: TNS:listener could not find available handler with matching protocol stack

As it turns out, it seems my settings for minimum connection pool size was more than what Oracle XE by default allows (i.e. 49). Thankfully, I found the perfect guide to solving this problem here. Thanks Andrew.

Having sweated that first part out, I restarted my application server again, only to get the next biggie.

ARJUNA-16027 Local XARecoveryModule.xaRecovery got XA exception XAException.XAER_RMERR: javax.transaction.xa.XAException
 at oracle.jdbc.xa.OracleXAResource.recover(OracleXAResource.java:638) 

It turns out again that the current user that I used to access my XE database was not XA enabled, with no ability to perform 2 phase commits. So I logged into my sqlplus console and entered the following statements, making sure I connected as ‘sysdba’. Note: MYUSER is the name of the user used to perform the XA transactions.

grant select on pending_trans$ to MYUSER;
grant select on dba_2pc_pending to MYUSER;
grant select on dba_pending_transactions to MYUSER;
grant execute on dbms_system to MYUSER;

 

I smiled after that, because my app started up nicely. Let’s see what else Oracle has up its sleeves to terrorise my life with.

Advertisements