Skip to Main Content
  • Questions
  • Redo log threads in Real Application Clusters

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Prashant.

Asked: December 31, 2006 - 12:41 pm UTC

Answered by: Tom Kyte - Last updated: May 02, 2018 - 1:55 am UTC

Category: Database - Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Tom,
I have been trying to understand the concept of redo log threads in a RAC environment. I understand how the redo logs work in a single instance database. But I cannot find proper documentation anywhere that clearly explains what a redo log thread is and how the whole thing works in a RAC setup.
e.g. Here is what I have on my 2 node RAC database:

SQL> select * from gv$log;

INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 17 52428800 1 NO CURRENT 951208 31-DEC-06
1 2 1 15 52428800 1 YES INACTIVE 940808 31-DEC-06
1 4 2 13 52428800 1 YES INACTIVE 948841 31-DEC-06
1 5 2 14 52428800 1 NO CURRENT 951206 31-DEC-06
2 1 1 17 52428800 1 NO CURRENT 951208 31-DEC-06
2 2 1 15 52428800 1 YES INACTIVE 940808 31-DEC-06
2 4 2 13 52428800 1 YES INACTIVE 948841 31-DEC-06
2 5 2 14 52428800 1 NO CURRENT 951206 31-DEC-06

8 rows selected.

SQL> select * from gv$logfile;

INST_ID GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------- ------- -------------------------------------------------- ---
1 2 ONLINE /ocfs/oradata/racdb/redo02.log NO
1 1 ONLINE /ocfs/oradata/racdb/redo01.log NO
1 4 ONLINE /ocfs/oradata/racdb/redo04.log NO
1 5 ONLINE /ocfs/oradata/racdb/redo05.log NO
2 2 ONLINE /ocfs/oradata/racdb/redo02.log NO
2 1 ONLINE /ocfs/oradata/racdb/redo01.log NO
2 4 ONLINE /ocfs/oradata/racdb/redo04.log NO
2 5 ONLINE /ocfs/oradata/racdb/redo05.log NO

8 rows selected.

SQL> exit

My question is why do I see multiple redo log members when I query the gv$logfile view? How do the INST_ID, GROUP# and THREAD# relate to each other?

Can you please make it clear to me?

Thank you

and we said...

each instance has it's own personal set of redo - each redo thread is made up of at least two groups that have one or more members (files)


two instances will never write to the same redo files - each instance has it's own set of redo logs to write to. that is the inst_id you see above, it shows which instance owns what redo logs.

another instance may well READ some other instances redo logs - after that other instance fails for example - to perform recovery.

and you rated our response

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

but...

January 02, 2007 - 11:02 am UTC

Reviewer: Prashant from Pittsburgh, PA

You said, "each instance has it's own personal set of redo ".
Then how come I see the same redo log file e.g. redo01.log corresponding to both the instances? And same is the case with redo02.log, redo04.log and redo05.log?

Another observation that confuses me is: Group 1 and 5 are CURRENT in both the instances. These groups correspond to files redo01.log and redo05.log respectively. CURRENT means the instance is writing to these redo log files - right now!
So how can both the instances be having the same groups CURRENT at the same time?

I will take a stab at the Prashant's question ...

January 03, 2007 - 5:01 pm UTC

Reviewer: Arup Nanda from Danbury, CT, USA

I will take a stab at the Prashant's question.

Most V$ views work by selecting information from the corresponding GV$ view with a predicate "where instance_id = <that instance>". So V$SESSION in Instance 1 is actually "SELECT * FROM GV$INSTANCE WHERE INST_ID = 1". On a three node RAC database, if you select from v$session, you get sessions from that instance only. Selecting from GV$SESSION creates parallel query slaves on the other instances and gets the information back to your session.

This works fine in almost all cases. There are few exceptions: in case of redo logs, the RAC instance must see all the redo logs of other instances as they become important for its recovery. Therefore, V$LOG actually shows all the redo logs, of all the instances, not just of its own. Contrast this with V$SESSION, which shows only sessions of that instance, not all. So, if there are 3 log file groups per instance (actually, per "thread") and there are 3 instances, V$LOG on any instance will show all 9 logfile groups, not 3.

When you select form GV$LOG, remember, the session gets the information from other instances as well. Unfortunately, the PQ servers on those instances also get 9 records each, since they also see the same information seen by the first instance. On a three instance RAC, you will get 3X9 = 27 records in GV$LOG!

The same explanation applies to GV$LOGFILE as well as GV$THREAD.

