Online Redo Logs and Archived Redo Logs in RAC Environment

In a typical RAC environment, if you query the v$ views for a instance, the views show the information from that instance. If you query gv$views, then it shows the information from all the RAC instances.

For example if you want to know information about sessions connected to your RAC database through a single RAC instance to which you are logged in right now, you would query v$session view. But if you want to know about all the sessions connected through all the instances to the cluster database then you would use gv$views.

But that is not true when it comes to the online redo logs and archived redo logs. Every RAC instance maintains its own thread of online redo logs and archived redo logs. When you query v$log or v$archived_log view from a single RAC instance, it shows the information from all RAC instances. If you query gv$log or gv$archived_log, it shows redundant information from all instances.

For example, see this:

Code snippet 1:


[fahd@fahdrac ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Oct 10 20:04:51 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options



SQL> select thread#,count(1)
  2  from V$ARCHIVED_LOG
  3  group by thread#;

   THREAD#   COUNT(1)
---------- ----------
         1       1151
         2       1198

Code snippet 2:

SQL> select thread#,count(1)
  2  from gV$ARCHIVED_LOG
  3  group by thread#;

   THREAD#   COUNT(1)
---------- ----------
         1       2302
         2       2396


In code snippet one, it displayed the correct information. It takes information from both threads of both instances.

In code snippet two, first it takes information from local instance (which in return also gets information from other instance), and then it queries the other thread, which returns again the same value, and gv$ aggregates the values and return the wrong result.

Same is the case with online redo logs:

SQL> select thread#,group# from v$log;

   THREAD#     GROUP#
---------- ----------
         1          1
         1          2
         2          3
         2          4


SQL> select thread#,group# from gv$log;

   THREAD#     GROUP#
---------- ----------
         1          1
         1          2
         2          3
         2          4
         1          1
         1          2
         2          3
         2          4

8 rows selected.


So in case of online redo logs and archived redo logs, always use the v$log, v$logfile, and v$archived_log views, instead of corresponding gv$ views.