最近有不少朋友咨询关于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)
加我QQ(107644445)
