• Database Blog
  • GoldenGate 12c
    • Top 50 Oracle Goldengate Interview Questions To Get the Job!
  • Master Oracle 12c DBA
  • Oracle Tutorials
    • Oracle Tips & Tricks
    • Oracle VirtualBox
  • Learn Excel

VitalSoftTech

Database Administration | Oracle | Oracle Training | DBA Support | Oracle 12c DBA | GoldenGate | GoldenGate 12c | DBA Interview Questions | Goldengate Interview Questions | Natik Ameen

  • Digital Marketing
  • Blogging Tutorial and Tips
  • Cool Names
  •   Login
« Previous Post
Next Post »

Open Database Transactions Effecting Oracle Golden Gate

May 1, 2014 by Natik Ameen   /  Goldengate

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?

Filed Under: Goldengate Tagged With: Goldengate

Comments

  1. rajuts@gmail.com'Tharimana S Raju says

    June 15, 2016 at 2:04 pm

    Hi Mr. Natik Ameen,
    I first refer your writings on blog about GoldenGate before searching anywhere. Thank you so much. I am wondering how GoldenGate handles Source Updates into Hadoop HDFS. I mean, what mechanism or process GoldenGate uses to update (not Append) source changed records on HDFS? Thanks in advance.

    Reply
  2. dhruwmishra@gmail.com'Dhruv says

    November 26, 2017 at 1:29 am

    Hi,
    The article is definately helpful.
    A question that I have is if there is any way to identify what transaction/statements the replicat is processing ?
    The GG version we are using is 12.1.2

    Reply

Leave a Reply Cancel reply

Your email address will not be published.

« Previous Post
Next Post »

Join over 3,000 others My posts. Your Inbox. Beautiful.

Database    GoldenGate

Natik Ameen

About Natik Ameen

Natik Ameen is an Oracle Production DBA, Oracle Certified RAC Expert and a DBA track Certification trainer for over 17 years. He is an Oracle Evangelist and has presented at IOUG & UTOUG conferences. He writes on topics such as Database Administration, RAC, GoldenGate and the Cloud.

Who is Natik Ameen?

Guides

  • My GoldenGate Tutorials
  • Oracle 12c Database Tutorials
  • GoldenGate GGSCI Command Series
  • My OraTips and Tricks

Popular Blogs

  • My GoldenGate Home Page
  • Top 50 Oracle Goldengate Interview Questions To Get the Job!
  • Top 30 RAC Interview Questions That Helped Me. Are You Prepared?
  • Oracle GoldenGate Concepts and Architecture Made Simple!
  • Virtualize Oracle RAC 12c on My Laptop
  • Step by Step Oracle 12c Database Install on Virtual Box

Popular Oracle Articles

Oracle Announces Beta Availability of Oracle Database 12c Release 2
Top 5 Announcements at Oracle Open World
News on Oracle Database STANDARD EDITION 12.1.0.2

Technology Centers

Oracle Business Intelligence and DW
Cloud Computing, SOA, Virtualization
Big data, Java, Linux, PLSQL, Security
Enterprise Architecture, Digital Experience

Oracle Documentation

10g R1, 10g R2, 11g R1, 11g R2, 12c R1
Oracle CPU's, EM 11g, EM 12c
GoldenGate 12c, Oracle VM, VirtualBox

Copyright ©2021

BLOGOracle Tutorials