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

日志文件大小问题解决方案报告整体解决分两步走目前修改建议：增大日志文件，同时增加日志组个数。争取能达到业务高峰期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_time last_time,round(a.first_time-b.first_time)*24*60,2) minates from v$log_history a,v$log_history b where a.recid=b.recid +1 order by a.first_time desc) where rownum115642010/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.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.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 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#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: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: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: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:5313.5115362010/11/8 9:27:482010/11/8 9:37:239.58115352010/11/8 9:14:022010/11/8 9:27:4813.77115342010/11/8 8:50:342010/11/8 9:14:0223.47115332010/11/8 8:15:572010/11/8 8:50:3434.62115322010/11/8 7:28:492010/11/8 8:15:574711/8 7:28:422010/11/8 7:28:490.12115302010/11/8 4:30:512010/11/8 7:28:42177.85115292010/11/8 1:56:312010/11/8 4:30:51154.33115282010/11/7 22:14:142010/11/8 1:56:31222.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 段落2# NO2 这一段最为频繁 开始时间 13:47:08 截止时间 13:42分#切换次数 12次 600M 115232010/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.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.35115152010/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#NO 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: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:372010/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# NO 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:32: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/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:210.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/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:08: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.3114662010/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:052010/11/7 10:06:2039.25114632010/11/7 8:00:182010/11/7 9:27:0586.78114622010/11/7 7:10:332010/11/7 8:00:1849.75114612010/11/7 6:00:492010/11/7 7:10:3369.73114602010/11/7 2:19:572010/11/7 6:00:49220.87114592010/11/6 22:51:212010/11/7 2:19:57208.6114582010/11/6 19:38:412010/11/6 22:51:21192.67114572010/11/6 18:59:292010/11/6 19:38:4139.2114562010/11/6 18:47:092010/11/6 18: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查看日志组信息 select thread#,group#,sequence#,bytes/1024/1024,members,first_time,status from v$log;THREAD#GROUP#SEQUENCE#BYTES/1024/1024MEMBERSFIRST_TIMESTATUS11115725012010/11/8 20:44:20INACTIVE12115735012010/11/8 21:04:18INACTIVE13115745012010/11/8 21:24:20CURRENT从上述信息可见日志文件仅为50M，对于业务高峰期时数据量很大（全国性数据录入）的环境下显的太小了且是日志组少。数据库DBWR信息：（等第一步方案实施后再进一步诊断）具体措施:select * from gv$bgprocess where name like DBW_ and paddr00 order by inst_id;dbwr进程数每个实例3个（具体是否合适等到调完日志文件大小后再诊断）数据分析过程：选取业务高峰期时段的日志切换信息 截取4段 5分钟 最为频繁的切换日志的大小：日志大小为50M未修改原始参数。(250 + 600 +550 + 450 )/ 4= 462.5 选定高峰期具体时间段为： 2010/11/8 7:28:42 - 2010/11/8 15:09:37时间间隔：27715秒日志切换次数：35次日志文件大小：50M 总体数据量： 35*50=1750M高峰期redo数据产生量：1750M /27715=0.064M/S根据分析结果得到：业务高峰期5分钟切换一次日志，日志文件需设定18.94m业务高峰期3分钟切换一次日志，日志文件需设定为11 m选定高峰期具体时间段为：2010/11/7 12:54:39 - 2010/11/7 13:47:08时间间隔：2609秒日志切换次数：57次日志文件大小：50M 总体数据量： 57*50=2850M 高峰期redo数据产生量：2850/2609约等于 1.1M/S根据分析结果得到：业务高峰期5分钟切换一次日志，日志文件需设定330m业务高峰期3分钟切换一次日志，日志文件需设定为198m最后取三种算法平均值 386.4M 取平均值 为 400M 考虑到相关业务增长 目前方案：将日志文件大小设定为400M并给每个实例增加二个日志组alter database add logfile group 4(/oradata/orcl/REDO0401.LOG,/oradata/orcl/REDO0402.LOG) size 50malter database drop logfile member /oradata/orcl/ REDO0401.LOG;alter database drop logfile member /oradata/orcl/REDO0402.LOGalter database drop logfile member /oradata/orcl/ REDO0401.LOG;alter system switch logfile;ALTER DATABASE DROP LOGFILE GROUP 1alter database drop logfile member /oradata/orcl/redo01_1.logalter database drop logfile member /oradata/orcl/redo01.logalter database drop logfile member D:oracleproduct10.2.0oradataorclREDO02.LOGalter database drop logfile member D:oracleproduct10.2.0oradataorclREDO02.LOG;alter database add logfile member D:oracleproduct10.2.0oradataorclREDO021.LOG to group 2alter database add logfile group 4(/oradata/orcl/REDO0401.LOG,/oradata/orcl/REDO0402.LOG) size 50malter database add