scn headroom

Script:挖掘AWR实现查询SCN历史增长走势

2012/11/18 by admin 2 Comments

AWR中记录了快照时间内calls to kcmgas的统计值，calls to kcmgas的意义在于通过递归调用获得一个新的SCN，该统计值可以看做SCN增长速度的主要依据，通过挖掘AWR可以了解SCN的增长走势，对于我们诊断SCN HEADROOM问题有所帮助：

alter session set nls_date_format='dd-mon-yy';
set lines 160 pages 1000 echo off feedback off
col stat_name for a25
col date_time for a40
col BEGIN_INTERVAL_TIME for a20
col END_INTERVAL_TIME for a20
prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."

WITH sysstat AS
 (select sn.begin_interval_time begin_interval_time,
         sn.end_interval_time end_interval_time,
         ss.stat_name stat_name,
         ss.value e_value,
         lag(ss.value, 1) over(order by ss.snap_id) b_value
    from dba_hist_sysstat ss, dba_hist_snapshot sn
   where  ss.snap_id = sn.snap_id
     and ss.dbid = sn.dbid
     and ss.instance_number = sn.instance_number
     and ss.dbid = (select dbid from v$database)
     and ss.instance_number = (select instance_number from v$instance)
     and ss.stat_name = 'calls to kcmgas')
select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char(END_INTERVAL_TIME, '_hh24_mi') date_time,
stat_name,
round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
+ extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60
+ extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec
from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0
/

示例输出如下，可以看到最近快照中SCN增速迅速变大：


