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;