Oracle 的 DBMS_SCN 修正以及SCN的auto-rollover新特性

2018年3月16日

盖国强

3468

在 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 NAME

Rem dbmsscnc.sql - dbms_scn package definition

Rem

Rem DESCRIPTION

Rem

Rem NOTES

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 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.

这是一个非常重要的变化。