暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

ORACLE SCN issue Best Practice (最佳实践)

原创 Anbob 2018-12-29
966
Recently, we have faced a very serious problem with Oracle SCN. The SCN with a production env ORACLE RDBMS grows very fast, the SCN rate is more than 30k per second . In theory, there should not be such a high transaction volume. The environment is a 11.2.0.3 2-nodes RAC ON AIX 6.1 platform, and have applied PSU11.2.0.3.7 . Last week, when a patch was installed, The SCN problem is obviously relieved, With no changed to the application, the SCN drops below 10K per second. I hope that if you have the same troubles as us, you can follow the best practices and install the corresponding patches.
Minimum recommended patch set / PSU /RU for various Oracle Database Releases, w.r.t SCN issues











































Database ReleaseMinimum Recommended PSU /RU levelAny Additional Patches Required
12.2.0.1
12.1.0.2 12.1.0.2.160419 (Apr 2016) or higher
12.1.0.1 12.1.0.1.1 (Oct 2013) or higherPatch 22168163
11.2.0.4 11.2.0.4.7 (Jul 2015) or higherPatch 22168163
11.2.0.3 11.2.0.3..15 (Jul 2015) or higher Patch 22168163  13632140(for RAC)
11.2.0.211.2.0.2.12(Oct 2013) or higherPatch 13632140
10.2.0.510.2.0.5.12 (Jul 2013)  or higher Patch 12780098 12748240 13632140 13916709

Bug 22168163













Range of versions believed to be affectedVersions BELOW 12.2
Versions confirmed as being affected









The fix for 22168163 is first included in


Note:
The statistic "calls to kcmgas" gives an indication of how often this instance has incremented the database SCN itself, as opposed to an SCN increment triggered by some other action such as communication over a database link.
Note that the value is a cumulative value, the calculation is scn_diff/time_diff
Monitor SCN RATE Scripts:
--increment by itself
select * from v$sysstat where name like '%kcmgas';
--increment by all(itself+ dblink ...)
select current_scn from v$database;

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
;
select * from (
select begin_time,to_char(end_interval_time,'yyyy-mm-dd hh24:mi:ss') end_time,startup_time,
(case when startup_same=1 then round((value-lag_value)/((end_interval_time-lag_end_interval_time)*3600*24)) else null end) gas_rate
from (
select to_char(begin_interval_time,'yyyy-mm-dd hh24:mi:ss') begin_time,
to_char(startup_time,'yyyy-mm-dd hh24:mi:ss') startup_time,
value,
snap_id,
lag(value,1) over (order by snap_id) lag_value,
to_date(to_char(end_interval_time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') end_interval_time,
to_date(to_char(lag(end_interval_time,1) over (order by snap_id),'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') lag_end_interval_time,
(case when startup_time=lag(startup_time,1) over (order by snap_id) then 1 else 0 end) startup_same
from (
select a.snap_id,value,b.begin_interval_time,b.end_interval_time,startup_time
from dba_hist_sysstat a,dba_hist_snapshot b
where stat_name='calls to kcmgas'
and a.snap_id=b.snap_id
and a.dbid=b.dbid
and a.instance_number=b.instance_number
and b.dbid=(select dbid from v$database)
and b.instance_number=b.instance_number
and b.instance_number=(select instance_number from v$instance)
and b.begin_interval_time>=sysdate-10
) order by snap_id
) order by snap_id
)
where
gas_rate>=5000
order by begin_time;

Here is the effect of our DB 11.2.0.3.7 after installing patch 13632140.
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论