SCN Basics

SCN (System Change Number) is a monotonically increasing logical timestamp within an Oracle database, essential for recovery, transaction ACID, consistent reads, and distributed transactions. Internally, a 48-bit SCN is split into a 32-bit low part (scn base) and a 16-bit high part (scn wrap), giving a maximum value of 2^48 (approximately 281 trillion).

The Maximum Reasonable SCN (RSL) is the highest permissible SCN at a given time. SCN Headroom represents the time remaining before the current SCN reaches the RSL at the maximum allowed growth rate. The maximum growth rate is 16K (16384) per second prior to 11.2.0.2 and 32K (32768) per second from 11.2.0.2 onward (controllable by the hidden parameter _max_reasonable_scn_rate).

Abnormal SCN growth can arise from bugs or manual manipulation. Crucially, SCN propagates through database links (DB Links): if databace A has a higher SCN than database B, B's SCN is advanced to match A's during distributed operations. Conversely, if B's SCN is too high, A may reject the synchronization with an ORA-19706: Invalid SCN error to protect its own headroom. The threshold for rejection is determined by the hidden parameter _external_scn_rejection_threshold_hours.

The Impact of the January 2012 CPU/PSU Patches

Before these patches, the rejection threshold was effectively 1 hour. The patches introduced _external_scn_rejection_threshold_hours with the following default values:

• 11.2.0.2 and later: 24 hours
• Earlier versions: 744 hours (31 days)

This change significantly raised the rejection threshold for older databases, making them more susceptible to ORA-19706 when receiving high SCN values from patched databases via DB links. The recommended practice is to set this parameter to 24 for all databases to reduce the risk of connectivity issues. In extreme situations, it can be set as low as 1.

Diagnosing SCN Headroom Issues

Alert Log Warnings

The alert log may contain messages like:

Warning - High Database SCN: Current SCN value is 0x0b7b.0008e40b, threshold SCN value is 0x0b75.055dc000
Warning: The SCN headroom for this database is only NN days!
Rejected the attempt to advance SCN over limit by 9374 hours worth to 0x0c00.00000f66, by distributed transaction remote logon, remote DB: REMDB.XX.ORACLE.COM.

Checking Current Headroom

Execute the following SQL to compute headroom in days:

SELECT
  VERSION,
  TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') DATE_TIME,
  ((((
   ((TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-1988)*12*31*24*60*60) +
   ((TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-1)*31*24*60*60) +
   (((TO_NUMBER(TO_CHAR(SYSDATE,'DD'))-1))*24*60*60) +
   (TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))*60*60) +
   (TO_NUMBER(TO_CHAR(SYSDATE,'MI'))*60) +
   (TO_NUMBER(TO_CHAR(SYSDATE,'SS')))
   ) * (16*1024)) - DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
  / (16*1024*60*60*24) 
  ) INDICATOR    --day
FROM V$INSTANCE;

Health status based on scnhealthcheck.sql:

• Headroom > 62 days: Healthy (A)
• 10 < Headroom <= 62 days: Warning (B)
• Headroom <= 10 days: Critical (C)

Tracking SCN Growth Trends

For archivelog mode databases, use:

SET NUMWIDTH 17
SET PAGES 1000
ALTER SESSION SET NLS_DATE_FORMAT='DD/Mon/YYYY HH24:MI:SS';
SELECT TIM, GSCN,
  ROUND(RATE_PER_SEC),
  ROUND((CHK16KSCN - GSCN)/24/3600/16/1024,1) "Headroom"
