June 23 2019, date of the SCN-bug

The what? I read about the SCN bug a few years ago (a wildly increasing value of the SCN till a certain limit has been reached ) , and took this for granted – most of it. Until recently a customer asked for help with a 9.2 database which had to last for another year. And another customer is in a large project of upgrading a 10.2 database to a 12 version, and is afraid that this will not be completed before 2019Q3. It is important when you’ve got older versions of databases, connected to newer databases. And.. what to do about it when you can’t upgrade.

What’s the bug? The bug is explained in Doc ID 2335265.1, about recommended patching:

At any point in time, the Oracle Database calculates a “not to exceed” limit for the number of SCNs a database can have used, based on the number of seconds elapsed since 1988. This is known as the database’s current maximum SCN limit. Doing this ensures that Oracle Databases will ration SCNs over time, allowing over 500 years of data processing for any Oracle Database.

If this patch is not applied, the unpatched database will have a lower SCN rate or lower current max SCN limit.
The newer or patched databases will have higher SCN rate or higher current max SCN limit.
Therefore, there can be situations when the patched database is at a higher SCN level (due to the higher SCN rate allowance) and the unpatched database is at a much lower SCN level (due to lower SCN rate allowance).
When you open a dblink between these two databases, it has to sync the SCN levels of both the databases and if the SCN increase needed in the unpatched database for this sync is beyond it’s allowed SCN rate or current max SCN limit, then the dblink connection cannot be established.

This situation will not rise immediately after the change, but can potentially arise any time after 23rd June 2019.

In short the bug: performing a hot backup can increase the SCN value by millions or billions quite quickly, and when connected to a lot of (newer versions) databases, their SCN is also increased.

Mr Shan Nawaz explained this here.

From the above diagram, databases db1 to db8 are interconnected by db links. So  even if one of the database gets infected with the bug, it propagates to all the databases.

All the databases can be patched, except…..

In summary, 12.2.0.1 and higher releases, 11.2.0.4 and 12.1.0.2 patchsets have this fix included, while patches are available for 11.1.0.7 and 11.2.0.3 releases. If you have any other database server installations (e.g. 10.2.0.5, 11.2.0.2), you should be aware about potential dblink issues in future and consider applying the required patches or upgrading the databases, or not using dblinks with newer versions of databases.

I feel a lot of similarity with the millennium bug by the way.

Advise Oracle:

You should be aware about potential dblink issues in future and consider about upgrading the databases or not using dblinks with newer versions of databases . If you continue to have such database links after June 2019, you may get run-time errors during database link operations (as explained above) and you would need to disconnect those database links at that time.

Ok, so not using dblinks anymore connecting to newer versions of databases (when the database can’t be upgraded / patched). Thinking about an alternative for dblinks with (nearly) the same functionality is quite harsh. Most of the alternatives involves a lot of application changes, and I think  that the most elegant way of avoiding dblinks is to deploy a REST service on the newer version of the database and call this with a HTTP package.

This has been described by Lucas Jellema in this blog with one of the longest titles ever: publish a rest service from plsql to handle http post request using the embedded plsql gateway.

To determine if you are getting short of SCN, this community post should be of help. Two scripts in particular:

The first script (Oracle 10g and above) will check how much of the hard and soft limits you have exhausted:

WITH limits AS (
  SELECT 
      current_scn
  --, dbms_flashback.get_system_change_number as current_scn -- Oracle 9i
    , (SYSDATE - TO_DATE('1988-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * 24*60*60 * 16384 
        AS SCN_soft_limit
    , 281474976710656 AS SCN_hard_limit
  FROM V$DATABASE
)
SELECT
    current_scn
  , current_scn/scn_soft_limit*100 AS pct_soft_limit_exhausted
  , scn_soft_limit
  , current_scn/scn_hard_limit*100 AS pct_hard_limit_exhausted
  , scn_hard_limit
FROM limits;

The second script estimates how much time your database still got left:

# Show the amount of SCN keyspace we have used so far on this database
# By default the SCN max on a 10g/11g 
# instance is a 48-bit integer (281,474,976,710,656) 
SELECT NAME,  
   (current_scn/281474976710656)*100 as PCT_OF_SCN_KEYSPACE_USED,  
   ROUND(SYSDATE-CREATED) as DAYS_SINCE_DB_CREATION, 
   ROUND(1/(current_scn/281474976710656)*(SYSDATE-CREATED)) AS EST_DAYS_BEFORE_SCN_EXHAUSTED, 
   ROUND(1/(current_scn/281474976710656)*(SYSDATE-CREATED)/365) AS EST_YEARS_BEFORE_SCN_EXHAUSTED  
FROM v$database;

Sources

• Recommended patching and actions for Oracle database versions 12.1.0.1, 11.2.0.3 and earlier – before June 2019 (Doc ID 2335265.1) :  https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=294768841527319&id=2335265.1&_adf.ctrl-state=dxhzricbz_57
• Article Infoworld : https://www.infoworld.com/article/2618409/security/security-fundamental-oracle-flaw-revealed.html
• https://mikedietrichde.com/2018/03/13/databases-need-patched-minimum-april-2019/
• Shan Nawaz – TwelveC : https://twelvec.com/2012/02/08/oracle-scn-flaw-hot-backup-bug/
• Short of SCN: http://oraclenz.org/?p=2656
• How do I find my scn: https://dba.stackexchange.com/questions/11405/how-do-i-find-my-current-scn?