A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

What is redo log thread?

2 min read

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

Two instances will never write to the same redo files – each instance has it’s own set of redo logs to write to . Another instance may well READ some other instances redo logs – after that other instance fails for example – to perform recovery. Here is a scenario which helps us to understand the thread concepts .

Most V$ views work by selecting information from the corresponding GV$ view with a predicate "where instance_id = <that instance>". So V$SESSION in single Instance(i.e, 1) is actually 

SQL>select  *  from  gv$instance where inst_id= 1 ;

On a three node RAC database, if we select from v$session, we get sessions from that instance only. Selecting from GV$SESSION creates parallel query slaves on the other instances and gets the information back to our 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 we 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, we will get 3X9 = 27 records in GV$LOG! 
To avoid this: 

1.) Always select from V$LOG, V$LOGFILE and V$THREAD in a RAC instance. GV$ views are misleading. 
2.)  Add a predicate to match  THREAD#  with  INST_ID. (Beware: thread numbers are by default the same as the instance_id; 
but we may have defined a different thread number while creating the database) as 
SQL> select * from gv$log where inst_log=thread# ;