查看归档日志切换频率

1.查看归档日志切换频率

SET LINE 300 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 GV$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.确认日志大小及成员

col member for a50
select l.group#,member,bytes/1024/1024,l.status from v$log l,v$logfile f where l.group#=f.group# order by 1;
    GROUP# MEMBER					      BYTES/1024/1024 STATUS
---------- -------------------------------------------------- --------------- ----------------
	 1 +DATASTD/prod/onlinelog/group_1.262.1165427695		   50 INACTIVE
	 1 +DATASTD/prod/onlinelog/group_1.263.1165427695		   50 INACTIVE
	 2 +DATASTD/prod/onlinelog/group_2.260.1165427695		   50 CURRENT
	 2 +DATASTD/prod/onlinelog/group_2.261.1165427695		   50 CURRENT
	 3 +DATASTD/prod/onlinelog/group_3.259.1165427695		   50 INACTIVE
	 3 +DATASTD/prod/onlinelog/group_3.258.1165427695		   50 INACTIVE
	 4 +DATASTD/prod/onlinelog/group_4.277.1165427695		   50 CURRENT
	 4 +DATASTD/prod/onlinelog/group_4.276.1165427695		   50 CURRENT

3.添加新的日志组

alter database add logfile group  15 ('+DATASTD','+DATASTD')   SIZE 50M;
select l.inst_id, l.group#,member,bytes/1024/1024,l.status from gv$log l,gv$logfile f where l.group#=f.group# order by 1;

4.切换日志为不活动状态

alter system switch logfile

5. 删除原有的redo日志组

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

6.删除操作系统上文件

cd xxx
rm xxx