本文共 5318 字，大约阅读时间需要 17 分钟。

我们都知道，之前在 Oracle 官方支持站点 MOS 上，最近发布了两篇告警文章，引发了用户的广泛关注，这两篇文章分别是：

Oracle Databases Need to be Patched to a Minimum Patchset/PSU/RU level before April 2019 (Doc ID 2361478.1)

Mandatory Patching Requirement for Database Versions 11.2.0.3 or Earlier, Using DB Links (Doc ID 2335265.1)

下面给出官方给出的脚本来监测scn的健康状况

RemRem $Header: rdbms/admin/scnhealthcheck.sql st_server_tbhukya_bug-13498243/8 2012/01/17 03:37:18 tbhukya Exp $RemRem scnhealthcheck.sqlRemRem Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved.RemRem    NAMERem      scnhealthcheck.sql - Scn Health checkRemRem    DESCRIPTIONRem      Checks scn health of a DBRemRem    NOTESRem      .RemRem    MODIFIED   (MM/DD/YY)Rem    tbhukya     01/11/12 - CreatedRemRemdefine LOWTHRESHOLD=10define MIDTHRESHOLD=62define VERBOSE=FALSEset veri off;set feedback off;set serverout onDECLARE 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;/

具体运行如下：

SQL> @scnhealthcheck.sql--------------------------------------------------------------ScnHealthCheck--------------------------------------------------------------Current Date: 2018/12/17 10:02:24Current SCN:  5985871549643Version:      11.2.0.4.0--------------------------------------------------------------Result: A - SCN Headroom is goodApply the latest recommended patchesbased on your maintenance scheduleFor further information review MOS document id 1393363.1---------

转载地址：http://jjhji.baihongyu.com/