首页 » Cloud, ORACLE 9i-23ai » ORACLE SCN issue Best Practice (最佳实践)

ORACLE SCN issue Best Practice (最佳实践)

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 Release Minimum Recommended PSU /RU level Any 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 higher Patch 22168163
11.2.0.4  11.2.0.4.7 (Jul 2015) or higher Patch 22168163
11.2.0.3  11.2.0.3..15 (Jul 2015) or higher  Patch 22168163  13632140(for RAC)
11.2.0.2 11.2.0.2.12(Oct 2013) or higher Patch 13632140
10.2.0.5 10.2.0.5.12 (Jul 2013)  or higher  Patch 12780098 12748240 13632140 13916709

Bug 22168163

Range of versions believed to be affected Versions 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.

打赏

,

对不起,这篇文章暂时关闭评论。