DATE_TIME                                STAT_NAME                    PER_SEC
---------------------------------------- ------------------------- ----------
11/27/09_01_00_02_01                     calls to kcmgas                    0
11/27/09_02_01_03_00                     calls to kcmgas                    0
11/27/09_03_00_04_00                     calls to kcmgas                    0
11/27/09_04_00_05_00                     calls to kcmgas                    0
11/27/09_05_00_06_00                     calls to kcmgas                    0
11/27/09_06_00_07_00                     calls to kcmgas                    0
11/27/09_07_00_08_00                     calls to kcmgas                    0
11/27/09_08_00_09_00                     calls to kcmgas                    0
11/27/09_09_00_10_00                     calls to kcmgas                    0
11/27/09_10_00_11_00                     calls to kcmgas                    0
11/27/09_11_00_12_00                     calls to kcmgas                    0
11/27/09_12_00_13_00                     calls to kcmgas                    0
11/27/09_13_00_14_00                     calls to kcmgas                    0
11/27/09_14_00_15_00                     calls to kcmgas                    0
11/27/09_15_00_16_00                     calls to kcmgas                    0
11/27/09_16_00_17_00                     calls to kcmgas                    0
11/27/09_17_00_18_00                     calls to kcmgas                    0
11/27/09_18_00_19_00                     calls to kcmgas                    0
11/27/09_19_00_20_00                     calls to kcmgas                    0
11/27/09_20_00_21_00                     calls to kcmgas                    0
11/27/09_21_00_22_00                     calls to kcmgas                    1
11/27/09_22_00_23_00                     calls to kcmgas                    0
11/27/09_23_00_00_00                     calls to kcmgas                    0
11/28/09_00_00_01_00                     calls to kcmgas                    0
11/28/09_01_00_02_00                     calls to kcmgas                    0
11/28/09_02_00_03_00                     calls to kcmgas                    0
11/28/09_03_00_04_00                     calls to kcmgas                    0
11/28/09_04_00_05_00                     calls to kcmgas                    0
11/28/09_05_00_06_00                     calls to kcmgas                    1
11/28/09_06_00_07_00                     calls to kcmgas                    0
11/28/09_07_00_08_00                     calls to kcmgas                    0
11/28/09_08_00_09_00                     calls to kcmgas                    0
11/28/09_09_00_10_00                     calls to kcmgas                    0
11/28/09_10_00_11_00                     calls to kcmgas                    1
11/28/09_11_00_12_00                     calls to kcmgas                    0
11/28/09_12_00_13_00                     calls to kcmgas                    0
11/28/09_13_00_14_00                     calls to kcmgas                    0
11/28/09_14_00_15_00                     calls to kcmgas                    0
11/28/09_15_00_16_00                     calls to kcmgas                    0
11/28/09_16_00_17_00                     calls to kcmgas                    0
11/28/09_17_00_18_00                     calls to kcmgas                    0
11/28/09_18_00_19_00                     calls to kcmgas                    0
11/28/09_19_00_20_00                     calls to kcmgas                    0
11/28/09_20_00_21_00                     calls to kcmgas                    0
11/28/09_21_00_22_00                     calls to kcmgas                    0
11/28/09_22_00_23_00                     calls to kcmgas                    1
11/28/09_23_00_00_00                     calls to kcmgas                    0
11/29/09_00_00_01_00                     calls to kcmgas                    0
11/29/09_01_00_02_00                     calls to kcmgas                    0
11/29/09_02_00_03_00                     calls to kcmgas                    0
11/29/09_03_00_04_00                     calls to kcmgas                    0
11/29/09_04_00_05_00                     calls to kcmgas                    0
11/29/09_05_00_06_00                     calls to kcmgas                    1
11/29/09_06_00_07_00                     calls to kcmgas                    0
11/29/09_07_00_08_00                     calls to kcmgas                    0
11/29/09_08_00_09_00                     calls to kcmgas                    0
11/29/09_09_00_10_00                     calls to kcmgas                    0
11/29/09_10_00_11_00                     calls to kcmgas                    1
11/29/09_11_00_12_00                     calls to kcmgas                    0
11/29/09_12_00_13_00                     calls to kcmgas                    0
11/29/09_13_00_14_00                     calls to kcmgas                    0
11/29/09_14_00_15_00                     calls to kcmgas                    1
11/29/09_15_00_16_00                     calls to kcmgas                    0
11/29/09_16_00_17_00                     calls to kcmgas                    0
11/29/09_17_00_18_00                     calls to kcmgas                    0
11/29/09_18_00_19_00                     calls to kcmgas                    0
11/29/09_19_00_20_00                     calls to kcmgas                    0
11/29/09_20_00_21_00                     calls to kcmgas                    0
11/29/09_21_00_22_00                     calls to kcmgas                    0
11/29/09_22_00_23_00                     calls to kcmgas                    1
11/29/09_23_00_00_00                     calls to kcmgas                    0
11/30/09_00_00_01_00                     calls to kcmgas                    0
11/30/09_01_00_02_00                     calls to kcmgas                    0
11/30/09_02_00_03_00                     calls to kcmgas                    0
11/30/09_03_00_04_00                     calls to kcmgas                    0
11/30/09_04_00_05_00                     calls to kcmgas                    0
11/30/09_05_00_06_00                     calls to kcmgas                    0
11/30/09_06_00_07_00                     calls to kcmgas                    0
11/30/09_07_00_08_00                     calls to kcmgas                    0
11/30/09_08_00_09_00                     calls to kcmgas                    0
11/30/09_09_00_10_00                     calls to kcmgas                    0
11/30/09_10_00_11_00                     calls to kcmgas                    0
11/30/09_11_00_12_00                     calls to kcmgas                    0
11/30/09_12_00_13_00                     calls to kcmgas                    0
11/30/09_13_00_14_00                     calls to kcmgas                    0
11/30/09_14_00_15_00                     calls to kcmgas                    0
11/30/09_15_00_16_00                     calls to kcmgas                    0
11/30/09_16_00_17_00                     calls to kcmgas                    0
11/30/09_17_00_18_00                     calls to kcmgas                    0
11/30/09_18_00_19_00                     calls to kcmgas                    0
11/30/09_19_00_20_00                     calls to kcmgas                    0
11/30/09_20_00_21_00                     calls to kcmgas                    0
11/30/09_21_00_22_00                     calls to kcmgas                    1
11/30/09_22_00_23_00                     calls to kcmgas                    1
11/30/09_23_00_00_00                     calls to kcmgas                    0
12/01/09_00_00_01_00                     calls to kcmgas                    0
12/01/09_01_00_02_01                     calls to kcmgas                    0
12/01/09_02_01_03_00                     calls to kcmgas                    0
12/01/09_03_00_04_00                     calls to kcmgas                    0
12/01/09_04_00_05_00                     calls to kcmgas                    0
12/01/09_05_00_06_00                     calls to kcmgas                    0
12/01/09_06_00_07_00                     calls to kcmgas                    0
12/01/09_07_00_08_00                     calls to kcmgas                    0
12/01/09_08_00_09_00                     calls to kcmgas                    0
12/01/09_09_00_10_00                     calls to kcmgas                    0
12/01/09_10_00_11_00                     calls to kcmgas                    0
12/01/09_11_00_12_00                     calls to kcmgas                    0
12/01/09_12_00_13_00                     calls to kcmgas                    0
12/01/09_13_00_14_00                     calls to kcmgas                    0
12/01/09_14_00_15_00                     calls to kcmgas                    0
12/01/09_15_00_16_00                     calls to kcmgas                    0
12/01/09_16_00_17_00                     calls to kcmgas                    0
12/01/09_17_00_18_00                     calls to kcmgas                    0
12/01/09_18_00_19_00                     calls to kcmgas                    0
12/01/09_19_00_20_00                     calls to kcmgas                    0
12/01/09_20_00_21_00                     calls to kcmgas                    0
12/01/09_21_00_22_00                     calls to kcmgas                    0
12/01/09_22_00_23_00                     calls to kcmgas                    1
12/01/09_23_00_00_00                     calls to kcmgas                    0
12/02/09_00_00_01_00                     calls to kcmgas                    0
12/02/09_01_00_02_00                     calls to kcmgas                    0
12/02/09_02_00_03_00                     calls to kcmgas                    0
12/02/09_03_00_04_00                     calls to kcmgas                    0
12/02/09_04_00_05_00                     calls to kcmgas                    0
12/02/09_05_00_06_00                     calls to kcmgas                    0
12/02/09_06_00_07_00                     calls to kcmgas                    0
12/02/09_07_00_08_00                     calls to kcmgas                    0
12/02/09_08_00_09_00                     calls to kcmgas                    0
12/02/09_09_00_10_00                     calls to kcmgas                    0
12/02/09_10_00_11_00                     calls to kcmgas                    0
12/02/09_11_00_12_00                     calls to kcmgas                    0
12/02/09_12_00_13_00                     calls to kcmgas                    0
12/02/09_13_00_14_00                     calls to kcmgas                    0
12/02/09_14_00_15_00                     calls to kcmgas                    0
12/02/09_15_00_16_00                     calls to kcmgas                    0
12/02/09_16_00_17_00                     calls to kcmgas                    0
12/02/09_17_00_18_00                     calls to kcmgas                    0
12/02/09_18_00_19_00                     calls to kcmgas                    0
12/02/09_19_00_20_00                     calls to kcmgas                    0
12/02/09_20_00_21_00                     calls to kcmgas                    0
12/02/09_21_00_22_00                     calls to kcmgas                    1
12/02/09_22_00_23_00                     calls to kcmgas                    1
12/02/09_23_00_00_00                     calls to kcmgas                    0
12/03/09_00_00_01_00                     calls to kcmgas                    1
12/03/09_01_00_02_00                     calls to kcmgas                    0
12/03/09_02_00_03_00                     calls to kcmgas                    0
12/03/09_03_00_04_00                     calls to kcmgas                    0
12/03/09_04_00_05_00                     calls to kcmgas                    0
12/03/09_05_00_06_00                     calls to kcmgas                    0
12/03/09_06_00_07_00                     calls to kcmgas                    0
12/03/09_07_00_08_00                     calls to kcmgas                    0
12/03/09_08_00_09_00                     calls to kcmgas                    0
12/03/09_09_00_10_00                     calls to kcmgas                    0
12/03/09_10_00_11_00                     calls to kcmgas                    0
12/03/09_11_00_12_00                     calls to kcmgas                    0
12/03/09_12_00_13_00                     calls to kcmgas                    0
12/03/09_13_00_14_00                     calls to kcmgas                    0
12/03/09_14_00_15_00                     calls to kcmgas                    0
12/03/09_15_00_16_00                     calls to kcmgas                    0
12/03/09_16_00_17_00                     calls to kcmgas                    0
12/03/09_17_00_18_00                     calls to kcmgas                    0
12/03/09_18_00_19_00                     calls to kcmgas                    0
12/03/09_19_00_20_00                     calls to kcmgas                    0
12/03/09_20_00_21_00                     calls to kcmgas                    0
12/03/09_21_00_22_00                     calls to kcmgas                    0
12/03/09_22_00_23_00                     calls to kcmgas                    1
12/03/09_23_00_00_00                     calls to kcmgas                    0
12/04/09_00_00_01_00                     calls to kcmgas                    1
12/04/09_01_00_02_00                     calls to kcmgas                    0
12/04/09_02_00_03_00                     calls to kcmgas                    0
12/04/09_03_00_04_00                     calls to kcmgas                    0
12/04/09_04_00_05_00                     calls to kcmgas                    0
12/04/09_05_00_06_00                     calls to kcmgas                    0
12/04/09_06_00_07_00                     calls to kcmgas                    0
12/04/09_07_00_08_00                     calls to kcmgas                    0
12/04/09_08_00_09_00                     calls to kcmgas                    0
12/04/09_09_00_10_00                     calls to kcmgas                    0
12/04/09_10_00_11_00                     calls to kcmgas                    0
12/04/09_11_00_12_00                     calls to kcmgas                    0
12/04/09_12_00_13_00                     calls to kcmgas                    0
12/04/09_13_00_14_00                     calls to kcmgas                    0
12/04/09_14_00_15_00                     calls to kcmgas                    0
12/04/09_15_00_16_00                     calls to kcmgas                    0
12/04/09_16_00_17_00                     calls to kcmgas                    0
12/04/09_17_00_18_00                     calls to kcmgas                    0
12/04/09_18_00_19_00                     calls to kcmgas                    0
12/04/09_19_00_20_00                     calls to kcmgas                    0
12/04/09_20_00_21_00                     calls to kcmgas                    0
12/04/09_21_00_22_00                     calls to kcmgas                    1
12/04/09_22_00_23_00                     calls to kcmgas                    0
12/04/09_23_00_00_00                     calls to kcmgas                    0
12/05/09_00_00_01_00                     calls to kcmgas                    1
12/05/09_01_00_02_00                     calls to kcmgas                    0
12/05/09_02_00_03_00                     calls to kcmgas                    0
12/05/09_03_00_04_00                     calls to kcmgas                    0
12/05/09_04_00_05_00                     calls to kcmgas                    0
12/05/09_05_00_06_00                     calls to kcmgas                    1
12/05/09_06_00_07_00                     calls to kcmgas                    0
12/05/09_07_00_08_00                     calls to kcmgas                    0
12/05/09_08_00_09_00                     calls to kcmgas                    0
12/05/09_09_00_10_00                     calls to kcmgas                    0
12/05/09_10_00_11_00                     calls to kcmgas                    1
12/05/09_12_00_12_37                     calls to kcmgas                    9
12/05/09_12_37_12_38                     calls to kcmgas                  581

