Oracle工作总结——日志文件切换频率的调整

1、日志文件大小问题解决方案报告 整体解决分两步走目前修改建议：増人日志文件，同时増加日志组个数。争取能达到业务高峰期3-5分钟切换一次。平均15-20分钟切一次。第一步：sql> alter session set nls_date_format=1yyyy-mm-dd hh24:mi:ss select * from (select b.recid,b.firsafirst_time last_time,round(a.firsb.first_time)*24*60,2) minates from v$log_history a,v$log_history bwhere a.recid=

2、b.recid +1order by a.firsdesc) where rownum<120recidfirst_timelast_timeminates115722010/11/8 20:44:202010/11/8 21:04:1819.97115712010/11/8 20:43:162010/11/8 20:44:201.07115702010/11/8 18:47:182010/11/8 20:43:16115.97115692010/11/8 17:03:012010/11/8 18:47:18104.28115682010/11/8 16:59:122010/11/8 1

3、7:03:013.82115672010/11/8 16:26:432010/11/8 16:59:1232.48115662010/11/8 15:39:182010/11/8 16:26:4347.42115652010/11/8 15:09:372010/11/8 15:39:1829.68»»»»»»»»»»»»»»»»»»»»»»»»»&

4、#187;»»»115642010/11/8 15:06:072010/11/8 15:09:373.5115632010/11/8 15:05:552010/11/8 15:06:070.2115622010/11/8 15:05:412010/11/8 15:05:550.23115612010/11/8 15:05:282010/11/8 15:05:410.22115602010/11/8 15:03:592010/11/8 15:05:281.48115592010/11/8 14:52:442010/11/8 15:03:5911.2511558201

5、0/11/8 14:35:272010/11/8 14:52:4417.28115572010/11/8 14:21:472010/11/8 14:35:2713.67115562010/11/8 14:10:302010/11/8 14:21:4711.28115552010/11/8 14:00:582010/11/8 14:10:309.53115542010/11/8 13:53:082010/11/8 14:00:587.83115532010/11/8 13:26:042010/11/8 13:53:0827.07115522010/11/8 12:01:152010/11/8 1

6、3:26:0484.82115512010/11/8 11:23:402010/11/8 12:01:1537.58115502010/11/8 11:07:022010/11/8 11:23:4016.63#no1第一段5分钟切换次数5 次 250m115492010/11/8 11:06:202010/11/8 11:07:020.7115482010/11/8 11:05:292010/11/8 11:06:200.85115472010/11/8 11:04:462010/11/8 11:05:290.72115462010/11/8 11:04:022010/11/8 11:04:4

7、60.73115452010/11/8 11:03:162010/11/8 11:04:020.77#115442010/11/8 11:01:442010/11/8 11:03:161.53115432010/11/8 11:00:492010/11/8 11:01:440.92115422010/11/8 10:58:282010/11/8 11:00:492.35115412010/11/8 10:51:542010/11/8 10:58:286.57115402010/11/8 10:34:172010/11/8 10:51:5417.62115392010/11/8 10:0&

8、;002010/11/8 10:34:1726.28115382010/11/8 9:50:53 2010/11/8 10:08:0017.12115372010/11/8 9:37:23 2010/11/8 9:50:53 13.5115362010/11/8 9:27:48 2010/11/8 9:37:23 9.58115352010/11/8 9:14:02 2010/11/8 9:27:48 13.77115342010/11/8 8:50:34 2010/11/8 9:14:02 23.47115332010/11/8 8:15:57 2010/11/8 8:50:34 34.62

9、115322010/11/8 7:28:49 2010/11/8 8:15:57 4711/8 7:2&42 2010/11/8 7:28:49 0.12»»»»»»»»»»»»»»»»»»»»»»»»»»»»»115302010/11/8 4:30:51 2010/11/8

10、 7:28:42 177.85115292010/11/8 1:56:31 2010/11/8 4:30:51 154.33115282010/11/7 22:14:142010/11/8 1:56:31 222.28115272010/11/7 18:21:212010/11/7 22:14:14232.88115262010/11/7 16:05:222010/11/7 18:21:21135.98115252010/11/7 14:32:282010/11/7 16:05:2292.9115242010/11/7 13:47:082010/11/7 14:32:2845.33»

11、»»»»»»»»»»»» 段落 2»»»»»»»»»»>»»»»»»»»>#no2这一段最为频繁 开始时间13:47:08截止时间13:42分#切换次数12次600m115232010/11/7 13:46:112010/11/7 13:47:080.95115222010/11/7 1

12、3:45:382010/11/7 13:46:110.55115212010/11/7 13:45:112010/11/7 13:45:380.45115202010/11/7 13:44:382010/11/7 13:45:110.55115192010/11/7 13:44:162010/11/7 13:44:380.37115182010/11/7 13:43:562010/11/7 13:44:160.33115172010/11/7 13:43:342010/11/7 13:43:560.37115162010/11/7 13:43:132010/11/7 13:43:340.351

13、15152010/11/7 13:42:522010/11/7 13:43:130.35115142010/11/7 13:42:312010/11/7 13:42:520.35115132010/11/7 13:42:072010/11/7 13:42:310.4115122010/11/7 13:41:432010/11/7 13:42:070.4# #n0 3开始时间13:41:43截止时间13:36分#切换次数11次550m115112010/11/7 13:41:192010/11/7 13:41:430.4115102010/11/7 13:40:582010/11/7 13:41

14、:190.35115092010/11/7 13:40:342010/11/7 13:40:580.4115082010/11/7 13:40:012010/11/7 13:40:340.55115072010/11/7 13:39:252010/11/7 13:40:010.6115062010/11/7 13:38:342010/11/7 13:39:250.85115052010/11/7 13:38:012010/11/7 13:38:340.55115042010/11/7 13:37:072010/11/7 13:38:010.9115032010/11/7 13:36:37201

15、0/11/7 13:37:070.5115022010/11/7 13:35:422010/11/7 13:36:370.92115012010/11/7 13:34:542010/11/7 13:35:420.8# #n0 3开始时间13:34:54截止时间13:29分#切换次数9次450m115002010/11/7 13:34:062010/11/7 13:34:540.8114992010/11/7 13:33:072010/11/7 13:34:060.98114982010/11/7 13:32:362010/11/7 13:33:070.52114972010/11/7 13:3

16、2:122010/11/7 13:32:360.4114962010/11/7 13:31:422010/11/7 13:32:120.5114952010/11/7 13:30:512010/11/7 13:31:420.85114942010/11/7 13:30:122010/11/7 13:30:510.65114932010/11/7 13:29:122010/11/7 13:30:121114922010/11/7 13:27:492010/11/7 13:29:121.38#114912010/11/7 13:27:192010/11/7 13:27:490.5114902010

17、/11/7 13:26:542010/11/7 13:27:190.42114892010/11/7 13:26:272010/11/7 13:26:540.45114882010/11/7 13:24:092010/11/7 13:26:272.3114872010/11/7 13:23:282010/11/7 13:24:090.68114862010/11/7 13:22:512010/11/7 13:23:280.62114852010/11/7 13:22:212010/11/7 13:22:510.5114842010/11/7 13:21:532010/11/7 13:22:21

18、0.47114832010/11/7 13:21:272010/11/7 13:21:530.43114822010/11/7 13:20:542010/11/7 13:21:270.55114812010/11/7 13:20:242010/11/7 13:20:540.5114802010/11/7 13:19:392010/11/7 13:20:240.75114792010/11/7 13:18:532010/11/7 13:19:390.77114782010/11/7 13:17:472010/11/7 13:18:531.1114772010/11/7 13:16:182010/

19、11/7 13:17:471.48114762010/11/7 13:15:022010/11/7 13:16:181.27114752010/11/7 13:14:022010/11/7 13:15:021114742010/11/7 13:12:442010/11/7 13:14:021.3114732010/11/7 13:12:052010/11/7 13:12:440.65114722010/11/7 13:11:172010/11/7 13:12:050.8114712010/11/7 13:10:112010/11/7 13:11:171.1114702010/11/7 13:0

