ORA19706和_external_scn_rejection_threshold_hours的前世今生

由老白论坛中整理 http://www.oraclefans.cn/forum/showtopic.jsp?rootid=42850&CPages=1

最近这几天和几个客户一直在讨论一个最近爆发的ORA-19706的故障。在一个数据库访问另外一个数据库的时候出现了：

1
2
3
4
5
SQL> select  * from tab@to10g2;
select  * from tab@to10g2
                   *
ERROR at line 1:
ORA-19706: invalid SCN

错误，在ALERT LOG中，也发现了一些报警信息：

Rejected the attempt to advance SCN over limit by 13 hours worth to 0x0bd8.0000133b, by distributed transaction remote logon, remote DB: ORA10G.

通过MOS查到了这是SCN HEEADROOM的问题，我们暂且称之为SCN天花板。MOS的建议是下载并安装最新的PSU，并设置参数_external_scn_rejection_threshold_hours为24.刚开始的时候并没有搞明白这个参数具体的含义。确实设置这个参数后，问题就没有再出现了。不过随着关于这个问题讨论的深入，很多疑问又冒了出来。特别是周末时和美国ORACLE的一个哥们在MSN上聊到此事，那个哥们说是由于客户安装了CPU 2012 JAN安全补丁，才出现很多用户出现了ORA-19706的问题。回退了这个补丁，问题就解决了。而且这个问题在ORACLE内部讳莫如深，他们也不太清楚具体的原因是什么。 

《《《《本贴附件为各个版本的oracle公司提供的scnhealthcheck.sql，实际上各个版本（11g之前）的差异不是很大，部分打了补丁的11g的系统，每秒SCN增长阀值被加大为32K了》》》

今天正好没什么事，和几个DBA又讨论起这个问题，于是我仔细翻阅了一些资料。终于把整个故事都搞明白了。ORACLE的CPU 2012 JAN确实对SCN的算法做出了一系列的改进。这起源于INFOWORLD在2011年底发现的ORACLE的一个安全漏洞。关于这个安全漏洞的故事，可以参考http://www.infoworld.com/d/security/fundamental-oracle-flaw-revealed-184163-0?page=0,0 。这里我简单介绍一下这个故事：

