SCN Compatibility问题汇总-2019年6月23日

最近有不少朋友咨询关于2019年6月23日之前数据库scn，数据库是否要升级或者打补丁等问题，这里对相关问题做一个汇总。
1. 是否一定要升级或者打补丁
不一定，以下几种情况可以不用处理
1) 数据库版本11.1.0.7.20+ /11.2.0.3.9+ /11.2.0.4+版本已经包含了该补丁，无需处理
2) 数据库版本全部低于1)中数据库版本
3) 你的数据库环境中不存在1和2中数据库dblink访问
4) 你的数据库环境中有1和2中的数据库dblink访问,但是通过通过dbms_scn设置,不让1中的数据库版本scn compatibility改变(都保持为1)

2. 到了2019年6月23日之后,数据库的scn发生什么改变
简单的说就是数据库每秒可以使用的scn变大了,距离天花板的scn更加大,出现数据库scn用完的概率大大降低

--compat            -- SCN compatibility value
--headroom_in_scn   -- Difference between current SCN and RSL
--headroom_in_sec   -- number of seconds it would take to reachRSL

SQL> set serveroutput on ;
SQL>  declare
  2   rsl number;
  3   headroom_in_scn number;
  4   headroom_in_sec number;
  5   cur_scn_compat number;
  6   max_scn_compat number;
  7   begin
  8   dbms_scn.GetCurrentSCNParams(rsl,headroom_in_scn,headroom_in_sec,cur_scn_compat,max_scn_compat);
  9   dbms_output.put_line('rsl=' || rsl);
 10   dbms_output.put_line('headroom_in_scn=' || headroom_in_scn);
 11   dbms_output.put_line('headroom_in_sec=' || headroom_in_sec);
 12   dbms_output.put_line('cur_scn_compat=' || cur_scn_compat);
 13   dbms_output.put_line('max_scn_compat=' || max_scn_compat);
 14   end;
 15   /
rsl=16424869609472
headroom_in_scn=16424867538319
headroom_in_sec=1002494356
cur_scn_compat=1
max_scn_compat=3

PL/SQL procedure successfully completed.

SQL> Alter Database Set SCN Compatibility 2;

Database altered.

SQL>  declare
  2   rsl number;
  3   headroom_in_scn number;
  4   headroom_in_sec number;
  5   cur_scn_compat number;
  6   max_scn_compat number;
  7   begin
  8   dbms_scn.GetCurrentSCNParams(rsl,headroom_in_scn,headroom_in_sec,cur_scn_compat,max_scn_compat);
  9   dbms_output.put_line('rsl=' || rsl);
 10   dbms_output.put_line('headroom_in_scn=' || headroom_in_scn);
 11   dbms_output.put_line('headroom_in_sec=' || headroom_in_sec);
 12   dbms_output.put_line('cur_scn_compat=' || cur_scn_compat);
 13   dbms_output.put_line('max_scn_compat=' || max_scn_compat);
 14   end;
 15   /
rsl=21792299122688
headroom_in_scn=21792297051479
headroom_in_sec=665048127
cur_scn_compat=2
max_scn_compat=3

PL/SQL procedure successfully completed.


SQL> Alter Database Set SCN Compatibility 3;

Database altered.

SQL>  declare
  2   rsl number;
  3   headroom_in_scn number;
  4   headroom_in_sec number;
  5   cur_scn_compat number;
  6   max_scn_compat number;
  7   begin
  8   dbms_scn.GetCurrentSCNParams(rsl,headroom_in_scn,headroom_in_sec,cur_scn_compat,max_scn_compat);
  9   dbms_output.put_line('rsl=' || rsl);
 10   dbms_output.put_line('headroom_in_scn=' || headroom_in_scn);
 11   dbms_output.put_line('headroom_in_sec=' || headroom_in_sec);
 12   dbms_output.put_line('cur_scn_compat=' || cur_scn_compat);
 13   dbms_output.put_line('max_scn_compat=' || max_scn_compat);
 14   end;
 15   /
rsl=34585263898624
headroom_in_scn=34585261822622
headroom_in_sec=351819476
cur_scn_compat=3
max_scn_compat=3

PL/SQL procedure successfully completed.

这里大概演示了数据库scn compatibility变化带来的相关变化,这里可以看出来每秒

3. 如何禁用/启用scn compatibility自动升级

SQL>  begin dbms_scn.DisableAutoRollover; end;
  2   /

PL/SQL procedure successfully completed.

SQL> declare
  2   EFFECTIVE_AUTO_ROLLOVER_TS date;
  3   TARGET_COMPAT number;
  4   IS_ENABLED boolean;
  5   begin
  6    dbms_scn.GETSCNAUTOROLLOVERPARAMS(EFFECTIVE_AUTO_ROLLOVER_TS,TARGET_COMPAT,IS_ENABLED);
  7    dbms_output.put_line('EFFECTIVE_AUTO_ROLLOVER_TS='||to_char(EFFECTIVE_AUTO_ROLLOVER_TS,'yyyy-mm-dd hh24:mi:ss'));
  8    dbms_output.put_line('TARGET_COMPAT=' || TARGET_COMPAT);
  9   if(IS_ENABLED)then
 10    dbms_output.put_line('IS_ENABLED IS TURE'); 
 11   else  
 12    dbms_output.put_line('IS_ENABLED IS FALSE'); 
 13   end if;
 14   end;
 15   /
EFFECTIVE_AUTO_ROLLOVER_TS=2019-06-23 00:00:00
TARGET_COMPAT=3
IS_ENABLED IS FALSE

PL/SQL procedure successfully completed.

SQL> 

4. scn compatibility手工调整

SQL> Alter Database Set SCN Compatibility 2;

Database altered.

SQL> Alter Database Set SCN Compatibility 3;

Database altered.

SQL> Alter Database Set SCN Compatibility 1;
Alter Database Set SCN Compatibility 1
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 4999609080 bytes
Fixed Size                  9145080 bytes
Variable Size            1040187392 bytes
Database Buffers         3942645760 bytes
Redo Buffers                7630848 bytes
Database mounted.
SQL>  Alter Database Set SCN Compatibility 1;

Database altered.

SQL> alter database open;

Database altered.

SQL>  set serveroutput on ;
SQL>  declare
  2   rsl number;
  3   headroom_in_scn number;
  4   headroom_in_sec number;
  5   cur_scn_compat number;
 max_scn_compat number;
  6    7   begin
  8   dbms_scn.GetCurrentSCNParams(rsl,headroom_in_scn,headroom_in_sec,cur_scn_compat,max_scn_compat);
  9   dbms_output.put_line('rsl=' || rsl);
 dbms_output.put_line('headroom_in_scn=' || headroom_in_scn);
 10   11   dbms_output.put_line('headroom_in_sec=' || headroom_in_sec);
 12   dbms_output.put_line('cur_scn_compat=' || cur_scn_compat);
 13   dbms_output.put_line('max_scn_compat=' || max_scn_compat);
 end;
 / 14   15  
rsl=16425127591936
headroom_in_scn=16425125502261
headroom_in_sec=1002510101
cur_scn_compat=1
max_scn_compat=3

PL/SQL procedure successfully completed.

参考文档：
Recommended patching and actions for Oracle database versions 12.1.0.1, 11.2.0.3 and earlier-before June 2019(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)