Issues with DTP, XA and Rollback Segments in an Oracle Database

Issues with DTP, XA and Rollback Segments in OracleOracle Database applications that connect to multiple databases via database links or that use XA (extended Architecture) for distributed transaction processing (DTP) may occasionally experience issues with pending transactions. When such issues arise, it’s not unusual to encounter extreme contention, often to the point that the database instance “hangs,” which may cause all database DML operations to halt until the problem is resolved. The following checklist and corresponding scripts will help to ascertain if an Oracle database instance is in danger of hanging due to issues with DTP via XA:

  1. Run the following query to determine if there are any pending distributed transactions and, if so, build a set of commands to force any pending distributed transactions to commit:

 

SET HEADING OFF
SELECT 'commit force '''| |local_tran_id| |''';' FROM dba_2pc_pending;
SQL>
commit force '151.23.987365';
commit force '155.29.1615583'; 
commit force '231.10.1069716'; 
commit force '237.18.648972'; 
commit force '238.15.811599'; 
commit force '36.5.1329177'; 
commit force '393.41.746115'; 
commit force '4733.28.915649'; 
commit force '613.17.686683';
9 rows selected.

This output identifies nine pending distributed transactions that must be resolved by forcing them to explicitly commit. While it’s also acceptable to roll back these transactions, it makes much better sense to commit these transactions because committing them diminishes the chance of losing any transaction data.

      2. Next, execute the commands previously generated

 

commit force '151.23.987365';

 commit force '155.29.1615583'; 

commit force '231.10.1069716'; 

commit force '237.18.648972'; 

commit force '238.15.811599'; 

commit force '36.5.1If executing this command fails to bring329177'; 

commit force '393.41.746115';

 commit force '4733.28.915649'; 

commit force '613.17.686683';

Commit complete.

Commit complete.

Commit complete.

 

  1. Run this next query to build a set of transactions to execute on this instance to purge any lost transactions from the database:

 

SELECT 'Execute

DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('''||local_tran_id||''');commit;'

FROM dba_2pc_pending;

Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('151.23.987365');commit;

Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRy('155.29.1615583');commit;

Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRy('231.10.1069716');commit;

Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRy('237.18.648972');commit;

Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRy('238.15.811599');commit;

Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRy('36.5.1329177');commit;

Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRy('393.41.746115');commit;

Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRy('4733.28.915649');commit;

Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRy('613.17.686683');commit;

9 rows selected.

Simply copy and paste the output from the previous command into a SQL*Plus session to clean up any additional pending distributed transactions. However, if the distributed transactions persist, it may be necessary to investigate and resolve one last possibility: the status of the rollback segments associated with these purged transactions may have changed from ONLINE to PARTLY AVAILABLE.

  1. The PARTLY AVAILABLE status means that the rollback segment contains data for an in-doubt transaction or a recovered distributed transaction. The database instance’s background recoverer process (RECO) should automatically resolve in-doubt or pending transactions; however, it still may be necessary for the Oracle DBA to intervene to reset the affected rollback segments to their proper status of ONLINE. Doing so may cause the database to hang if transactions other than the purged distributed transactions were using those rollback segments.

To correct this situation, run the following query to locate any rollback segments still marked as partly available:

 

 SELECT

'ALTER ROLLBACK SEGMENT "'||segment_name||'" ONLINE;'

FROM dba_rollback_segs

WHERE status LIKE 'PARTLY_AVAILABLE';

  1. If executing this command fails to bring the rollback segments back online, some additional remedial action is required. Take the rollback segment offline and then verify that its status is now OFFLINE instead of PRTLY AVAILABLEA using the following commands:

 

ALTER ROLLBACK SEGMENT "_SYSSMU168$" OFFLINE;

SELECT status

FROM dba_rollback_segs

WHERE SEGMENT_NAME = '_SYSSMU168$';

  1. Once the rollback segment is taken offline successfully, drop it and then immediately re-create it, as follows:
DROP ROLLBACK SEGMENT "_SYSSMU168$";

CREATE ROLLBACK SEGMENT '_SYSSMU168$' TABLESPACE UNDOTBS1;

This step should resolve any rollback segment-related issues, and database application user sessions should now be able to proceed without any danger of halting or causing the database instance to hang.

 

Other Unusual Rollback and Undo Segment Issues

Sometimes, a session can hang when the DBA tries to force a transaction to commit or roll back (COMMIT FORCE or ROLLBACK FORCE). In the Oracle database, we can see the session hang as it waits on the event “free global transaction table entry.” Other rare instances can occur when underlying metadata about the transaction is corrupted. In this case, it will most likely require manually deleting and re­inserting the transaction information within the appropriate internal tables. See MOS Note 401302.1, “How to resolve stranded DBA_2PC_PENDING entries,” for more information.

 

Вас заинтересует / Intresting for you:

Overview of Undo Management in...
Overview of Undo Management in... 914 views Игорь Воронов Sat, 21 Jul 2018, 15:48:31
Oracle Database and Instance d...
Oracle Database and Instance d... 506 views Masha Thu, 21 Jun 2018, 18:23:39
Oracle Database and Security: ...
Oracle Database and Security: ... 868 views sepia Mon, 26 Nov 2018, 12:20:58
Basic Oracle Database 12c Secu...
Basic Oracle Database 12c Secu... 792 views Zero Cool Sat, 06 Oct 2018, 13:53:13

Comments on Issues with DTP, XA and Rollback Segments in an Oracle Database

apv
apv You share very valuable ideas. Thank you very much for this. Solving problems with DTP, XA and Rollback Segments can be very difficult and muddled,... Show more 1 year ago
loader
loader
Attachment
Please login to comment