记一次对SCN天花板问题的处理

昨天晚上接到告警电话,2套数据库发生异常宕机,N套数据库出现下面的错误,导致所有的DB_LINK不能使用。

Tue Sep 18 23:47:16 BEIST 2018
Rejected the attempt to advance SCN over limit by 0 hours worth to 0x0eb5.90804f3d, by distributed transaction remote logon, remote DB: SDB. Client info : DB logon user DIC, machine jtyw, program think@jtyw1(TNS V1-V3), and OS user dic Reconfiguration complete

那么这种问题怎么处理呢?首先我们来讲一下思路和步骤。
SCN天花板是一个什么概念呢?就是正常SCN根据时间的增速是每秒16K(在11.2.0.2版本之前)。假设有一套数据库增速超过了16K,这样它和别的库做DB_LINK的时候,就会同步SCN,让别的库增长也变得过快。这样导致SCN很快到接近了天花板。Oracle在这个地方使用了一个隐含参数”_external_scn_rejection_threshold_hours”来进行控制,防止问题库增速太快,然后传播到其他库,当小于了这个参数值的时候,就直接拒绝db_link访问,拒绝同步SCN。
所以了解了整个原理之后,我们需要做的第一步就是普查每一个库的SCN增速。
1.查询所有库的SCN增速情况.每个实例都要检查

select * from
(
SELECT SS.SNAP_ID AS SNAP_ID,
TO_CHAR(SN.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS "SNAP_DATE",
SUM(DECODE(STAT_NAME, 'calls to kcmgas', VALUE, 0)) -
LAG(SUM(DECODE(STAT_NAME, 'calls to kcmgas', VALUE, 0)), 1) OVER(ORDER BY SS.SNAP_ID) "KCMGAS",
TRUNC((SUM(DECODE(STAT_NAME, 'calls to kcmgas', VALUE, 0)) -
LAG(SUM(DECODE(STAT_NAME, 'calls to kcmgas', VALUE, 0)), 1)
OVER(ORDER BY SS.SNAP_ID)) /
TRUNC((CAST(SN.END_INTERVAL_TIME AS DATE) -
CAST(SN.BEGIN_INTERVAL_TIME AS DATE)) * 86400)) "KCMGAS PER SEC"
FROM SYS.DBA_HIST_SYSSTAT SS, SYS.DBA_HIST_SNAPSHOT SN
WHERE SS.SNAP_ID = SN.SNAP_ID
AND SS.STAT_NAME IN ('calls to kcmgas')
AND SS.DBID = SN.DBID
AND SS.INSTANCE_NUMBER = SN.INSTANCE_NUMBER
AND SN.INSTANCE_NUMBER = (select instance_number from v$instance)
AND SN.DBID = (select dbid from v$database)
GROUP BY SS.SNAP_ID, SN.BEGIN_INTERVAL_TIME, SN.END_INTERVAL_TIME
)


如图所示,这里的“KCMGAS PER SEC”显示增速,超过了16K。是有很大问题的,这个就是异常增长的源头数据库,必须想办法把这个增速下降到16K。
2.找到问题实例之后,检查是什么语句导致SCN增长过快。当然还有一些是Bug,比如使用的热备份。

select * from 
(
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 
) where value>50000;


这里最好多运行几次,我们可以发现是一条Update语句导致的。
3.如果有问题SQL,对问题SQL的对象进行整改。
我这里发现主要的一个问题是对压缩表进行大量的update,delete操作。具体整改措施就是先备份数据,然后解压缩该表,然后truncate表,重新回插数据。
4.处理完成之后,看一下scn的Headroom有没有回升。

set numwidth 17
set pages 1000
alter session set nls_date_format='YYYY-MM-DD 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;


这里可以看到处理完成之后scn的Headroom在逐步的回升。当回升到24以上之后,db_link就又可以重新使用了。

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569