Suggestion and Solution to SCN Compatibility and DB Link Interconnection Issue

ByJoel Pérez ( Oracle ACE Director )

Suggestion and Solution to SCN Compatibility and DB Link Interconnection Issue

1. Background

 

All current Oracle databases have an upper limit on the SCN according to Oracle’s kernel design, the upper limit is the second’s difference between the current time and 1988/01/01 then times 16k.

For some specified high version database (11.1.0.7.20, 11.2.0.3.9, 11.2.0.4.*, 12.1.0.2 and above), the current value of this limit is calculated by the algorithm whose main factor is SCN COMPATIBILITY. Through this parameter,the value of SCN won’t exceed the upper limit, and DB Link between databases works well.

However, the SCN COMPATIBILITY of these high version database will be adjusted from 1 to 3 after June 23, 2019, meaning the maximum growth rate will reach 96k, which may result in the situation that the upper limit of high-version DB is much higher than the low-version databases.so when that happens, Low version database cannot connect with high version through DB Link.

Then what do we do?

2. Which are high version DB and which are not?

Basically, the exact version of DB and PSU information can help you to distinguish these two kinds of Database.The high-version database includes the following:

All 12.1.0.2 and above databases, including Oracle 18c
11.2.0.4 databases which contain all PSUs
11.2.0.3 databases, with whose PSU at least to 11.2.0.3.9
11.1.0.7 databases, with whose PSU at least to 11.1.0.7.20

all the other versions are lower versions, including:

Oracle 11.1.0.6 and previous versionsincluding Oracle 10g.
Oracle 11.1.0.7, with whose PSU less than 11.1.0.7.20
Oracle 11.2.0.1/11.2.0.2
Oracle 11.2.0.3, with whose PSU less than 11.2.0.3.9

If the accurate PSU is known, you can also check the DBMS_SCN package. For in the highversion,Oracle introduces the features of SCN Compatibility, and which is managed by DBMS_SCN package, so if DBMS_SCN package exists, the database is high version.

use the following SQL to find out:

Select count(*) from dba_objects

Where

owner = ‘SYS’ and object_name =’DBMS_SCN’ and object_type=’PACKAGE BODY’;

COUNT(*)

———-

1

If the SQL return 1, it proves the DB is a high version, otherwise a low version. For a higher version database, the current SCN Compatibility information can be obtained by calling DBMS_SCN with the following code.

Set serverout on

declare

v_autorollover_date date;

v_target_compat number;

v_RSL number;

v_hr_in_scn number;

v_hr_in_sec number;

v_t4 number;

v_max_cmpat number;

v_isenabled boolean;

v_current_compat number;

begin

dbms_scn.GETCURRENTSCNPARAMS(

​​v_RSL,v_hr_in_scn,v_hr_in_sec,v_current_compat,v_max_cmpat);

dbms_scn.GETSCNAUTOROLLOVERPARAMS(

​​v_autorollover_date,v_target_compat,v_isenabled);

dbms_output.put_line(‘Current SCN compatibility:’||v_current_compat);

dbms_output.put_line(‘Current SCN RATE:’||round((v_hr_in_scn/v_hr_in_sec)/1024)||’k’);

if(v_isenabled) then

dbms_output.put_line(‘AUTO SCN compatibility rollover is ENABLED!!!’);

dbms_output.put_line(‘AUTO rollover time:’||to_char(v_date,’YYYY/MM/DD’));

dbms_output.put_line(‘AUTO rollover target value:’||v_target_compat );

else

dbms_output.put_line(‘AUTO SCN compatibility rollover is DISABLED!!!’);

end if;

end;

/

If no changes have been made, then the Current SCN compatibility is 1 and AUTO rollover is enabled, the time is 2019, 06/23, and the target is 3.

For older databases, we can assume the SCN compatibility is 1.

3. Potential risk analysis

According to the upper limit algorithm of Oracle’s SCN, the upper limit of 16k speed and 96k speed is obviously different.

If some of the databases automatically change SCN Compatibility to 3 on 2019/06/23, then the SCN limit of these databases will become 3.5E13 or higher soon, and those have not changed will stay between 1.8E13 and 2E13 for 10 years.

Imagine this: the SCN Compatibility = 3 in one database, and SCN abnormal increase due to certain reasons (bugs, manual settings, etc.), and the value exceeds the SCN ceiling with whose rate is 16k if DB Link communication between two data is required, the mutual SCN will equivalent to the higher one, and then value exceeds the upper limit, DB Link communication will fail.

To sum up, the SCN issue usually doesn’t occur unless all the following factors are met:

A. The SCN Compatibility between the two databases is inconsistent.

B. Abnormal growth happens in the Database with high SCN compatibility, and exceeds the upper limit value of databases with low SCN Compatibility

C. These two kinds of databases connected through the DBLink.

4. Suggestion and solution

To avoid the above problem, the most thorough approach is to unify the SCN Compatibility. There are four situations:

If the databases are all high-version databases, nothing need to be done.
If the database is mainly a high version and a few low- version databases, then it is highly recommended to upgrade the low-version database to a higher version.
If the databases are all lower versions, there is no rush to upgrade the databases right away. But the issue of adding new versions database in the future should be concerned.
If the databases is mainly a low version, and it is inconvenient to upgrade; or a high version is main, yetlow version databases cannot be upgraded, then it is recommended to set SCN Compatibility=1 for all databases, and close the Auto Rollover function of high version to avoid automatically changing of SCN Compatibility.
 

5. Method by adjusting Auto Rollover and SCN Compatibility

1、If you decide to keep the overall environment in the SCN Compatibility=1 after 2019/06/23, you need to disable the SCN Compatibility AutoRollover function of the new version of the database before 2019/06/23.

Note: the following actions are risky and it is not recommended to do it by yourself.
Begin

DBMS_SCN.DISABLEAUTOROLLOVER;

end;

/

The following information will appear in the Alert log

Fri Mar 16 17:15:15 2018

Database SCN compatibility auto-rollover disaedbl

You can also check this by running the script of the second chapter, the expected output is:

AUTO SCN compatibility rollover is DISABLED!!!

2If you decide to keep the entire environment in the SCN compatibility=1 state, then you also need to pay attention to the database added after 2019/06/23, it needs to be manually changed to SCN compatibility=1 to alter the default value, this operation needs to be performed in the Mount state, as follows.

 

Begin

DBMS_SCN.DISABLEAUTOROLLOVER;

end;

/

shutdown immediate

startup mount

alter database set SCN COMPATIBILITY 1;

 

The following information will appear in the Alert log

Completed: alter database set scn compatibility 1

6. Reference

Oracle Support document:

Oracle Databases Need to be Patched to a Minimum Patchset/PSU/RU level before April 2019 (Doc ID 2361478.1)

Mandatory Patching Requirement for Database Versions 11.2.0.3 or Earlier, Using DB Links (Doc ID 2335265.1)

 

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/

Subscribe to our Blog and enjoy our Weekly Cloud article..!

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/

About the author

Joel Pérez ( Oracle ACE Director ) administrator