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

1、日志文件大小问题解决方案报告整体解决分两步走目前修改建议：增大日志文件，同时增加日志组个数。争取能达到业务高峰期 3-5分钟切换一次。平均15-20分钟切一次 第一步：SQL alter session set nls_date_format= yyyy-mm-dd hh24:mi:ss select * from (select , last_time ,round( minatesfrom v$log_history a,v$log_history bwhere = + 1order bydesc) where rownum115642010/11/8 15:06:072010/11/8

2、15:09:37115632010/11/8 15:05:552010/11/8 15:06:07115622010/11/8 15:05:412010/11/8 15:05:55115612010/11/8 15:05:282010/11/8 15:05:41115602010/11/8 15:03:592010/11/8 15:05:28115592010/11/8 14:52:442010/11/8 15:03:59115582010/11/8 14:35:272010/11/8 14:52:44115572010/11/8 14:21:472010/11/8 14:35:2711556

3、2010/11/8 14:10:302010/11/8 14:21:47115552010/11/8 14:00:582010/11/8 14:10:30115542010/11/8 13:53:082010/11/8 14:00:58115532010/11/8 13:26:042010/11/8 13:53:08115522010/11/8 12:01:152010/11/8 13:26:04115512010/11/8 11:23:402010/11/8 12:0111/8 11:07:022010/11/8 11:23:40#NO1第一段 5 分钟切换次数5

4、次 250M115492010/11/8 11:06:202010/11/8 11:07:02115482010/11/8 11:05:292010/11/8 11:06:20115472010/11/8 11:04:462010/11/8 11:05:29115462010/11/8 11:04:022010/11/8 11:04:46115452010/11/8 11:03:162010/11/8 11:04:02II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II

5、 II II II II II II II II II II II II II II II II II II II II II II a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a115442010/11/8 11:01:442010/11/8 11:03:16115432010/11/8 11:00:492010/11/8 11:01:44115422010/11/8 10:58:282010/11/8 11:00:491154120

6、10/11/8 10:51:542010/11/8 10:58:28115402010/11/8 10:34:172010/11/8 10:51:54115392010/11/8 10:08:002010/11/8 10:3411/8 9:50:532010/11/8 10:08:00115372010/11/8 9:37:232010/11/8 9:50:53115362010/11/8 9:27:482010/11/8 9:37:23115352010/11/8 9:14:022010/11/8 9:27:48115342010/11/8 8:50:342010/

7、11/8 9:14:02115332010/11/8 8:15:572010/11/8 8:50:34115322010/11/8 7:28:492010/11/8 8:15:57115312010/11/8 7:28:422010/11/8 7:28:49115302010/11/8 4:30:512010/11/8 7:28:42115292010/11/8 1:56:312010/11/8 4:30:51115282010/11/7 22:14:142010/11/8 1:56:31115272010/11/7 18:21:212010/11/7 22:1411

8、/7 16:05:222010/11/7 18:21:21115252010/11/7 14:32:282010/11/7 16:05:22115242010/11/7 13:47:082010/11/7 14:32:28 段落# NO2 这一段最为频繁 开始时间 13:47:08 截止时间 13:42 分# 切换次数 12 次 600M115232010/11/7 13:46:112010/11/7 13:47:08115222010/11/7 13:45:382010/11/7 13:46:11115212010/11/7 13:45:112010/11/7 13:45:381152020

9、10/11/7 13:44:382010/11/7 13:45:11115192010/11/7 13:44:162010/11/7 13:44:38115182010/11/7 13:43:562010/11/7 13:44:16115172010/11/7 13:43:342010/11/7 13:43:56115162010/11/7 13:43:132010/11/7 13:43:34115152010/11/7 13:42:522010/11/7 13:4311/7 13:42:312010/11/7 13:42:52115132010/11/7 13:42

10、:072010/11/7 13:42:31115122010/11/7 13:41:432010/11/7 13:42:07II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a

11、 a a a a a a a a a a a a a a a a a a a a a a a a a a a a#NO 3开始时间 13:41:43截止时间 13:36 分# 切换次数 11 次 550M115112010/11/7 13:41:192010/11/7 13:41:43115102010/11/7 13:40:582010/11/7 13:41:19115092010/11/7 13:40:342010/11/7 13:40:58115082010/11/7 13:40:012010/11/7 13:40:34115072010/11/7 13:39:252010/11/7 1

12、3:40:01115062010/11/7 13:38:342010/11/7 13:39:25115052010/11/7 13:38:012010/11/7 13:38:34115042010/11/7 13:37:072010/11/7 13:38:01115032010/11/7 13:36:372010/11/7 13:37:07115022010/11/7 13:35:422010/11/7 13:36:37115012010/11/7 13:34:542010/11/7 13:35:42II II II II II II II II II II II II II II II II

13、 II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II II a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a# NO 3 开始时间 13:34:54 截止时间 13:29 分# 切换次数 9 次 450M11500

14、2010/11/7 13:34:062010/11/7 13:34:54114992010/11/7 13:33:072010/11/7 13:34:06114982010/11/7 13:32:362010/11/7 13:33:07114972010/11/7 13:32:122010/11/7 13:32:36114962010/11/7 13:31:422010/11/7 13:32:12114952010/11/7 13:30:512010/11/7 13:31:42114942010/11/7 13:30:122010/11/7 13:30:51114932010/11/7 13:

15、29:122010/11/7 13:30:121114922010/11/7 13:27:492010/11/7 13:29:12a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a11491201

16、0/11/7 13:27:192010/11/7 13:27:49114902010/11/7 13:26:542010/11/7 13:27:19114892010/11/7 13:26:272010/11/7 13:26:54114882010/11/7 13:24:092010/11/7 13:26:27114872010/11/7 13:23:282010/11/7 13:24:09114862010/11/7 13:22:512010/11/7 13:23:28114852010/11/7 13:22:212010/11/7 13:22:51114842010/11/7 13:21:

17、532010/11/7 13:22:21114832010/11/7 13:21:272010/11/7 13:21:53114822010/11/7 13:20:542010/11/7 13:21:27114812010/11/7 13:20:242010/11/7 13:20:54114802010/11/7 13:19:392010/11/7 13:20:24114792010/11/7 13:18:532010/11/7 13:19:39114782010/11/7 13:17:472010/11/7 13:18:53114772010/11/7 13:16:182010/11/7 1

18、3:17:47114762010/11/7 13:15:022010/11/7 13:1611/7 13:14:022010/11/7 13:15:021114742010/11/7 13:12:442010/11/7 13:14:02114732010/11/7 13:12:052010/11/7 13:12:44114722010/11/7 13:11:172010/11/7 13:12:05114712010/11/7 13:10:112010/11/7 13:1111/7 13:08:562010/11/7 13:10:1111469

19、2010/11/7 13:06:342010/11/7 13:08:56114682010/11/7 13:05:572010/11/7 13:06:34114672010/11/7 12:54:392010/11/7 13:05:57114662010/11/7 11:11:592010/11/7 12:54:39114652010/11/7 10:06:202010/11/7 11:11:59114642010/11/7 9:27:052010/11/7 10:06:20114632010/11/7 8:00:182010/11/7 9:27:05114622010/11/7 7:10:3

20、32010/11/7 8:0011/7 6:00:492010/11/7 7:10:33114602010/11/7 2:19:572010/11/7 6:00:49114592010/11/6 22:51:212010/11/7 2:19:57114582010/11/6 19:38:412010/11/6 22:51:21114572010/11/6 18:59:292010/11/6 19:38:41114562010/11/6 18:47:092010/11/6 18:59:29114552010/11/6 18:34:332010/11/6 18:47:09

21、114542010/11/6 17:55:312010/11/6 18:34:33查看日志组信息,first_time ,status fromselect thread# ,group# ,sequenee# ,bytes /1024 /1024 membersv$log ;THREAD# GROUP#SEQUENCE# BYTES/1024/1024MEMBERS FIRST_TIMESTATUS11115725012010/11/8 20:44:20INACTIVEINACTIVE12115735012010/11/8 21:04:18CURRENT1 3 11574 50 1 2010

22、/11/8 21:24:20从上述信息可见日志文件仅为 50M ，对于业务高峰期时数据量很大（全国性数据录入）的环境下显 的太小了且是日志组少。数据库 DBWR 信息：（等第一步方案实施后再进一步诊断）具体措施 :select * from gv$bgprocess where name like DBW_ and paddr00 order by inst_id; dbwr 进程数每个实例 3 个（具体是否合适等到调完日志文件大小后再诊断） 数据分析过程：选取业务高峰期时段的日志切换信息 截取 4段 5分钟 最为频繁的切换日志的大小： 日志大小为 50M 未修改原始参数。（250 + 600

23、 +550 + 450 ）/ 4=选定高峰期具体时间段为： 2010/11/8 7:28:42 - 2010/11/8 15:09:37 时间间隔： 27715 秒 日志切换次数： 35 次日志文件大小： 50M总体数据量： 35*50=1750M高峰期 redo 数据产生量： 1750M /27715=S根据分析结果得到：业务高峰期 5 分钟切换一次日志，日志文件需设定业务高峰期 3 分钟切换一次日志，日志文件需设定为11 m选定高峰期具体时间段为： 2010/11/7 12:54:39 2010/11/7 13:47:08时间间隔： 2609 秒日志切换次数： 57 次日志文件大小： 50M总体数据量： 57*50=2850M高峰期 redo 数据产生量： 2850/2609 约等于 S根据分析结果得到：业务高峰期 5 分钟切换一次日志，日志文件需设定 330m业务高峰期 3 分钟切换一次日志，日志文件需设定为 198m 最后取三种算法平均值 取平均值 为400M 考虑到相关业务增长目前方案： 将日志文件大小设定为 400M 并给每个实例增加二个日志组alter database add logfile group 4(7oradata/orcl/,/oradata/orcl/) size 50malter database drop logfile