初始状态
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 INACTIVE 17
2 INACTIVE 18
3 CURRENT 19
SQL> alter system switch logfile;
System altered.
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 CURRENT 20
2 INACTIVE 18
3 INACTIVE 19
切换日志后,日志组1变为CURRENT状态,对此有两种理解方式
第一种理解是循环,3完了当然轮到1
第二种理解是每次切换时,oracle都会优先考虑当前序列号最低的日志
为了验证这一点,添加日志组4
SQL> alter database add logfile '/tmp/4.log' size 4m;
Database altered.
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 CURRENT 20
2 INACTIVE 18
3 INACTIVE 19
4 UNUSED 0 //新添加的日志组4,注意它的序列号为0
SQL> alter system switch logfile;
System altered.
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 ACTIVE 20
2 INACTIVE 18
3 INACTIVE 19
4 CURRENT 21 //经过一次切换后,日志组4变成CURRENT状态,而如果按照第一种理解方式,此时CURRENT状态的应该是日志组2
再次切换后,原本序列号为18(当前情况下序列号最低)的日志组变为CURRENT
SQL> alter system switch logfile;
System altered.
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 ACTIVE 20
2 CURRENT 22
3 INACTIVE 19
4 ACTIVE 21
这次是19
SQL> alter system switch logfile;
System altered.
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 ACTIVE 20
2 ACTIVE 22
3 CURRENT 23
4 ACTIVE 21
为增强说服力,再添加日志组5
SQL> alter database add logfile '/tmp/5.log' size 4m;
Database altered.
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 ACTIVE 20
2 ACTIVE 22
3 CURRENT 23
4 ACTIVE 21
5 UNUSED 0
SQL> alter system switch logfile;
System altered.
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 ACTIVE 20
2 ACTIVE 22
3 ACTIVE 23
4 ACTIVE 21
5 CURRENT 24
总结:使用语句"alter system switch logfile"进行日志切换时,下一个变为CURRENT状态的一定是当前状态下,序列号最低的那个日志组
补充:其实这很好理解,oracle的日志本来就是循环使用的,只不过这里的循环不是首尾相接式的,而是根据日志序列号判断,将最老的日志给覆盖掉而已