A few days or weeks ago I saw a Twitter post about database links failing on Oracle databases next year. So, I have researched this issue. My company has a bunch of Oracle databases with a web of database links connecting them. After researching this for a while I have convinced myself that our company will not experience an issue. I want to share some of what I have found in this post.
First, I have read some helpful documents on Oracle’s support site. I recommend that you review these if you want to understand the issue:
Mandatory Patching Requirement for Database Versions 11.2.0.3 or Earlier, Using DB Links (Doc ID 2335265.1)
Recommended patches and actions for Oracle databases versions 12.1.0.1, 11.2.0.3 and earlier – before June 2019 (Doc ID 2361478.1)
Master Note: Overview for SCN issues (Doc ID 1503937.1)
System Change Number (SCN), Headroom, Security and Patch Information (Doc ID 1376995.1)
ORA-19706 and Related Alert Log Messages (Doc ID 1393360.1)
Bug 23130347 : HIGH SCN GROWTH RATE
Besides reading Oracle support documents I wanted to recreate a database link issue on a couple of test databases. My idea was to build a database that was not patched enough based on Oracle’s recommendations and one that was. Then connect them and set the date forward to after June 23, 2019 and see if I could create the issues that could happen at that time. I was not successful in recreating the issue. But I did prove out one interesting fact about database links and SCN numbers. When you use a database link the Oracle RDBMS synchronizes the SCN numbers of the two databases. Here is an example. I have 11.2.0.3.0 and 12.1.0.2 databases running on small Linux VMs on top of VirtualBox on my laptop.
Here are their SCN numbers after I create a database link between the two and before I use the database link:
NLSRTL Version 11.2.0.3.0 - Production
ORCL:SYSTEM>ORCL:SYSTEM>
CURRENT_SCN
----------------
1003971
NLSRTL Version 12.1.0.2.0 - Production
ORCL:SYSTEM>ORCL:SYSTEM>
CURRENT_SCN
----------------
1858602
Here are their SCN numbers after an update over a link:
NLSRTL Version 11.2.0.3.0
ORCL:SYSTEM>ORCL:SYSTEM>
CURRENT_SCN
----------------
1859090
NLSRTL Version 12.1.0.2.0
ORCL:SYSTEM>ORCL:SYSTEM>
CURRENT_SCN
----------------
1859091
I did the same thing with a select statement and got the same sort of synchronization of SCN numbers.
Knowing that database links cause SCNs to sync I started looking at our SCNs on all of our databases and found that there were two groups of databases with very similar SCNs. One group’s SCN was around 475,000,000,000 and the other group’s SCN was around 970,000,000,000.
But, based on the documents that I have read the max SCN for May 16th, 2018, today, is almost 16 trillion. We are under 1 trillion on our busiest system so we have a lot of headroom. The Oracle support documents say that there is some internal limit to how much your SCN can grow and Oracle bases the limit on the number of seconds since midnight January 1, 1988. You multiply the seconds by 16K SCN numbers per second to get the max SCN for today’s date. I found a lot of this math in scnhealthcheck.sql which Oracle’s Doc ID 1503937.1 includes. I hacked it into different pieces that I found helpful but I didn’t think I should post them because it is a copyrighted script. Just download it from Oracle support and see if what I’m saying makes sense.
Anyway, after reading up on all this stuff I don’t think that the SCN issues with database links apply to us. We have far too much headroom. Maybe a company with higher transaction rate databases would be closer to the limit. We will find out on June 23, 2019 if I am right.
Bobby
I believe it was my post, We have a headroom of 20 to 25 days, already in an issue, You can use below parameter to reject the advancement. Minimum version required is 10.2.0.5.8.
_external_scn_logging_threshold_seconds=3600
_external_scn_rejection_delta_threshold_minutes=120
_external_scn_rejection_threshold_hours=240
Thank you for the information. I guess more people have the issue than I realized.
alter system set “_external_scn_logging_threshold_seconds” = 3600; — this is equivalent to an hour
This parameter controls an SCN “jump” warning level. If an operation, such as communication over a database link, causes the local database SCN to increase by more than this threshold then a warning is written to the alert log of the form:
alter system set “_external_scn_rejection_delta_threshold_minutes” = 120; — this is equivalent to 2 hours
This parameter controls an SCN delta threshold – attempts by a database link (or other foreign influence) to raise the SCN by more than the delta threshold will fail with an ORA-19706. The threshold is specified in a unit of minutes which assumes the current DB SCN consumption rate (16k in most cases).
Default: The default value is an empty string, or 0, which means that the SCN delta rejection functionality is not active.
hi, Bobby! There are two hidden params in Oracle for alarming to alert if scn changed dramatically and showing linked DB’s SID which was the reason… We used it last year when have big troble with scn headroom less for 30 days. And we successfully solved the problem when scn headroom was 1.45!!! In the same time, Oracle Indian guys recommended to shutdown DB for 1 month and wait for SCN headroom increasing. Unfortunately, the problem still exists. SCN headroom will fall if your database has daily average transaction count more than 16K per sec.
Interesting. Maybe it is more common than I thought. One of the Oracle documents that I referenced said that the vast majority of customers wouldn’t have these issues.
Yes, Oracle support ask us if it is possible to decrease number of transactions. We did some tuning in GSM online data charging procedures, and decreased number of transactions and commits. After 3 month, Oracle released a patch, which also helps a little. Now there is less than 16K trans per sec, so headroom is increasing very slowly. But it doesn’t fall any more and we are happy)
1 year ago:
ScnHealthCheck
————————————————————–
Current Date: 2017/06/14 13:15:00 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
2017/06/14 13:15:00
Current SCN: 15503605841113
SCN Headroom: 4.41 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Now:
DATE SCN_HEADROOM SCN_RATE INT_SCN_RATE
14-05-18 91.66 14476 12249
15-05-18 91.78 14698 12228
16-05-18 91.88 14680 12238
17-05-18 91.98 14421 11870
Interesting. Thank you for sharing the details of your experience.
I have a question about your previos post about SCN. Looks like you drop it. But i have a copy)
It is not clear, what did you do with database to get such big SCN , which was synced to second DB?
Here is what it is on 12.1.0.2:
GROUP# STATUS FIRST_TIM NEXT_CHANGE#
———- —————- ——— —————-
1 INACTIVE 17-AUG-17 1859118
2 CURRENT 01-JUL-19 281474976710655
3 INACTIVE 15-AUG-17 1805994
Here is what it is on 11.2.0.3.5:
GROUP# STATUS FIRST_TIM NEXT_CHANGE#
———- —————- ——— —————-
1 CURRENT 01-JUL-19 281474976710655
2 INACTIVE 10-MAY-18 998197
3 INACTIVE 10-MAY-18 1859701
That post was just a blunder on my part. I was trying to figure out how to get the current SCN and assumed that NEXT_CHANGE# on v$log shows it but it doesn’t. It seems that NEXT_CHANGE# on the current log just shows the highest possible SCN number. If you query v$log on any recent version of Oracle I think you will see that same number.
Interesting , That’s how they calculate headroom
COLUMN gscn FORMAT 999,999,999,999,999,999,999;
SELECT ‘SCN_Rate-NoArchivelog’ “CHECK_NAME”,tim, gscn,
round(rate),
round((chk16kscn – gscn)/24/3600/16/1024,1) “Headroom”
FROM
(
select tim, gscn, rate,
((
((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
from (select first_time, lead(first_time, 1) over (order by first_time) as next_time,
FIRST_CHANGE#, NEXT_CHANGE#
from v$log_history
where thread#=1
and first_time > trunc(sysdate-4))
where (next_time > first_time)
)
)
order by 1,2
/
v$log_history doesn’t have the huge scn number just v$log.