SMON_SCN_TIME and ORA-8161? Digging deeper

In the recent versions of the Oracle database, we’ve had the ability to convert between a System Change Number (SCN) and the approximate time to which that SCN pertains. These functions are unsurprisingly called SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN. The only potential misnomer here is that even though the functions are called “timestamp” and return a datatype of timestamp, on most platforms you are going to notice that the granularity doesn’t run down into fractions of seconds


SQL> select scn_to_timestamp(14816563713652) from dual;

SCN_TO_TIMESTAMP(14816563713652)
---------------------------------------------------------
08-JUN-19 02.30.59.000000000 AM

This all looks great until you start poking around too far into the past, and you end up in territory like this:


SQL> select scn_to_timestamp(14816563693489) from dual;
select scn_to_timestamp(14816563693489) from dual
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

If I poke around in V$LOG_HISTORY, then I can pretty easily confirm that SCN 14816563693489 was indeed a valid SCN for this database at some stage in the past, so the fact that we can encounter ORA-08181 suggests that there is a finite structure that holds the mapping between SCNs and the time at which those SCNs pertain to. And indeed there is. The table is called SYS.SMON_SCN_TIME


SQL> desc sys.smon_scn_time
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------
 THREAD                                             NUMBER
 TIME_MP                                            NUMBER
 TIME_DP                                            DATE
 SCN_WRP                                            NUMBER
 SCN_BAS                                            NUMBER
 NUM_MAPPINGS                                       NUMBER
 TIM_SCN_MAP                                        RAW(1200)
 SCN                                                NUMBER
 ORIG_THREAD                                        NUMBER

and in most instances, it will hold around a week’s worth of SCN to timestamp mapping information.


SQL> select min(time_dp), max(time_dp) from sys.smon_scn_time;

