Skip to Main Content
  • Questions

  • Redo log threads in Real Application Clusters

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Prashant.

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

Last updated: January 18, 2022 - 2:44 am UTC

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 Tom 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.

Rating

  (13 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

but...

Prashant, January 02, 2007 - 11:02 am UTC

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

Arup Nanda, January 03, 2007 - 5:01 pm UTC

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!

Prashant, January 04, 2007 - 12:56 pm UTC

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

Theetha, March 26, 2009 - 11:26 am UTC

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
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"

Laura Sallwasser, January 31, 2011 - 6:34 pm UTC

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

sumesh, March 02, 2011 - 11:27 am UTC

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

raghu, March 03, 2012 - 12:19 am UTC

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
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?

A reader, January 16, 2014 - 10:26 pm UTC

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"?

A reader, September 05, 2014 - 7:37 pm UTC

Thanks A lot !!

Log Group Stauts in RAC environment

Basanta Nayak, May 01, 2018 - 11:49 am UTC

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
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

online and standby redo

oj, January 12, 2022 - 5:21 pm UTC

Related to the above - "In your case, you have 2 instances and there are 2 groups in each instance, so you have 4 groups in all"

For ONLINE REDO - to alleviate "checkpoint incomplete" message seen in alert.log, sometimes we try adding a logfile group, so with the above in mind, will I be adding a logfile group to each instance, so I end up with the scenario of "2 instances and 3 groups in each instance" for ONLINE REDO ?

Similarly, for STANDBY REDO, I understand it's good practice to have the same size but 1 more group than ONLINE REDO - so after adding to ONLINE REDO as I described, if I apply the same to STANDBY REDO, I should end up with the scenario of "2 instances and 4 groups in each instance" for STANDBY REDO ?

Connor McDonald
January 13, 2022 - 1:12 am UTC

The old adage of "checkpoint incomplete = add logfiles" is perhaps a little outdated.

I'd suggest reading Tanel's excellent post on this

https://tanelpoder.com/posts/log-file-switch-checkpoint-incomplete-and-lgwr-waiting-for-checkpoint-progress/

and following the steps in it to determine what course of action is needed. It might not be adding logs.

online and standby redo

oj, January 13, 2022 - 4:38 pm UTC

Thanks Connor.

I'll read up on Tanel's post.

However, assuming I've added logfile group as described, do my numbers look right for ONLINE and STANDBY ?
Connor McDonald
January 14, 2022 - 6:04 am UTC

yes

online and standby redo

oj, January 14, 2022 - 11:53 am UTC

Thanks again Connor. And also for Tanel's link, most useful.

Although looking back at the old adage of "checkpoint incomplete = add logfiles", the "ancient" year 2000 link I often go back to, does also mention DBWR as other possible cause;

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:69012348056

So great that you provided the "newer" link where we can get a better understanding
Connor McDonald
January 18, 2022 - 2:44 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Multitenant

Need more information on Multitenant? Check out the Multitenant docs for the Oracle Database