SWITCH LOGFILE vs ARCHIVE LOG CURRENT

  • by

Same Results

Both ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM 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
  • Current redo log. This implies that the redo log is active. The redo log could be open or closed.

  • ACTIVE
  • 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.

  • INACTIVE
  • 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. ALTER SYSTEM 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, ALTER SYSTEM 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 ALTER SYSTEM 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, ALTER SYSTEM 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 ALTER SYSTEM SWITCH LOGFILE affects only the current instance whereas ALTER SYSTEM 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.

ALTER SYSTEM 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.

ALTER SYSTEM 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 should I use? If I'm NOT in a RAC or data guard environment, I will use ALTER SYSTEM SWITCH LOGFILE because I don't want to wait. Otherwise, I use ALTER SYSTEM ARCHIVE LOG CURRENT to make sure everything is ready before doing anything important.

Leave a Reply

Your email address will not be published. Required fields are marked *