Oracle-SCN号健康检查（Doc ID 1393363.1）
1. 上传脚本scnhealthcheck.sql到/tmp目录
Rem Rem $Header: rdbms/admin/scnhealthcheck.sql mtiwary_blr_backport_13498243_11.2.0.4.0/1 2013/12/18 00:47:15 mtiwary Exp $ Rem Rem scnhealthcheck.sql Rem Rem Copyright (c) 2012, 2013, Oracle and/or its affiliates. Rem 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=FALSE set veri off; set feedback off; set serverout on DECLARE verbose boolean:=&&VERBOSE; BEGIN For C in ( 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.version NOT 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.version NOT 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.version NOT 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; /
2. 执行脚本
sqlplus "/ as sysdba" <<-EOF -- ALTER SESSION SET CURRENT_SCHEMA = SYS; @/tmp/scnhealthcheck.sql EOF
3. 结果输出
1） Result: A - SCN Headroom is good 2） Result: B - SCN Headroom is low，建议安装数据库补丁，并每天监控 3） Result: C - SCN Headroom is low ，SCN增长非常快，建议立刻安装数据库补丁
4. 查找SCN增长的起因
4.1 Finding out External SCN jump using DBA_EXTERNAL_SCN_ACTIVITY in 12.2 (Doc ID 2171090.1)
create or replace view check_ext_scn as (SELECT RESULT, OPERATION_TIMESTAMP, EXTERNAL_SCN, SCN_ADJUSTMENT, HOST_NAME, DB_NAME, SESSION_ID, SESSION_SERIAL# FROM DBA_EXTERNAL_SCN_ACTIVITY a, DBA_DB_LINK_SOURCES s WHERE a.INBOUND_DB_LINK_SOURCE_ID = s.SOURCE_ID) UNION (SELECT RESULT, OPERATION_TIMESTAMP, EXTERNAL_SCN, SCN_ADJUSTMENT, dbms_tns.resolve_tnsname(HOST) HOST_NAME, NULL DB_NAME, SESSION_ID, SESSION_SERIAL# FROM DBA_EXTERNAL_SCN_ACTIVITY a, DBA_DB_LINKS o, DBA_DB_LINK_SOURCES s WHERE a.OUTBOUND_DB_LINK_NAME = s.SOURCE_ID AND OUTBOUND_DB_LINK_OWNER = o.OWNER) UNION (SELECT RESULT, OPERATION_TIMESTAMP, EXTERNAL_SCN, SCN_ADJUSTMENT, s.MACHINE HOST_NAME, NULL DB_NAME, SESSION_ID, SESSION_SERIAL# FROM DBA_EXTERNAL_SCN_ACTIVITY a, V$SESSION s WHERE a.SESSION_ID = s.SID AND a.SESSION_SERIAL#=s.SERIAL# AND INBOUND_DB_LINK_SOURCE_ID IS NULL AND OUTBOUND_DB_LINK_NAME IS NULL AND OUTBOUND_DB_LINK_OWNER IS NULL); The description of each column in above output is as below: RESULT: whether the SCN spike accepted or rejected OPERATION_TIMESTAMP: when the spike happened EXTERNAL_SCN: The new SCN value to which current SCN jumped SCN_ADJUSTMENT: How much increment the SCN observed HOST_NAME: From which host the dblink originated DB_NAME : From which database the dblink originated SESSION_ID: session id of local instance which added this entry SESSION_SERIAL# : serial# of local instance which added this entry
4.2 Evidence to collect when reporting "high SCN rate" issues to Oracle Support (Doc ID 1388639.1)
-- in archived mode 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 ; -- noarchived mode with t1 as( 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 /
4.3 How to Extract the Historical Values of a Statistic from the AWR Repository (Doc ID 948272.1)
-- 在AWR中提取统计值
set trimspool on set pages 50000 set lines 132 set tab off set feedback off clear break compute; repfooter off; ttitle off; btitle off; set timing off veri off space 1 flush on pause off termout on numwidth 10; set echo off feedback off pagesize 50000 linesize 1000 newpage 1 recsep off; set trimspool on trimout on; -- -- Request the DB Id and Instance Number, if they are not specified column instt_num heading "Inst Num" format 99999; column instt_name heading "Instance" format a12; column dbb_name heading "DB Name" format a12; column dbbid heading "DB Id" format a12 just c; column host heading "Host" format a20; prompt prompt prompt instances IN this workload repository SCHEMA prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SELECT DISTINCT ( CASE WHEN cd.dbid = wr.dbid AND cd.name = wr.db_name AND ci.instance_number = wr.instance_number AND ci.instance_name = wr.instance_name THEN '* ' ELSE ' ' END ) || wr.dbid dbbid, wr.instance_number instt_num, wr.db_name dbb_name, wr.instance_name inst_name, wr.host_name host FROM dba_hist_database_instance wr, v$database cd, v$instance ci; prompt prompt USING &&dbid FOR DATABASE id -- -- Set up the binds for dbid and instance_number variable dbid NUMBER; BEGIN :dbid := &dbid; END; / -- Error reporting whenever SQLERROR EXIT; variable max_snap_time CHAR(10); DECLARE CURSOR cidnum IS SELECT 'X' FROM dba_hist_database_instance WHERE dbid = :dbid; CURSOR csnapid IS SELECT To_char(Max(end_interval_time), 'dd/mm/yyyy') FROM dba_hist_snapshot WHERE dbid = :dbid; vx CHAR(1); BEGIN -- Check Database Id/Instance Number is a valid pair OPEN cidnum; FETCH cidnum INTO vx; IF cidnum%NOTFOUND THEN Raise_application_error(-20200, 'Database/Instance ' || :dbid || '/' || ' does not exist in DBA_HIST_DATABASE_INSTANCE'); END IF; CLOSE cidnum; -- Check Snapshots exist for Database Id/Instance Number OPEN csnapid; FETCH csnapid INTO :max_snap_time; IF csnapid%NOTFOUND THEN Raise_application_error(-20200, 'No snapshots exist for Database/Instance ' ||:dbid ||'/'); END IF; CLOSE csnapid; END; / whenever SQLERROR CONTINUE; -- -- Ask how many days of snapshots to display set termout ON; column instart_fmt noprint; column inst_name format a12 heading 'Instance'; column db_name format a12 heading 'DB Name'; column snap_id format 99999990 heading 'Snap Id'; column snapdat format a18 heading 'Snap Started' just c; column lvl format 99 heading 'Snap|Level'; prompt prompt prompt specify the NUMBER OF days OF snapshots TO choose FROM prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt entering the NUMBER OF days (n) will result IN the most recent prompt (n) days OF snapshots being listed. pressing without prompt specifying a NUMBER LISTS ALL completed snapshots. prompt prompt set heading OFF; column num_days new_value num_days noprint; SELECT 'Listing ' || Decode(Nvl('&&num_days', 3.14), 0, 'no snapshots', 3.14, 'all Completed Snapshots', 1, 'the last day''s Completed Snapshots', 'the last &num_days days of Completed Snapshots' ), Nvl('&&num_days', 3.14) num_days FROM sys.dual; set heading ON; -- -- List available snapshots break ON inst_name ON db_name ON host ON instart_fmt skip 1; ttitle OFF; SELECT To_char(s.startup_time, 'dd Mon "at" HH24:mi:ss') instart_fmt, di.instance_name inst_name, di.db_name db_name, s.snap_id snap_id, To_char(s.end_interval_time, 'dd Mon YYYY HH24:mi') snapdat, s.snap_level lvl FROM dba_hist_snapshot s, dba_hist_database_instance di WHERE s.dbid = :dbid AND di.dbid = :dbid AND di.dbid = s.dbid AND di.instance_number = s.instance_number AND di.startup_time = s.startup_time AND s.end_interval_time >= Decode(&num_days, 0, To_date('31-JAN-9999', 'DD-MON-YYYY' ), 3.14, s.end_interval_time, To_date(:max_snap_time, 'dd/mm/yyyy') - ( &num_days - 1 )) ORDER BY db_name, instance_name, snap_id; clear break; ttitle OFF; -- -- Ask for the snapshots Id's which are to be compared prompt prompt prompt specify the BEGIN AND END SNAPSHOT ids prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt BEGIN SNAPSHOT id specified: &&begin_snap prompt prompt END SNAPSHOT id specified: &&end_snap prompt -- -- Set up the snapshot-related binds -- variable bid NUMBER; variable eid NUMBER; BEGIN :bid := &begin_snap; :eid := &end_snap; END; / prompt -- -- Ask for Statistics Name Filter -- prompt prompt prompt search statistic prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt search BY STATISTICS name. pressing without prompt specifying anything show ALL STATISTICS. set heading OFF; column stat_search new_value stat_search noprint; SELECT 'Statistic Name Filter: ' || Nvl('&&stat_search', '%'), Nvl('&&stat_search', '%') stat_search FROM sys.dual; set heading ON; column stat_id heading "Statistic ID" format 9999999999999; column name heading "Statistic Name" format a64; column class_name heading "Statistic Class" format a10; SELECT stat_id, ( CASE WHEN class = 1 THEN 'USER' WHEN class = 2 THEN 'REDO' WHEN class = 4 THEN 'ENQUEUE' WHEN class = 8 THEN 'CACHE' WHEN class = 16 THEN 'OS' WHEN class = 32 THEN 'RAC' WHEN class = 40 THEN 'RAC-CACHE' WHEN class = 64 THEN 'SQL' WHEN class = 72 THEN 'SQL-CACHE' WHEN class = 128 THEN 'DEBUG' ELSE To_char(class) END ) CLASS_NAME, name FROM v$sysstat WHERE Upper(name) LIKE Trim(Upper('%&stat_search%')) ORDER BY class, name / -- -- Ask for the statistics variable stat_filter_id NUMBER variable stat_filter_name VARCHAR2(64) prompt prompt prompt specify the STATISTICS prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt enter STATISTICS id OR STATISTICS name. prompt BEGIN SELECT To_number('&&stat_input') INTO :stat_filter_id FROM dual; EXCEPTION WHEN invalid_number THEN :stat_filter_name := '&stat_input'; END; / prompt STATISTICS specified : &&stat_input column end_interval_time heading 'Snap Started' format a18 just c; column dbid heading 'DB Id' format a12 just c; column instance_number heading 'Inst|Num' format 99999; column elapsed heading 'Elapsed' format 999999; column stat_value heading 'Stat Value' format 999999999999 column stat_name heading 'Stat Name' format a64 just l; SELECT snap_id, To_char(dbid) DBID, instance_number, elapsed, To_char(end_interval_time, 'dd Mon YYYY HH24:mi') END_INTERVAL_TIME, --stat_name, ( CASE WHEN stat_value > 0 THEN stat_value ELSE 0 END ) STAT_VALUE FROM (SELECT snap_id, dbid, instance_number, elapsed, end_interval_time, stat_name, ( stat_value - Lag (stat_value, 1, stat_value) over ( PARTITION BY dbid, instance_number ORDER BY snap_id) ) AS STAT_VALUE FROM (SELECT snap_id, dbid, instance_number, elapsed, end_interval_time, stat_name, SUM(stat_value) AS STAT_VALUE FROM (SELECT X.snap_id, X.dbid, X.instance_number, Trunc(SN.end_interval_time, 'mi') END_INTERVAL_TIME, X.stat_name, Trunc(( Cast(SN.end_interval_time AS DATE) - Cast(SN.begin_interval_time AS DATE) ) * 86400) ELAPSED, ( CASE WHEN ( X.stat_name = :stat_filter_name OR X.stat_id = :stat_filter_id ) THEN X.value ELSE 0 END ) AS STAT_VALUE FROM dba_hist_sysstat X, dba_hist_snapshot SN, (SELECT instance_number, Min(startup_time) STARTUP_TIME FROM dba_hist_snapshot WHERE snap_id BETWEEN :bid AND :eid GROUP BY instance_number) MS WHERE X.snap_id = sn.snap_id AND X.dbid = sn.dbid AND x.dbid = :dbid AND x.snap_id BETWEEN :bid AND :eid AND SN.startup_time = MS.startup_time AND SN.instance_number = MS.instance_number AND X.instance_number = sn.instance_number AND ( X.stat_name = :stat_filter_name OR X.stat_id = :stat_filter_id )) GROUP BY snap_id, dbid, instance_number, elapsed, end_interval_time, stat_name)); undefine dbid undefine num_days undefine begin_snap undefine end_snap undefine stat_id undefine stat_search undefine stat_filter_name undefine stat_filter_id undefine stat_input
AWR中记录了快照时间内calls to kcmgas的统计值，calls to kcmgas的意义在于通过递归调用获得一个新的SCN，该统计值可以看做SCN增长速度的主要依据。可以用来诊断SCN HEADROOM问题的一种手段。
-- 利用AWR统计值查询SCN历史增长走势
alter session set nls_date_format='yyyy-mm-dd'; set lines 160 pages 1000 echo off feedback off col stat_name for a25 col date_time for a40 col BEGIN_INTERVAL_TIME for a20 col END_INTERVAL_TIME for a20 prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc." WITH sysstat AS (SELECT sn.begin_interval_time begin_interval_time ,sn.end_interval_time end_interval_time ,ss.stat_name stat_name ,ss.value e_value ,lag(ss.value, 1) over(ORDER BY ss.snap_id) b_value FROM dba_hist_sysstat ss, dba_hist_snapshot sn WHERE ss.snap_id = sn.snap_id AND ss.dbid = sn.dbid AND ss.instance_number = sn.instance_number AND ss.dbid = (SELECT dbid FROM v$database) AND ss.instance_number = (SELECT instance_number FROM v$instance) AND ss.stat_name = 'calls to kcmgas') SELECT to_char(begin_interval_time, 'mm/dd/yy_hh24_mi') || to_char(end_interval_time, '_hh24_mi') date_time ,stat_name ,round((e_value - nvl(b_value, 0)) / (extract(DAY FROM(end_interval_time - begin_interval_time)) * 24 * 60 * 60 + extract(hour FROM(end_interval_time - begin_interval_time)) * 60 * 60 + extract(minute FROM(end_interval_time - begin_interval_time)) * 60 + extract(SECOND FROM(end_interval_time - begin_interval_time))) ,0) per_sec FROM sysstat WHERE (e_value - nvl(b_value, 0)) > 0 AND nvl(b_value, 0) > 0 ;
附：查看隐含参数
select name ,value ,decode(isdefault, 'TRUE','Y','N') as "Default" ,decode(ISEM,'TRUE','Y','N') as SesMod ,decode(ISYM,'IMMEDIATE', 'I','DEFERRED', 'D','FALSE', 'N') as SysMod ,decode(IMOD,'MODIFIED','U','SYS_MODIFIED','S','N') as Modified ,decode(IADJ,'TRUE','Y','N') as Adjusted ,description from ( --GV$SYSTEM_PARAMETER select x.inst_id as instance ,x.indx+1 ,ksppinm as name ,ksppity ,ksppstvl as value ,ksppstdf as isdefault ,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM ,decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM ,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD ,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ ,ksppdesc as description from x$ksppi x,x$ksppsv y where x.indx = y.indx and substr(ksppinm,1,1) = '_' and x.inst_id = USERENV('Instance') ) where name like '%&&par%' order by name /
