repeating log warning XAException

[George Callhoun]

I have 4 wildfly version 22 standalone instances (dev, test, train, prod) that get a repeating log error about every 130 seconds (below).  It's been with this app for a few years back to version 20 (maybe further) and I'm trying to address it now.  I've googled and the one suggestion I tried was to execute some sql grant statements on the associated Oracle database, didn't help though.  

Searching this group, I found one question resolved by deleting files from ./standalone/data.  I wiped out all of the data subdir contents but that didn't help.

Any ideas?  I'd like to solve the real issue but also would just like to get rid of the message as it doesn't seem to reflect a noticeable problem.

2024-01-25 13:21:54,343 WARN  [com.arjuna.ats.jta] (Periodic Recovery) ARJUNA016027: Local XARecoveryModule.xaRecovery got XA exception XAException.XAER_RMERR: javax.transaction.xa.XAException
        at ora...@19.3.0.0.0//oracle.jdbc.xa.OracleXAResource.recover(OracleXAResource.java:732)
        at org.jboss.ironjac...@1.5.3.Final//org.jboss.jca.adapters.jdbc.xa.XAManagedConnection.recover(XAManagedConnection.java:373)
        at org.jboss.iro...@1.5.3.Final//org.jboss.jca.core.tx.jbossts.XAResourceWrapperImpl.recover(XAResourceWrapperImpl.java:185)
        at org.jboss.jts//com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.xaRecoveryFirstPass(XARecoveryModule.java:735)
        at org.jboss.jts//com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:249)
        at org.jboss.jts//com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:191)
        at org.jboss.jts//com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:770)
        at org.jboss.jts//com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:382)

[Marco Sappe Griot]

Thanks George for sharing your details,

I am adding the WildFly community again in the thread (I deleted it by mistake) so that we can share this thread with the community.

I would like to note that the XAER_RMERR is a database error so I recommend you ask Oracle's support for this issue.

BTW I noticed you didn't list ' GRANT FORCE ANY TRANSACTION TO <user>;'.

In fact quoting the Oracle documentation "Grant FORCE ANY TRANSACTION privilege to the Oracle user who might commit or roll back XA transactions created by other users".

I hope that helped,

Kind regards,

Marco




On Mon, Jan 29, 2024 at 4:46 PM George Callhoun <gcall...@gmail.com> wrote:

Hi Marco - thanks for the reply.  Here are the answers to your questions.

Oracle 19.3.0 (19c)

sql grants (from another redhat blog posting):

 grant select on pending_trans$ to <user>;
 grant select on dba_2pc_pending to <user>;
 grant select on dba_pending_transactions to <user>;
 grant execute on dbms_system to <user>;  (If using Oracle 10.2.0.3 or lower JDBC driver) <-- we are 19.3 so didn't run this one
 grant execute on dbms_xa to <user>; (If using Oracle 10.2.0.4 or higher JDBC driver)

Note: Another blog suggested after running the grant updates one might need to "clear the oracle transaction log" or the error messages might continue.  I've requested the DBA do this but I'm still waiting.  I will also point out the problem exists in our lower envs but not in the production env.  Prod data is refreshed down to the lower envs periodically.  Did that refresh move an incongruent transaction log as well?  I don't know.  Is there even anything in the transaction log?  I don't know.  But it might be part of the problem.  Of course, prod would be immune as nothing is refreshing to it.  As far as I know there isn't any other state in tables that might be a mis-match to the lower envs (other than the transaction log, if that is even a problem).

Another note question:  An IBM Websphere blog suggests this same grant statements update as the solution.  (https://www.ibm.com/support/pages/exception-occurs-during-recovery-oracle-database-transactions).  They also say to  delete the transaction logs.  But it is unclear if what they mean by transaction logs is the application server transaction logs or the Oracle transaction log.  So now I wonder what was meant by "transaction log" in the wildfly directed blogs:  (i.e. application or Oracle?).  As I mentioned before, I attempted to clean out wildfly dirs under ../standlalone/data/* - that didn't help.

XA config from standalone.xml

----------------------------------------

<xa-datasource jndi-name="java:jboss/datasources/ras-ds" pool-name="rasPool" enabled="true" use-java-context="true" use-ccm="true">
<xa-datasource-property name="URL">
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=devDbHost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=devDbServiceName)))
</xa-datasource-property>
<xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
<driver>oracle</driver>
<xa-pool>
<is-same-rm-override>false</is-same-rm-override>
<no-tx-separate-pools>true</no-tx-separate-pools>
</xa-pool>
<security>
<user-name>DEVDBA</user-name>
<credential-reference store="ras_store" alias="DEVDBApwd"/>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
<stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
</validation>
</xa-datasource>

