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

1、日 志 文 件 大 小 問 題 解 決 方 案 報 告整體解決分兩步走目前修改建議：增大日志文件，同時增加日志組個數(shù)。爭取能達(dá)到業(yè)務(wù)高峰期 3-5分鐘切換一次。平均15-20分鐘切一次。第一步：SQL> alter session set nls_date_format= 'yyyy-mm-dd hh24:mi:ss' select *from (select b . recid, b. first_time,a. first_timelast_time,round(a.first_time- b. first_time) * 24 * 60 , 2)minatesfro

2、m v$log_history a, v$log_history b where a. recid =b. recid +1orderby a. first_timedesc) 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:0

3、12010/11/8 18:47:18104.28115682010/11/8 16:59:122010/11/8 17: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、>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>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

5、/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.25115582010/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.5311554201

6、0/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 13: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#NO111549第一段5分鐘切換次數(shù)2010/11/8 11:06:205 次 250M 2010/11/8 11:07:020.7115482010/11

7、/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:460.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:49

8、2.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:08:002010/11/8 10:34:1726.28115382010/11/8 9:50:532010/11/8 10:08:0017.12115372010/11/8 9:37:232010/11/8 9:50:53 13.5115362010/11/8 9:27:482010/11/8 9:37:23 9.58115352010/11/8 9:14:022010

9、/11/8 9:27:48 13.77115342010/11/8 8:50:342010/11/8 9:14:02 23.47115332010/11/8 8:15:572010/11/8 8:50:34 34.62115322010/11/8 7:28:492010/11/8 8:15:57 4711/8 7:28:422010/11/8 7:28:49 0.12>>>>>>>>>>>>>>>>>>>>>>>>>&g

10、t;>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>115302010/11/8 4:30:512010/11/8 7:28:42 177.85115292010/11/8 1:56:312010/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/

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>>>>>>>>>>>>>>>>>>>>>>>>>>段落2>>>>>>>>>>

12、;>>>>>>>>>>>>>>>>>>>>>>>>>>>># NO2 這一段最為頻繁開始時間13:47:08 截止時間13:42 分#切換次數(shù)12 次 600M115232010/11/7 13:46:112010/11/7 13:47:080.95115222010/11/7 13:45:382010/11/7 13:46:110.55115212010/11/7 13:45:112010/11/7 13:45:380.451

13、15202010/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.35115152010/11/7 13:42:522010/11/7 13:43:130.35115142010/11/7 13:42:312010/11/

14、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#NO 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:190.35115092010/11/7 13:40:342010/11/7 13:40:580.4115082010/11/7 13:40:0

15、12010/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:372010/11/7 13:37:070.5115022010/11/7 13:35:422010/11/7 13:36:370.92115012010/

16、11/7 13:34:542010/11/7 13:35:420.8# NO 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:32:122010/11/7 13:32:360.4114962010/11/7 13:31:422010/11/7 13:32:120.51

17、14952010/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/11/7 13:26:542010/11/7 13:27:190.42114892010/11/7 13:26:272010/11/7 1

18、3: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:210.47114832010/11/7 13:21:272010/11/7 13:21:530.43114822010/11/7 13:20:

19、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/11/7 13:17:471.48114762010/11/7 13:15:022010/11/7 13:16:181.2711475201

20、0/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:08:562010/11/7 13:10:111.25114692010/11/7 13:06:342010/11/7 13:08:562.3

21、7114682010/11/7 13:05:572010/11/7 13:06:340.62114672010/11/7 12:54:392010/11/7 13:05:5711.3>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>&

22、gt;>>>>>>>>>>>>>>>>>>>>>>>>>>114662010/11/7 11:11:592010/11/7 12:54:39102.67114652010/11/710:06:20114642010/11/79:27:05114632010/11/78:0011/77:10:33114612010/11/76:00:49114602010/11/72:19:57114592010/11/622:51

23、:21114582010/11/619:38:41114572010/11/618:59:29114562010/11/618:47:09114552010/11/618:34:33114542010/11/617:55:312010/11/7 11:11:5965.652010/11/7 10:06:2039.252010/11/7 9:27:05 86.782010/11/7 8:00:18 49.752010/11/7 7:10:33 69.732010/11/7 6:00:49 220.872010/11/7 2:19:57 208.62010/11/6 22:51:21192.672

24、010/11/6 19:38:4139.22010/11/6 18:59:2912.332010/11/6 18:47:0912.62010/11/6 18:34:3339.03查看日志組信息select thread# , group# , sequence# , bytes / 1024 / 1024 , members , first time, status fromv$log ;THREAD# GROUP#SEQUENCE# BYTES/1024/1024 MEMBERS FIRST_TIME STATUS111157250 1121157350 12010/11/8 20:44:2

25、02010/11/8 21:04:18INACTIVEINACTIVE1157450 12010/11/8 21:24:20CURRENT從上述信息可見日志文件僅為 50M ,對于業(yè)務(wù)高峰期時數(shù)據(jù)量很大（全國性數(shù)據(jù)錄入）的環(huán)境下顯的太小了且是日志組少。數(shù)據(jù)庫DBWR信息：（等第一步方案實施后再進一步診斷） 具體措施：select * from gv$bgprocess where name like 'DBW_' and paddr<>'00' order by inst_id;dbwr進程數(shù)每個實例3個（具體是否合適等到調(diào)完日志文件大小后再診斷）數(shù)

26、據(jù)分析過程：選取業(yè)務(wù)高峰期時段的日志切換信息截取4段5分鐘 最為頻繁的切換日志的大?。喝罩敬笮?0M未修改原始參數(shù)。（250 + 600 +550 + 450 ）/ 4= 462.5選定高峰期具體時間段為：2010/11/87:28:42 - 2010/11/815:09:37時間間隔：27715秒日志切換次數(shù)：35次日志文件大?。?0M總體數(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選定高峰期具體時間

27、段為：2010/11/712:54:39 - 2010/11/713:47:08時間間隔：2609秒日志切換次數(shù)：57次日志文件大?。?0M總體數(shù)據(jù)量：57*50=2850M高峰期redo數(shù)據(jù)產(chǎn)生量：2850/2609 約等于1.1M/S根據(jù)分析結(jié)果得到：業(yè)務(wù)高峰期5分鐘切換一次日志，日志文件需設(shè)定330m? ?業(yè)務(wù)高峰期3分鐘切換一次日志，日志文件需設(shè)定為198m最后取三種算法平均值386.4M 取平均值為400M考慮到相關(guān)業(yè)務(wù)增長目前方案：將日志文件大小設(shè)定為 400M并給每個實例增加二個日志組alter database add logfile group 4(7oradata/orcl/REDO0401.LOG',7oradata/orcl/REDO0402.LOG') size 50malter database drop logfile member '/oradata/orcl/ REDO0401.LOG'alter database drop logfile member '/oradata/orcl