In your case, you have 2 instances and there are 2 groups in each instance, so you have 4 groups in all. When you select from GV$LOG, the output shows all groups against all instances. Note your output:

  INST_ID  GROUP#  THREAD# SEQUENCE#    BYTES  MEMBERS ARC STATUS      FIRST_CHANGE# FIRST_TIM                                                            
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------                                                            
      1      1      1      17  52428800      1 NO CURRENT          951208 31-DEC-06                                                            
      1      2      1      15  52428800      1 YES INACTIVE          940808 31-DEC-06                                                            
      1      4      2      13  52428800      1 YES INACTIVE          948841 31-DEC-06            


This shows thread# 1 and 2 both under instance# 1. This is obviously incorrect.

To avoid this:

(1) Always select from V$LOG, V$LOGFILE and V$THREAD in a RAC instance. GV$ views are misleading.

OR

(2) add a predicate to match THREAD# with INST_ID. (Beware: thread numbers are by default the same as the instance_id; but you may have defined a different thread number while creating the database):

SELECT * FROM GV$LOG WHERE INST_ID = THREAD#

But I see no advantage in doing so. Your best bet is to use V$LOG.

You hit the nail on the head!

January 04, 2007 - 12:56 pm UTC

Reviewer: Prashant from Pittsburgh, PA

Arup,

Thank you very much for explaining this. This is the exactly what I wanted to know.
This is the kind of information that is hard to find in books or manual.

Again thanks a lot!

This should be a bug

March 26, 2009 - 11:26 am UTC

Reviewer: Theetha from GA, USA

I just observed this behavior. I feel it is a Bug.

For ex: I created a 3 node RAC database that will have
. 12 online redolog groups (4 redolog groups per thread)
. Per Group 2 Members. (So 24 Members)


I would expect the following to return 12 rows.
SELECT COUNT(*) FROM GV$LOG
But this returns 36 rows.

I would expect the following to return 24 rows.
SELECT COUNT(*) FROM GV$LOGFILE;
But this returns 72 rows.


Tom Kyte

Followup  

March 30, 2009 - 2:56 pm UTC

did you actually take a second to inspect the data and see if it made sense.


why do you expect 12 rows from the first query? Look at the data in the table - present us with that data - and explain which rows you think "do not belong"

Thank you to Arup Nanda; Excellent "Stab"

January 31, 2011 - 6:34 pm UTC

Reviewer: Laura Sallwasser from San Diego, CA USA

Thank you, Arup for taking the time to answer the real question posed by Prashant. This is exactly what I had been trying to understand. Your explanation was clear and concise. What's more, it answered this and a question I'd had about the difference between a thread number and an instance number.

Best regards,

Laura Sallwasser

March 02, 2011 - 11:27 am UTC

Reviewer: sumesh from India

Going through the comments, one question came to my mind. I see the sequence numbers are same for both nodes. My understanding was the sequence number(Logfile ID) will be continous number counting all redlo logs in all instances. for eg, RAC instance A has sequuence no as 1 for redlog1, then RAC instance B will have sequence no as 2 for its redolog1 not 1. Is that not correct?

Sumesh Question

March 03, 2012 - 12:19 am UTC

Reviewer: raghu from Ca, USA

I had been Thinking about the redos for some time and its been clearly stated and understood.

Can I get answer to Sumesh Question about sequence numbers
Tom Kyte

Followup  

March 03, 2012 - 8:35 am UTC

sequence numbers are sequential within a thread and every instance has its own thread.

What is the definition of thread?

January 16, 2014 - 10:26 pm UTC

Reviewer: A reader

At the beginning of this thread you said "Each instance has it's own personal set of redo and each redo thread is made up of at least two groups that have one or more members (files)." Is there any relationship between the "personal set" and the "thread"?

September 05, 2014 - 7:37 pm UTC

Reviewer: A reader

Thanks A lot !!

Log Group Stauts in RAC environment

May 01, 2018 - 11:49 am UTC

Reviewer: Basanta Nayak from India

I see, two log groups are in CURRENT state. I am not able to understand this. Can anyone explain on this?

SQL> SELECT GROUP#, ARCHIVED, STATUS, BYTES/1024/1024 as "Size (MB)" FROM V$LOG;

GROUP# ARC STATUS Size (MB)
---------- --- ---------------- ----------
1 YES ACTIVE 500
2 NO CURRENT 500
3 YES INACTIVE 500
4 YES ACTIVE 500
5 NO CURRENT 500
6 YES INACTIVE 500
7 YES INACTIVE 500
8 YES ACTIVE 500

8 rows selected.

Connor McDonald

Followup  

May 02, 2018 - 1:55 am UTC

Add the THREAD# column - and read the earlier entries in this (no pun intended) thread :-)

'n' instances = 'n' logical sets of redo = 'n' redo logs current