Oracle SCN健康状态检查

步骤1、执行以下脚本，查询当前数据库SCN状态SQL> @scnhealthcheck.sql$ vi scnhealthcheck.sql 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    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=30 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 );   dbms_output.put_line( 'SCN Headroom: '||round(C.indicator,2) );   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; /执行结果如下，Result A表示当前SCN状态良好。 步骤2、查询当前数据库SCN自动升级状态，执行以下存储过程 $sqlplus / as sysdba SQL> set serverout on declare v_autorollover_date date; v_target_compat number; v_RSL number; v_hr_in_scn number; v_hr_in_sec number; v_t4 number; v_max_cmpat number; v_isenabled boolean; v_current_compat number; begin dbms_scn.GETCURRENTSCNPARAMS(v_RSL,v_hr_in_scn,v_hr_in_sec,v_current_compat,v_max_cmpat); dbms_scn.GETSCNAUTOROLLOVERPARAMS( v_autorollover_date,v_target_compat,v_isenabled); --dbms_output.put_line('Current SCN compatibility:'||v_current_compat); --dbms_output.put_line('Current SCN RATE:'||round((v_hr_in_scn/v_hr_in_sec)/1024)||'k'); if (v_isenabled) then dbms_output.put_line('AUTO SCN compatibility rollover is ENABLED!!!'); dbms_output.put_line('AUTO rollover time:'||to_char(v_autorollover_date,'YYYY/MM/DD')); dbms_output.put_line('AUTO rollover target value:'||v_target_compat ); else dbms_output.put_line('AUTO SCN compatibility rollover is DISABLED!!!'); end if; end; /执行结果如下，表示已在2019/6/23日进行了SCN相关调整，级别为3，无需相关操作。     步骤3、查询当前数据库DBLINK链接信息 select * from dba_db_links;返回为空表示当前数据库没有db_link，没有导致SCN异常的因素。