summary

Oracle database automatically launched the new mechanism of SCN in June 2019, that is, the maximum growth of SCN rate can reach 96kb, far exceeding the previous 32kb. Of course, it does not mean that all versions of Oracle databases will automatically enable this feature (it feels like Oracle has buried a pit). To be more precise, the time point is June 23, 2019;

Start time point of new mechanism of SCN

Specific time points can be found through the following sql:

set serveroutput on

declare
  v_autorollover_date date;
  v_target_compat number;
  v_is_enabled boolean;
begin
  dbms_scn.getscnautorolloverparams(v_autorollover_date, v_target_compat, v_is_enabled);
  dbms_output.put_line('auto rollover date      : '||to_char(v_autorollover_date,'YYYY-MM-DD'));
  dbms_output.put_line('target scheme           : '||v_target_compat);
  dbms_output.put_line('rollover enabled (1=yes): '||sys.diutil.bool_to_int(v_is_enabled));
end;
/

Scope of influence of SCN

The following versions of libraries automatically started the new SCN mechanism in June 2019.

How to judge whether my database is affected by the change of this scn mechanism?

• If all your Oracle databases are new versions such as 11.2.0.4 or 12.2, no processing is required;
• If some lower versions (such as 10205 and 11.1) of all databases need to access the higher version libraries (such as 11.2.0.4 and 12.2) through dblink, there may be risks. It is recommended to upgrade the lower version libraries or install the Patch recommended above;
• If the databases are all low version libraries, they will not be affected;
• If there is no dblink between databases to access each other, it will not be affected!

New mechanism of SCN

For the above version of the database, the new mechanism will be automatically implemented on June 23. So what is the new mechanism? To put it simply, the scn mechanism of the old version can be understood as scheme 1. The new version will be automatically changed to scheme 3, and the threshold for allowing growth per second is greater.
The maximum value of scn in the old version is power(2,48); The new mechanism was later called Big Scn, which can reach power(2,64), with a difference of tens of thousands of times.

declare
      v_rsl number;
      v_headroom_in_scn number;
      v_headroom_in_sec number;
      v_cur_scn_compat number;
      v_max_scn_compat number;
    begin
dbms_scn.getcurrentscnparams(v_rsl, v_headroom_in_scn, v_headroom_in_sec, v_cur_scn_compat, v_max_scn_compat);
dbms_output.put_line('reasonable scn limit (soft limit):'||to_char(v_rsl,'999,999,999,999,999,999'));
dbms_output.put_line('headroom in scn                   : '||to_char(v_headroom_in_scn,'999,999,999,999,999,999'));
dbms_output.put_line('headroom in sec                  : '||v_headroom_in_sec);
    dbms_output.put_line('current scn compatibility scheme : '||v_cur_scn_compat);
     dbms_output.put_line('max scn compatibility scheme     : '||v_max_scn_compat);
end;
/

How to judge whether the scn of a database is abnormal and reaches the scheme limit

It can be judged from the following script: if the percentage of to RSL scheme 1 is as high as 90%, it indicates that your database scn growth is abnormal. It is recommended to handle it.

select dbms_flashback.get_system_change_number "current value",
       ((((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)) "RSL scheme 1",
       round(dbms_flashback.get_system_change_number /
             ((((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)) * 100,
             5) "% to RSL scheme 1"
  from dual;

Check the health of SCN Headroom in the database

When the indicator continues to decline, we should be vigilant. Under normal circumstances, it will keep floating up and down at a level or continue to grow.

select version,
       date_time,
       dbms_flashback.get_system_change_number current_scn,
       indicator
  from (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
          from v$instance);

Disable SCN new mechanism

Oracle introduced a DBMS in these releases_ SCN package to control this mechanism. For example, we can directly use DBMS here_ SCN to disable this mechanism.

exec dbms_Scn.DISABLEAUTOROLLOVER;
shutdown immediate
startup mount
alter database set scn compatibility 1;
alter database open;

declare
      v_autorollover_date date;
      v_target_compat number;
      v_is_enabled boolean;
    begin
      dbms_scn.getscnautorolloverparams(v_autorollover_date, v_target_compat, v_is_enabled);
      dbms_output.put_line('auto rollover date      : '||to_char(v_autorollover_date,'YYYY-MM-DD'));
      dbms_output.put_line('target scheme           : '||v_target_compat);
      dbms_output.put_line('rollover enabled (1=yes): '||sys.diutil.bool_to_int(v_is_enabled));
   end;
/