Oracle 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:
- 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.
- 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
.
- 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 ofONLINE
. 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';
- 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 ofPRTLY AVAILABLEA
using the following commands:
ALTER ROLLBACK SEGMENT "_SYSSMU168$" OFFLINE;
SELECT status
FROM dba_rollback_segs
WHERE SEGMENT_NAME = '_SYSSMU168$';
- 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 reinserting the transaction information within the appropriate internal tables. See MOS Note 401302.1, “How to resolve stranded DBA_2PC_PENDING
entries,” for more information.