Oracle scn健康状态检查脚本—scnhealthcheck.sql

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

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

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

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

Rem
Rem $Header: rdbms/admin/scnhealthcheck.sql st_server_tbhukya_bug-13498243/8 2012/01/17 03:37:18 tbhukya Exp $
Rem
Rem scnhealthcheck.sql
Rem
Rem Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved.
Rem
Rem    NAME
Rem      scnhealthcheck.sql - Scn Health check
Rem
Rem    DE ION
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(\' d 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(\' d 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:24
Current SCN:  5985871549643
Version:      11.2.0.4.0
--------------------------------------------------------------
Result: A - SCN Headroom is good
Apply the latest recommended patches
 d on your maintenance schedule
For further information review MOS document id 1393363.1
---------