Multiple locations
experiencing issue connecting to because
of pending transactions:
Step 1)
SQL> select
local_tran_id, global_tran_id, state,mixed, host, commit# from dba_2pc_pending;
LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX HOST COMMIT#
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2500.16.262202
1145324612.AD0A41E9A41CB544A01235C40376167A00000000 prepared no
ADM\DAA3 192752694778
step 2)
Find which transactions
in "prepared" state and also in "forced rollback" state.
-- For
"Prepared" state transaction:
-- Rollback the
transactions using rollback force:
SQL> rollback force
'<local_tran_id>';
SQL> ROLLBACK FORCE
'2500.16.262202';
step 3)
Check again for state,
it should be forced rollback.
SQL> select
local_tran_id, global_tran_id, state,mixed, host, commit# from dba_2pc_pending
where local_tran_id='2500.16.262202';
LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX HOST COMMIT#
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2500.16.262202
1145324612.AD0A41E9A41CB544A01235C40376167A00000000 forced rollback no
ADM\ADMROCW3 192752694778
step 4)
For transactions in
"forced rollback" state: (Need confirmation)
SQL> alter session
set "_smu_debug_mode" = 4;
SQL> execute
DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('<local_tran_id>');
SQL> commit;
Purging Pending Rows
from the Data Dictionary
Before RECO recovers an
in-doubt transaction, the transaction appears in DBA_2PC_PENDING.STATE as
COLLECTING, COMMITTED, or PREPARED. If
you force an in-doubt transaction using COMMIT FORCE or ROLLBACK FORCE, then
the states FORCED COMMIT or FORCED ROLLBACK may appear.
Automatic recovery
normally deletes entries in these states. The only exception is when recovery
discovers a forced transaction that is in a state inconsistent with other sites
in the transaction. In this case, the entry can be left in the table and the
MIXED column in DBA_2PC_PENDING has a value of YES. These entries can be cleaned
up with the DBMS_TRANSACTION.PURGE_MIXED procedure.
If automatic recovery
is not possible because a remote database has been permanently lost, then
recovery cannot identify the re-created database because it receives a new
database ID when it is re-created. In this case, you must use the
PURGE_LOST_DB_ENTRY procedure in the DBMS_TRANSACTION package to clean up the
entries. The entries do not hold up database resources, so there is no urgency
in cleaning them up.
Manually Overriding
In-Doubt Transactions
Use the COMMIT or
ROLLBACK statement with the FORCE option and a text string that indicates
either the local or global transaction ID of the in-doubt transaction to
commit.
Note:
In all examples, the
transaction is committed or rolled back on the local node, and the local
pending transaction table records a value of forced commit or forced termination
for the STATE column of this transaction's row.
This section contains
the following topics:
1. Manually
Committing an In-Doubt Transaction
2. Manually
Rolling Back an In-Doubt Transaction
1. Manually
Committing an In-Doubt Transaction
Before attempting to
commit the transaction, ensure that you have the proper privileges. Note the
following requirements:
If the transaction was
committed by...
Then you must have this privilege...
You
FORCE TRANSACTION
Another user
FORCE ANY TRANSACTION
Committing Using Only
the Transaction ID
The following SQL
statement commits an in-doubt transaction:
COMMIT FORCE
'transaction_id';
The variable
transaction_id is the identifier of the transaction as specified in either the
LOCAL_TRAN_ID or GLOBAL_TRAN_ID columns of the DBA_2PC_PENDING data dictionary
view.
For example, assume
that you query DBA_2PC_PENDING and determine that LOCAL_TRAN_ID for a
distributed transaction is 1:45.13.
You then issue the
following SQL statement to force the commit of this in-doubt transaction:
COMMIT FORCE '1.45.13';
Committing Using an
SCN
Optionally, you can
specify the SCN for the transaction when forcing a transaction to commit. This
feature allows you to commit an in-doubt transaction with the SCN assigned when
it was committed at other nodes.
Consequently, you
maintain the synchronized commit time of the distributed transaction even if
there is a failure. Specify an SCN only when you can determine the SCN of the
same transaction already committed at another node.
For example, assume you
want to manually commit a transaction with the following global transaction ID:
SALES.ABCM.COM.55d1c563.1.93.29
First, query the
DBA_2PC_PENDING view of a remote database also involved with the transaction in
question.
Note the SCN used for the commit of the
transaction at that node. Specify the SCN when committing the transaction at the
local node. For example, if the SCN is 829381993, issue:
COMMIT FORCE 'SALES.ABCM.COM.55d1c563.1.93.29',
829381993;
2. Manually
Rolling Back an In-Doubt Transaction
Before attempting to
roll back the in-doubt distributed transaction, ensure that you have the proper
privileges.
Note the following requirements:
If the transaction was
committed by...
Then you must have this privilege...
You
FORCE TRANSACTION
Another user
FORCE ANY TRANSACTION
The following SQL
statement rolls back an in-doubt transaction:
ROLLBACK FORCE
'transaction_id';
The variable
transaction_id is the identifier of the transaction as specified in either the
LOCAL_TRAN_ID
or GLOBAL_TRAN_ID columns of the
DBA_2PC_PENDING data dictionary view.
For example, to roll
back the in-doubt transaction with the local transaction ID of 2.9.4, use the
following statement:
ROLLBACK FORCE '2.9.4';
Note:
You cannot roll back an
in-doubt transaction to a savepoint.
Below are the tables to
find the pending transactions:
select * DBA_2PC_PENDING;
select * from
DBA_2PC_NEIGHBORS;
select * from
sys.pending_trans$;
select * from
SYS.PENDING_SESSIONS$;
select * from
SYS.PENDING_SUB_SESSIONS$;
