Monday, March 6, 2017

Concept of Redo Thread

  • The redo log groups of an instance are collectively called a thread, or more appropriately, a redo log thread.

  • Each redo thread is made up of at least two groups that have one or more members.

  • In RAC , each instance has it's own redo thread

  • In a stand-alone instance, there is only one thread. In a RAC system, typically there are as many threads as instances.

  • Two instances will never write to the same redo files - each instance has it's own set of redo logs to write.

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

  • The threads may have different numbers of redo groups.

  • Every redo group has a group number, which is a unique number in the database.

  • All the redo log files supporting the redo groups reside on shared storage so that every instance in the cluster can access all the redo groups during the recovery process.

  • 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-Jan-17
 1 2 1 15 52428800 1 YES        INACTIVE        940808    31-Jan-17 
 1 4 2 13 52428800 1 YES        INACTIVE        948841    31-Jan-17
 1 5 2 14 52428800 1 NO         CURRENT         951206    31-Jan-17
 2 1 1 17 52428800 1 NO         CURRENT         951208    31-Jan-17
 2 2 1 15 52428800 1 YES        INACTIVE        940808    31-Jan-17
 2 4 2 13 52428800 1 YES        INACTIVE        948841    31-Jan-17
 2 5 2 14 52428800 1 NO         CURRENT         951206    31-Jan-17

Key Points:- 

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

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

3.When you select form GV$LOG, remember, the session gets the information from other instances as well. Unfortunately, other 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! 

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


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. 

No comments:

Post a Comment