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

Enjoy!

twitter: @dbasolved

blog:http//dbasolved.com

About these ads

4 comments

  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?

      Bobby

  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.

    Thanks
    Satsuah

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

      Thanks

      Bobby

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

HeliFromFinland

Heli's Oracle thoughts

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

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

PeteWhoDidNotTweet

Stuff that interests me, if not you!

The Oracle Instructor

Explain, Exemplify, Empower

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Oracle Spin - Flimatech Blog

Sharing Our Database Experience

Follow

Get every new post delivered to your Inbox.

Join 1,589 other followers

%d bloggers like this: