eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Oracle全面修正了关于DB Link和SCN补丁的公告 | Blog首页 | Mac Terminal 终端防范 idle 避免自动中断的方法 »

SCN 新算法:DBMS_SCN的用法及范例

我们在之前的文章中介绍,Oracle修改了SCN算法,使得SCN的增长率最高可以达到96K/s。
并且设置了3个兼容性级别,对应不同的增长率,1,2,3 是三个可设置级别,3 是终极目标的 96K/s 增长率。

为了推进数据库自动演进到3级的SCN算法,引入了 Auto-Rollover 特性,
根据缺省设置,在 2019年6月23日,数据库将自动启用这个级别。

为了更好的监控和管理SCN,引入了DBMS_SCN包。我们来看一下这个包的使用。

以下是创建脚本:
CREATE OR REPLACE LIBRARY DBMS_SCN_LIB TRUSTED AS STATIC;
/

CREATE OR REPLACE PACKAGE DBMS_SCN AUTHID CURRENT_USER IS

DBMS_SCN_API_MAJOR_VERSION  CONSTANT NUMBER := 1; 
DBMS_SCN_API_MINOR_VERSION  CONSTANT NUMBER := 0;

以下存储过程用于获得当前的SCN参数,包括当前的SCN兼容性,Headroom:
PROCEDURE GetCurrentSCNParams(
                rsl      OUT number,
                headroom_in_scn OUT number,
                headroom_in_sec OUT number,
                cur_scn_compat OUT number,
                max_scn_compat OUT number);

--      Currently no exceptions are thrown.
--      rsl             - Reasonable SCN Limit as of 'now'
--      headroom_in_scn - Difference between current SCN and RSL
--      headroom_in_sec - number of seconds it would take to reach RSL
--                        assuming a constant SCN consumption rate associated
--                        with current SCN compatibility level
--      cur_scn_compat  - current value of SCN compatibility
--      max_scn_compat  - max value of SCN compatibility this database
--                        understands

采用这个过程可以获得如下信息:
DECLARE
 crsl  NUMBER;
 hscn NUMBER;
 hsec NUMBER;
 csc  NUMBER;
 msc  NUMBER;
BEGIN
  dbms_scn.getCurrentSCNParams(crsl, hscn, hsec, csc, msc);
  dbms_output.put_line('Current  RSL:'||TO_CHAR(crsl));
  dbms_output.put_line('Headroom SCN:'||TO_CHAR(hscn));
  dbms_output.put_line('Headroom Sec:'||TO_CHAR(hsec));
  dbms_output.put_line('Currnt SCOMP:'||TO_CHAR(csc));
  dbms_output.put_line('Max_SCN_COMP:'||TO_CHAR(msc));
END;
/
Current  RSL:20764257779712
Headroom SCN:20764254578401
Headroom Sec:633674761
Currnt SCOMP:2
Max_SCN_COMP:3

以下函数用于获得兼容性的信息:
FUNCTION GetSCNParamsByCompat(
                compat IN number,
                rsl           OUT number,
                headroom_in_scn OUT number,
                headroom_in_sec OUT number
         ) RETURN boolean;

--     compat           -- SCN compatibility value
--     rsl              -- Reasonable SCN Limit
--     headroom_in_scn  -- Difference between current SCN and RSL
--     headroom_in_sec  -- number of seconds it would take to reach RSL
--                         assuming a constant SCN consumption rate associated
--                         with specified database SCN compatibility
--
--     Returns False if 'compat' parameter value is invalid, and OUT parameters
--     are not updated.
DECLARE
 boo  BOOLEAN;
 rsl  NUMBER;
 hscn NUMBER;
 hsec NUMBER;
BEGIN
  boo := dbms_scn.getSCNParamsByCompat(1, rsl, hscn, hsec);
  IF boo THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
  dbms_output.put_line(TO_CHAR(rsl));
  dbms_output.put_line(TO_CHAR(hscn));
  dbms_output.put_line(TO_CHAR(hsec));
END;
/
T
15910860898304
15910857696641
971121685

以下存储过程获得自动Rollover的时间和目标兼容性,启用与否的信息:
PROCEDURE GetSCNAutoRolloverParams(
                effective_auto_rollover_ts OUT DATE,
                target_compat OUT number,
                is_enabled OUT boolean);

--      effective_auto_rollover_ts  - timestamp at which rollover becomes
--                                    effective
--      target_compat               - SCN compatibility value this database
--                                    will move to, as a result of
--                                    auto-rollover
--      is_enabled                  - TRUE if auto-rollover feature is
--                                    currently enabled

执行如下代码的输出:
DECLARE
 boo     BOOLEAN;
 efrt    DATE;
 tcompat NUMBER;
BEGIN
  dbms_scn.GetSCNAutoRolloverParams(efrt, tcompat, boo);
  dbms_output.put_line('Eff time:'||TO_CHAR(efrt));
  dbms_output.put_line('Tar compt:'||TO_CHAR(tcompat));
  IF boo THEN
    dbms_output.put_line('Enabled');
  ELSE
    dbms_output.put_line('Not Enabled');
  END IF;
END;
/

Eff time:23-JUN-19 -- 可以看到启用时间是2019年6月23日。
Tar compt:3
Enabled



PROCEDURE EnableAutoRollover;

PROCEDURE DisableAutoRollover;

END DBMS_SCN;
/

历史上的今天...
    >> 2012-03-19文章:
    >> 2009-03-19文章:
    >> 2008-03-19文章:
    >> 2007-03-19文章:
           收到Oracle ACE的名牌
    >> 2006-03-19文章:
           再次选择Google桌面搜索
    >> 2005-03-19文章:
           人民大会堂看电影记

By eygle on 2018-03-19 20:42 | Comments (0) | Oracle12c/11g | 3282 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com