SCN to Timestamp and back

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 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.


twitter: @dbasolved



  1. satsuah · · Reply

    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.
    Any suggestions?

    1. Have you looked at using heartbeat tables?


  2. satsuah · · Reply

    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.


    1. I have written some scripts that watch the GG processes at the OS level.



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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: