Last updated on FEBRUARY 06, 2017
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 to 220.127.116.11 [Release 8.1.7 to 11.1]
Information in this document applies to any platform.
It sometimes becomes necessary to cleanup failed distributed transactions.
This could be due to an error message flooding the alert log showing a local
transaction ID. The error messages may be found in the alert.log, trace files
and you may even get them on a startup of the database.
The error messages could include, but are not limited to, the
Cause: The user attempted to connect or log in to a remote
database using a connection description that could not
Action: Specify an existing database link. Query the data
dictionary to see all existing database links. See
your operating system-specific SQL*Net documentation
for valid connection descriptors.
ORA-02058: "no prepared transaction found with ID %s"
Cause: A COMMIT FORCE was attempted on a transaction, but the
transaction with LOCAL_TRAN_ID or GLOBAL_TRAN_ID was
not found in the DBA_2PC_INDOUBT table in prepared
Action: Check the DBA_2PC_INDOUBT table to ensure the proper
transaction ID is used and attempt the commit again.
ORA-02068: "following severe error from %s%s
Cause: A severe error (disconnect, fatal Oracle error) received
from the indicated database link. See following error
Action: Contact the remote system administrator.
ORA-02050: "transaction %s rolled back, some remote DBs may be
Cause: Network or remote failure in 2PC.
Action: Notify operations; remote DBs will automatically re-sync
when the failure is repaired.
The following information comes from $ORACLE_HOME\rdbms\admin\dbmsutil.sql
which describes why Distributed Transactions can sometimes get into this state
and the action needed to take care of it.
procedure purge_lost_db_entry(xid varchar2);
-- When a failure occurs during commit processing, automatic recovery will
-- consistently resolve the results at all sites involved in the
-- transaction. However, if the remote database is destroyed or
-- recreated before recovery completes, then the entries used to
-- control recovery in DBA_2PC_PENDING and associated tables will never
-- be removed, and recovery will periodically retry. Procedure
-- purge_lost_db_entry allows removal of such transactions from the
-- local site.
-- WARNING: purge_lost_db_entry should ONLY be used when the other
-- database is lost or has been recreated. Any other use may leave the
-- other database in an unrecoverable or inconsistent state.
-- Before automatic recovery runs, the transaction may show
-- up in DBA_2PC_PENDING as state "collecting", "committed", or
-- "prepared". If the DBA has forced an in-doubt transaction to have
-- a particular result by using "commit force" or "rollback force",
-- then states "forced commit" or "forced rollback" may also appear.
-- Automatic recovery will normally delete entries in any of these
-- states. The only exception is when recovery finds a forced
-- transaction which is in a state inconsistent with other sites in the
-- transaction; in this case, the entry will be left in the table
-- and the MIXED column will have a value 'yes'.
-- However, under certain conditions, it may not be possible for
-- automatic recovery to run. For example, a remote database may have
-- been permanently lost. Even if it is recreated, it will get a new
-- database id, so that recovery cannot identify it (a possible symptom
-- is ORA-02062). In this case, the DBA may use the procedure
-- purge_lost_db_entry to clean up the entries in any state other
-- than "prepared". The DBA does not need to be in any particular
-- hurry to resolve these entries, since they will not be holding any
-- database resources.
-- The following table indicates what the various states indicate about
-- the transaction and what the DBA actions should be:
-- State State of State of Normal Alternative
-- Column Global Local DBA DBA
-- Transaction Transaction Action Action
-- ---------- ------------ ------------ ------ ---------------
-- collecting rolled back rolled back none purge_lost_db_entry (1)
-- committed committed committed none purge_lost_db_entry (1)
-- prepared unknown prepared none force commit or rollback
-- forced unknown committed none purge_lost_db_entry (1)
-- forced unknown rolled back none purge_lost_db_entry (1)
-- forced mixed committed (2)
-- forced mixed rolled back (2)
-- (1): Use only if significant reconfiguration has occurred so that
-- automatic recovery cannot resolve the transaction. Examples are
-- total loss of the remote database, reconfiguration in software
-- resulting in loss of two-phase commit capability, or loss of
-- information from an external transaction coordinator such as a TP
-- (2): Examine and take any manual action to remove inconsistencies,
-- then use the procedure purge_mixed.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms