Oracle工作總結(jié)——日志文件切換頻率的調(diào)整

1、日志文件大小問題解決方案報告 整體解決分兩步走目前修改建議：増人日志文件，同時増加日志組個數(shù)。爭取能達(dá)到業(yè)務(wù)高峰期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分鐘切換次數(shù)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分#切換次數(shù)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分#切換次數(shù)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分#切換次數(shù)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,對于業(yè)務(wù)高峰期時數(shù)據(jù)量很人（全國性數(shù)據(jù)錄入）的 環(huán)境下顯的太小了且是口志組少。數(shù)據(jù)庫dbwr信息：（等第一步方案實施后再進(jìn)一步診斷） 具體措施: select * from gv$bgprocess where name like fdbw_f and paddrofoof order by inst_id;| m m < r-| | |dbwr進(jìn)程數(shù)每個實例3個(具體是否合適等到調(diào)完日志文件大小后再診斷) 數(shù)據(jù)分析過程：選取業(yè)務(wù)

25、高峰期時段的h志切換信息截取4段5分鐘最為頻繁的切換h志的大小: 日志大小為50m未修改原始參數(shù)。(250 + 600 +550 + 450 )/ 4= 462.5選定高峰期具體時間段為：2010/11/8 7:2&42-2010/11/815:09:37時間間隔：27715秒日志切換次數(shù)：35次日志文件大小：50m總體數(shù)據(jù)最：35*50=1750m高峰期 redo 數(shù)據(jù)產(chǎn)生量：1750m /27715=0.064m/s根據(jù)分析結(jié)果得到：業(yè)務(wù)高峰期5分鐘切換一次日志，日志文件需設(shè)定18.94m業(yè)務(wù)高峰期3分鐘切換一次日志，日志文件需設(shè)定為11 m選定高峰期具體時間段為：2010/11/

26、712:54:39 - 2010/11/713:47:08時間間隔：2609秒日志切換次數(shù)：57次日志文件大?。?0m總體數(shù)據(jù)最：57*50=2850m高峰期redo數(shù)據(jù)產(chǎn)生量：2850/2609約等t 1.1 m/s根據(jù)分析結(jié)果得到：業(yè)務(wù)高峰期5分鐘切換一次日志，日志文件需設(shè)定330m業(yè)務(wù)高峰期3分鐘切換一次日志，日志文件需設(shè)定為198m最后取三種算法平均值386.4m取平均值為400m考慮到相關(guān)業(yè)務(wù)增長h前方案：將日志文件大小設(shè)定為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