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 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 drop logfile member 'D:\oracle\product\10.2.0\oradata\orcl\REDO02.LOG'
alter database drop logfile member 'D:\oracle\product\10.2.0\oradata\orcl\REDO02.LOG';

alter database add logfile member 'D:\oracle\product\10.2.0\oradata\orcl\REDO021.LOG' to group 2

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 add logfile group 4('D:\oracle\product\10.2.0\oradata\orcl\REDO0401.LOG','D:\oracle\product\10.2.0\oradata\orcl\REDO0402.LOG') size 50m

alter database drop logfile member 'D:\database\product\10.2.0\oradata\xtdb\REDO021.LOG';

alter database drop logfile group 1

alter database add logfile group 1 ('D:\oracle\product\10.2.0\oradata\orcl\REDO01.LOG',
'D:\oracle\product\10.2.0\oradata\orcl\REDO012.LOG') size 100m reuse

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