If you will get an error like this "locked held by distributed in doubt transactions for some transaction id". Please follow the following steps to recover from locking.
- Try to find out the locked transaction id from the server error logs.
- Execute select * from dba_2pc_pending in DB console. You should be able to see the particular locked transaction Id under "local_tran_id" column, and "state" for this should be PREPARED in dba_2pc_pending table.
- Login to the database as sysdba. Execute the following command - sqlplus './as sysdba' rollback force "locked transaction id" Now if you will check dba_2pc_pending table again, the "state" should be changed to FORCED ROLLBACK for the transaction id.
- Now you should not get the above error any more. You can also execute commit force "locked transaction id" to avoid this error.