Oracle工作总结——日志文件切换频率的调整
北京某医疗投资企业，数据库经常出现日志切换等待，查看后发现数据库日志切换很频繁。
解决办法
争取能达到业务高峰期3-5分钟切换一次。平均15-20分钟切一次。
第一步：
--日志大小
SELECT thread#,
group#,
sequence#,
bytes / 1024 / 1024,
members,
first_time,
status
FROM v$log;
--查看日志切换频率
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 4开始时间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信息：（等第一步方案实施后再进一步诊断）
具体措施:
数据分析过程：
选取业务高峰期时段的日志切换信息截取4段 5分钟最为频繁的切换日志的大小：
日志大小为50M未修改原始参数。
每5分钟需要 (250 + 600 +550 + 450 ) / 4= 462.5
更改联机日志大小位500M
目前方案：
将日志文件大小设定为500M
并给每个实例增加二个日志组
--当遇到日志文件太小的时候需要添加日志文件组以及更改日志文件组中日志文件的大小
alter database drop logfile group 1
--如果删除不了就做一下日志切换还有检查点
alter system switch logfile;
alter system checkpoint;
alter database addlog filegroup 1
('/oradata/orcl/redo01.log','/oradata/orcl/redo01_1.log')
size 400m reuse ;
