What DB priveleges are needed by Arjuna Recovery Manager in JBOSS7.1.1

[ion_mayank]

I am constantly getting following error by recovery manager in JBOSS 7.1.1

11:24:30,821 INFO  [com.arjuna.ats.jta] ARJUNA016013: Rolling back < formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffffc0a82dca:22c9bd23:502e2834:2472, node_name=1, branch_uid=0:ffffc0a82dca:22c9bd23:502e2834:247a, subordinatenodename=null, eis_name=java:/com/xyz/datasources/XADataSource >

11:24:30,825 ERROR [stderr] oracle.jdbc.xa.OracleXAException

11:24:30,827 ERROR [stderr]     at oracle.jdbc.xa.OracleXAResource.checkError(OracleXAResource.java:1050)

11:24:30,829 ERROR [stderr]     at oracle.jdbc.xa.client.OracleXAResource.rollback(OracleXAResource.java:937)

11:24:30,831 ERROR [stderr]     at org.jboss.jca.adapters.jdbc.xa.XAManagedConnection.rollback(XAManagedConnection.java:342)

11:24:30,832 ERROR [stderr]     at org.jboss.jca.core.tx.jbossts.XAResourceWrapperImpl.rollback(XAResourceWrapperImpl.java:171)

11:24:30,834 ERROR [stderr]     at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.handleOrphan(XARecoveryModule.java:706)

11:24:30,836 ERROR [stderr]     at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.xaRecovery(XARecoveryModule.java:612)

11:24:30,837 ERROR [stderr]     at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.resourceInitiatedRecoveryForRecoveryHelpers(XARecoveryModule.java:471)

11:24:30,839 ERROR [stderr]     at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.bottomUpRecovery(XARecoveryModule.java:385)

11:24:30,841 ERROR [stderr]     at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkSecondPass(XARecoveryModule.java:166)

11:24:30,843 ERROR [stderr]     at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:789)

11:24:30,845 ERROR [stderr]     at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:371)

11:24:30,846 ERROR [stderr] Caused by: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges

11:24:30,848 ERROR [stderr]

11:24:30,849 ERROR [stderr]     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)

11:24:30,850 ERROR [stderr]     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:388)

11:24:30,851 ERROR [stderr]     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:381)

11:24:30,852 ERROR [stderr]     at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:564)

11:24:30,853 ERROR [stderr]     at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)

11:24:30,854 ERROR [stderr]     at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)

11:24:30,856 ERROR [stderr]     at oracle.jdbc.driver.T4CTTIOtxen.doOTXEN(T4CTTIOtxen.java:168)

11:24:30,857 ERROR [stderr]     at oracle.jdbc.driver.T4CXAResource.doTransaction(T4CXAResource.java:746)

11:24:30,858 ERROR [stderr]     at oracle.jdbc.driver.T4CXAResource.kputxrec(T4CXAResource.java:809)

11:24:30,859 ERROR [stderr]     at oracle.jdbc.driver.T4CXAResource.doRollback(T4CXAResource.java:645)

11:24:30,860 ERROR [stderr]     at oracle.jdbc.xa.client.OracleXAResource.rollback(OracleXAResource.java:932)

11:24:30,861 ERROR [stderr]     ... 9 more

I have given the following DB priveleges to my Oracle user to enable XARecovery in Oracle 10g.

GRANT SELECT ON sys.dba_pending_transactions TO db_user;

GRANT SELECT ON sys.pending_trans$ TO db_user;

GRANT SELECT ON sys.dba_2pc_pending TO db_user;

GRANT EXECUTE ON sys.dbms_xa TO db_user; 

GRANT EXECUTE ON sys.dbms_system TO db_user;

GRANT EXECUTE ON dbms_system TO db_user;

Still I am getting insufficient priveleges error from Oracle when Recovery Manager is trying to rollback.

[tomjenkinson]

Hi,

I assume db_user is the user you have configured for the xa-datasource in standalone.xml?

Tom

[ion_mayank]

Hi,

No. db_user is just the placeholder. Before running the SQL commands I have replaced them with my actual DB user in database.

I was just wondering which DB credentials Arjuna is using to do the rollback. Could it be that the recovery manager is using some default annoynomous credentials

for which it is crying for priveleges.

Another thing I can see from logs is that it is calling handleOrphan() method which could be because it is not able to find any entry in ObjectStore although Oracle DB has entries in sys.dba_pending_transactions

table for recovery.

This situation could arise if someone clears the ObjectStore manually from the disk but DB has entries in the table. But even after that privelege exception should not come.

My Oracle DB table has following entries:

sys.dba_pending_transactions

