Wednesday, April 11, 2012

What is redo log thread in oracle ?

On googling about the redo log thread, i have not found proper documentation  that clearly explains clearly what the redo log thread is . Here i am trying to cover the redo log threads in case of single instance and  RAC  taking reference from ASKTOM site .

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  or 
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# ; 

Ref :

Enjoy    :-) 

No comments: