All the Views expressed here are my own and do not reflect opinions or views of the anyone else.All the views are tested on my testing environment and kindly test the post before applying anything on production.You can reach to me at email@example.com .
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