Active Transaction's

Clearing Active Transactions in Database:

I would like to share one of my experience on trouble shooting databases. I was working on a project database migration  to the newer and faster hardware on a different OS platform. During the migration it was required to check the compatibility of database to the new platform. While running the compatibility check we hit an error.

declare

db_ready   boolean;

begin

db_Ready :=dbms_tdb.check_db ('AIX-Based Systems (64-bit)');

end;

/

“There are active transactions in the database. Open the database in READ/WRITE mode and roll back the transactions. After the active transactions are rolled back, open the database in READ-ONLY mode and retry”.

We bounced the database to see if those active transactions got cleared from the database. And reran the compatibility check, No luck still the error/transaction persists in the database. Upon research on how there could be any transaction in the undo would be active even after the database bounce. We found these transactions are distributed transactions meaning the transactions involving more than one databases.

The distributed transactions in databases should comply with ACID properties of database transaction which guarantees all-or-nothing outcomes for the unit of work. In order to complete the distributed transaction correctly an algorithm is applied i.e. two phase commit protocol (2PC).

During the execution of distributed transaction the protocol completes the transaction in two phases.

 i) Commit Request Phase: A coordinator process (where the transaction is been initiated) attempts to prepare all the participating processes (the other DB’s involved) to take necessary steps for either commit or rollback the transaction.

 ii) Commit Phase: The coordinator decides whether to commit or rollback the transaction notifies the participating transactions of the decision to take necessary action on the local resources to complete the transaction.

In our case we some have had some segments in undo tablespace holding these transactions in active state waiting for acknowledgement from the coordinator process for the decision to commit or rollback. Since the database is no longer doing any sorts of distributed transactions with any database, we decided to get rid of these transactions.

So here are the brief steps to clear such transactions. Oracle provides two views DBA_2PC_PENDING, DBA_2PC_NEIGHBORS to identify the distributed transactions.

Steps to clear the active transaction in the undo segments:

Step 1:  Run the query to check status of the active transaction

SQL> select local_tran_id, state from dba_2pc_pending;

   LOCAL_TRAN_ID          STATE
   ----------------------         ----------------
    99.20.68809                  prepared
    111.12.10265                prepared

Step 2:  Run the query to determine the active transactions in the rollback segment

SQL>   select KTUXEUSN, KTUXESLT, KTUXESQN, KTUXESTA status
        2  from x$ktuxe
        3  where ktuxesta != 'INACTIVE'
        4  and ktuxeusn IN (99, 111);

KTUXEUSN  KTUXESLT  KTUXESQN  STATUS                 
 ----------         ----------      ---------- -----------  ----- ---------          
     99                20            68809      PREPARED             
    111               12           10265      PREPARED              

Step 3: Run the force commit statements

Commit force ‘99.20.68809’;

Commit force ‘111.12.10265’;

 Commit;

Step 4:  Repeat step2 to confirm the active transactions no longer exists. If they exist then run   step 5.

  

Step 5: Run the force rollback statements

Rollback force ‘99.20.68809’;

Rollback force ‘111.12.10265’;

 

Commit;

Step 6:  Repeat step2 to confirm the active transactions no longer exists. If they exist then run   step 7.

Step 7:  If the above commit and rollback statements failed then manually clear the transactions by running the following queries.

a)  delete from sys.pending_trans$

     where local_tran_id IN (‘99.20.68809’, ‘111.12.10265’);

b)  delete from sys.pending_sessions$

      where local_tran_id IN (‘99.20.68809’, ‘111.12.10265’);

c) delete from sys.pending_sub_sessions$

      where local_tran_id IN (‘99.20.68809’, ‘111.12.10265’);    

commit;

Step 8:  Repeat step2 to confirm the deletion of active transactions.