MIN(TIME_ MAX(TIME_
--------- ---------
07-JUN-19 13-JUN-19

SQL> select count(*) from  sys.smon_scn_time;

  COUNT(*)
----------
      1603

When SMON_SCN_TIME first came into existence, the granularity of data in this table was an entry approximately every 5 minutes. Hence you could map an SCN to, at best, a 5 minute window. In more recent releases, this has been improved to a granularity of 3 seconds. But the table does not appear to reflect that evolution if you look at some sample data.


SQL> select scn, time_dp
  2  from sys.smon_scn_time s
  3  where rownum <= 10;

               SCN TIME_DP
------------------ -------------------
    14816565366476 11/06/2019 08:25:11
    14816565366679 11/06/2019 08:30:38
    14816565366808 11/06/2019 08:35:11
    14816565367164 11/06/2019 08:40:44
    14816565367291 11/06/2019 08:45:12
    14816565367475 11/06/2019 08:50:32
    14816565029366 10/06/2019 01:28:13
    14816565029605 10/06/2019 01:33:40
    14816565032515 10/06/2019 01:38:13
    14816565032779 10/06/2019 01:43:40

It still has a row every 5 minutes to handle backward compatibility, and to drill down to the 3 second level, you are expected to use the functions SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN already mentioned.

But what if you wanted to store this SCN to timestamp mapping for longer? Activating flashback data archive is one way of achieving this, but what if you just wanted to store the mapping forever without doing this? At first, it seems to be a simple task. I can simply get the lowest SCN from SMON_SCN_TIME:


SQL> select min(scn) scn from   sys.SMON_SCN_TIME;

               SCN
------------------
    14816563714099
    

and then use a familiar CONNECT BY technique to roll through the SCNs from that point onward


SQL> select 14816563714099+rownum scn,  
  2         scn_to_timestamp(14816563714099+rownum) ts
  3  from ( select 1 from dual connect by level <= 50 );

               SCN TS
------------------ -----------------------------------
    14816563714100 08-JUN-19 02.41.53.000000000 AM
    14816563714101 08-JUN-19 02.41.56.000000000 AM
    14816563714102 08-JUN-19 02.41.59.000000000 AM
    14816563714103 08-JUN-19 02.41.59.000000000 AM
    14816563714104 08-JUN-19 02.41.59.000000000 AM
    14816563714105 08-JUN-19 02.41.59.000000000 AM
    14816563714106 08-JUN-19 02.41.59.000000000 AM
    14816563714107 08-JUN-19 02.41.59.000000000 AM
    14816563714108 08-JUN-19 02.41.59.000000000 AM
    14816563714109 08-JUN-19 02.41.59.000000000 AM
    14816563714110 08-JUN-19 02.41.59.000000000 AM
    14816563714111 08-JUN-19 02.42.05.000000000 AM
    14816563714112 08-JUN-19 02.42.08.000000000 AM
    14816563714113 08-JUN-19 02.42.11.000000000 AM
    14816563714114 08-JUN-19 02.42.14.000000000 AM
    14816563714115 08-JUN-19 02.42.17.000000000 AM
    14816563714116 08-JUN-19 02.42.20.000000000 AM
    14816563714117 08-JUN-19 02.42.23.000000000 AM
    14816563714118 08-JUN-19 02.42.26.000000000 AM
    14816563714119 08-JUN-19 02.42.29.000000000 AM
    14816563714120 08-JUN-19 02.42.32.000000000 AM
    14816563714121 08-JUN-19 02.42.35.000000000 AM
    14816563714122 08-JUN-19 02.42.38.000000000 AM
    14816563714123 08-JUN-19 02.42.41.000000000 AM
    14816563714124 08-JUN-19 02.42.44.000000000 AM
    14816563714125 08-JUN-19 02.42.47.000000000 AM
    14816563714126 08-JUN-19 02.42.50.000000000 AM
    14816563714127 08-JUN-19 02.42.53.000000000 AM
    14816563714128 08-JUN-19 02.42.56.000000000 AM
    14816563714129 08-JUN-19 02.42.59.000000000 AM
    14816563714130 08-JUN-19 02.42.59.000000000 AM
    14816563714131 08-JUN-19 02.42.59.000000000 AM
    14816563714132 08-JUN-19 02.42.59.000000000 AM
    14816563714133 08-JUN-19 02.42.59.000000000 AM
    ...
    ...
  

But if we are going to store this data, we can start to see some things that can be improved.

Firstly, a lot of rows have the same timestamp. As mentioned, there is only a granularity of 3 seconds, and we can certainly burn through a lot of SCNs in a 3 second period. I can use some simple analytics to see if a timestamp matches the trailing one and filter it out if that is the case.


SQL> with
  2  t as
  3    ( select min(scn) lo from   sys.smon_scn_time ),
  4  all_scns as
  5    ( select lo+rownum scn, scn_to_timestamp(lo+rownum) ts
  6      from  t
  7      connect by level <= 100 ),
  8  delta_scns as
  9    ( select a.*, row_number() over ( partition by ts order by scn ) as rn
 10      from all_scns a )
 11  select * from delta_scns
 12  where rn = 1;

               SCN TS                                               RN
------------------ ---------------------------------------- ----------
    14816563714100 08-JUN-19 02.41.53.000000000 AM                   1
    14816563714101 08-JUN-19 02.41.56.000000000 AM                   1
    14816563714102 08-JUN-19 02.41.59.000000000 AM                   1
    14816563714111 08-JUN-19 02.42.05.000000000 AM                   1
    14816563714112 08-JUN-19 02.42.08.000000000 AM                   1
    14816563714113 08-JUN-19 02.42.11.000000000 AM                   1
    14816563714114 08-JUN-19 02.42.14.000000000 AM                   1
    14816563714115 08-JUN-19 02.42.17.000000000 AM                   1
    14816563714116 08-JUN-19 02.42.20.000000000 AM                   1
    14816563714117 08-JUN-19 02.42.23.000000000 AM                   1
    14816563714118 08-JUN-19 02.42.26.000000000 AM                   1
    14816563714119 08-JUN-19 02.42.29.000000000 AM                   1
    14816563714120 08-JUN-19 02.42.32.000000000 AM                   1
    14816563714121 08-JUN-19 02.42.35.000000000 AM                   1
    14816563714122 08-JUN-19 02.42.38.000000000 AM                   1
    14816563714123 08-JUN-19 02.42.41.000000000 AM                   1
    14816563714124 08-JUN-19 02.42.44.000000000 AM                   1
    14816563714125 08-JUN-19 02.42.47.000000000 AM                   1
    14816563714126 08-JUN-19 02.42.50.000000000 AM                   1
    14816563714127 08-JUN-19 02.42.53.000000000 AM                   1
    14816563714128 08-JUN-19 02.42.56.000000000 AM                   1
    14816563714129 08-JUN-19 02.42.59.000000000 AM                   1
    ...
    ...
 

Secondly, we need to stop at a particular point. I can easily get the highest SCN from the table


SQL> select max(scn) scn from   sys.smon_scn_time;

               SCN
------------------
    14816565838367
 

but that is when things start to get problematic. Even for just this small set of data (just a few days), on my almost entirely idle database (its my laptop):


SQL> select max(scn) - 14816563714100 from   sys.smon_scn_time;

MAX(SCN)-14816563714100
-----------------------
                2124267
    

you can see that I’ve burned through over 2million SCNs. When I put that back into my query


SQL> with
  2  t as
  3    ( select min(scn) lo, max(scn) hi from   sys.smon_scn_time ),
  4  all_scns as
  5    ( select lo+rownum scn, scn_to_timestamp(lo+rownum) ts
  6      from  t
  7      connect by level <= hi-lo  )
  8  delta_scns as
  9    ( select a.*, row_number() over ( partition by ts order by scn ) as rn
 10      from all_scns a )
 11  select * from delta_scns
 12  where rn = 1
 

it took a looonnnggg time to come back. This is understandable – it is millions of calls to extract a timestamp, followed by some heavy duty analytics to remove duplicates. It has become a very expensive operation to perform.

So that got me thinking – how does the database do it? I started with – how does the database get down to 3 second granularity when there is still only a row every 5 minutes in SMON_SCN_TIME? This is where the TIM_SCN_MAP column comes into play.


SQL> select scn, num_mappings, tim_scn_map
  2  from sys.smon_scn_time
  3  where scn = 14816565797824

               SCN NUM_MAPPINGS TIM_SCN_MAP
------------------ ------------ -----------------------------------------------------------------------------
    14816565797824          100 2F90015DC12324C0790D00003290015DC22324C0790D00003590015DC32324C0790D0000...

Searching along raw data and looking for patterns, I could see that the “D0000” was repeated every 24 characters (or 12 bytes). A simple check confirmed that this appears to equate to the NUM_MAPPINGS column, so it is reasonable to assume that the raw data is a set of time mappings. Given that the maximum value for NUM_MAPPINGS is 100, this also fits the hypothesis because 100 x 3 seconds granularity yields 5 minutes, which is how often we get a row in SMON_SCN_TIME


SQL> select scn, num_mappings, length(tim_scn_map)/24
  2  from   sys.smon_scn_time
  3  /

               SCN NUM_MAPPINGS LENGTH(TIM_SCN_MAP)/24
------------------ ------------ ----------------------
    14816565366476          100                    100
    14816565366679           83                     83
    14816565366808          100                    100
    14816565367164           82                     82
    14816565367291          100                    100
    14816565367475           86                     86
    14816565029366          100                    100
 ...
 

So 24 characters (12 bytes) is probably an occurrence of a SCN to timestamp mapping. The task now is dig out that information from that raw encoding. I’ll grab that first 12 bytes from the row above 2F90015DC12324C0790D0000 and see if we can align it to other values in the table. The first thing I noticed is that ‘790D0000’ rarely changes across the whole table, so I converted that to decimal to see if I could match it to anything. We have to swap the bytes first, and then I got:


SQL> select to_number('0D79','xxxx') from dual;

TO_NUMBER('0D79','XXXX')
------------------------
                    3449

That matched the SCN_WRP column in SMON_SCN_TIME. Grabbing the next 4 bytes gives


SQL> select to_number('C02423C1','xxxxxxxx') from dual;

TO_NUMBER('C02423C1','XXXXXXXX')
--------------------------------
                      3223593921

That matched the SCN_BAS column in SMON_SCN_TIME. So SCN_WRP and SCN_BAS are enough to form a complete SCN number. Which would mean that the remaining 4 bytes should be the time information.


SQL> select to_number('5D01902F','xxxxxxxx') from dual;

TO_NUMBER('5D01902F','XXXXXXXX')
--------------------------------
                      1560383535
   

Obviously that does not look like a date or a time, but this is an internal format that is seen in various places around Oracle database, for example, in redo logs etc. To explain where it comes from, it is better to approach it from the other direction (starting with time).

Lets say I wanted a unique number for clock time of just hours and minutes (eg: 09:37). I could do something like:

unique val = hours * 100 + minutes

That would work because I know that minutes is capped at 59, so there is no chance of an overlap by using a multiplier of 100. So here is how that concept can be taken to its full extent to get a unique number for a full date and time. I’ll pick a date/time of 2019-6-12 23:52:15 as an example

  • Take the year, 2019.
  • We don’t need anything before Oracle was “born”, so subtract 1976 = 43
  • For the months, multiply by 13 (anything more than 12 months) = 559
  • Because we’re IT nerds, we’ll start months at zero (0=January), so we add 5 for June = 564
  • For the day in a month, multiply by 32 (anything more than 31) = 18048
  • Because we’re IT nerds, we’ll start day of month at zero (=1st), so we add 11 for the 12th = 18059
  • For the hours, multiply by 24 (because hours never exceed 23) = 433416
  • No need for nerdy stuff here, because hours are already normalised at 0-23, so we add 23 = 433439
  • For the minutes, multiply by 60 (minutes are 0 to 59) = 26006340
  • Then add the minutes = 26006392
  • Same for the seconds, multiply by 60 = 1560383520
  • and finally we add the seconds = 1560383535

And voila! You can that it matches the decimal expansion of the 4 bytes of 5D01902F above. Now that we know how the time to number conversion is done, a little PL/SQL lets the reverse be done (number to time):


SQL> declare
  2    t int := 1560383535;
  3    secs int;
  4    mins int;
  5    hrs int;
  6    dy int;
  7    mon int;
  8    yr int;
  9  begin
 10   secs := mod(t,60);
 11   t := t - secs;
 12   t := floor(t / 60);
 13   mins := mod(t,60);
 14   t := t - mins;
 15   t := floor(t / 60);
 16   hrs := mod(t,24);
 17   t := t - hrs;
 18   t := floor(t / 24);
 19   dy := mod(t,32);
 20   t := t - dy;
 21   t := floor(t / 32);
 22   dy := dy + 1;
 23   mon := mod(t,13);
 24   t := t - mon;
 25   mon := mon + 1;
 26   t := floor(t / 13);
 27   yr := t + 1976;
 28   dbms_output.put_line(yr||'-'||mon||'-'||dy||' '||hrs||':'||mins||':'||secs);
 29  end;
 30  /
2019-6-12 23:52:15

To make all of this simpler and modular, I’ll build a couple of functions to bring all the bits and pieces we’ve learnt together. First a function to take a non-byte swapped hex string to a SCN


SQL> create or replace
  2  function raw_to_scn(p_scn varchar2) return number is
  3  begin
  4    return to_number(
  5                  substr(p_scn,7,2)||
  6                  substr(p_scn,5,2)||
  7                  substr(p_scn,3,2)||
  8                  substr(p_scn,1,2),'xxxxxxxx');
  9  end;
 10  /

Function created.

and then a function that will extend the anonymous block above to take a non-byte swapped hex string and return a timestamp (well, a date in reality).


SQL> create or replace
  2  function raw_to_date(p_time varchar2) return date is
  3    t int := to_number(
  4                  substr(p_time,7,2)||
  5                  substr(p_time,5,2)||
  6                  substr(p_time,3,2)||
  7                  substr(p_time,1,2),'xxxxxxxx');
  8    secs int;
  9    mins int;
 10    hrs int;
 11    dy int;
 12    mon int;
 13    yr int;
 14  begin
 15   secs := mod(t,60);
 16   t := t - secs;
 17   t := floor(t / 60);
 18   mins := mod(t,60);
 19   t := t - mins;
 20   t := floor(t / 60);
 21   hrs := mod(t,24);
 22   t := t - hrs;
 23   t := floor(t / 24);
 24   dy := mod(t,32);
 25   t := t - dy;
 26   t := floor(t / 32);
 27   dy := dy + 1;
 28   mon := mod(t,13);
 29   t := t - mon;
 30   mon := mon + 1;
 31   t := floor(t / 13);
 32   yr := t + 1976;
 33   return to_date(yr||'-'||mon||'-'||dy||' '||hrs||':'||mins||':'||secs,'yyyy-mm-dd hh24:mi:ss');
 34  end;
 35  /

Function created.

With these in place, I can create a couple of types to serve as return datatypes for a pipelined function


SQL> create or replace
  2  type scn_dte as object
  3    ( scn int, dte date );
  4  /

Type created.

SQL>
SQL> create or replace
  2  type scn_dte_nt as table of scn_dte
  3  /

Type created.

and here is a function that will take important components from SMON_SCN_TIME namely SCN_BAS, NUM_MAPPINGS, SCAN_WRAP and the raw data in TIM_SCN_MAP to spit out the 3-second interval data rather than just 1 row per 5 minutes.


SQL> create or replace
  2  function full_smon_scn_timestamp(p_start_scn int default 0) return scn_dte_nt pipelined as
  3    l_map varchar2(2400);
  4  begin
  5    for i in ( select * from sys.smon_scn_time
  6               where scn > p_start_scn
  7               order by scn
  8             )
  9    loop
 10      pipe row ( scn_dte(i.scn_wrp * 4294967296 + i.scn_bas,i.time_dp));
 11      l_map := i.tim_scn_map;
 12      for j in 1 .. i.num_mappings
 13      loop
 14         pipe row (
 15           scn_dte(
 16             i.scn_wrp * 4294967296 + raw_to_scn(substr(l_map,9,8)),
 17             raw_to_date(substr(l_map,1,8))
 18             )
 19          );
 20         l_map := substr(l_map,25);
 21      end loop;
 22    end loop;
 23  end;
 24  /

Function created.

Let’s give that function a run to see what comes out:


SQL> select * from full_smon_scn_timestamp()
  2  where rownum <= 20;

               SCN DTE
------------------ -------------------
    14816563726597 07/06/2019 21:50:43
    14816563726598 07/06/2019 21:50:46
    14816563726599 07/06/2019 21:50:49
    14816563726600 07/06/2019 21:50:52
    14816563726601 07/06/2019 21:50:55
    14816563726602 07/06/2019 21:50:58
    14816563726603 07/06/2019 21:51:01
    14816563726604 07/06/2019 21:51:04
    14816563726605 07/06/2019 21:51:07
    14816563726606 07/06/2019 21:51:10
    14816563726607 07/06/2019 21:51:13
    14816563726608 07/06/2019 21:51:16
    14816563726609 07/06/2019 21:51:19
    14816563726610 07/06/2019 21:51:22
    14816563726611 07/06/2019 21:51:25
    14816563726612 07/06/2019 21:51:28
    14816563726633 07/06/2019 21:51:34
    14816563726634 07/06/2019 21:51:37
    14816563726635 07/06/2019 21:51:40
    14816563726636 07/06/2019 21:51:43

Each row in SMON_SCN_TIME contains a starting value for the SCN plus all of the mappings, so the total number of 3-second intervals is:


SQL> select sum(num_mappings+1) from sys.smon_scn_time;

SUM(NUM_MAPPINGS+1)
-------------------
             145262
  

and I can compare that to the total number of rows that will be returned from my function


SQL> select count(*) from full_smon_scn_timestamp();

  COUNT(*)
----------
    145262
   

So there you have a way to grab all of those 3-second granularity entries in SMON_SCN_TIME without having to call SCN_TO_TIMESTAMP millions of time. You could run this function (say) daily by passing in a starting SCN value equal to the highest value you have so far retrieved to build up your own custom SMON_SCN_TIME variant that you can keep for as long as you want.

Of course, poking around in raw fields is totally unsupported, so don’t come crying to me if you decide to use this for anything more than exploration and one day it all blows up in your face šŸ™‚

3 Comments on “SMON_SCN_TIME and ORA-8161? Digging deeper

  1. Interesting read !
    Small remark : the example formula to get a unique number for hours and minutes should be “unique val = hours * 60 + minutes” because minutes are 0 to 59. With 30 as multiplier, there is some overlap.

Got some thoughts? Leave a comment

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 )

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: