在2012年第一季度的CPU補(bǔ)丁中，包含了一個(gè)關(guān)于SCN修正的重要變更，這個(gè)補(bǔ)丁提示，在異常情況下，Oracle的SCN可能出現(xiàn)異常增長(zhǎng)，使得數(shù)據(jù)庫(kù)的一切事務(wù)停止，由于SCN不能后退，所以數(shù)據(jù)庫(kù)必須重建，才能夠重用。
我曾經(jīng)在以下鏈接中描述過(guò)這個(gè)問(wèn)題：
http://www.eygle.com/archives/2012/03/oracle_scn_bug_exhaused.html
Oracle使用6 Bytes記錄SCN，也就是48位，其最大值是：
SQL> col scn for 999,999,999,999,999,999 SQL> select power(2,48) scn from dual; SCN ------------------------ 281,474,976,710,656
Oracle在內(nèi)部控制每秒增減的SCN不超過(guò) 16K，按照這樣計(jì)算，這個(gè)數(shù)值可以使用大約544年：
SQL> select power(2,48) / 16 / 1024 / 3600 / 24 / 365 from dual; POWER(2,48)/16/1024/3600/24/365 ------------------------------- 544.770078
然而在出現(xiàn)異常時(shí)，尤其是當(dāng)使用DB Link跨數(shù)據(jù)庫(kù)查詢時(shí)，SCN會(huì)被同步，在以下鏈接中，我曾經(jīng)描述過(guò)此問(wèn)題：
http://www.eygle.com/archives/2006/11/db_link_checkpoint_scn.html
一個(gè)數(shù)據(jù)庫(kù)當(dāng)前最大的可能SCN被稱為"最大合理SCN"，該值可以通過(guò)如下方式計(jì)算：
col scn for 999,999,999,999,999,999 select ( ( ( ( ( ( to_char(sysdate,'YYYY')-1988 )*12+ to_char(sysdate,'mm')-1 )*31+to_char(sysdate,'dd')-1 )*24+to_char(sysdate,'hh24') )*60+to_char(sysdate,'mi') )*60+to_char(sysdate,'ss') ) * to_number('ffff','XXXXXXXX')/4 scn from dual / 這個(gè)算法即SCN算法，以1988年1月1日 00點(diǎn)00時(shí)00分開(kāi)始，每秒計(jì)算1個(gè)點(diǎn)數(shù)，最大SCN為16K。
這個(gè)內(nèi)容可以參考如下鏈接：
http://www.eygle.com/archives/2006/01/how_big_scn_can_be.html
在CPU補(bǔ)丁中，Oracle提供了一個(gè)腳本 scnhealthcheck.sql 用于檢查數(shù)據(jù)庫(kù)當(dāng)前SCN的剩余情況。
該腳本的算法和以上描述相同，最終將最大合理SCN 減去當(dāng)前數(shù)據(jù)庫(kù)SCN，計(jì)算得出一個(gè)指標(biāo)：HeadRoom。也就是SCN尚余的頂部空間，這個(gè)頂部空間最后折合成天數(shù)：
以下是這個(gè)腳本的內(nèi)容：
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=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('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; / 在應(yīng)用補(bǔ)丁之后，一個(gè)新的隱含參數(shù) _external_scn_rejection_threshold_hours 引入，通常設(shè)置該參數(shù)為 24 小時(shí)：
_external_scn_rejection_threshold_hours=24
這個(gè)設(shè)置降低了SCN Headroom的頂部空間，以前缺省的設(shè)置容量至少為31天，降低為 24 小時(shí)，tb可以增大SCN允許增長(zhǎng)的合理空間。
但是如果不加控制，SCN仍然可能會(huì)超過(guò)最大的合理范圍，導(dǎo)致數(shù)據(jù)庫(kù)問(wèn)題。
這個(gè)問(wèn)題的影響會(huì)極其嚴(yán)重，我們建議用戶檢驗(yàn)當(dāng)前數(shù)據(jù)庫(kù)的SCN使用情況，以下是檢查腳本的輸出范例：
-------------------------------------- ScnHealthCheck -------------------------------------- Current Date: 2012/01/15 14:17:49 Current SCN: 13194140054241 Version: 11.2.0.2.0 -------------------------------------- Result: C - SCN Headroom is low If you have not already done so apply the latest recommended patches right now AND contact Oracle support immediately. For further information review MOS document id 1393363. -------------------------------------- 這個(gè)問(wèn)題已經(jīng)出現(xiàn)在客戶環(huán)境中，需要引起大家的足夠重視。