FROM
(
 SELECT TIM, GSCN, RATE_PER_SEC,
  ((
  ((TO_NUMBER(TO_CHAR(TIM,'YYYY'))-1988)*12*31*24*60*60) +
  ((TO_NUMBER(TO_CHAR(TIM,'MM'))-1)*31*24*60*60) +
  (((TO_NUMBER(TO_CHAR(TIM,'DD'))-1))*24*60*60) +
  (TO_NUMBER(TO_CHAR(TIM,'HH24'))*60*60) +
  (TO_NUMBER(TO_CHAR(TIM,'MI'))*60) +
  (TO_NUMBER(TO_CHAR(TIM,'SS')))
  ) * (16*1024)) CHK16KSCN
 FROM
 (
   SELECT FIRST_TIME TIM , FIRST_CHANGE# GSCN,
          ((NEXT_CHANGE#-FIRST_CHANGE#)/
           ((NEXT_TIME-FIRST_TIME)*24*60*60)) RATE_PER_SEC
     FROM V$ARCHIVED_LOG
    WHERE (NEXT_TIME > FIRST_TIME)
 )
)
ORDER BY 1,2;

For databases without archiving:

WITH T1 AS(
SELECT TIME_DP , 24*60*60*(TIME_DP - LAG(TIME_DP) OVER (ORDER BY TIME_DP)) TIMEDIFF,
  SCN - LAG(SCN) OVER(ORDER BY TIME_DP) SCNDIFF
FROM SMON_SCN_TIME
)
SELECT TIME_DP , TIMEDIFF, SCNDIFF,
       TRUNC(SCNDIFF/TIMEDIFF) RATE_PER_SEC
FROM T1
ORDER BY 1;

Identifying the Source of High SCN Activity

After applying patch 13498243, the alert log can reveal the source:

Advanced SCN by 2280 minutes worth to 0x0e26.e74b94ff, by distributed transaction end, remote DB: ORCL.
 Client info: DB logon user DB_CX, machine HDDS-FXGLDB, program ORACLE.EXE, and OS user SYSTEM

In 12.2 and later, use the following views:

• DBA_DB_LINKS
• DBA_EXTERNAL_SCN_ACTIVITY
• DBA_DB_LINK_SOURCES

Query to find the source of high SCN activity:

(SELECT RESULT, OPERATION_TIMESTAMP, EXTERNAL_SCN, SCN_ADJUSTMENT, HOST_NAME, DB_NAME, 
SESSION_ID, SESSION_SERIAL#   
    FROM DBA_EXTERNAL_SCN_ACTIVITY a, DBA_DB_LINK_SOURCES s   
    WHERE a.INBOUND_DB_LINK_SOURCE_ID = s.SOURCE_ID)
UNION 
(SELECT RESULT, OPERATION_TIMESTAMP, EXTERNAL_SCN, SCN_ADJUSTMENT, 
DBMS_TNS.RESOLVE_TNSNAME(HOST) HOST_NAME, NULL DB_NAME, SESSION_ID, 
SESSION_SERIAL#   
    FROM DBA_EXTERNAL_SCN_ACTIVITY a, DBA_DB_LINKS o, DBA_DB_LINK_SOURCES s 
    WHERE a.OUTBOUND_DB_LINK_NAME = s.SOURCE_ID AND  
OUTBOUND_DB_LINK_OWNER = o.OWNER)
UNION 
(SELECT RESULT, OPERATION_TIMESTAMP, EXTERNAL_SCN,   SCN_ADJUSTMENT,  
s.MACHINE HOST_NAME, NULL DB_NAME, SESSION_ID, SESSION_SERIAL#   
    FROM DBA_EXTERNAL_SCN_ACTIVITY a, V$SESSION s
    WHERE a.SESSION_ID = s.SID AND           
        a.SESSION_SERIAL#=s.SERIAL# AND
        INBOUND_DB_LINK_SOURCE_ID IS NULL AND
        OUTBOUND_DB_LINK_NAME IS NULL AND
        OUTBOUND_DB_LINK_OWNER IS NULL);

Recommended Actions

1. Apply Patch 13498243 (or a more recent PSU) to all databases involved in DB Link connections to ensure consistent SCN handling.
2. Set hidden parameters to safe values (e.g., _external_scn_rejection_threshold_hour=24, _external_scn_logging_threshold_second=86400).
3. Monitor SCN headroom regularly using scnhealthcheck.sql or the SQL scripts above.
4. Isolate high-SCN sources by breaking DB Links temporarily or enforcing stricter controls.
5. Inventory all DB Links across the environment to track potential propagation paths.

Hidden Parameter Query

SELECT NAME
    ,VALUE
    ,DECODE(ISDEFAULT, 'TRUE','Y','N') AS "Default"
    ,DECODE(ISEM,'TRUE','Y','N') AS SesMod
    ,DECODE(ISYM,'IMMEDIATE', 'I','DEFERRED', 'D','FALSE', 'N') AS SysMod
    ,DECODE(IMOD,'MODIFIED','U','SYS_MODIFIED','S','N') AS Modified
    ,DECODE(IADJ,'TRUE','Y','N') AS Adjusted
    ,DESCRIPTION
    FROM (
        SELECT X.INST_ID AS INSTANCE           
            ,X.INDX+1
            ,KSPPINM AS NAME
            ,KSPPITY
            ,KSPPSTVL AS VALUE
            ,KSPPSTDF AS ISDEFAULT
            ,DECODE(BITAND(KSPPIFLG/256,1),1,'TRUE','FALSE') AS ISEM
            ,DECODE(BITAND(KSPPIFLG/65536,3),1,'IMMEDIATE',2,'DEFERRED','FALSE') AS ISYM
            ,DECODE(BITAND(KSPPSTVF,7),1,'MODIFIED','FALSE') AS IMOD
            ,DECODE(BITAND(KSPPSTVF,2),2,'TRUE','FALSE') AS IADJ
            ,KSPPDESC AS DESCRIPTION
        FROM X$KSPPI X,X$KSPPSV Y
    WHERE X.INDX = Y.INDX
    AND SUBSTR(KSPPINM,1,1) = '_'
    AND X.INST_ID = USERENV('Instance')
)
WHERE NAME LIKE '%&par%'
ORDER BY NAME;

Interpretation of Common Questions

Will unpatched DB Links fail automatically? No. Without the patch, the SCN growth mechanism remains unchanged. Problems only arise when a patched database (with potentially higher SCN) propagates its SCN to an unpatched one.

Do patched databases eliminate SCN headroom issues? No. They only increase the headroom limit. Abnormal SCN growth from bugs or intense activity can still spread via DB Links and cause issues.

What about older versions (10.2 and earlier)? They are not affected if they don't connect to patched databases. However, if they do connect, and the patched database's SCN grows faster than 16K/s, headroom problems may occur.

What about 11.2.0.4, 12.1.0.2, and 12.2.0.1? These versions include all necessary fixes. SCN headroom problems are extremely rare unless the database generates SCNs at rates exceeding ~90K per second.

Will two unpatched databases experience issues? No. Only connectinos between patched and unpatched (or older) databases introduce risk.

References

• Document ID 1376995.1: System Change Number (SCN), Headroom, Security and Patch Information
• Document ID 1393363.1: Installing, Executing and Interpreting output from the "scnhealthcheck.sql" script
• Document ID 1393360.1: ORA-19706 and Related Alert Log Messages
• Document ID 948272.1: How to Extract the Historical Values of a Statistic from the AWR Repository