1、查看目前日志组和日志文件情况
SQL> select * from v$logfile order by 1;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE +FRA/stone/onlinelog/group_1.257.884819615 YES
1 ONLINE +DATA/stone/onlinelog/group_1.261.884819613 NO
2 ONLINE +FRA/stone/onlinelog/group_2.258.884819623 YES
2 ONLINE +DATA/stone/onlinelog/group_2.262.884819619 NO
3 ONLINE +DATA/stone/onlinelog/group_3.266.884820745 NO
3 ONLINE +FRA/stone/onlinelog/group_3.259.884820749 YES
4 ONLINE +DATA/stone/onlinelog/group_4.267.884820755 NO
4 ONLINE +FRA/stone/onlinelog/group_4.260.884820759 YES
SQL> select group#,thread#,bytes/1024/1024,archived,status,members from v$log;
GROUP# THREAD# BYTES/1024/1024 ARC STATUS MEMBERS
---------- ---------- --------------- --- ---------------- ----------
1 1 50 NO CURRENT 2
2 1 50 YES INACTIVE 2
3 2 50 YES INACTIVE 2
4 2 50 NO CURRENT 2
可以看到目前是2个节点,每个节点2组日志,每组日志2个日志文件,大小均为50M,分别位于+DATA和+FRA磁盘组。
2、查看日志组切换频率和归档日志生成量
SQL> select b.SEQUENCE#, b.FIRST_TIME,a.SEQUENCE#,a.FIRST_TIME,round(((a.FIRST_TIME-b.FIRST_TIME)*24)*60,2) from v$log_history a, v$log_history b where a.SEQUENCE#=b.SEQUENCE#+1 and b.THREAD#=1 order by a.SEQUENCE# desc;
SEQUENCE# FIRST_TIM SEQUENCE# FIRST_TIM ROUND(((A.FIRST_TIME-B.FIRST_TIME)*24)*60,2)
---------- --------- ---------- --------- --------------------------------------------
41 15-SEP-15 42 15-SEP-15 .43
40 15-SEP-15 41 15-SEP-15 .42
39 15-SEP-15 40 15-SEP-15 .53
38 15-SEP-15 39 15-SEP-15 .22
37 15-SEP-15 38 15-SEP-15 .18
36 15-SEP-15 37 15-SEP-15 29.93
35 22-JUL-15 36 15-SEP-15 79165.62
SQL> select trunc(completion_time),sum(mb)/1024 day_gb from(select name,completion_time,blocks*block_size /1024 /1024 mb from v$archived_log) group by trunc(completion_time) order by (trunc(completion_time));
TRUNC(COM DAY_GB
--------- ----------
11-JUL-15 .048826694
12-JUL-15 .170285702
13-JUL-15 .141009331
14-JUL-15 .135242462
15-JUL-15 .153475761
16-JUL-15 .09009409
17-JUL-15 .031218529
18-JUL-15 .084494114
20-JUL-15 .088860512
21-JUL-15 .011713982
22-JUL-15 .106176376
TRUNC(COM DAY_GB
--------- -----