"FORMATID"	"GLOBALID"	"BRANCHID"
131077	00000000000000000000FFFFC0A82DCA22C9BD23502E28340000247231	00000000000000000000FFFFC0A82DCA22C9BD23502E28340000247A0000000100000000

select * from sys.pending_trans$

"LOCAL_TRAN_ID"

"GLOBAL_TRAN_FMT"

"GLOBAL_ORACLE_ID"

"GLOBAL_FOREIGN_ID"

"TRAN_COMMENT"

"STATE"

"STATUS"

"HEURISTIC_DFLT"

"SESSION_VECTOR"

"RECO_VECTOR"

"TYPE#"

"FAIL_TIME"

"HEURISTIC_TIME"

"RECO_TIME"

"TOP_DB_USER"

"TOP_OS_USER"

"TOP_OS_HOST"

"TOP_OS_TERMINAL"

"GLOBAL_COMMIT#"

"SPARE1"

"SPARE2"

"SPARE3"

"SPARE4"

"8.20.23482"

131077

""

00000000000000000000FFFFC0A82DCA22C9BD23502E28340000247231

""

"prepared"

"P"

"

select * from sys.dba_2pc_pending

"LOCAL_TRAN_ID"

"GLOBAL_TRAN_ID"

"STATE"

"MIXED"

"ADVICE"

"TRAN_COMMENT"

"FAIL_TIME"

"FORCE_TIME"

"RETRY_TIME"

"OS_USER"

"OS_TERMINAL"

"HOST"

"DB_USER"

"COMMIT#"

"8.20.23482"

"131077.00000000000000000000FFFFC0A82DCA22C9BD23502E28340000247231"

"prepared"

"no"

"

Regards,

Mayank

[tomjenkinson]

Hi Mayank, Arjuna is using the xa-datasource configured in the standalone.xml.

You can tell it is using a JCA provided datasource from the top of the stack trace:

11:24:30,825 ERROR [stderr] oracle.jdbc.xa.OracleXAException

11:24:30,827 ERROR [stderr]     at oracle.jdbc.xa.OracleXAResource.checkError(OracleXAResource.java:1050)

11:24:30,829 ERROR [stderr]     at oracle.jdbc.xa.client.OracleXAResource.rollback(OracleXAResource.java:937)

11:24:30,831 ERROR [stderr]     at org.jboss.jca.adapters.jdbc.xa.XAManagedConnection.rollback(XAManagedConnection.java:342)

11:24:30,832 ERROR [stderr]     at org.jboss.jca.core.tx.jbossts.XAResourceWrapperImpl.rollback(XAResourceWrapperImpl.java:171)

11:24:30,834 ERROR [stderr]     at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.handleOrphan(XARecoveryModule.java:706)

> This situation could arise if someone clears the ObjectStore manually from the disk but DB has entries in the table. But even after that privelege exception should not come.

+1 on both counts, what appears to have happened is the database was prepared but Arjuna crashed before it could write its log (or the object store was deleted). As you say this is expected and priviledge issues should not be related.

It is also reassuring that our branch ID logged in the stack trace "branch_uid=0:ffffc0a82dca:22c9bd23:502e2834:247a" matches that in the sys.dba_pending_transactions "00000000000000000000FFFFC0A82DCA22C9BD23502E28340000247A0000000100000000"

I can't work out why the GLOBALID in sys.dba_pending_transactions seems a little different: "00000000000000000000FFFFC0A82DCA22C9BD23502E28340000247231" compared to  "tx_uid=0:ffffc0a82dca:22c9bd23:502e2834:2472". Perhaps this is just a rendering issue. As you say, I would think that insufficient privilidges is not related.

Take a look at: https://access.redhat.com/knowledge/docs/en-US/JBoss_Enterprise_Application_Platform/5/html/Transactions_Administrators_Guide/ch07s04.html#id2137017

Which version of oracle are you using? Also if you login using the sqlplus (is that the CLI?) with the user can you manipulate date in those tables?

Also, you seem to have given more privilidges that we would recommend, maybe there is some safety protection in oracle?

Also I looked on the internet for that error Oracle message and got links to file system level permission issues rather than oracle privs.

I assume you didn't configure anything except the XA datasource in standalone.xml? What is your second XA resource?

Tom

[ion_mayank]

Hi Tom,

Thanks for the detailed reply.

Exact condition was --> Object store was manually deleted and Oracle transaction tables have the entries as mentioned before.

* I am able to run select queries from my XA-DataSource user from SQLPLus.

* I have removed the additional sys.dbms_system EXECUTE privilege from my user.

* My Oracle version is 10.2.0.5.0

