scnhealthcheck.sql脚本

Installing, Executing and Interpreting output from the "scnhealthcheck.sql" script (文档 ID 1393363.1)中提到对scn的增长与检查。

Patch:13498243 中有此脚本，下面把脚本放出来了。

RemRem $Header: rdbms/admin/scnhealthcheck.sql apfwkr_blr_backport_13498243_12.1.0.2.0/1 2015/05/26 22:44:51 apfwkr Exp $RemRem scnhealthcheck.sqlRemRem Copyright (c) 2012, 2015, Oracle and/or its affiliates. Rem 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;/