Filed Under: Oracle, Oracle脚本script Tagged With: scn headroom

SCN Headroom与时光倒流到1988年的Oracle数据库

2012/06/03 by admin 11 Comments

最近一阵关于scn headroom的讨论很热，这是由于在最新的2012 Apr的PSU中例如10.2.0.5上的PSU 13632743和 patch 13916709: SCN: HIGH CALLS TO KCMGAS AFTER APPLYING SCN PATCHES 中引入了对scn增长过快的FIX修复。

Oracle SCN(System Change Number)也叫做系统变更号，Oracle中的Commit操作与SCN紧密相关。

引入SCN的最根本目的在于：

为读一致性所用
为redolog中的记录排序，以及恢复

SCN由SCN Base和Scn Wrap组成，是一种6个字节的结构(structure)。其中SCN Base占用4个字节，而SCN wrap占用2个字节。但在实际存储时SCN-like的stucture常会占用8个字节。

ub4 kscnbas
ub2 kscnwrp

struct kcvfhcrs, 8 bytes                 @100                              Creation Checkpointed at scn
ub4 kscnbas                        @100      0x000a8849              www.askmac.cn
ub2 kscnwrp                        @104      0x0000

当事务提交COMMIT时，需要完成第一个操作就是得到一个SCN值。

SCN是Oracle数据库内部的一种逻辑时间戳，通过SCN将数据库内的事件理清次序，这是保证事务属性ACID的必要信息。

数据库使用SCN来帮助实现查询和跟踪变化。举例来说，当一个事务更新一行数据，那么数据库就需要将该update发生时的SCN记录下来，该事务(transaction)中的其他修改操作通常都会使用同样的SCN。当一个事务commit提交，数据库又会相应地记录提交时的SCN。多个事务同事commit可能会共享同一个SCN。

SCN总是单调递增的序列， Oracle数据库最大可以使用到的SCN上限值是一个天文数字，目前该上限是281万亿，即281,474,976,710,656(2的48次方)。这是对SCN的硬限制，理论上一个数据库的SCN总是不能超过281万亿，以每秒16K的增速计算，花费557年SCN上限才会被耗尽，作为一个hard limit ，我们很少有机会触及。

除了281万亿的hard limit外， Oracle数据库还存在一种 soft limit 即SCN headroom，为了保证SCN的增长速度不要过于离谱，Oracle使用了一种基于时间的限量供应SCN的系统。

关于headroom ，字典翻译头上空间，实际你可以理解为在一个房间内天花板和头部之间的空间，数据库的Current SCN就是你头部的高度，那么(房间高度-头部高度)=headroom，

在任何时间点上，Oracle数据库均会计算一个当前时间点DB 不能超过的SCN LIMIT上限，注意这里提到了时间点，通俗点说这个SCN LIMIT是随着时间流逝在增加的。
Oracle计算的算法基于从1988年到当前时间点的秒数，再乘上16,384(16k)，用SQL表达就如以下语句：

select
(((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60) +
((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60) +
--www.askmac.cn
(((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'))))*16384 from dual;

变化下公式就是 (current_time-1988) * 16384 – (current_scn) = headroom。
1988到当前时间的秒数 * 16384 – 当前SCN

即SCN headroom是当前SCN 和(1988年到当前时间的秒数* 16384)之间的差距。

通过将SCN的增长率和时间流逝关联起来，确保SCN的限量增长，保证Oracle数据库理论上可以处理500年的数据。

通过以上公式我们可以发现SCN每秒的合理增长量为16,384，然而Oracle公司在近年发现某些软件层面的BUG会导致数据库试图超过或接近这个当前时间点的SCN 最大值。
常规情况下，若数据库尝试超过当前的SCN最大值，数据库将会cancel取消掉引发该超越事件的事务，在应用程序层面将看到一个错误。在接下来的一秒钟，上限值会随着时间而增长16k，因此通常应用程序会在短暂停顿后继续工作。但是在极少数情况下，数据库可能需要为了保护自身的完整性而shutdown关闭，理论上不会造成数据丢失或corrupted。

类似于计算机网络中的时钟同步，当2个数据库通过DBLINK相互交流访问时，他们会选用2者中当前Current SCN最大的一个来同步SCN，譬如说数据库A 的SCN 是10000，而数据库B是20000，当2者发生DBLINK联系时，将会用最大的SCN (20000)来同步，数据库A的SCN将jump跳跃到20000。在一些环境中，往往不是本地数据库触发了SCN快速增长的bug，而是众多数据库中的某一个存在活跃的SCN BUG，而其他数据库与该问题数据库发生DBLINK联系后，就会因为SCN同步而经历 SCN headroom的的极速减少；换句话说就是一只老鼠坏了一锅汤，异常高的SCN会通过DBLINK传播给正常的数据库，这种传播往往呈爆炸式发展。
由于数据库总是会拒绝SCN超过当前的SCN上限，所以Oracle官方宣称Oracle数据库理论运行500年的SCN预备量不会受以上问题的影响。但是受到传播的数据库仍可能由于自我保护的原因而宕机。

Oracle官方宣布所有与SCN headroom相关的bug均已在January 2012 CPU 和相关的PSU中修复了，同样的修复补丁也被包含在DB Patchset Update (PSU) 以及最新的Exadata和Windows的Bundle Patch上。

有一些客户纠结于他们的SCN接近于当前SCN最大值，且SCN的增长量远大于他们处理数据库的合理值。在所有这些cases中Oracle 发现均是January 2012 CPU 中已经修复bug的现象，在客户实施这些修复后SCN headroom 开始有效增长了。

为了保证系统不出现潜在的问题，用户可以运行Metalink Note”Installing, Executing and Interpreting output from the “scnhealthcheck.sql” script [ID 1393363.1]”中包含的脚本scnhealthcheck.sql来检查特定数据库的当前SCN距离当前SCN 最大值有多少差距。该脚本会警告用户该数据库接近于当前SCN的最大上限，在这种情况下建议立即对受影响数据库实施CPU/PSU补丁。实施以上补丁后的预期结果是SCN headroom有效增长，官方宣称在所有case中都如预期一样。

Oracle推荐尽可能客户尽可能快地APPLY CPU补丁以处理最新发现的安全问题。

从长远来看Oracle会在今后的版本或补丁中将SCN的hard limit从281万亿提高到一个更高数字。

你肯定好奇于为什么这里要使用 1988年到当前时间的秒数，1988这个年份有什么意义？

我们来看看Oracle数据库中的1988:

[oracle@vrh8 ~]$ strings $ORACLE_HOME/bin/oracle > oracle.log
[oracle@vrh8 ~]$ grep 1988  oracle.log
1988
xsaggr.c:1988
Version: %d {0 = 1988 }

SQL>
SQL> select  * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump controlf 4
Statement processed.
SQL> oradebug tracefile_name;
/s01/admin/G10R25/udump/g10r25_ora_9823.trc

THREAD #1 - status:0x2 flags:0x0 dirty:252
low cache rba:(0xe.7742.0) on disk rba:(0xe.7b44.0)
on disk scn: 0x0000.002d4ac2 06/03/2012 10:03:34
resetlogs scn: 0x0000.00294b33 05/22/2012 11:33:28
heartbeat: 784994177 mount id: 2670678794
THREAD #2 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #3 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #4 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #5 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #6 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00

***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 428, compat size = 428, section max = 100, section in-use = 8,
last-recid= 421, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
(name #10) /s01/oradata/G10R25/datafile/o1_mf_system_7ch7d4mn_.dbf
creation size=0 block size=8192 status=0xe head=10 tail=10 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:418 scn: 0x0000.002d2072 06/03/2012 03:41:16
Stop scn: 0xffff.ffffffff 05/24/2012 09:51:21
Creation Checkpointed at scn:  0x0000.00000007 04/20/2010 08:24:26

1988年为什么如此重要？

在1988年发布了Oracle V6，首次实现了行级锁定，首次实现了数据库热备份，Oracle公司从Belmont移到加利福尼亚的redwood shores，并引入了PL/SQL。

我相信目前使用版本7-11g 仍沿用了大量的V6的代码，V6的代码中做了大量DEFINE的工作，这大概是一切的开始。

这就好像是”And God said, Let there be light”！

Technical Data and Computer Software (October 1988) 这个所有权著名，你可以在很多Oracle的早起文档上找到。

你肯定又要问，我们可以在1988年之前运行Oracle V6以后的程序吗？假设我们获得了时光机，拿着Oracle 10.2.0.5回到1988年前

[root@vrh8 ~]# date -s "1985-07-25 00:00:00"
Thu Jul 25 00:00:00 EDT 1985

SQL> startup;
ORA-01513: invalid current time returned by operating system

[oracle@vrh8 ~]$ strace -o startup.log  -p 9935
Process 9935 attached - interrupt to quit

SQL> startup;
ORA-01513: invalid current time returned by operating system

[oracle@vrh8 ~]$ oerr ora 01513
01513, 00000, "invalid current time returned by operating system"
// *Cause:  The operating system returned a time that was not between
//          1988 and 2121.
// *Action: Correct the time kept by the operating system.

可以看到 Oracle数据库可运行的时间区间其实是 1988-2121年，500年的SCN headroom其实没什么用处，没有哪个凡人或者DBA等得起5个世纪！

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: scn headroom

_external_scn_rejection_threshold_hour

2012/04/18 by admin 3 Comments

1?Warning - High Database SCN: Current SCN value is 0x0b7b.0008e40b, threshold SCN value is 0x0b75.055dc000
If you have not previously reported this warning on this database,
please notify Oracle Support so that additional diagnosis can be performed.

2?Warning: The SCN headroom for this database is only NN days!

3?Warning: The SCN headroom for this database is only N hours!

4?WARNING: This patch can not take full effect until this RAC database has been completely shutdown and restarted again.
Oracle recommends that it is done at the earliest convenience.

5?Rejected the attempt to advance SCN over limit by 9374 hours worth to 0x0c00.00000f66, by distributed transaction remote logon,
remote DB: REMDB.XX.ORACLE.COM.
Client info : DB logon user ME, machine yy, program sqlplus@yy (TNS V1-V3), and OS user uuu

6?Rejected the attempt to advance SCN over limit by 9375 hours worth to 0x0c00.000003c6, by distributed transaction logon,
remote DB: REMDB.XX.ORACLE.COM.
Client info : DB logon user TC, machine xx, program oracle@xx (TNS V1-V3), and OS user xxx

7?Rejected the attempt to advance SCN over limit by 9374 hours worth to 0x0c00.00000f66, by XXXXX
Client info : DB logon user TC, machine mmm, program sqlplus@mmm (TNS V1-V3), and OS user uuu

Where XXXXX is a string such as:
? PL/SQL RPC (remote)
? sql exec with curSCN
? sql exec with outSCN

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)

VERSION DATE_TIME CURRENT_SCN INDICATOR
----------------- ------------------- ----------- ----------
11.2.0.3.0 2012/05/17 12:38:49 2775567 9068.525

------------------------------------------------------------
ScnHealthCheck
------------------------------------------------------------
Current Date: 2012/01/17 01:01:09
Current SCN: 384089
Version: 11.1.0.7.0
------------------------------------------------------------
Result: A - SCN Headroom is good
Apply the latest recommended patches
based on your maintenance schedule
AND set _external_scn_rejection_threshold_hours=24 after apply.
For further information review MOS document id 1393363.1
------------------------------------------------------------

In addition to the above result the script output may advise to set the hidden parameter
"_external_scn_rejection_threshold_hours" on some Oracle versions.
The following text gives more information about setting this parameter:

Set _external_scn_rejection_threshold_hours=24 after apply
The hidden parameter "_external_scn_rejection_threshold_hours" is introduced in J
anuary 2012 Critical Patch Update (CPU) and Patch Set Update (PSU) releases (and related bundles).
Oracle recommends setting this parameter to the value 24 in 10g and 11.1 releases -
it does not need to be set in 11.2 releases.
The parameter is static and so must be set in the init.ora or spfile used to start the instance.
eg:

In init.ora:
# Set threshold on dd/mon/yyyy - See MOS Document 1393363.1
_external_scn_rejection_threshold_hours = 24

In the spfile:
alter system set "_external_scn_rejection_threshold_hours" = 24
comment='Set threshold on dd/mon/yyyy - See MOS Document 1393363.1'
scope=spfile ;

Why do I need to set "_external_scn_rejection_threshold_hours"=24 ?
Oracle has many hidden parameters which all have default or derived values, and those parameters are
not generally intended to be set by customers. Oracle determined that the most suitable value for this
new hidden parameter is "24" and that this value should be used across all releases.
10g and 11.1 January 2012 CPU / PSU releases have a different default value compiled in and so this
setting has to be made explicitly on those releases to ensure that the required value of 24 is used.
Customers are not expected to tune this value themselves.

[oracle@vrh1 ~]$ oerr ora 19706
19706, 00000, "invalid SCN"
// *Cause: The input SCN is either not a positive integer or too large.
// *Action: Check the input SCN and make sure it is a valid SCN.

The system change number (SCN) is a logical, internal timestamp used by the Oracle Database.
SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction.

The database uses SCNs to query and track changes. For example, if a transaction updates a row,
then the database records the SCN at which this update occurred. Other modifications in this transaction typically
have the same SCN. When a transaction commits, the database records an SCN for this commit.
Multiple transactions that commit at the same time may share the same SCN.

SCNs occur in a monotonically increasing sequence, and there is a very large upper
limit to how many SCNs an Oracle Database can use - that limit is currently 281 trillion,
or specifically 281,474,976,710,656 (is 2^48) SCN values.

Given that there is an upper limit, it is important that any given Oracle Database does not
run out of available SCNs. The Oracle Database uses a time based rationing system to ensure that this does not happen.

At any point in time, the Oracle Database calculates a "not to exceed" limit for the number
of SCNs a database can have used, based on the number of seconds elapsed since 1988, multiplied by 16,384.

This is known as the database's current maximum SCN limit.
Doing this ensures that Oracle Databases will ration SCNs over time,
allowing over 500 years of data processing for any Oracle Database.

The difference between the current SCN the database is using, and the "not to exceed" upper limit,
is known as the SCN headroom. For almost all Oracle Databases, this headroom is constantly increasing every second.

However, Oracle has determined that some software bugs could cause the database to attempt to exceed
the current maximum SCN value (or get closer to the limit than was warranted).

Generally if the database does try to exceed the current maximum SCN value, the transaction that
caused this event would be cancelled by the database, and the application would see an error.

The next second the limit increases, so typically the application then continues with a slight hiccough in processing.
However, in some very rare cases, the database does need to shut down to preserve its integrity.
In no cases is data lost or corrupted.

Similar to how clocks are kept synchronized in a computer network, when two databases communicate
with each other over a database link, they synchronize their SCNs by picking the largest SCN in use by the two.
So in some cases, databases experienced rapidly decreasing SCN headroom not because of a bug in that specific database,
but because the bug was active in one or more of the databases that database was connected to. Since the database always
rejects SCNs that exceed the current maximum SCN, the provision of being able to run Oracle Databases for more than 500 years
was not affected in any of the cases.

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: dblink, scn headroom