<xa-datasource jndi-name="java:jboss/datasources/jbpm-ds" pool-name="jbpmPool" enabled="true" use-java-context="true" use-ccm="true">
<xa-datasource-property name="URL">
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=devDbHost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=devDbServiceName)))
</xa-datasource-property>
<xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
<driver>oracle</driver>
<xa-pool>
<is-same-rm-override>false</is-same-rm-override>
<no-tx-separate-pools>true</no-tx-separate-pools>
</xa-pool>
<security>
<user-name>jbpm6</user-name>
<credential-reference store="ras_store" alias="JBPMpwd"/>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
<stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
</validation>
</xa-datasource>

<xa-datasource jndi-name="java:jboss/datasources/ifx" pool-name="bxPool" enabled="true" use-java-context="true" use-ccm="true">
<xa-datasource-property name="IfxIFXHOST">
ifxDevHost
</xa-datasource-property>
<xa-datasource-property name="ServerName">
ifxServerName
</xa-datasource-property>
<xa-datasource-property name="PortNumber">
ifxPort
</xa-datasource-property>
<xa-datasource-property name="DatabaseName">
ifxDev
</xa-datasource-property>
<xa-datasource-class>com.informix.jdbcx.IfxXADataSource</xa-datasource-class>
<driver>informix</driver>
<xa-pool>
<is-same-rm-override>false</is-same-rm-override>
<no-tx-separate-pools>true</no-tx-separate-pools>
</xa-pool>
<security>
<user-name>ifxUser</user-name>
<credential-reference store="our_store" alias="ifxUserpwd"/>
</security>
</xa-datasource>

<drivers>
<driver name="h2" module="com.h2database.h2">
<xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
</driver>
<driver name="oracle" module="oracle">
<xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
</driver>
<driver name="informix" module="informix">
<xa-datasource-class>com.informix.jdbcx.IfxXADataSource</xa-datasource-class>
</driver>
</drivers>
 -------------------------------------------

On Fri, Jan 26, 2024 at 6:46 AM Marco Sappe Griot <msap...@redhat.com> wrote:

Thanks George for reaching out!

It might be a permission problem as you mentioned. Could I ask you about the Oracle version you are using and the grant statements you executed? Additionally could you share your xa-datasource configuration?

In the meantime I would recommend you to have a look at the Oracle database documentation and see if that might help:

• Oracle 12 Documentation
• Oracle 11 Documentation

Kind regards,

Marco

--
You received this message because you are subscribed to the Google Groups "WildFly" group.
To unsubscribe from this group and stop receiving emails from it, send an email to wildfly+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/wildfly/426f24a5-61ef-46fe-97d4-0f83f0593ce7n%40googlegroups.com.

[Michael Musgrove]

According to the XA specification, an XAER_RMERR response to the recover call means that "An error occurred in determining the XIDs to return".

To find out why the error occurred you would need to look at the Oracle XA trace logs - instructions on how to obtain those are at https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/xa.html#GUID-194B0A1B-1A10-47B5-B898-5CDECD7FFA9B. If you can capture those then we should be able tell you why it can't satisfy the `recover` call, the reason should be repeated on every transaction manager recovery pass which is every two minutes by default.

[Michael Musgrove]

> I wiped out all of the data subdir contents but that didn't help.

The recovery manager asks all configured resources if they have any branches belonging to our TM so you will still see that warning repeated on every pass even after wiping (I think you meant backing up and wiping). Deleting the transaction managers log isn't encouraged because the TM might be able to use it to correlate the branch with a txn log and use it to provide you with better recovery information.

> I'd like to solve the real issue but also would just like to get rid of the message as it doesn't seem to reflect a noticeable problem.

That's reasonable, txns work seamlessly the majority of the time so when things go wrong it's useful to know what the scenario is, so I would be interested to see the oracle trace logs accompanying that log message.

[George Callhoun]

Per Marco's suggestion from Oracle doc, I ran this sql but it did not fix the problem.

GRANT FORCE ANY TRANSACTION TO <user>

Waiting on Oracle DBA response for what might be in TRACE log.

Curious why previous developers might have chosen to use "XA" to connect to Oracle as Oracle doc says right at the beginning:  "Avoid using XA if possible, for these reasons...".  I'm thinking just a plain simple connection is all we need in a single server, single database setup.  XA is for something more advanced right?  Or is Wildfly married to it for some reason?  I don't see XA in the out-of-the-box standalone.xml as an example.

Another consideration in this problem is it only happens in our lower environments that were database refreshed from the production env.  Maybe that production snapshot doesn't play nicely with an existing Wildfly instance even after wiping the data dir.  As I read suggestions to "delete the transaction log"  I wonder whether that means (1) something in Wildfly or (2) the Oracle transaction log?  Could XA be complicated on the Oracle side with junk from a prior database refresh that needs to be wiped?  These are lower environments and purging an Oracle transaction log is not a big deal to us.

--

You received this message because you are subscribed to the Google Groups "WildFly" group.
To unsubscribe from this group and stop receiving emails from it, send an email to wildfly+u...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/wildfly/69f6d06d-2154-4539-b97a-6cb3d7f801f1n%40googlegroups.com.