alert日志报错：

 

oracle:/opt/oracle/database/admin/ltdb/bdump>vi alert_ltdb1.log

 

Wed May  8 20:21:37 2013

Error 2068 trapped in 2PC on transaction 10.41.377599. Cleaning up.

Error stack returned to user:

ORA-02050: transaction 10.41.377599 rolled back, some remote DBs may be in-doubt

ORA-02068: following severe error from LINK_ORCL5

ORA-03113: end-of-file on communication channel

Wed May  8 20:21:37 2013

DISTRIB TRAN LTDB.c1208b62.10.41.377599

  is local tran 10.41.377599 (hex=0a.29.5c2ff)

  insert pending collecting tran, scn=5914599929 (hex=1.6089a1f9)

Wed May  8 20:21:37 2013

Errors in file /opt/oracle/database/admin/ltdb/bdump/ltdb1_reco_10426.trc:

ORA-02019: connection description for remote database not found

Wed May  8 20:21:37 2013

Errors in file /opt/oracle/database/admin/ltdb/bdump/ltdb1_reco_10426.trc:

ORA-02019: connection description for remote database not found

Wed May  8 20:21:37 2013

Errors in file /opt/oracle/database/admin/ltdb/bdump/ltdb1_reco_10426.trc:

ORA-02019: connection description for remote database not found

Wed May  8 20:21:37 2013

Errors in file /opt/oracle/database/admin/ltdb/bdump/ltdb1_reco_10426.trc:

ORA-02019: connection description for remote database not found

 

---------------------

Error:  ORA 2050
Text:   transaction rolled back, some remote DBs may be in-doubt
-------------------------------------------------------------------------------
Cause:  There was a network failure or a remote failure during the two-phase commit.
Action:  Notify the database administrator.
             The remote databases will automatically resynchronize when the failure is repaired.

原因：我把a这台服务器上的数据库迁到了其他服务器上，当时是做的fail over。在数据库b上有到a的db link, 我在做迁移时，可能b上仍有事务在访问。到a的db link失效，导致有未决事务。

 

 

Oracle 会自动处理分布事务，保证分布事务的一致性，所有站点全部提交或全部回滚。一般情况下，处理过程在很短的时间内完成，根本无法察觉到。但是，如果在commit或rollback时，出现了连接中断或某个数据库站点crash的情况，则提交操作可能会无法继续，此时DBA_2PC_PENDING和dba_2pc_neighbors中会包含尚未解决的分布事务。

 

对于绝大多数情况，当恢复连接或crash的数据库重新启动后，会自动解决分布式事务，不需要人工干预。只有分布事务锁住的对象急需被访问，锁住的回滚段阻止了其他事务的使用，网络故障或crash的数据库的恢复需要很长时间等情况出现时，才使用人工操作的方式来维护分布式事务。

**************************

解决过程：

 

1．Identify the id of the transaction

SQL> select local_tran_id, global_tran_id from dba_2pc_pending;

 

LOCAL_TRAN_ID          GLOBAL_TRAN_ID

---------------------- --------------------------------------------------------------------------------

10.41.377599           LTDB.c1208b62.10.41.377599

24.1.24483             LTDB.c1208b62.24.1.24483

127.11.3170            LTDB.c1208b62.127.11.3170

31.31.81563            LTDB.c1208b62.31.31.81563

119.14.14873           LTDB.c1208b62.119.14.14873

 

2.purge the transaction

execute dbms_transaction.purge_lost_db_entry('10.41.377599');

commit;

 

3.confirm that the transaction has been purged

 select local_tran_id, global_tran_id from dba_2pc_pending;

 

 **************************

 具体原因及解决方法参考：

$ORACLE_HOME\rdbms\admin\dbmsutil.sql

 这个问题的解决不用着急，因其不会占用数据库资源。

 可以把这些分布式未决事务清除。不再让reco进程报出告警信息

 

  可供参考的方法：

1.查询信息以确定操作方法

SQL> select state, tran_comment, advice from dba_2pc_pending

where local_tran_id = '';

 

2.提交或者回滚事务

To commit:

SQL> commit force '';

 

To rollback:

SQL> rollback force '';

 

3.以下操作只能在远程机器已shudown，不再使用时。

SQL> execute sys.dbms_transaction.purge_lost_db_entry('');

 

SQL> COMMIT;

 

 

 

 

 

附：Ora-3135 ORA-2019 ORA-2058 ORA-2068 ORA-2050: Failed Distributed Transactions [ID 1012842.102]

Oracle Server - Enterprise Edition - Version 8.1.7.4 to 11.1.0.7 [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
following:

ORA-02019: "connection description for remote database not found"
Cause: The user attempted to connect or log in to a remote
database using a connection description that could not
be found.
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
state.
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
text.
Action: Contact the remote system administrator.

ORA-02050: "transaction %s rolled back, some remote DBs may be
in-doubt"
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 commit  unknown      committed    none   purge_lost_db_entry (1)

  --   forced rollback     unknown      rolled back  none   purge_lost_db_entry (1)

  --    forced commit     mixed        committed    (2)         (mixed)

  --    forced rollback     mixed        rolled back  (2)         (mixed)

 

-- (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
-- Monitor.
-- (2): Examine and take any manual action to remove inconsistencies,
-- then use the procedure purge_mixed.

Problem References:
===================

$ORACLE_HOME/rdbms/admin/dbmsutil.sql

Before you begin, make note of the local transaction ID, , from
the error message reported.

1. Determine if you can attempt a commit or rollback of this
transaction. You can do the following select to help determine what
action to take:

SQL> select state, tran_comment, advice from dba_2pc_pending
where local_tran_id = '';

Review the TRAN_COMMENT column as this could give more information
as to the origin of the transaction or what type of transaction it was.

Review the ADVICE column as well. Many applications prescribe advice
about whether to force commit or force rollback the distributed
transaction upon failure.

2. Commit or rollback the transaction.

To commit:

SQL> commit force '';

To rollback:

SQL> rollback force '';

WARNING: Step 3 (purge_lost_db_entry) and Step 4 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.

3.execute purge_lost_db_entry from the suggestions above

SQL> execute sys.dbms_transaction.purge_lost_db_entry('');

SQL> COMMIT;

 

NOTE: You must run above procedure as sys (since package is owned by sys),
you must have execute privileges, or have DBA privileges

4. If running a release below 7.3 but were suggested to execute
purge_lost_db_entry :

Connect to Server Manager or SQL*Plus and execute the following
commands:

SQL> connect sys/
SQL> set transaction use rollback segment system;
SQL> delete from dba_2pc_pending where
local_tran_id = '';
SQL> delete from pending_sessions$ where
local_tran_id = '';
SQL> delete from pending_sub_sessions$ where
local_tran_id = '';
SQL> COMMIT;

Solution Explanation:
=====================
The above steps work ONLY after verifying that the other database
the Distributed Transaction is dependent on is no longer
available. The steps above will allow these transactions
to be deleted and no longer cause RECO to keep giving you
the error messages stated in the problem.