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

日志文件大小问题解决方案报告 整体解决分两步走 目前修改建议：增大日志文件，同时增加日志组个数 争取能达到业务高峰期 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 rownum< 120 RECID FIRST_TIME LAST_TIME MINATES 11572 2010/11/8 20:44:20 2010/11/8 21:04:18 19.97 11571 2010/11/8 20:43:16 2010/11/8 20:44:20 1.07 11570 2010/11/8 18:47:18 2010/11/8 20:43:16 115.97 11569 2010/11/8 17:03:01 2010/11/8 18:47:18 104.28 11568 2010/11/8 16:59:12 2010/11/8 17:03:01 3.82 11567 2010/11/8 16:26:43 2010/11/8 16:59:12 32.48 11566 2010/11/8 15:39:18 2010/11/8 16:26:43 47.42 11565 2010/11/8 15:09:37 2010/11/8 15:39:18 29.68 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 11564 2010/11/8 15:06:07 2010/11/8 15:09:37 3.5 11563 2010/11/8 15:05:55 2010/11/8 15:06:07 0.2 11562 2010/11/8 15:05:41 2010/11/8 15:05:55 0.23 11561 2010/11/8 15:05:28 2010/11/8 15:05:41 0.22 11560 2010/11/8 15:03:59 2010/11/8 15:05:28 1.48 11559 2010/11/8 14:52:44 2010/11/8 15:03:59 11.25 11558 2010/11/8 14:35:27 2010/11/8 14:52:44 17.28 11557 2010/11/8 14:21:47 2010/11/8 14:35:27 13.67 11556 2010/11/8 14:10:30 2010/11/8 14:21:47 11.28 11555 2010/11/8 14:00:58 2010/11/8 14:10:30 9.53 11554 2010/11/8 13:53:08 2010/11/8 14:00:58 7.83 11553 2010/11/8 13:26:04 2010/11/8 13:53:08 27.07 11552 2010/11/8 12:01:15 2010/11/8 13:26:04 84.82 11551 2010/11/8 11:23:40 2010/11/8 12:01:15 37.58 11550 2010/11/8 11:07:02 2010/11/8 11:23:40 16.63 #NO1 第一段 5 分钟切换次数 5 次 250M 11549 2010/11/8 11:06:20 2010/11/8 11:07:02 0.7 11548 2010/11/8 11:05:29 2010/11/8 11:06:20 0.85 11547 2010/11/8 11:04:46 2010/11/8 11:05:29 0.72 11546 2010/11/8 11:04:02 2010/11/8 11:04:46 0.73 11545 2010/11/8 11:03:16 2010/11/8 11:04:02 0.77 #################################################### 11544 2010/11/8 11:01:44 2010/11/8 11:03:16 1.53 11543 2010/11/8 11:00:49 2010/11/8 11:01:44 0.92 11542 2010/11/8 10:58:28 2010/11/8 11:00:49 2.35 11541 2010/11/8 10:51:54 2010/11/8 10:58:28 6.57 11540 2010/11/8 10:34:17 2010/11/8 10:51:54 17.62 11539 2010/11/8 10:08:00 2010/11/8 10:34:17 26.28 11538 2010/11/8 9:50:53 2010/11/8 10:08:00 17.12 11537 2010/11/8 9:37:23 2010/11/8 9:50:53 13.5 11536 2010/11/8 9:27:48 2010/11/8 9:37:23 9.58 11535 2010/11/8 9:14:02 2010/11/8 9:27:48 13.77 11534 2010/11/8 8:50:34 2010/11/8 9:14:02 23.47 11533 2010/11/8 8:15:57 2010/11/8 8:50:34 34.62 11532 2010/11/8 7:28:49 2010/11/8 8:15:57 47.13 11531 2010/11/8 7:28:42 2010/11/8 7:28:49 0.12 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 11530 2010/11/8 4:30:51 2010/11/8 7:28:42 177.85 11529 2010/11/8 1:56:31 2010/11/8 4:30:51 154.33 11528 2010/11/7 22:14:14 2010/11/8 1:56:31 222.28 11527 2010/11/7 18:21:21 2010/11/7 22:14:14 232.88 11526 2010/11/7 16:05:22 2010/11/7 18:21:21 135.98 11525 2010/11/7 14:32:28 2010/11/7 16:05:22 92.9 11524 2010/11/7 13:47:08 2010/11/7 14:32:28 45.33 >>>>>>>>>>>>>>>>>>>>>>>>>> 段落2>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> # NO2 这一段最为频繁 开始时间 13:47:08 截止时间 13:42 分 #切换次数 12 次 600M 11523 2010/11/7 13:46:11 2010/11/7 13:47:08 0.95 11522 2010/11/7 13:45:38 2010/11/7 13:46:11 0.55 11521 2010/11/7 13:45:11 2010/11/7 13:45:38 0.45 11520 2010/11/7 13:44:38 2010/11/7 13:45:11 0.55 11519 2010/11/7 13:44:16 2010/11/7 13:44:38 0.37 11518 2010/11/7 13:43:56 2010/11/7 13:44:16 0.33 11517 2010/11/7 13:43:34 2010/11/7 13:43:56 0.37 11516 2010/11/7 13:43:13 2010/11/7 13:43:34 0.35 11515 2010/11/7 13:42:52 2010/11/7 13:43:13 0.35 11514 2010/11/7 13:42:31 2010/11/7 13:42:52 0.35 11513 2010/11/7 13:42:07 2010/11/7 13:42:31 0.4 11512 2010/11/7 13:41:43 2010/11/7 13:42:07 0.4 ########################################################### #NO 3开始时间 13:41:43 截止时间 13:36 分 #切换次数 11 次 550M 11511 2010/11/7 13:41:19 2010/11/7 13:41:43 0.4 11510 2010/11/7 13:40:58 2010/11/7 13:41:19 0.35 11509 2010/11/7 13:40:34 2010/11/7 13:40:58 0.4 11508 2010/11/7 13:40:01 2010/11/7 13:40:34 0.55 11507 2010/11/7 13:39:25 2010/11/7 13:40:01 0.6 11506 2010/11/7 13:38:34 2010/11/7 13:39:25 0.85 11505 2010/11/7 13:38:01 2010/11/7 13:38:34 0.55 11504 2010/11/7 13:37:07 2010/11/7 13:38:01 0.9 11503 2010/11/7 13:36:37 2010/11/7 13:37:07 0.5 11502 2010/11/7 13:35:42 2010/11/7 13:36:37 0.92 11501 2010/11/7 13:34:54 2010/11/7 13:35:42 0.8 ########################################################### ## NO 3 开始时间 13:34:54 截止时间 13:29 分 #切换次数 9 次 450M 11500 2010/11/7 13:34:06 2010/11/7 13:34:54 0.8 11499 2010/11/7 13:33:07 2010/11/7 13:34:06 0.98 11498 2010/11/7 13:32:36 2010/11/7 13:33:07 0.52 11497 2010/11/7 13:32:12 2010/11/7 13:32:36 0.4 11496 2010/11/7 13:31:42 2010/11/7 13:32:12 0.5 11495 2010/11/7 13:30:51 2010/11/7 13:31:42 0.85 11494 2010/11/7 13:30:12 2010/11/7 13:30:51 0.65 11493 2010/11/7 13:29:12 2010/11/7 13:30:12 1 11492 2010/11/7 13:27:49 2010/11/7 13:29:12 1.38 ######################################################### 11491 2010/11/7 13:27:19 2010/11/7 13:27:49 0.5 11490 2010/11/7 13:26:54 2010/11/7 13:27:19 0.42 11489 2010/11/7 13:26:27 2010/11/7 13:26:54 0.45 11488 2010/11/7 13:24:09 2010/11/7 13:26:27 2.3 11487 2010/11/7 13:23:28 2010/11/7 13:24:09 0.68 11486 2010/11/7 13:22:51 2010/11/7 13:23:28 0.62 11485 2010/11/7 13:22:21 2010/11/7 13:22:51 0.5 11484 2010/11/7 13:21:53 2010/11/7 13:22:21 0.47 11483 2010/11/7 13:21:27 2010/11/7 13:21:53 0.43 11482 2010/11/7 13:20:54 2010/11/7 13:21:27 0.55 11481 2010/11/7 13:20:24 2010/11/7 13:20:54 0.5 11480 2010/11/7 13:19:39 2010/11/7 13:20:24 0.75 11479 2010/11/7 13:18:53 2010/11/7 13:19:39 0.77 11478 2010/11/7 13:17:47 2010/11/7 13:18:53 1.1 11477 2010/11/7 13:16:18 2010/11/7 13:17:47 1.48 11476 2010/11/7 13:15:02 2010/11/7 13:16:18 1.27 11475 2010/11/7 13:14:02 2010/11/7 13:15:02 1 11474 2010/11/7 13:12:44 2010/11/7 13:14:02 1.3 11473 2010/11/7 13:12:05 2010/11/7 13:12:44 0.65 11472 2010/11/7 13:11:17 2010/11/7 13:12:05 0.8 11471 2010/11/7 13:10:11 2010/11/7 13:11:17 1.1 11470 2010/11/7 13:08:56 2010/11/7 13:10:11 1.25 11469 2010/11/7 13:06:34 2010/11/7 13:08:56 2.37 11468 2010/11/7 13:05:57 2010/11/7 13:06:34 0.62 11467 2010/11/7 12:54:39 2010/11/7 13:05:57 11.3 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 11466 2010/11/7 11:11:59 2010/11/7 12:54:39 102.67 11465 2010/11/7 10:06:20 2010/11/7 11:11:59 65.65 11464 2010/11/7 9:27:05 2010/11/7 10:06:20 39.25 11463 2010/11/7 8:00:18 2010/11/7 9:27:05 86.78 11462 2010/11/7 7:10:33 2010/11/7 8:00:18 49.75 11461 2010/11/7 6:00:49 2010/11/7 7:10:33 69.73 11460 2010/11/7 2:19:57 2010/11/7 6:00:49 220.87 11459 2010/11/6 22:51:21 2010/11/7 2:19:57 208.6 11458 2010/11/6 19:38:41 2010/11/6 22:51:21 192.67 11457 2010/11/6 18:59:29 2010/11/6 19:38:41 39.2 11456 2010/11/6 18:47:09 2010/11/6 18:59:29 12.33 11455 2010/11/6 18:34:33 2010/11/6 18:47:09 12.6 11454 2010/11/6 17:55:31 2010/11/6 18:34:33 39.03 查看日志组信息 select thread#, group#, sequence#, bytes/ 1024 / 1024 , members , first_time, status from v$log; THREAD# GROUP# SEQUENCE# BYTES/1024/1024 MEMBERS FIRST_TIME STATUS 1 1 11572 50 1 2010/11/8 20:44:20 INACTIVE 1 2 11573 50 1 2010/11/8 21:04:18 INACTIVE 1 3 11574 50 1 2010/11/8 21:24:20 CURRENT 从上述信息可见日志文件仅为 50M ， 对于业务高峰期时数据量很大 （全国性数据录入）的环境下显的太小了且是日志组少。

