How to Map Timestamp to SCN or Vice Versa

  • by

If you are trying to do an incomplete recovery (also called Point-In-Time Recovery, PITR), you may want to know a System Change Number (SCN) using a TIMESTAMP as an input, or vice versa. Oracle provides two functions: timestamp_to_scn and scn_to_timestamp for DBA to map, let’s describe the two functions below:

SQL> DESC timestamp_to_scn;

FUNCTION timestamp_to_scn RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 QUERY_TIME                     TIMESTAMP               IN

SQL> DESC scn_to_timestamp;

FUNCTION scn_to_timestamp RETURNS TIMESTAMP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 QUERY_SCN                      NUMBER                  IN

For example, if you want to recover to a timestamp before an erroneous batch job began. Then, what is SCN at that time?

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> SELECT timestamp_to_scn('2011-07-28 02:00:00') SCN FROM dual;

       SCN
----------
   3056149

SQL> SELECT scn_to_timestamp(3056149) TIMESTAMP FROM dual;

TIMESTAMP
-------------------------------------
2011-07-28 01:59:57

As you can see, the real recovered timestamp will be a little early than the originally assigned one. This is coincidentally good, because we should recover a few seconds before bad things happened. But, if you want a more accurate PITR, you should choose a SCN rather than a timestamp.

Further reading: How to Check Current System Change Number (SCN)

Leave a Reply

Your email address will not be published. Required fields are marked *