ALTER SESSION ADVISE COMMIT;
INSERT INTO PAYROLL@LONDON . . . ;
When a 2PC transaction fails, you can query the
dba_2pc_pending table to check the STATE column. You can enter
SQL*DBA and use the Recover In-Doubt Transaction dialog box to force
either a roll back or a commit of the pending transaction. If you do
this, the row will disappear from dba_2pc_pending after the
transaction has been resolved. If you force the transaction the wrong
way (for example, roll back when other nodes committed), RECO will
detect the problem, set the MIXED column to yes, and the row will
remain in the dba_2pc_pending table.
Internally, Oracle examines the init.ora parameters
to determine the rank that the commit processing will take. The
commit_point_strength init.ora parameter determines which of the
distributed databases is to be the commit point site. In a distributed
update, the database with the largest value of
commit_point_strength will be the commit point site.
Viewing pending two phase commit
transactions
The commit point site is the database
that must successfully complete before the transaction is updated at
the other databases. Conversely, if a transaction fails at the commit
point site, the entire transaction will be rolled back at all of the
other databases. In general, the commit point site should be the
database that contains the most critical data. Below is a script that
will identify a two-phase commit transaction that has failed to
complete.
Two Phase commit pending.sql reports
on any pending distributed transactions.
set pagesize 999;
set feedback off;
set wrap on;
column
local_tran_id format a22 heading 'Local Txn Id'
column
global_tran_id format a50 heading 'Global Txn Id'
column state
format a16 heading 'State'
column mixed
format a5 heading 'Mixed'
column advice
format a5 heading 'Advice'
select
local_tran_id,
global_tran_id,
state,mixed,advice
from
dba_2pc_pending
order
by local_tran_id;
Managing two phase commits
When a distributed update (or delete) has finished processing,
SQL*Net will coordinate commit processing so that if any portion of
the transaction fails, the entire transaction will roll back. The
first phase of this process is a "prepare phase" to each node,
followed by the commit, and then terminated by a "forget phase."
If a distributed update is in the process of issuing the 2PC and a
network connection breaks, Oracle will place an entry in the
dba_2pc_pending table, and the recovery background process,
reco, will roll back or commit the good node to match the state
of the disconnected node, thereby ensuring consistency.
You can activate reco via the alter system enable
distributed recovery command. The reco process is applicable
only when the distributed option is installed in Oracle, and is only
used to manage 2PCs. If your system does not perform cross-database
synchronization, you may want to disable reco.
The dba_2pc_pending table contains an "advise" column that
directs the database to either commit or rollback the pending item.
You can use the alter session advise syntax to direct the 2PC
mechanism. For example, to force the completion of an insert, you can
enter the following:
ALTER SESSION ADVISE COMMIT;
INSERT INTO PAYROLL@LONDON . . . ;
When a 2PC transaction fails, you can query the dba_2pc_pending
table to check the "state" column. You can enter SQL*DBA and use the
"recover in-doubt transaction" dialog box to force either a rollback
or a commit of the pending transaction. If you do this, the row will
disappear from dba_2pc_pending after the transaction has been
resolved.
If you force the transaction the wrong way (for example, to roll
back when other nodes have already committed), reco will detect the
problem, set the mixed column to "yes," and the row will remain in
dba_2pc_pending table.
