Oracle 11G redo日志组大小调整及日志 频率检查

1.日志切换频率查询
SET LINE 150 PAGES 9999
COL "00" FOR A3
COL "01" FOR A3
COL "02" FOR A3
COL "03" FOR A3
COL "04" FOR A3
COL "05" FOR A3
COL "06" FOR A3
COL "07" FOR A3
COL "08" FOR A3
COL "09" FOR A3
COL "10" FOR A3
COL "11" FOR A3
COL "12" FOR A3
COL "13" FOR A3
COL "14" FOR A3
COL "15" FOR A3
COL "16" FOR A3
COL "17" FOR A3
COL "18" FOR A3
COL "19" FOR A3
COL "20" FOR A3
COL "21" FOR A3
COL "22" FOR A3
COL "23" FOR A3
COL DAY FOR A15
SELECT DAY,
     TO_CHAR(SUM(DECODE(H, '00', T, 0))) AS "00",
     TO_CHAR(SUM(DECODE(H, '01', T, 0))) AS "01",
     TO_CHAR(SUM(DECODE(H, '02', T, 0))) AS "02",
     TO_CHAR(SUM(DECODE(H, '03', T, 0))) AS "03",
     TO_CHAR(SUM(DECODE(H, '04', T, 0))) AS "04",
     TO_CHAR(SUM(DECODE(H, '05', T, 0))) AS "05",
     TO_CHAR(SUM(DECODE(H, '06', T, 0))) AS "06",
     TO_CHAR(SUM(DECODE(H, '07', T, 0))) AS "07",
     TO_CHAR(SUM(DECODE(H, '08', T, 0))) AS "08",
     TO_CHAR(SUM(DECODE(H, '09', T, 0))) AS "09",
     TO_CHAR(SUM(DECODE(H, '10', T, 0))) AS "10",
     TO_CHAR(SUM(DECODE(H, '11', T, 0))) AS "11",
     TO_CHAR(SUM(DECODE(H, '12', T, 0))) AS "12",
     TO_CHAR(SUM(DECODE(H, '13', T, 0))) AS "13",
     TO_CHAR(SUM(DECODE(H, '14', T, 0))) AS "14",
     TO_CHAR(SUM(DECODE(H, '15', T, 0))) AS "15",
     TO_CHAR(SUM(DECODE(H, '16', T, 0))) AS "16",
     TO_CHAR(SUM(DECODE(H, '17', T, 0))) AS "17",
     TO_CHAR(SUM(DECODE(H, '18', T, 0))) AS "18",
     TO_CHAR(SUM(DECODE(H, '19', T, 0))) AS "19",
     TO_CHAR(SUM(DECODE(H, '20', T, 0))) AS "20",
     TO_CHAR(SUM(DECODE(H, '21', T, 0))) AS "21",
     TO_CHAR(SUM(DECODE(H, '22', T, 0))) AS "22",
     TO_CHAR(SUM(DECODE(H, '23', T, 0))) AS "23"
   FROM (SELECT TO_CHAR(FIRST_TIME, 'YYYY-MM-DD') DAY,
               TO_CHAR(FIRST_TIME, 'HH24') H,
               COUNT(1) T
             FROM V$LOG_HISTORY
            WHERE FIRST_TIME > SYSDATE - 15
         GROUP BY TO_CHAR(FIRST_TIME, 'YYYY-MM-DD'),
                  TO_CHAR(FIRST_TIME, 'HH24')
         ORDER BY 1)
GROUP BY DAY
ORDER BY DAY DESC;

2.redo日志太小，需要调大方法(先加日志组，再切换日志为不活动状态；最后删除之前过小日志组）:
2.1确认日志大小及成员：
select l.group#,member,bytes/1024/1024,l.status from v$log l,v$logfile f where l.group#=f.group# order by 1;

2.2添加新的日志组：
alter database add logfile group 4('/oracle/app/oradata/www111/redo04a.log','/oracle/app/oradata/www111/redo04b.log') SIZE 300M;
alter database add logfile group 5('/oracle/app/oradata/www111/redo05a.log','/oracle/app/oradata/www111/redo05b.log') SIZE 300M;
alter database add logfile group 6('/oracle/app/oradata/www111/redo06a.log','/oracle/app/oradata/www111/redo06b.log') SIZE 300M;

2.3切换日志为不活动状态：
检查并切换日志为不活动状态。
alter system switch logfile(多次执行，直到原有的redo日志为不活动状态）


2.4删除原有的redo日志组（必须为不活动状态）：
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

2.5drop旧有的日志组后，再删除操作系统上文件；
cd /oracle/app/oradata/www111/;rm redo01.log redo02.log redo03.log;

2.6最后再添加新的日志group 1 、group 2 、group3：
alter database add logfile group 1('/oracle/app/oradata/www111/redo01a.log','/oracle/app/oradata/www111/redo01b.log') size 300M;
alter database add logfile group 2('/oracle/app/oradata/www111/redo02a.log','/oracle/app/oradata/www111/redo02b.log')  size 300M;
alter database add logfile group 3('/oracle/app/oradata/www111/redo03a.log','/oracle/app/oradata/www111/redo03b.log') size 300M;

3.最后切换日志 ，检查各日志及组状态

一切到位。