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

« Oracle 18c 安装ORA-12754 Runtime Environment的两种解决方案 | Blog首页 | 揭秘Oracle 11.2.0.4前版本DB Link必须在2019年4月前升级 »

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

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


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


历史上的今天...
    >> 2012-03-14文章:
    >> 2011-03-14文章:
    >> 2010-03-14文章:
    >> 2009-03-14文章:
    >> 2008-03-14文章:
           我的搬家历程
    >> 2005-03-14文章:

By eygle on 2018-03-14 21:49 | Comments (0) | Oracle12c/11g | 3277 |


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