Hello all,
I could use some advice with the following problem. Hopefully you're able to help me a bit in the right direction.
On several remote locations (each having his own unique instance name, but same configuration/applications) we keep seeing the same behavior. Each location has two instances running on the same database server. Application A is running in Instance 'PROD1' and Application B in instance 'PROD2'. There is a Database Link between 'PROD1' and 'PROD2'.
Although this setup has runned fine for years, since this year occasionaly errors keep coming up in the alert file:
"ORA-24756: transaction does not exist"
Via the trace files we see that these relate to records in the 'dba_2pc_pending' View. In this View we indeed find transactions that are executed via the Database Link. All records have the State 'collecting'. Both in Instance 'PROD1' and 'PROD2'. I could purge them via the command:
execute dbms_transaction.purge_lost_db_entry ('[LOCAL_TRAN_ID]')
That however will not solve our issue in a structural manner, as the FAIL_TIME shows that recently (actually yesterday) more of these "in-doubt" transactions occured. Currently I have no clue about why this happened. If it was a network related issue, I would not expect that it happened at completely seperated environments. There are no signs of that an Oracle process has crashed, or there was any interference. Also it concerns two local instances, running on the same database server. Don't see (yet) what can cause that a distributed transaction would fail.
It would help if I was able to identify what the statements actually where. My question is whether it is possible to use the LOCAL_TRAN_ID, or the GLOBAL_TRAN_ID, to fetch the SQL Text in some way. I don't know whether these values can be related to other available Data Dictionary Views where the SQL Text is stored? Or perhaps you have a suggestion what could be a better approach to determine what is going on. If I need to provide more information, please let me know.
FYI: we use Oracle Database 11.2 on Windows Server 2008 R2 SP1 servers.
Thanks for your replies/suggestions in advance.
Regards,
Weis