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_POSITION
• APPLIED_HIGH_POSITION
• OLDEST_POSITION
• 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
from DBA_GG_INBOUND_PROGRESS
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:

SERVER_NAME APPLIED_LO APPLIED_HI OLDEST_POS APPLIED_LO
————— ———- ———- ———- ———-
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)
from DBA_GG_INBOUND_PROGRESS
where server_name = ‘OGG$IREPSOE’;

Which produces the following output:

SERVER_NAME SCN_TO_TIMESTAMP(APPLIED_LOW_PO SCN_TO_TIMESTAMP(OLDEST_POSITIO
————— ——————————- ——————————-
OGG$IREPSOE 23-FEB-18 09.49.45.000000000 AM 23-FEB-18 09.49.42.000000000 AM

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.

Enjoy!!

Please follow and like: