Warning：Oracle Databases Need to be Patched to a Minimum Patchset/PSU/RU level before April 2019
There are two warning articles which were recently released on Oracle‘s official support site My Oracle Support, have caused widespread concern among users of Oracle Database.
Those two articles’ ID are respectively 2361478.1 and 2335265.1
Different from normal reports or news for oracle users, Oracle point the exact version(18.104.22.168), and specific deadline(before April 2019) for certain Database scenarios（Using DB Links） and operations（Patch）and use the strong word( Mandatory) in the title，make it necessary for oracle users to put it into schedule as the deadline is not so far.
After those two articles were published, many users wondered:
How does Oracle trigger this problem after April 2019, how come the exact time point, could it be a time trigger that Oracle laid into the database?
We did a little research and find out this time constraint does exist, but the real trigger time is: June 23, 2019. (about the time difference, we’ll explain it later)
now, let us analyze it step by step.
As we can see, the document 2361478.1 was created and published on the site on February 15, 2018
We may remember that on February 16, 2018, the Oracle 18c database was released,
（referring to: Oracle 18c database has been released and new features introduced）
which means this document was released with the new version Oracle 18c, and the new features of 18c can lead to major changes of Oracle Internal mechanism.
Let’s look at some key contents of this article:
What we are announcing:
All supported releases of Oracle Databases need to be patched to a minimum patchset/PSU level before April 2019 to ensure proper functioning of database links.
Scope of impact: 22.214.171.124 and later versions are not affected, the 126.96.36.199 and 188.8.131.52 patch sets already contain the necessary fixes, and the released patches are available for versions 184.108.40.206 and 220.127.116.11.
Other versions have no patches and need to be upgraded, otherwise there may be problems when connecting other libraries of lower versions and new versions through DB Link.
What changes have caused this effect? The document explain it briefly and effectively as following:
What is the change introduced by the patches listed above?
The patches listed above make the older databases capable of supporting increased SCN soft limit (i.e. support transactions with higher SCN rate) though the increased SCN soft limit only becomes effective later (after April 2019).
Simply put，the algorithm of the SCN needs to be altered, the growth rate to be specific. That way, when data is accessed in multi-DB environment through DB link, the lower and higherversions must use the same algorithm to ensure that the DB Link access is working well.
In those articles, the term “Soft Limit” was described as follows. In the Oracle 18.104.22.168 and before, the threshold of this value 16K,
“At any point in time, the Oracle Database calculates a “not to exceed” limit for the SCN a database can have used, based on the number of seconds elapsed since 1988, multiplied by 16,384. This is known as the ‘Soft Limit’. If the soft limit is likely to be exceeded in the next SCN increment, then Oracle will issue an ORA-0600 error and the process will be cancelled.”
Once the SCN exhausted, problem appeared and the threshold would rise to 32K:
I wrote a article explain how SCN works and may cause problems in the situation of DBlink, you can access that in my blog(www.eygle.com). It works like this: the data query via DB Link will synchronize the SCN of the database. This leads to many Headroom issues that when SCN runs out:
[oracle@jumper oracle]$ sqlplus “/ as sysdba“
SQL*Plus: Release 22.214.171.124.0 – Production on Tue Nov 7 21:07:56 2006
SQL> select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER scn from dual;
What would happen then?
Some of the clues can already be seen in 18c. This new feature was introduced in official document as following:
Consistency Levels for Multi-Shard Queries
You can specify different consistency levels for queries across multiple shards in a shardeddatabase. For example, you might want some queries to avoid the cost of SCN synchronization across shards, and these shards could be globally distributed. Another use case is when you use standbys for replication and slightly stale data is acceptable for cross-shard queries, as the results could be fetched from the primary and its standbys. You can use the initialization parameter MULTISHARD_QUERY_DATA_CONSISTENCY to set different consistency levels when executing multi-shard queries across shards.
This feature enables you to avoid the cost of SCN synchronization while executing multi-shard queries across shards and these shards potentially could be distributed globally.
For multi-shard queries, this feature allows slightly stale data from the standby databases.
This feature is currently targeted at the Oracle Sharding database, which means that the cost of SCN synchronization can be avoided in cross-Shard queries.
what does this really mean?
Many customers have been asking questions, which exact BUG should be repaired? Oracle did not answer it in the documentation directly, but it proposes the minimum patch requirements, and different versions of the patch application matrix:
What is the exact minimum patch set we are talking about in this list?
After analyzing, we finally confirmed that the BUG is 14121009, for the minimum patch required to be applied by April 2019 has the exactly same version with the BUG 14121009,Such as 126.96.36.199.9, 188.8.131.52.20, and Windows 184.108.40.206 Patch 28, 220.127.116.11 Patch 57.
So what key function was introduced in this patch and changed the algorithm of Oracle SCN?
From the article we’ve learnt this new feature is SCN compatibility, which has a time limit set in it and requires this patch to be applied.
The command to modify the database SCN algorithm is:
ALTER DATABASE SET SCN COMPATIBILITY.
warnning: do not test it in any of your important environment, it may cause you into big trouble.
The compatibility feature has 4 options that can be changed to three values: 1, 2, 3
SQL> ALTER DATABASE SET SCN COMPATIBILITY 2;
SQL> ALTER DATABASE SET SCN COMPATIBILITY 3;
When the SCN compatibility is modified to a small value, the database needs to be restarted:
The final key question is, what does this all have to do with time?
Our answer is: Oracle sets the time point for each compatibility of the SCN, that means, low-level compatibility will automatically expire in certain time, and all the revised database will beJumped to Compatibility 3 on June 23 2019.
Level 3 allows for a higher SCN growth rate, exceed 32K is possible.
Therefore, if your old database is not upgraded, the database that connects to Level 3 compatibility may immediately exceed the SCN limit, and the data access may be denied.
All this is because Oracle introduced a feature called Auto-RollOver in the kernel, which sets the time for different SCN growth rates and automatically expires. As time goes by, new SCN algorithm will replace the old one yet user known.
You can get the internal information through the DBMS_SCN package. It’s like a time bomb buried in the database
—effective_auto_rollover_ts – timestamp at which rollover becomes effective
—target_compat – SCN compatibility value this database
— will move to, as a result of auto-rollover
— is_enabled – TRUE if auto-rollover feature is currently enabled
The reason why Oracle prompts in the warning 2019 April, I think it is to leave the user 84 days of margin.
Based on the above analysis, we also got the answer to following questions which users most concerned:
No, there will be no problem if there are mutual accesses to the low-level databases that have not been patched; however, problems may arise if there is a data access between the unpatched low-level version and the applied high-level version.
Not necessarily, cross-DB Link access is not necessarily a problem, especially if the SCN’s growth rate keeps a low database; however, due to changes in the algorithm, problems are likely to occur, and the probability is high;
Because SCN is Oracle’s core mechanism, the Headroom problem encountered in the past must be eliminated, so the algorithm needs to be adjusted.
It does not matter if the database is maintained at a low version or does not communicate with each other through DB Link; Oracle also provides the function of disabling this feature, but does not guarantee that it will not change afterwards; since versions below 18.104.22.168 are unsupported versions, users are highly recommended to upgrade.
So what happens if something goes wrong? In MOS document 1393360.1, If the lower version of the database SCN cannot be elevated, then ORA-19706: invalid SCN may be encountered.
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.
Client info: DB logon user ME, machine yy, program sqlplus@yy (TNS V1-V3), and OS user uuu
In fact, the last part of this document has revealed changes in the new features of the SCN. In the new version, the SCN’s growth rate can be fully adjusted to be dynamic:
Warning: The SCN intrinsic growth rate has been consistently
Current SCN intrinsic growth rate is 24416 per sec., zas 7fffff!
The Current SCN value is 3354492471, SCN Compat value is 1
Additional reading related to this topic:
The article finishes here!
We hope the content could be useful for your Oracle DBA Tasks!
This article is brought by Yunhe Enmo (Beijing) Technology Co.,LTD En.enmotech.com
Follow our Company in Linkedin: https://www.linkedin.com/company/1856106/
Joel Pérez’s Linkedin Profile: https://www.linkedin.com/in/sirdbaasjoelperez/
Post Author Bios:
Joel Pérez is an Expert DBA ( Oracle ACE Director, Maximum Availability OCM, OCM Cloud Admin. & OCM12c/11g ) with over 17 years of Real World Experience in Oracle Technology, specialised in design and implement solutions of: High Availability, Disaster Recovery, Upgrades, Replication, Cloud and all area related to Oracle Databases. International consultant with duties, conferences & activities in more than 50 countries and countless clients around the world. Habitual and one of leading writers of Technical Oracle articles for: OTN Spanish, Portuguese, English and more. Regular Speaker in worldwide Oracle events like: OTN LAD (Latin America), OTN MENA (Middle East & Africa), OTN APAC ( Asian Pacific), DTCC China, Oracle Code.. . Joel has always been known for being a pioneer in Oracle technology since the early days of his career being the first Latin American awarded as “OTN Expert” at year 2003 by Oracle Corp., one of the first “Oracle ACE” globally in the Oracle ACE Program at year 2004. He was honoured as one of the first “OCM Database Cloud Administrator” & Maximum Availability OCM in the world. Currently Joel works as Senior Cloud Database Architect in “Yunhe Enmo (Beijing) Technology Co.,Ltd”., company located in Beijing, China En.enmotech.com
Joel Pérez’s Linkedin Profile: https://www.linkedin.com/in/sirdbaasjoelperez/