ORACLE 11gr2 ASM redo log 增加

               目前数据库的redo 日志切换频繁，根据需求添加数据库的日志组以及成员；

os:

[oracle@db1 ~]$ lsb_release -a
LSB Version:	:core-4.0-amd64:core-4.0-ia32:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-ia32:printing-4.0-noarch
Distributor ID:	RedHatEnterpriseServer
Description:	Red Hat Enterprise Linux Server release 5.8 (Tikanga)
Release:	5.8
Codename:	Tikanga
[oracle@db1 ~]$ 

db：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

alter告警日志：（大量）

Wed Jul 30 10:14:21 2014
Archived Log entry 387070 added for thread 1 sequence 203585 ID 0x5eb711b2 dest 1:
Wed Jul 30 10:14:44 2014
Thread 1 cannot allocate new log, sequence 203587
Checkpoint not complete
  Current log# 1 seq# 203586 mem# 0: +DATADG/db/onlinelog/group_1.257.825271349
  Current log# 1 seq# 203586 mem# 1: +FRADG/db/onlinelog/group_1.257.825271351
  Current log# 1 seq# 203586 mem# 2: +DATADG/db/onlinelog/group_1.297.852053653
Thread 1 advanced to log sequence 203587 (LGWR switch)
  Current log# 6 seq# 203587 mem# 0: +DATADG/db/onlinelog/group_6.295.852051819
  Current log# 6 seq# 203587 mem# 1: +FRADG/db/onlinelog/group_6.37766.852051819

Log switch 时，会触发检查点，标记检查点时，DBWR要把log file 中的covered bythe log being checkpointed 的数据写入到数据文件上，若DBWR没有写完，那前一个logfile是不能覆盖的。所以Alter 文件中记录checkpoint not complete 事件，这通常表明dbwr写入过慢或者logfile组数过少或者logfile太小。

用下面的sql 来详细的采集redo log 切换的频率

set linesize 230
SELECT THREAD#,
       trunc(first_time) "Date",
       to_char(first_time, 'Dy') "Day",
       substr(count(1), 1, 5) as "Total",
       substr(SUM(decode(to_char(first_time, 'hh24'), '00', 1, 0)), 1, 3) as "h00",
       substr(SUM(decode(to_char(first_time, 'hh24'), '01', 1, 0)), 1, 3) as "h01",
       substr(SUM(decode(to_char(first_time, 'hh24'), '02', 1, 0)), 1, 3) as "h02",
       substr(SUM(decode(to_char(first_time, 'hh24'), '03', 1, 0)), 1, 3) as "h03",
       substr(SUM(decode(to_char(first_time, 'hh24'), '04', 1, 0)), 1, 3) as "h04",
       substr(SUM(decode(to_char(first_time, 'hh24'), '05', 1, 0)), 1, 3) as "h05",
       substr(SUM(decode(to_char(first_time, 'hh24'), '06', 1, 0)), 1, 3) as "h06",
       substr(SUM(decode(to_char(first_time, 'hh24'), '07', 1, 0)), 1, 3) as "h07",
       substr(SUM(decode(to_char(first_time, 'hh24'), '08', 1, 0)), 1, 3) as "h08",
       substr(SUM(decode(to_char(first_time, 'hh24'), '09', 1, 0)), 1, 3) as "h09",
       substr(SUM(decode(to_char(first_time, 'hh24'), '10', 1, 0)), 1, 3) as "h10",
       substr(SUM(decode(to_char(first_time, 'hh24'), '11', 1, 0)), 1, 3) as "h11",
       substr(SUM(decode(to_char(first_time, 'hh24'), '12', 1, 0)), 1, 3) as "h12",
       substr(SUM(decode(to_char(first_time, 'hh24'), '13', 1, 0)), 1, 3) as "h13",
       substr(SUM(decode(to_char(first_time, 'hh24'), '14', 1, 0)), 1, 3) as "h14",
       substr(SUM(decode(to_char(first_time, 'hh24'), '15', 1, 0)), 1, 3) as "h15",
       substr(SUM(decode(to_char(first_time, 'hh24'), '16', 1, 0)), 1, 3) as "h16",
       substr(SUM(decode(to_char(first_time, 'hh24'), '17', 1, 0)), 1, 3) as "h17",
       substr(SUM(decode(to_char(first_time, 'hh24'), '18', 1, 0)), 1, 3) as "h18",
       substr(SUM(decode(to_char(first_time, 'hh24'), '19', 1, 0)), 1, 3) as "h19",
       substr(SUM(decode(to_char(first_time, 'hh24'), '20', 1, 0)), 1, 3) as "h20",
       substr(SUM(decode(to_char(first_time, 'hh24'), '21', 1, 0)), 1, 3) as "h21",
       substr(SUM(decode(to_char(first_time, 'hh24'), '22', 1, 0)), 1, 3) as "h22",
       substr(SUM(decode(to_char(first_time, 'hh24'), '23', 1, 0)), 1, 3) as "h23"
  FROM V$log_history