由老白论坛中整理 http://www.oraclefans.cn/forum/showtopic.jsp?rootid=42850&CPages=1
最近这几天和几个客户一直在讨论一个最近爆发的ORA-19706的故障。在一个数据库访问另外一个数据库的时候出现了:
SQL>
select
*
from
tab@to10g2;
select
*
from
tab@to10g2
*
ERROR
at
line
1:ORA-19706:
invalid SCN
Rejected the attempt to advance SCN over limit by 13 hours worth to 0x0bd8.0000133b, by distributed transaction remote logon, remote DB: ORA10G.错误,在ALERT LOG中,也发现了一些报警信息:
通过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:
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])。也可以通过下面的脚本检查某个会话的情况:
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$sysstatwhere 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:
set numwidth 17
set pages 1000
alter sessionset 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 verioff;
set feedbackoff;
set serverouton
DECLARE
verbose boolean:=&&VERBOSE;
BEGIN
For Cin (
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.versionNOT 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.versionNOT 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.versionNOT 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用途,甚至包括关联对象等信息
本方案主要针对以下数据库版本:
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增长速度的脚本,挺有用的
with t1as(
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 sessionset 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_idfrom 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的限制去掉。或者两边都跑一下
联系客服