首页 » Cloud, ORACLE 9i-23ai » SCN compat no change even Auto-RollOver is enable (SCN 兼容级别未改变)

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

  • 2019/06/26
  • Cloud, ORACLE 9i-23ai
  • 377 views
  • 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:

-- author: weejar zhang(www.anbob.com)
-- version 1.2
-- purpose: to Check SCN compat
-- file: 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_hava_scn        BINARY_INTEGER;
    is_rolloverd       BOOLEAN;
    db_name            VARCHAR2(100);
    db_role            VARCHAR2(100);
    started_ts         DATE;
BEGIN
    SELECT
        banner
    INTO
        version
    FROM
        v$version
    WHERE
        ROWNUM = 1;
    SELECT
        database_role,
        name
    INTO
        db_role,db_name
    FROM
        v$database;
    SELECT
        startup_time
    INTO
        started_ts
    FROM
        v$instance;
    SELECT
        COUNT(*)
    INTO
        is_hava_scn
    FROM
        dba_objects
    WHERE
        owner = 'SYS'
        AND   object_name = 'DBMS_SCN'
        AND   object_type = 'PACKAGE BODY';
    dbms_output.put_line('Current datatime:'
    || TO_CHAR(SYSDATE,'RRRRmmdd hh24:mi:ss') );
    dbms_output.put_line('Oracle DBNAME:'
    || db_name);
    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') );
    IF
        is_hava_scn > 0
    THEN
        dbms_scn.getcurrentscnparams(rsl,headroom_in_scn,headroom_in_sec,cur_scn_compat,max_scn_compat);
        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;
    ELSE
        dbms_output.put_line('Error: the DBMS_SCN package not found!');
        dbms_output.put_line('CUR_SCN_COMPAT is default 1.');
    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可以解决)。

Related Posts:

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：