* Only 1 XA datasource is configured. Apart from it hornet-ra adapter for JMS connection for MDB and direct JMS connection factories are also XA resources.

Even after this same exception comes after starting the server.

Insert the following as sysdba and start JBOSS7 server same exception is reproducible for a XA resource pointing to this DB user.

Insert into sys.pending_trans$ (LOCAL_TRAN_ID,GLOBAL_TRAN_FMT,GLOBAL_ORACLE_ID,GLOBAL_FOREIGN_ID,TRAN_COMMENT,STATE,STATUS,HEURISTIC_DFLT,SESSION_VECTOR,RECO_VECTOR,TYPE#,FAIL_TIME,HEURISTIC_TIME,RECO_TIME,TOP_DB_USER,TOP_OS_USER,TOP_OS_HOST,TOP_OS_TERMINAL,GLOBAL_COMMIT#,SPARE1,SPARE2,SPARE3,SPARE4) values ('8.20.23482',131077,null,'00000000000000000000FFFFC0A82DCA22C9BD23502E28340000247231',null,'prepared','P','','00000002','00000000',0,to_timestamp('17-AUG-12 16:56:43','DD-MON-RR HH24.MI.SSXFF'),null,to_timestamp('20-AUG-12 04:48:35','DD-MON-RR HH24.MI.SSXFF'),null,'Gupta.Mayank','mymachine-W7','unknown','64104005',null,null,null,null);

-Mayank

[ion_mayank]

This exception keep on coming whenever PerodicRecoveryManager runs. Only way to stop is to delete the entry from sys.pending_trans$  table.

[tomjenkinson]

Hi Mayank, as you say, this will occur repeatedly with the PeriodicRecovery thread until the underlying cause is known. Do the oracle .trc files (client/server) give more detail on what is the underlying security priviledge being violated?

Tom

[tomjenkinson]

Also, to be explicity. The user "db_user" (as replaced) is the same one you have configured in the standalone.xml as the one you are using sqlplus with?

Also, can you delete the row from sys.pending_trans$ as the db user. So far you said you can select from the table, did you try to remove?

[ion_mayank]

Hi Tom,

There is no helpful log in .trc files. I will need to install EM to check Oracle logs for error violation.

Delete privilege is not GRANTED to my XA resource user. OnlySelect is granted and that is executing fine.

I have double checked my db_user entry in SQLPlus and Standalone.xml and both are same.

Thanks,

Mayank

[tomjenkinson]

I guess the deletion from that table is performed by some function of a cascade on sys.dbms_xa.

Can you clarify you are using Oracle 10g R2 (patched for bug 5945463) - I don't happen to know if 10.2.0.5.0 has that patch in it?

[ion_mayank]

Hi Tom,

Yes you are correct, deletion must be performed by sys.dbms_xa package.

As far as bug number is concerned Oracle website says "The patch number for 10.2.0.5 is 8202632" which must be released after 5945463.

https://forums.oracle.com/forums/thread.jspa?messageID=9805416

Thanks,

Mayank

[tomjenkinson]

Please can you paste in your xa-datasource configuration?

[ion_mayank]

               <xa-datasource jndi-name="java:/com/xyz/datasources/XADataSource" pool-name="Tx_Pool" enabled="true" use-java-context="true" use-ccm="true">

                    <xa-datasource-property name="URL">

                        jdbc:oracle:thin:@localhost:1521:ORCLDB10

                    </xa-datasource-property>

                    <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>

                    <driver>oracle</driver>

                    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                    <xa-pool>

                        <max-pool-size>40</max-pool-size>

                        <is-same-rm-override>false</is-same-rm-override>

                        <interleaving>true</interleaving>

                        <no-tx-separate-pools>true</no-tx-separate-pools>

                    </xa-pool>

                    <security>

                        <security-domain>DSDbRealm</security-domain>

                    </security>

                    <validation>

                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>

                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>

                    </validation>

                </xa-datasource>

and the referring security domain configuration is:

               <security-domain name="DSDbRealm" cache-type="default">

                    <authentication>

                        <login-module code="SecureIdentity" flag="required">

                            <module-option name="username" value="MAYANKG600"/>

                            <module-option name="password" value="-4af99cbc35f3adb65adfa9c3a34d9aac"/>

                        </login-module>

                    </authentication>

                </security-domain>

Thanks,

Mayank

[hillol.pal]

Hi mayank.

  what steps were taken for the resolution of your problem.

  I am having similar issue.

  Appreciate your help.

BR
Rony

[tomjenkinson]

Hi Rony,

I suggest you create a new thread. Please include version numbers and configuration of all the components you are using.

Thanks,

Tom