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;
/
By eygle on 2018-03-19 20:42 |
Comments (0) |
Oracle12c/11g | 3282 |