ANBOB™

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

Bug 22168163

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.

打赏

微信扫一扫，打赏作者吧～