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異常的因素。