在 Oracle 11.2.0.2 之后, 随着一系列 SCN 耗尽问题的出现, 很多补丁涌现出来, 一个新的 Package 增加进来
这个 Package 就是 DBMS_SCN
如果你的数据库中存在这个 Package, 也就意味着你已经安装具备了关于 DB Link 的修正补丁
以下是这个包的主要函数过程以及说明, 这个内容来自 Oracle 11.2.0.4 版本平台:
- Rem
- Rem $Header: rdbms/admin/dbmsscnc.sql /st_rdbms_11.2.0/1 2013/04/18 23:05:40 vgokhale Exp $
- Rem
- Rem dbmsscn.sql
- Rem
- Rem Copyright (c) 2012, 2013, Oracle and/or its affiliates.
- Rem All rights reserved.
- Rem
- Rem NAME
- Rem dbmsscnc.sql - dbms_scn package definition
- Rem
- Rem DESCRIPTION
- Rem
- Rem
- Rem NOTES
- Rem
- Rem
- Rem MODIFIED (MM/DD/YY)
- Rem mtiwary 05/26/12 - Declarations and definitions related to DBMS_SCN
- Rem package.
- Rem mtiwary 05/26/12 - Created
- Rem
- Rem
- Rem BEGIN SQL_FILE_METADATA
- Rem SQL_SOURCE_FILE: rdbms/admin/dbmsscn.sql
- Rem SQL_SHIPPED_FILE:
- Rem SQL_PHASE:
- Rem SQL_STARTUP_MODE: NORMAL
- Rem SQL_IGNORABLE_ERRORS: NONE
- Rem SQL_CALLING_FILE:
- Rem END SQL_FILE_METADATA
- SET ECHO ON
- SET FEEDBACK 1
- SET NUMWIDTH 10
- SET LINESIZE 80
- SET TRIMSPOOL ON
- SET TAB OFF
- SET PAGESIZE 100
- 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;
- 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
- 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.
- 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
- PROCEDURE EnableAutoRollover;
- PROCEDURE DisableAutoRollover;
- END DBMS_SCN;
- /
这里就可以看到 auto-rollover 的自动 SCN 兼容性终止时间, 也就是说, 在不同的兼容性设置中, SCN 的算法不同, 但是内置了天然的算法过期时间
在此之后, 可以通过命令修改数据库的 SCN 兼容性算法:
- SQL> ALTER DATABASE SET SCN COMPATIBILITY 2;
- Database altered.
从高级别向低级别修改, 需要数据库在 Mount 状态:
- SQL> ALTER DATABASE SET SCN COMPATIBILITY 2;
- ALTER DATABASE SET SCN COMPATIBILITY 2
- *
- 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 4609830912 bytes
- Fixed Size 2260888 bytes
- Variable Size 989855848 bytes
- Database Buffers 3607101440 bytes
- Redo Buffers 10612736 bytes
- Database mounted.
- SQL> ALTER DATABASE SET SCN COMPATIBILITY 2;
- Database altered.
- SQL> alter database open;
- Database altered.
这是一个非常重要的变化
来源: https://yq.aliyun.com/articles/544993