Same Results
Both SWITCH LOGFILE and ARCHIVE LOG CURRENT force Log Writer process (LGWR) switch to the next redo log group and start to write, then archival follow. In other words, they make the same result, but they behave differently.
Let's see an example before switching groups.
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
According to the descriptions of V$LOG in 19c, the statuses of redo log mean:
- CURRENT
- ACTIVE
- INACTIVE
Current redo log. This implies that the redo log is active. The redo log could be open or closed.
Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
Log is no longer needed for instance recovery. It may be in use for media recovery. It may or may not be archived.
Different Behaviors
1. No-Wait vs Wait
The first difference is the responses. SWITCH LOGFILE is a no-wait statement, once it was issued by DBA, it'll return to the user and do the switching in the background. On the other side, ARCHIVE LOG CURRENT will wait for Archiver Process (ARCH) to complete the archiving and then return to user.
For small redo logs, both can return less than 1 second, although ARCHIVE LOG CURRENT takes a little longer.
SQL> set timing on;
SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:00.04
SQL> alter system archive log current;
System altered.
Elapsed: 00:00:00.12
Now the statuses of all groups are:
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 ACTIVE
3 ACTIVE
For larger redo logs, you might see the difference.
2. Data Guard
A big difference can be seen in data guard environments, especially in 9i.
SQL> alter system archive log current;
System altered.
Elapsed: 00:01:46.87
As you can see, ARCHIVE LOG CURRENT takes almost 2 minutes for a 1GB redo logs to return. This is because it's waiting for the completion of network transferring and archiving on the remote standby database. Also, the larger redo size, the longer it takes.
In the middle of remote archiving, there could be a temporary archive gap.
3. RAC Database
For a cluster database, statement SWITCH LOGFILE affects only the current instance whereas ARCHIVE LOG CURRENT makes redo log switch on all nodes (threads).
Let's see all statuses of groups before switching.
SQL> select thread#, instance_name from v$instance;
THREAD# INSTANCE_NAME
---------- ----------------
1 TESTCDB1
SQL> select thread#, group#, status from v$log order by 1,2;
THREAD# GROUP# STATUS
---------- ---------- ----------------
1 1 INACTIVE
1 3 INACTIVE
1 5 CURRENT
2 2 CURRENT
2 4 INACTIVE
2 6 INACTIVE
6 rows selected.
SWITCH LOGFILE
First of all, we make a switch log file.
SQL> alter system switch logfile;
System altered.
SQL> select thread#, group#, status from v$log order by 1,2;
THREAD# GROUP# STATUS
---------- ---------- ----------------
1 1 CURRENT
1 3 INACTIVE
1 5 ACTIVE
2 2 CURRENT
2 4 INACTIVE
2 6 INACTIVE
6 rows selected.
As we can see, only redo groups of thread #1 is switched.
ARCHIVE LOG CURRENT
By default, it switches redo groups on all nodes (threads).
SQL> alter system archive log current;
System altered.
SQL> select thread#, group#, status from v$log order by 1,2;
THREAD# GROUP# STATUS
---------- ---------- ----------------
1 1 ACTIVE
1 3 CURRENT
1 5 INACTIVE
2 2 ACTIVE
2 4 CURRENT
2 6 INACTIVE
6 rows selected.
Both threads switched their redo groups.
The syntax of ALTER SYSTEM also supports to switch only one instance. In this case, we switch the redo group of node 2.
SQL> alter system archive log instance 'ORCLCDB2' current;
System altered.
SQL> select thread#, group#, status from v$log order by 1,2;
THREAD# GROUP# STATUS
---------- ---------- ----------------
1 1 INACTIVE
1 3 CURRENT
1 5 INACTIVE
2 2 INACTIVE
2 4 ACTIVE
2 6 CURRENT
6 rows selected.
Summary
Which statement is better? Which one should I use? If I'm NOT in a RAC or data guard environment, I will use SWITCH LOGFILE because I don't want to wait. Otherwise, I use ARCHIVE LOG CURRENT to make sure everything is ready before doing anything important.