20、8:562010/11/7 13:10:111.25114692010/11/7 13:06:342010/11/7 13:08:562.37114682010/11/7 13:05:572010/11/7 13:06:340.62114672010/11/7 12:54:392010/11/7 13:05:5711.3»»»»»»»»»»»»»»»»»»»»»»»&

21、#187;»»»»»»»»»»»»»»»>114662010/11/7 11:11:592010/11/7 12:54:39102.67114652010/11/7 10:06:202010/11/7 11:11:5965.65114642010/11/7 9:27:05 2010/11/7 10:06:2039.25114632010/11/7 8:00:18 2010/11/7 9:27:05 86.78114622010/11/7 7

22、:10:33 2010/11/7 8:00:18 49.75114612010/11/7 6:00:49 2010/11/7 7:10:33 69.73114602010/11/7 2:19:57 2010/11/7 6:00:49 220.87114592010/11/6 22:51:212010/11/7 2:19:57 208.6114582010/11/6 19:38:412010/11/6 22:51:21192.67114572010/11/6 18:59:292010/11/6 19:3&4139.2114562010/11/6 18:47:092010/11/6 18:

23、59:2912.33114552010/11/6 18:34:332010/11/6 18:47:0912.6114542010/11/6 17:55:312010/11/6 18:34:3339.03查看日志组信息selectthread#,group#,sequence#,bytes/1024/1024,members,first_timez status from v$log;thread#group# sequence#bytes/1024/1024 membersfirst_time status11115725012010/11/8 20:44:20inactive12115735

24、012010/11/8 21:04:18inactive13115745012010/11/8 21:24:20current从上述信息可见h志文件仅为50m,对于业务高峰期时数据量很人（全国性数据录入）的 环境下显的太小了且是口志组少。数据库dbwr信息：（等第一步方案实施后再进一步诊断） 具体措施: select * from gv$bgprocess where name like fdbw_f and paddrofoof order by inst_id;| m m < r-| | |dbwr进程数每个实例3个(具体是否合适等到调完日志文件大小后再诊断) 数据分析过程：选取业务

25、高峰期时段的h志切换信息截取4段5分钟最为频繁的切换h志的大小: 日志大小为50m未修改原始参数。(250 + 600 +550 + 450 )/ 4= 462.5选定高峰期具体时间段为：2010/11/8 7:2&42-2010/11/815:09:37时间间隔：27715秒日志切换次数：35次日志文件大小：50m总体数据最：35*50=1750m高峰期 redo 数据产生量：1750m /27715=0.064m/s根据分析结果得到：业务高峰期5分钟切换一次日志，日志文件需设定18.94m业务高峰期3分钟切换一次日志，日志文件需设定为11 m选定高峰期具体时间段为：2010/11/

26、712:54:39 - 2010/11/713:47:08时间间隔：2609秒日志切换次数：57次日志文件大小：50m总体数据最：57*50=2850m高峰期redo数据产生量：2850/2609约等t 1.1 m/s根据分析结果得到：业务高峰期5分钟切换一次日志，日志文件需设定330m业务高峰期3分钟切换一次日志，日志文件需设定为198m最后取三种算法平均值386.4m取平均值为400m考虑到相关业务增长h前方案：将日志文件大小设定为400m并给每个实例増加二个日志组alter database add logfile group 4(7oradata/orcl/red00401.log f

27、7oradata/orcl/red00402.log ) size 50malter database drop logfile member '/oradata/orcl/ redo0401.log'alter database drop logfile member 7oradata/orcl/redo0402.logalter database drop logfile member 7oradata/orcl/redo01_l.log* alter database drop logfile member '/oradata/orcl/redool.log*al

28、terdatabasedroplogfilememberd:oraclgproduct10.2.0oradataorclredo02.log'alterdatabasedroplogfilemember'd:oracleproduct10.2.0oradataorclred002.log,;alterdatabaseaddlogfilemember d:oracleproduct10.2.0oradataorclred0021.log' to group 2 alter database add logfile group 4(7oradata/orcl/red00401.log f7oradata/orcl/red00402.log ) size 50m alter database add lo