Re: getting rid of distributed transaction

daud11_at_hotmail.com (Daud) wrote in message news:<f0bf3cc3.0212032125.2bba1ba6_at_posting.google.com>...
> Oracle 8.1.7.3 on HP-UX 11.00
>
> I saw this error message below and I am not sure how to solve it.
>
> Error 28500 trapped in 2PC on transaction 11.54.3771. Cleaning up.
> Error stack returned to user:
> ORA-02054: transaction 11.54.3771 in-doubt
> ORA-28500: connection from ORACLE to a non-Oracle system returned this
> message:
> [Transparent gateway for RDB]trying all tdps, ignoring failure
> ORA-02063: preceding 2 lines from WTMTAIRW
>
> I checked in pending_trans$ and saw the above transaction.
> How can I find the sid and serial# of the session that created the
> above transaction and possibly the dedicated oracle server process
> processid?
>
> I did - rollback force '11.54.3771'; but this did not do anything
> after 2 hours.
> The status in pending_trans$ shows 'Prepared'. Right now that
> transaction is holding some kind of lock on one table and I am not
> even able to select all records from the table. Any advice?
>
> I have encountered a similar problem before with a dblink to another
> oracle database. That problem happened when the WAN went down. I found
> that I had to kill the dedicated server process to remove the lock on
> a table. Killing the session didn't do anything.
>
> Thanks
> Daud

Daud, is the remote database available? If not, can you make it available so Oracle can resolve the transaction for you. Are there any messages in the alert log related to this transaction?

Clearing of indoubt distributed transactions is covered in the manuals. See the dbms_transaction package documentation.

example DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('11.54.3771');

HTH and good luck -- Mark D Powell -- Received on Wed Dec 04 2002 - 08:40:26 CST