Open Database Transactions Effecting Oracle Golden Gate

Oracle Golden Gate Replication will not propagate transactions to the destination which have NOT been committed on the source yet. However Oracle Golden gate will write the open transaction data to the dirtemp directory in the home directory. This data will then be read back later when the transaction is committed and needs to be propagated to the destination. This is especially true when bulk transactions are run, processing a large account of the uncommitted data, which then has to be written temporarily to the dirtemp location.

In cases such as this and others you may need to investigate these open transactions which are awaiting a commit. The script below will identify the SID, SCHEMANAME, etc providing info on these open transactions. Also the USED_UBLK and UREC can be used to gauge the the amount of changes made by the statement.

Open Database Transactions effecting GoldenGate Query

-----------------------------------------------------------------------------------
-- Open Database Transactions effecting GoldenGate
------------------------------------------------------------------------------------

SET LINES 300
COL start_time FOR A20
COL sid FOR 99999
COL serial# FOR 999999
COL username FOR A20
COL status FOR A10
COL schemaname FOR A10
COL process FOR A10
COL machine FOR A15
COL program FOR A30
COL module FOR A35
COL logon_time FOR A20

SELECT t.start_time,s.sid,s.serial#,s.username,
s.status, s.schemaname, s.process,s.machine,
s.program, s.module, used_ublk, used_urec,
TO_CHAR(s.logon_time,'mon-dd-yyyy HH24:MI:SS') logon_time
FROM v$transaction t, v$session s
WHERE s.saddr = t.ses_addR
ORDER BY start_time;

Go to Goldengate for a complete list of articles on Golden gate replication.

Troubleshooting Series

• GoldenGate: Skipping Transactions
• GoldenGate: Finding Open Database Transactions
• GoldenGate: How to use handle Collisions correctly?