首页 » ORACLE, ORACLE [C]系列 » SCN compat no change even Auto-RollOver is enable (SCN 兼容级别未改变)

SCN compat no change even Auto-RollOver is enable (SCN 兼容级别未改变)

相信近几个月好些DBA一定都被SCN compat(兼容级别)在2019年6月23日自动从1直接跳级到3的问题搞的紧张兮兮, 现在这个特殊日期已经过去几天,不知道是不是觉的风平浪静有些失望, 不要以为这事已过去,恰恰是问题才刚刚开始,SCN已经rollover的有可能会更快速的使用,更多原理以前有写过《 预警:2019年ORACLE SCN 兼容性特性( Compatibility)自动改变的影响》。

最近应该都开始检查是否SCN Compat是否已自动变为3, 在auto rollover未禁用的情况下,还是有些情况下SCN compat当前并没有改变,下面列几种情况。

检查脚本 scn_compat_check.sql:

set serveroutput on 
 declare
 rsl number;
 headroom_in_scn number;
 headroom_in_sec number;
 CUR_SCN_COMPAT number;
 MAX_SCN_COMPAT number;
 auto_rollover_ts date;
 target_compat number ;
 is_enabled boolean;
 VERSION varchar2(100);
 is_rolloverd boolean;
 db_role varchar2(100);
 started_ts date;
 begin
 select banner into VERSION from v$version where rownum=1;
 select DATABASE_ROLE into db_role from v$database;
 select startup_time into started_ts from v$instance;
 dbms_scn.GETCURRENTSCNPARAMS(rsl,headroom_in_scn,HEADROOM_IN_SEC,CUR_SCN_COMPAT,MAX_SCN_COMPAT);
 dbms_output.put_line('Current datatime:'||to_char(sysdate,'RRRRmmdd hh24:mi:ss'));
 dbms_output.put_line('Oracle Version:'||VERSION);
 dbms_output.put_line('Database role:' ||db_role);
 dbms_output.put_line('Instance starttime: '||to_char(started_ts,'RRRRmmdd hh24:mi:ss')); 
 dbms_output.put_line('RSL=' ||rsl);
 dbms_output.put_line('headroom_in_scn=' || headroom_in_scn);
 dbms_output.put_line('headroom_in_sec=' || headroom_in_sec);
 dbms_output.put_line('CUR_SCN_COMPAT=' ||CUR_SCN_COMPAT);
 dbms_output.put_line('MAX_SCN_COMPAT=' ||MAX_SCN_COMPAT);
 
 dbms_scn.getscnautorolloverparams (auto_rollover_ts, target_compat, is_enabled);
 dbms_output.put_line( 'auto_rollover_ts='|| to_char(auto_rollover_ts, 'YYYY-MM-DD' ));
 dbms_output.put_line( 'target_compat='||target_compat);
 if(is_enabled) then
 dbms_output.put_line(' Auto_rollover is enabled!' );
 if CUR_SCN_COMPAT = target_compat then
 dbms_output.put_line('SCN compat had Auto rollover !' );
 end if;
 if CUR_SCN_COMPAT < target_compat and sysdate > auto_rollover_ts then
 dbms_output.put_line('SCN compat No Auto_rollover !' );
 -- standby or read-only database 
 if started_ts<auto_rollover_ts and db_role='PHYSICAL STANDBY' then
 dbms_output.put_line('Tip: Restart Instance SCN compat will rollover automatic.' );
 end if;
 end if ;
 else
 dbms_output.put_line( ' Auto_rollover is disabled!');
 end if;
 end;
 /

1, online redo log  never switch until 20190623

SQL> @scn_compat_check.sql

Current datatime:20190624 18:30:32
Oracle Version:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
RSL=16577505394688
headroom_in_scn=124244680842
headroom_in_sec=7583293
CUR_SCN_COMPAT=1
MAX_SCN_COMPAT=3
auto_rollover_ts=2019-06-23
target_compat=3
Auto_rollover is enabled!
SCN compat No Auto_rollover !

