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
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);
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
---------- ---------- ---------- ----------- ----- ---------
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.
