Dealing with Oracle's distributed transaction failures

The last two days the production database has been reported ORA-24756 error, check the MoS document, see a similar, said is a bug (bug 19201866-reco reports ORA-24756 repeatedly into trace file ( Document ID 19201866.8), but the 11.2.0.4 on HP-UX does not resolve the need to upgrade to version 12.2, and the documentation says restarting the instance can also be resolved, but not realistic.

So the trace file when checking error is the following content

2017-02-04 09:17:56.058error, tran=42.13.2709343, ose=0:ora-24756: ...

See tran=42.13.2709343, the impression is distributed transactions, so look dba_2pc_pendings view

[Email protected]> col local_tran_id format a13[email protected]> col  global_tran_id format a90[email protected]> col state format a10[ Email protected]> col mixed format a3[email protected]> col host  FORMAT A10[email protected]> COL COMMIT# FORMAT A15[email  protected]> set linesize 240[email protected]> select local_tran_id,  Global_tran_id, state,fail_time,force_time,retry_time, mixed, host, commit#  2      from dba_2pc_pending  3  /local_tran_id global_ tran_id                                                                                 STATE      FAIL_TIME          FORCE_TIME        RETRY_TIME         mix host       commit#------------- ----------------- ------------------------------------------------------------------------- ---------- --------------- -- ----------------- ----------------- --- ---------- ---------------42.13.2709343  1096044365.31302E3235352E3233322E32332E746D313438363137313036383638333230333633             collecting 20170204 09:17:55                    20170206 01:12:41 no   bosbpm4s   764631398601

The

sees the TRAN in the local_tran_id and trace files consistent, and the first idea is correct. Where Fail_time is 20170204 09:17:55 corresponding to the background of the alert log, see the error message before and after this time

sat feb 04 09:17:50 2017error 22 trapped  in 2pc on transaction 42.13.2709343. cleaning up. Error stack returned to user:ora-02050: transaction 42.13.2709343 rolled  back, some remote dbs may be in-doubtora-00022: invalid session  ID; access deniedORA-02063: preceding line from LINK_DB2Sat Feb  04 09:17:56 2017distrib tran  41544f4d.31302e3235352e3233322e32332e746d313438363137313036383638333230333633  is local  tran 42.13.2709343  (hex=2a.0d.29575f)   insert pending collecting tran,  scn=764631398601  (HEX=B2.079538C9) sat feb 04 09:17:56 2017errors in  file /oracle11g/app/oracle/diag/rdbms/db1/db1/trace/db1_reco_23402.trc:ora-24756: 

This distributed transaction failure may lock the data and cause other transaction ORA-01591 to report an error or keep the undo segment from being reused by other transactions. This kind of failure needs to be handled manually.

[Email protected]> EXECUTE dbms_transaction. Purge_lost_db_entry (' 42.13.2709343 ');P L/sql procedure successfully completed. [Email protected]> commit; Commit complete. [Email protected]> SELECT local_tran_id, global_tran_id, State,fail_time,force_time,retry_time, MIXED, HOST, COMMIT # 2 from dba_2pc_pending 3/no rows selected

The cleanup is complete.

Official documentation for managing distributed transactions: http://docs.oracle.com/cd/E11882_01/server.112/e25494/ds_txnman.htm#ADMIN12252

MOS document: How toPurge a distributed Transaction from a Database (document ID 159377.1) ORA-30019 when executing dbms_t Ransaction. Purge_lost_db_entry (document ID 290405.1)

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1895371

Dealing with Oracle's distributed transaction failures