INFOWORLD在一个测试中，发现ORACLE 11.2中存在一个BEGIN BACKUP的BUG,也就是当执行了ALTER DATABASE BEGIN BACKUP后，SCN会增长的超出正常水平，使数据库很快达到SCN的软限制(SCN HEADROOM），一旦达到了这个限制，数据库将无法正常处理事务，甚至严重时导致宕机。更有甚者，当SCN超过HEADROOM的时候，可能会导致数据库出现ORA-600 [2252]而无法启动数据库（当然ORACLE有很多保护机制，很少会出现超出的情况）。这个问题在INFOWORLD发现之前，ORACLE已经有所发现，就是Bug 12371955 - Hot Backup can cause increased SCN growth rate leading to ORA-600 [2252] errors [ID 12371955.8]。目前这个BUG已经解决，并且不会出现在11g之前的版本中。

INFOWORLD在发现这个问题后，在进一步的测试发现更严重的问题，就是DBLINK的SCN同步机制。为了实现分布式处理，ORACLE在多个库做DBLINK访问的时候，会将SCN进行同步，也就是将较高的SCN同步微参与DBLINK的两个库的SCN，执行DBLINK操作后，两个库的SCN就同步了。这个机制就可能把SCN增长异常传播到所有的数据库服务器上。也就是说，如果一个服务级别很低的报表服务器出现故障，可能会影响服务级别特别高的生产服务器。INFOWORLD把这个问题报告给了ORACLE，并一起进行了一系列的工作，于是在CPU 2012 JAN中出现了对这个DBLINK问题的补丁。在介绍这个补丁之前，我们先来了解一段ORACLE SCN的背景资料。

背景：ORACLE SCN的硬限制和软限制：ORACLE的SCN是由48位来表示的，因此最大值不能超过2的48次方，这就是ORACLE scn的硬限制。Oracle为了确保48位的SCN能够用足够长的时间（500年），于是对SCN做出了一个限制，就是每秒钟SCN最大增长不能超过16K，Oracle使用了一个十分简单的算法，就是以从1988年1月1日0点0分0秒为基准时间，到当前的秒钟数乘以16K，就是当前SCN的最大允许值这就是SCN HEADROOM。如果在某个时刻SCN达到了这个最大值，那么事务就无法提交，需要等到下一秒，这个HEAD ROOM又变大了，才能继续进行事务的提交。

关于这些背景资料请参考

NOTE:1376995.1 - Information on the System Change Number (SCN) and how it is used in the Oracle Database

NOTE:1393363.1 - Installing, Executing and Interpreting output from the "SCNhealthcheck.sql" script

NOTE:1388639.1 - Evidence to collect when reporting "high SCN rate" issues to Oracle Support

NOTE:1393360.1 - ORA-19706 and Related Alert Log Messages

对于INFOWORLD发现的问题，ORACLE采取了两个很重要的手段，一个是在11.2中，将SCN 每秒最大的增长量从16K加大为32K，第二个是引入了一个阀值，用于阻断有SCN HEADROOM问题的系统将故障传播到其他系统。这个修复包含在CPU 2012 JAN中，打了这个补丁后，我们可以设置一个参数_external_scn_rejection_threshold_hours。这个参数的含义是，当有一个系统和我进行DBLINK操作的时候，我需要检查他的SCN HEADROOM的值，如果这个系统的SCN HEADROOM的值小于这个参数所设置的小时数，那么我就拒绝这个DBLINK访问（如果DBLINK两端的数据库都打了补丁，设置了参数，这种检查是双向的），通过拒绝DBLINK访问，来避免错误被传播。

由于10G的这个补丁打了后，_external_scn_rejection_threshold_hours的缺省值是31天（31*24），是个十分大的值，因此很多系统打了这个补丁后，就出现ORA-19706错误了。而回退了这个补丁后，这方面的限制取消了，报错就没有了。不过隐患就出现了。

实际上这个问题包含两个方面，一方面是如何防止问题的发生，就是防止核心生产系统出现SCN增长异常的问题出现。目前发现的一系列BUG都已经得到了控制，包括那个INFOWORLD发现的BEGIN BACKUP的问题。还有bug13916709，这个补丁是为了修复另外一个SCN BUG而引发的。当一个长时间运行的SQL和DDL,job或者GATHER操作一起执行的时候，会引起CURSOR INVALIDATE，从而触发SCN异常增长。

另外一个方面是如何在某个系统发生故障的时候，阻止这个故障的传播。CPU 2012 JAN就是用来解决这个问题的，不过这个补丁也摆了一个乌龙，在10g上，新引入的参数的缺省值太大，需要设置为24，以避出现过多的ORA-19706。如果由于目前系统的SCN已经比较高了，设置为24还无法避免ORA-19706,可以尝试进一步减小这个参数。很多客户把这个参数减少为1，这样可以最大限度的减少ORA-19706，不过设置为1，存在一定的隐患，一旦系统遇到了一个人新的SCN增长过快的BUG,那么，可能我们都没有时间来解决这个问题，建议尽可能不要设置这么低。

事实上，ORACLE的这些补丁解决了一部分SCN异常增长的情况，也防止了异常增长的扩散，不过仅仅这些还是没有彻底解决这个问题。如果SCN HEADROOM的问题出现在一个核心系统上，那么哪怕它没有被扩散，那么核心系统业务受到影响也是无法容忍的。因此我们需要做的事情还更多，特别是如何发现SCN的异常增长，这就需要我们经常性的对数据库进行监控，并把SCN HEADROOM检查作为标准的健康检查项，定期进行检查。

Oracle发布了一个补丁：Patch:13498243，这个补丁实际上包含的就是一个脚本scnhealthcheck.ql，通过这个脚本可以检查目前本系统的SCN HEADROOM(单位为天)，如果SCNHEADROOM大于62，那么说明这个系统没有SCN HEADROOM问题，如果小于62大于10，说明存在问题，不过还不致命。如果小于10，那么就说明问题十分严重，必须立即处理了。实际上这个脚本的核心是以下的SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select
 version,
 to_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') DATE_TIME,
 ((((
  ((to_number(to_char(sysdate, 'YYYY')) - 1988) * 12 * 31 * 24 * 60 * 60) +
  ((to_number(to_char(sysdate, 'MM')) - 1) * 31 * 24 * 60 * 60) +
  (((to_number(to_char(sysdate, 'DD')) - 1)) * 24 * 60 * 60) +
  (to_number(to_char(sysdate, 'HH24')) * 60 * 60) +
  (to_number(to_char(sysdate, 'MI')) * 60) +
  (to_number(to_char(sysdate, 'SS')))
 ) * (16 * 1024)) - dbms_flashback.get_system_change_number)
 / (16 * 1024 * 60 * 60 * 24)
 ) indicator
  from v$instance

除了这个脚本外，我们还可以通过以下手段进行检查（以下建议来自于Doc ID 1393383.1）：

1、 经常性检查"calls to kcmgas"系统调用的情况，并建立基线数据。这个指标可以从V$SYSSTATE去查找，也可以通过AWR查找历史数据（在AWR数据中查找历史统计数据的方法参考How to extract the historical values of a statistic from AWR Repository [ID 948272.1]）。也可以通过下面的脚本检查某个会话的情况：

1
2
3
4
5
6
7
select sess.sid, stat.value, sess.sql_id, vsql.sql_text
  from v$sesstat stat, v$session sess, v$sql vsql
 where statistic# =
       (select statistic# from v$sysstat where name = 'calls to kcmgas')
   and stat.sid = sess.sid
   and sess.sql_id = vsql.sql_id
 order by stat.value;

2、  如果本机的这个调用很少，那么很可能故障是从别的机器传播过来的，如果要查找哪个远程服务器经常连接到某个数据库，可以通过下面的方法：A)对于已经打了补丁的系统，当SCN传播发生时，ALERT LOG中会有记录，可以通过ALERT LOG查找问题的系统。

Advanced SCN by 13238271 minutes worth to 0x0bd6.00000f21, by distributed transaction remote logon, remote DB: ORA10G.

 Client info : DB logon user SCOTT, machine gc, program sqlplus@gc (TNS V1-V3), and OS user oracle

3、  除了上面的脚本外，ORACLE官方还在How to handle "high SCN rate" Service Requests (Doc ID 1393383.1中提供了一个脚本，可以查找历史的SCN HEADROOM：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
set numwidth 17
set pages 1000
alter session set nls_date_format='DD/Mon/YYYY HH24:MI:SS';
SELECT tim, gscn,
  round(rate),
  round((chk16kscn - gscn)/24/3600/16/1024,1) "Headroom"
FROM 
(
 select tim, gscn, rate,
  ((
  ((to_number(to_char(tim,'YYYY'))-1988)*12*31*24*60*60) +
  ((to_number(to_char(tim,'MM'))-1)*31*24*60*60) +
  (((to_number(to_char(tim,'DD'))-1))*24*60*60) +
  (to_number(to_char(tim,'HH24'))*60*60) +
  (to_number(to_char(tim,'MI'))*60) +
  (to_number(to_char(tim,'SS')))
  ) * (16*1024)) chk16kscn
 from
 (
   select FIRST_TIME tim , FIRST_CHANGE# gscn,
          ((NEXT_CHANGE#-FIRST_CHANGE#)/
           ((NEXT_TIME-FIRST_TIME)*24*60*60)) rate
     from v$archived_log
    where (next_time > first_time)
 )
)
order by 1,2
;
  
  
  
Rem
Rem $Header: rdbms/admin/scnhealthcheck.sql st_server_tbhukya_bug-13498243/8 2012/01/17 03:37:18 tbhukya Exp $
Rem
Rem scnhealthcheck.sql
Rem
Rem Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      scnhealthcheck.sql - Scn Health check
Rem
Rem    DESCRIPTION
Rem      Checks scn health of a DB
Rem
Rem    NOTES
Rem      .
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    tbhukya     01/11/12 - Created
Rem
Rem
define LOWTHRESHOLD=10
define MIDTHRESHOLD=62
define VERBOSE=TRUE
set veri off;
set feedback off;
set serverout on
DECLARE
 verbose boolean:=&&VERBOSE;
BEGIN
 For C in (
  select 
   version, 
   date_time,
   dbms_flashback.get_system_change_number current_scn,
   indicator
  from
  (
   select
   version,
   to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') DATE_TIME,
   ((((
    ((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60) +
    ((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60) +
    (((to_number(to_char(sysdate,'DD'))-1))*24*60*60) +
    (to_number(to_char(sysdate,'HH24'))*60*60) +
    (to_number(to_char(sysdate,'MI'))*60) +
    (to_number(to_char(sysdate,'SS')))
    ) * (16*1024)) - dbms_flashback.get_system_change_number)
   / (16*1024*60*60*24)
   ) indicator
   from v$instance
  ) 
 ) LOOP
  dbms_output.put_line( '-----------------------------------------------------'
                        || '---------' );
  dbms_output.put_line( 'ScnHealthCheck' );
  dbms_output.put_line( '-----------------------------------------------------'
                        || '---------' );
  dbms_output.put_line( 'Current Date: '||C.date_time );
  dbms_output.put_line( 'Current SCN:  '||C.current_scn );
  if (verbose) then
    dbms_output.put_line( 'SCN Headroom: '||round(C.indicator,2) );
  end if;
  dbms_output.put_line( 'Version:      '||C.version );
  dbms_output.put_line( '-----------------------------------------------------'
                        || '---------' );
  IF C.version > '10.2.0.5.0' and 
     C.version NOT LIKE '9.2%' THEN
    IF C.indicator>&MIDTHRESHOLD THEN 
      dbms_output.put_line('Result: A - SCN Headroom is good');
      dbms_output.put_line('Apply the latest recommended patches');
      dbms_output.put_line('based on your maintenance schedule');
      IF (C.version < '11.2.0.2') THEN
        dbms_output.put_line('AND set _external_scn_rejection_threshold_hours='
                             || '24 after apply.');
      END IF;
    ELSIF C.indicator<=&LOWTHRESHOLD THEN
      dbms_output.put_line('Result: C - SCN Headroom is low');
      dbms_output.put_line('If you have not already done so apply' );
      dbms_output.put_line('the latest recommended patches right now' );
      IF (C.version < '11.2.0.2') THEN
        dbms_output.put_line('set _external_scn_rejection_threshold_hours=24 '
                             || 'after apply');
      END IF;
      dbms_output.put_line('AND contact Oracle support immediately.' );
    ELSE
      dbms_output.put_line('Result: B - SCN Headroom is low');
      dbms_output.put_line('If you have not already done so apply' );
      dbms_output.put_line('the latest recommended patches right now');
      IF (C.version < '11.2.0.2') THEN
        dbms_output.put_line('AND set _external_scn_rejection_threshold_hours='
                             ||'24 after apply.');
      END IF;
    END IF;
  ELSE
    IF C.indicator<=&MIDTHRESHOLD THEN
      dbms_output.put_line('Result: C - SCN Headroom is low');
      dbms_output.put_line('If you have not already done so apply' );
      dbms_output.put_line('the latest recommended patches right now' );
      IF (C.version >= '10.1.0.5.0' and 
          C.version <= '10.2.0.5.0' and 
          C.version NOT LIKE '9.2%') THEN
        dbms_output.put_line(', set _external_scn_rejection_threshold_hours=24'
                             || ' after apply');
      END IF;
      dbms_output.put_line('AND contact Oracle support immediately.' );
    ELSE
      dbms_output.put_line('Result: A - SCN Headroom is good');
      dbms_output.put_line('Apply the latest recommended patches');
      dbms_output.put_line('based on your maintenance schedule ');
      IF (C.version >= '10.1.0.5.0' and
          C.version <= '10.2.0.5.0' and
          C.version NOT LIKE '9.2%') THEN
       dbms_output.put_line('AND set _external_scn_rejection_threshold_hours=24'
                             || ' after apply.');
      END IF;
    END IF;
  END IF;
  dbms_output.put_line(
    'For further information review MOS document id 1393363.1');
  dbms_output.put_line( '-----------------------------------------------------'
                        || '---------' );
 END LOOP;
end;
/

解决方式：

Oracle官方关于这个问题的解决方案中，还有两条，一是在今后的版本中将SCN扩展到64位，第二是今后的版本中可能进一步加大SCN HEADROOM为更大的值，因为目前已经出现了每秒超过16K个事务的系统. 
如果你的系统已经出现了问题，发现某台核心系统的SCN被传染了，想要找出是哪些系统和本机有DBLINK连接，可以通过两个方法，一是通过系统的LOGON触发器，记录LOGON的情况，二是通过LOGON审计，将LOGON审计数据写入DB，只要设置"AUDIT CREATE SESSION"审计，就可以通过下面的SQL查询到相关信息： 
select distinct "COMMENT$TEXT",userhost from aud$ where "COMMENT$TEXT" like '%DBLINK%';  

以下内容摘自Oracle公司给客户下发的SCN问题紧急处置方案中的内容

在《关于Oracle DB SCN 生成率过高的预警及处理建议》的文章中，我们已经提出SCN生产率过高的原因。作为oracle db的一种重要预警，我们建议对此作重点关注。在此，我们提出以下几点处理建议：

1.  在全系统内做SCN生成率的普查，看看各系统的SCN生成情况是否牵涉生成率过高的现象；

2.  发现SCN生成率过高的相关数据库，根据本指南及时进行修正处理；

3.  形成日常检查机制，每半月或者每月运行scnhealthcheck.sql，例行检查SCN的生成率情况；

4.根据相关数据库的dblink使用情况，形成dblink跟踪列表，便于日后检查SCN状态。列表内容包括源数据库名称、目标数据库名称、dblink名称、dblink用途，甚至包括关联对象等信息

方案一：安装PSU/CPU补丁修复方案

本方案主要针对以下数据库版本：

Oracle 10.2.0.5

Oracle 11.1.0.7

Oracle 11.2.0.2

Oracle 11.2.0.3

针对上述版本的数据库，oracle建议给数据库安装2012年4月发布的PSU，并在安装该PSU的基础上，安装补丁13916709。如果是集群架构，同时给集群软件最新安装PSU。《《《白鳝注：也可以直接安装2012年7月的PSU，这个PSU已经包含了13916709补丁的修复，如果觉得暂时无法升级PSU，那么也可以单独打13916709的独立补丁，这个补丁的目的是解决SCN异常增长的BUG》》》》》

Ø  10.2.0.5  数据库

对于版本为10.2.0.5的数据库， 建议安装2012年4月发布的PSU 13632743，并在安装PSU 13632743的基础上，安装补丁13916709。参数_external_scn_rejection_threshold_hours在2012年4月（包含2012年4月）以后发布的PSU/CPU中已经定义默认值为24，所以安装最新PSU补丁以后，不需要再设该参数。如果是集群架构，同时给集群软件安装PSU 9952245。

Ø  11.1.0.7  数据库

对于版本为11.1.0.7的数据库，建议安装2012年4月发布的PSU 13621679，并在安装PSU 13621679的基础上，安装补丁13916709。参数_external_SCN_rejection_threshold_hours在2012年4月（包含2012年4月）以后发布的PSU/CPU中已经定义默认值为24，所以安装最新PSU补丁以后，不需要再设该参��。如果是集群架构，同时给集群软件安装PSU 11724953。

Ø  11.2.0.2  数据库

对于版本为11.2.0.2的数据库，建议安装2012年4月发布的PSU 13696224，并在安装PSU 13696224的基础上，安装补丁13916709。如果是集群架构，同时给集群软件安装PSU 13696242。

Ø  11.2.0.3  数据库

对于版本为11.2.0.3的数据库，建议安装2012年4月发布的PSU 13696216，并在安装PSU 3696216的基础上，安装补丁13916709。如果是集群架构，同时给集群软件安装PSU 13696251。

《《白鳝注：10.2.0.4目前在PSU中也已经包含了13916709的修复。对于目前无补丁发布的版本，Oracle 10.2.0.1、Oracle 10.2.0.2、Oracle 10.2.0.3》》》

10.2.0.4版本解决方案

本方案针对数据库版本是Oracle 10.2.0.4。oracle为此提供两种修正方式：

Ø  方式一：紧急修复方案

采用前提：数据库SCN问题急需解决，但升级数据库版本从管理、业务应用、时间上都不适合。

方式建议程度：中

处理方式：保持数据库版本不变，安装2012年4月发布的PSU 12879933。在安装12879933之前，必须先安装9352164，这是安装10.2.0.4.4之后PSU的前提条件。如果是集群架构，同时给集群软件安装PSU 9294403。参数_external_SCN_rejection_threshold_hours在2012年4月（包含2012年4月）以后发布的PSU/CPU中已经定义默认值为24，所以安装���新PSU补丁以后，不需要再设该参数。

Ø  方式二：补丁集升级修复方案

采用前提：数据库SCN问题急需解决，安装最近补丁集，从管理、应用、时间上都比较适合，但数据库版本升级计划不能在短期内完成。

方式建议程度：中

处理方式：将数据库升级到10.2.0.5的版本，安装2012年4月发布的PSU 13632743，并在安装PSU 13632743的基础上，安装补丁13916709。如果是集群架构，同时给集群软件安装PSU 9952245。

《《白鳝注：对于其他未涉及版本，ORACLE为发出补丁包，ORACLE建议升级到上述已发补丁包的版本：

Oracle 9.2.0.1～9.2.0.8

Oracle 10.1.0.3～10.1.0.5

Oracle 11.1.0.6

Oracle 11.2.0.1

》》》

Oracle 11.2引入了一个新的隐含参数来控制每秒的SCN HEADROOM，11G的缺省值是32K 
Parameter                                Session Value 
---------------------------------------- ---------------------------------------- 
_max_reasonable_scn_rate                 32768

在网上看到一个查SCN增长速度的脚本，挺有用的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
with t1 as(
select time_dp , 24*60*60*(time_dp - lag(time_dp) over (order by time_dp)) timediff,
  scn - lag(scn) over(order by time_dp) scndiff
from smon_scn_time
)
select time_dp , timediff, scndiff,
       trunc(scndiff/timediff) rate_per_sec
from t1
order by 1
/
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
col first_change# format 99999999999999999999
col next_change# format 99999999999999999999
select  thread#,  first_time, next_time, first_change# ,next_change#, sequence#,
   next_change#-first_change# diff, round ((next_change#-first_change#)/(next_time-first_time)/24/60/60) rt
from (
select thread#, first_time, first_change#,next_time,  next_change#, sequence#,dest_id from v$archived_log
where next_time > sysdate-30 and dest_id=1
order by next_time
)
order by  first_time, thread#
/

我和海军连夜写了两个用于检查kcmgas的脚本，9i采集了statspack就可以使用。10g,11g基于awr   kcmgas_9i.sql

10g的没上去，补一下。这脚本没考虑数据库重启的情况，如果有重启，那么可能会有负值，不用管就行了另外RAC环境，这个脚本只计算了一个节点的，可以自己修改下把INSTANCE_NUMBER的限制去掉。或者两边都跑一下

   kcmgas.sql 

​





【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/djinmusic/p/2916967.html