第二天再查时

SQL> @scn_compat_check.sql
Current datatime:20190625 11:05:40
Oracle Version:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
RSL=35501998735360
headroom_in_scn=19048737747293
headroom_in_sec=193773780
CUR_SCN_COMPAT=3
MAX_SCN_COMPAT=3
auto_rollover_ts=2019-06-23
target_compat=3
Auto_rollover is enabled!
SCN compat had Auto rollover !

PL/SQL procedure successfully completed.

SQL> @log
Show redo log layout from V$LOG and V$LOGFILE...

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS                FIRST_CHANGE# FIRST_TIME        
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------------ ----------------- 
         1          1         69 4294967296        512          1 NO  INACTIVE             16451560456642 20190612 11:36:44 
         2          1         70 4294967296        512          1 NO  ACTIVE               16453257001681 20190615 09:09:43 
         3          1         71 4294967296        512          1 NO  CURRENT              16453260987629 20190625 11:04:02  <<<<
         4          1         68 4294967296        512          1 NO  INACTIVE             16402315721466 20190607 01:00:33 
         5          2          5 4294967296        512          1 NO  CURRENT              16402316741755 20190609 20:00:09 
         6          2          2 4294967296        512          1 NO  INACTIVE             15521625611890 20190416 10:59:46 
         7          2          3 4294967296        512          1 NO  INACTIVE             15521625904274 20190416 17:48:10 
         8          2          4 4294967296        512          1 NO  INACTIVE             16402306508842 20190513 05:00:36 

DB alert log

2019-06-25 11:04:02.353000 +08:00
Thread 1 advanced to log sequence 71 (LGWR switch)
  Current log# 3 seq# 71 mem# 0: +DATADG/SPDB/ONLINELOG/group_3.260.1004432869
Database SCN compatibility auto-rollover - control file update
SCN compatibility changed from 1 to 3 (auto-rollover)
2019-06-25 11:05:48.487000 +08:00

Note:
这是一套比较空闲的数据库,所以online redo log 也一致未切换,直到06/25日志切换时才自动SCN compatibility changed from 1 to 3。后来经测试的确可以手动切换online redo , SQL : alter system switch logfile,触发没有自动rollover的实例改变。

2,  Physical Standby database or  open read-only

SQL> @scn_compat_check.sql
Current datatime:20190626 14:47:10
Oracle Version:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Database role:PHYSICAL STANDBY
Instance starttime: 20190621 11:24:10
RSL=16580116971520
headroom_in_scn=117142463378
headroom_in_sec=7149808
CUR_SCN_COMPAT=1
MAX_SCN_COMPAT=3
auto_rollover_ts=2019-06-23
target_compat=3
Auto_rollover is enabled!
SCN compat No Auto_rollover !
Tip: Restart Instance SCN compat will rollover automatic.

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 6.4425E+10 bytes
Fixed Size                 29967376 bytes
Variable Size            3.2078E+10 bytes
Database Buffers         3.2212E+10 bytes
Redo Buffers              104247296 bytes
Database mounted.
Database opened.

SQL> @sch_compat_check.sql
Current datatime:20190626 14:49:09
Oracle Version:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Database role:PHYSICAL STANDBY
Instance starttime: 20190626 14:48:36
RSL=35511810359296
headroom_in_scn=19048835508702
headroom_in_sec=193774775
CUR_SCN_COMPAT=3
MAX_SCN_COMPAT=3
auto_rollover_ts=2019-06-23
target_compat=3
Auto_rollover is enabled!
SCN compat had Auto rollover !

PL/SQL procedure successfully completed.

Note:
对于ADG环境,如果实例是06/23以前启动的,需要重启一下实例,然后SCN compat会自动rollover到3,(open read-only 环境如果存在该现象同样重启instance可以解决)。

打赏

,

目前这篇文章还没有评论(Rss)

我要评论