Identifying Integrated Replicat transactional progress

From time-to-time, you may want to find out what transactions have been applied or still in flight while Oracle GoldenGate is running while using the Integrated Replicat (IR) products. This can be done easily by using the ALL_GG_INBOUND_PROGRESS/DBA_GG_INBOUND_PROCESS views. There are a few columns of interest in this view, they are:

  • APPLIED_LOW_SCN (not applicable for GoldenGate)

Note: For more information on this view, reference Oracle Docs – here

The APPLIED_LOW_POSITION represents the commit positions less than this SCN have been applied.
The APPLIED_HIGH_POSITION is the commit position of a transaction that has been applied.
The OLDEST_POSITION is the earliest position of transactions currently being applied.
The APPLIED_LOW_SCN is the marker that represents all SCN below or equal to this number have been successfully applied; however, this column is not applicable for GoldenGate replication since the source database may be non-Oracle in nature.

To identify these columns during replication, you can use a simple query like:

select server_name, applied_low_position, applied_high_position, oldest_position, applied_low_scn
where server_name = ‘OGG$IREPSOE’;

In the above query, I’m looking at a specific replicat, called OGG$IREPSOE. This an indicator that I’m looking at an Integrated Replicat (IR). When I run the query, I get the following output:

————— ———- ———- ———- ———-
OGG$IREPSOE 6232708 6232710 6232505 0

Using the definitions above, the APPLIED_LOW_SCN column can be thrown out, since we do not use it for identifying what has been applied. Taking the next three columns into account, I can see that SCN 6232505 (OLDEST_POSITION) is the last SCN applied. Any transactions with SCN lower or equal to this SCN has been applied to the database.

The next column we need to look at is the APPLIED_LOW_POSITION. This column represents transactions that have been applied to the database as well. Any SCN below or equal to this SCN has been applied. In this case the SCN is 6232708. This SCN looks really close to the OLDEST_POSITION SCN that was just discussed; within 203 value (6232708 – 6232505). This change represents just a 3 seconds in changes. You can see this by running this query:

select server_name, scn_to_timestamp(applied_low_position), scn_to_timestamp(oldest_position)
where server_name = ‘OGG$IREPSOE’;

Which produces the following output:

————— ——————————- ——————————-

Lastly, when looking at APPLIED_HIGH_POSITION. This column represents transactions that have been applied as well; however, any SCN/transaction that is higher than this SCN has not been applied yet.

Hopefully, this helps explain how you can identify what SCNs have been applied and help identify what to look for transactions that have not been applied.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Pardy DBA

ORA-00001: unique constraint (ORA.BLOG_TAGLINE_PK) violated

Martin Widlake's Yet Another Oracle Blog

Oracle performance, Oracle statistics and VLDBs


Heli's thoughts on Database Designing, Oracle SQL Developer Data Modeler, User Groups etc.

Julian Dontcheff's Database Blog

The good DBA is one who learns from his mistakes, the best DBA is one who learns from other DBA's mistakes

Martins Blog

Trying to explain complex things in simple terms

The Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

Stuff that interests me, if not you!

Uwe Hesse

about Database Technology

%d bloggers like this: