ORACLE SCN增长十分快
本帖最后由 路人呵呵 于 2014-5-5 15:22 编辑有一接口数据库的SCN增长太快了这样正常么,需要打上关于SCN的补丁还是升级数据库?
与接口数据库有大量数据交换的数据仓库SCN增长正常吗?
环境介绍 :
接口服务器
数据库版本信息
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Productio
NLSRTL Version 10.2.0.1.0 - Production
操作系统是 aix 6.1
数据仓库数据库版本信息
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
操作系统是 aix 6.1
主要处理业务
接口服务器(132.77.127.78)的脚本按照接口协议每天从指定FTP下载各种数据文件(主要是几个xx详单(三千万数据量),其它详单一百万不等)然后用sqlload导入接口数据数据库, 数据仓库通过database link 从接口数据库获取数据进行加工处理。这两台数据库与其它数据库也存在有database link,但与其它数据交换量少。
以下是在接口服务器的诊断
1 SCN 健康检查
ScnHealthCheck
--------------------------------------------------------------
Current Date: 2014/05/04 17:33:52
Current SCN: 13769590944337
Version: 10.2.0.1.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
--------------------------------------------------------------
2 在接口服务器(132.77.127.78) 查询scn变化
alter session set nls_date_format='DD/Mon/YYYY HH24:MI:SS';
SELECT tim, gscn,
round(rate),
round((chk16kscn - gscn)/24/3600/16/1024,1) "Headroom"
FROM
(
select tim, gscn, rate,
((
((to_number(to_char(tim,'YYYY'))-1988)*12*31*24*60*60) +
((to_number(to_char(tim,'MM'))-1)*31*24*60*60) +
(((to_number(to_char(tim,'DD'))-1))*24*60*60) +
(to_number(to_char(tim,'HH24'))*60*60) +
(to_number(to_char(tim,'MI'))*60) +
(to_number(to_char(tim,'SS')))
) * (16*1024)) chk16kscn
from
(
select FIRST_TIME tim , FIRST_CHANGE# gscn,
((NEXT_CHANGE#-FIRST_CHANGE#)/
((NEXT_TIME-FIRST_TIME)*24*60*60)) rate
from v$archived_log
where (next_time > first_time)
)
)
order by 1,2;
TIM GSCN ROUND(RATE) Headroom
05/04/2014 04:03:05 13,769,555,678,154 94 72
05/04/2014 11:01:34 13,769,558,043,533 1,662 72
05/04/2014 11:06:41 13,769,558,553,802 1,711 72
05/04/2014 11:11:41 13,769,559,067,209 1,695 72
05/04/2014 11:16:44 13,769,559,580,865 1,642 72
05/04/2014 11:21:55 13,769,560,091,629 1,684 72
05/04/2014 11:27:00 13,769,560,605,127 1,085 72
05/04/2014 11:40:42 13,769,561,496,889 1,677 72
05/04/2014 11:50:29 13,769,562,481,559 1,277 72
05/04/2014 12:02:21 13,769,563,390,544 1,640 72
05/04/2014 12:08:59 13,769,564,043,276 1,677 72
05/04/2014 12:15:29 13,769,564,697,127 1,779 72
05/04/2014 12:21:37 13,769,565,351,779 1,844 72
05/04/2014 12:27:30 13,769,566,002,708 1,793 72
05/04/2014 12:33:35 13,769,566,657,188 1,798 72
05/04/2014 12:39:39 13,769,567,311,841 1,789 72
05/04/2014 12:45:43 13,769,567,962,864 1,826 72
05/04/2014 12:51:41 13,769,568,616,568 1,794 72
05/04/2014 12:57:46 13,769,569,271,466 1,814 72
05/04/2014 13:03:45 13,769,569,922,556 1,677 72
05/04/2014 13:10:15 13,769,570,576,436 1,710 72
05/04/2014 13:16:38 13,769,571,231,207 1,682 72
05/04/2014 13:23:05 13,769,571,882,145 1,743 72
05/04/2014 13:29:20 13,769,572,535,873 1,687 72
05/04/2014 13:35:47 13,769,573,188,881 1,897 72
05/04/2014 13:41:23 13,769,573,826,408 1,949 72
05/04/2014 13:46:50 13,769,574,463,822 1,801 72
05/04/2014 13:53:03 13,769,575,135,636 1,678 72
05/04/2014 13:59:59 13,769,575,833,763 1,426 72
05/04/2014 14:09:48 13,769,576,673,812 1,386 72
05/04/2014 14:18:37 13,769,577,406,911 1,555 72
05/04/2014 14:27:04 13,769,578,195,258 1,341 72
05/04/2014 14:38:23 13,769,579,105,497 1,073 72
05/04/2014 14:43:59 13,769,579,465,945 1,002 72
05/04/2014 14:49:58 13,769,579,825,606 981 72
05/04/2014 14:56:08 13,769,580,188,448 932 72
05/04/2014 15:08:34 13,769,580,883,882 1,617 72
05/04/2014 15:15:44 13,769,581,579,150 1,551 73
05/04/2014 15:23:05 13,769,582,263,037 1,587 73
05/04/2014 15:29:58 13,769,582,918,365 1,660 73
05/04/2014 15:37:02 13,769,583,622,086 1,666 73
05/04/2014 15:43:53 13,769,584,306,934 1,601 73
05/04/2014 15:50:40 13,769,584,958,452 1,522 73
05/04/2014 15:58:23 13,769,585,663,081 1,552 73
05/04/2014 16:05:46 13,769,586,350,835 1,478 73
05/04/2014 16:13:07 13,769,587,002,682 1,498 73
05/04/2014 16:20:56 13,769,587,705,333 1,481 73
05/04/2014 16:28:41 13,769,588,394,032 1,423 73
05/04/2014 16:36:20 13,769,589,047,353 1,555 73
05/04/2014 16:43:46 13,769,589,740,834 1,670 73
05/04/2014 16:50:40 13,769,590,432,057 19 73
3 在接口服务器(132.77.127.78) 从AWR分析出SCN的变化:
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增速迅速变大:
Session altered.
"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."
05/04/14_00_00_01_00 calls to kcmgas 1
05/04/14_01_00_02_01 calls to kcmgas 1
05/04/14_02_01_03_00 calls to kcmgas 2
05/04/14_03_00_04_00 calls to kcmgas 1
05/04/14_04_00_05_00 calls to kcmgas 1
05/04/14_05_00_06_00 calls to kcmgas 10
05/04/14_06_00_07_00 calls to kcmgas 2
05/04/14_07_00_08_00 calls to kcmgas 2
05/04/14_08_00_09_00 calls to kcmgas 4
05/04/14_09_00_10_00 calls to kcmgas 1
05/04/14_10_00_11_00 calls to kcmgas 45
05/04/14_11_00_12_00 calls to kcmgas 749
05/04/14_12_00_13_01 calls to kcmgas 896
05/04/14_13_01_14_00 calls to kcmgas 893
05/04/14_14_00_15_00 calls to kcmgas 652
05/04/14_15_00_16_00 calls to kcmgas 765
05/04/14_16_00_17_00 calls to kcmgas 715
05/04/14_17_00_18_00 calls to kcmgas 2
05/04/14_18_00_19_00 calls to kcmgas 1
05/04/14_19_00_20_00 calls to kcmgas 1
05/04/14_20_00_21_01 calls to kcmgas 6
05/04/14_21_01_22_00 calls to kcmgas 1
05/04/14_22_00_23_00 calls to kcmgas 1
05/04/14_23_00_00_00 calls to kcmgas 1
--------- 接口服务器(132.77.127.78) ------------
--------- 数据仓库(132.77.127.81) ----------------
DATE_TIME STAT_NAME PER_SEC
05/04/14_00_01_01_00 calls to kcmgas 32
05/04/14_01_00_02_00 calls to kcmgas 32
05/04/14_02_00_03_00 calls to kcmgas 31
05/04/14_03_00_04_00 calls to kcmgas 32
05/04/14_04_00_05_01 calls to kcmgas 31
05/04/14_05_01_06_00 calls to kcmgas 24
05/04/14_06_00_07_01 calls to kcmgas 33
05/04/14_07_01_08_00 calls to kcmgas 33
05/04/14_08_00_09_00 calls to kcmgas 29
05/04/14_09_00_10_00 calls to kcmgas 32
05/04/14_10_00_11_00 calls to kcmgas 27
05/04/14_11_00_12_00 calls to kcmgas 32
05/04/14_12_00_13_00 calls to kcmgas 41
05/04/14_13_00_14_00 calls to kcmgas 32
05/04/14_14_00_15_01 calls to kcmgas 27
05/04/14_15_01_16_00 calls to kcmgas 34
05/04/14_16_00_17_00 calls to kcmgas 95
05/04/14_17_00_18_00 calls to kcmgas 63
05/04/14_18_00_19_00 calls to kcmgas 45
05/04/14_19_00_20_00 calls to kcmgas 31
05/04/14_20_00_21_00 calls to kcmgas 32
05/04/14_21_00_22_00 calls to kcmgas 32
05/04/14_22_00_23_00 calls to kcmgas 31
05/04/14_23_00_00_00 calls to kcmgas 32
页:
[1]