When working with Oracle GoldenGate, understanding the System Change Number (SCN) is important. The SCN is an internal number maintained by the database that keeps track of the changes made to the database for recovery purposes. The SCN is also important when working with Oracle GoldenGate. In many environments, instantiation of Oracle GoldenGate environments require knowing where to start the replicat from.
To find the current SCN for the database, Oracle has made this pretty easy. There is a column in the V$DATABASE view called CURRENT_SCN.
select current_scn from v$database;
Now that the current SCN has been found, it can be used in an data pump export parameter file using FLASHBACK_SCN to ensure a consistent copy of the database at that point-in-time. Once an import is completed on the target side of GoldenGate the replicat can be started using the SCN with the ATCSN or AFTERCSN option.
What does the SCN really mean to an Oracle GoldenGate Admin though? As outlined above it really is just a point-in-time place holder to key admins in on a place to start the replicat. At times there maybe need to start the replicat from an adjusted point-in-time. In order to do this, it is handy to know how to convert the SCN to a Timestamp and back to SCN. In order to do this, Oracle has provided two packages called SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN.
Using these packages is pretty simple. The following code blocks demonstrates how to convert from SCN to Timestamp and back.
Convert SCN to Timestamp:
select scn_to_timestamp(8697520) from dual;
Convert Timestamp to SCN:
select timestamp_to_scn('10-JUN-14 10.50.55.000000000 AM') from dual;
Knowing how to convert the SCN to Timestamp and back to SCN can be very handy in many different situations and useful when working with Oracle GoldenGate.
Current Oracle Certs
I’m Bobby Curtis and I’m just your normal average guy who has been working in the technology field for awhile (started when I was 18 with the US Army). The goal of this blog has changed a bit over the years. Initially, it was a general blog where I wrote thoughts down. Then it changed to focus on the Oracle Database, Oracle Enterprise Manager, and eventually Oracle GoldenGate.
If you want to follow me on a more timely manner, I can be followed on twitter at @dbasolved or on LinkedIn under “Bobby Curtis MBA”.
Is there anyway to monitor the exact golden gate lag in 11.2 or 12.1 through SCN.
Also send ,getlag or lag don’t exactly reflect the current lag (last recorded lag only) in time.
Have you looked at using heartbeat tables?
Thanks for the reply.
Yes, I have looked at heartbeat tables and they provide lag information with good accuracy.
To be more precise , is there any way for monitoring GG at individual process/component (extract/replicat/pump) level.
I have written some scripts that watch the GG processes at the OS level.