We know that Oracle will switch logs when the current online logs are full or some conditions are met, or when logs are manually switched, even if the current online log group is from this log group to another log group, the log loop mechanism is implemented. So how does oracle decide which of the remaining log groups is the next online log group?
I did a simple experiment to illustrate this problem.
Let's first look at the situation of the log group:
SQL> select GROUP #, SEQUENCE #, ARCHIVED, STATUS from v $ log;
GROUP # SEQUENCE # ARC STATUS
---------------------------------------
1 44 YES ACTIVE
2 45 NO CURRENT
3 43 YES INACTIVE
Then, we clear log group 1 so that its sequence # is smaller than 42 of the current log group 3.
SQL> alter database clear unarchived logfile group 1;
The database has been changed.
SQL> select GROUP #, SEQUENCE #, ARCHIVED, STATUS from v $ log;
GROUP # SEQUENCE # ARC STATUS
---------------------------------------
1 0 YES UNUSED
2 45 NO CURRENT
3 43 YES INACTIVE
Then, we switch to the current log group to see what happens?
SQL> alter system switch logfile;
The system has been changed.
SQL> select GROUP #, SEQUENCE #, ARCHIVED, STATUS from v $ log;
GROUP # SEQUENCE # ARC STATUS
---------------------------------------
1 46 NO CURRENT
2 45 YES ACTIVE
3 43 YES INACTIVE
Finally, we switch the log again to prove our guess:
SQL> alter system switch logfile;
The system has been changed.
SQL> select GROUP #, SEQUENCE #, ARCHIVED, STATUS from v $ log;
GROUP # SEQUENCE # ARC STATUS
---------------------------------------
1 46 YES ACTIVE
2 45 YES ACTIVE
3 43 NO CURRENT
From the experiment above, we can easily see the rule of selecting the next current log group during oracle log switching, that is, always switching to the smallest log group of log sequence # in the remaining log group.