数据库 DBWR 信息：（等第一步方案实施后再进一步诊断） 具体措施: select * from gv$bgprocess where name like 'DBW_' and paddr<>'00' 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 50m alter database drop logfile member '/oradata/orcl/ REDO0401.LOG'; alter database drop logfile member '/oradata/orcl/REDO0402.LOG' alter database drop logfile member '/oradata/orcl/ REDO0401.LOG'; alter system switch logfile; ALTER DATABASE DROP LOGFILE GROUP 1 alter database drop logfile member '/oradata/orcl/redo01_1.log' alter database drop logfile member '/oradata/orcl/redo01.log' alter database add logfile group 4 ('/oradata/orcl/REDO0401.LOG','/oradata/orcl/REDO0402.LOG') size 50m alter database add logfile group 4 ('/oradata/orcl/REDO0401.LOG','/oradata/orcl/REDO0402.LOG') size 50m alter database drop logfile group 1 alter database drop logfile group 3 alter database add logfile group 1 ('/oradata/orcl/redo01.log','/oradata/orcl/redo0102.log') size 400m reuse alter database add logfile group 2 ('/oradata/orcl/redo02.log','/oradata/orcl/redo02_2.log') size 400m reuse alter database add logfile group 3 ('/oradata/orcl/redo03.log','/oradata/orcl/redo03_2.log') size 400m reuse 。